MIS Systems Thread, SIMS <--> SQL DB relationship schema in Technical; Hey.
I know this topic has been covered ad nauseum, but I'm building a web gateway that extracts information from ...
23rd March 2009, 11:23 AM #1
SIMS <--> SQL DB relationship schema
I know this topic has been covered ad nauseum, but I'm building a web gateway that extracts information from SIMS's SQL Server database; but I don't want to go through SIMS to get it. That means no SIMS SDK or anything of that kind. I don't see the need for those extra levels of abstraction. I just want to connect directly to the database, run some queries, then do whatever I want with the information.
I've already had some success, and the project is going pretty well. Our LEA has given us its full support.
Now, we only want to pull data from the database - there will be no writing back to it. Capita are aware of our intentions, and their response was:
"We donít support the schema as that implies direct connection to the database. We would rather the school use IMS or the Command reporter."
So, they would rather we do things their way - but why wouldn't they? From what my LEA has told me, much of the talk on here about Capita rescinding their SIMS support if we circumvent their software is just so much scaremongering.
Anyway, regardless of the Capita issues, we've been given the go-ahead by the LEA to look into this.
It's going to happen.
So, I know a lot of folks have had some success in this area already (penfold_99, I believe, with Moodle integration; and the chaps at SalamanderSoft who did a lot of work for Twyneham, to name a couple); and I was wondering if anyone has managed to do any extensive mapping of the SIMS/SQL relationships, perhaps using the SQL Server Profiler or something similar to log the requests that SIMS is sending to the database.
We're really looking for people to collaborate with on this; and we would be massively appreciative to anyone who has already made substantial headway into mapping the relationships who might be willing to help. I know you're out there :)
Thanks in advance,
IDG Tech News
23rd March 2009, 11:30 AM #2
I know you probably won't see this as being too helpful, but can I just point out that one of the main reasons the schema isn't supported is that it is subject to change, every time an update is released...
23rd March 2009, 11:35 AM #3
Anyone who access the db direct will invalid there SIMS Support... it's like getting your pay paid annually then burning it to heat your house. Ok, it works, but till not the best way of doing it.
Nothing wrong with the command report tool.
23rd March 2009, 11:43 AM #4
Yeah, we're aware of that. But I just do waht I'm told :)
My workaround idea for that is to generate my SQL queries on the fly, using the data extracted from the SQL Profiler logs.
The information we want to pull isn't that extensive, so if I could use SIMS to run a specific function every day, then use SQL Profiler to log the queries SIMS passes to SQL Server, I can run a script to extract the correct schema data from the logs and build my own SQL queries at runtime.
It's still early-doors yet, and the whole project might fall on its arse; but part of my job is to try and come up with these kinds of workarounds and try to see how feasible they are.
So, although the schema might change during SIMS updates, at most (touch wood) the downtime of our own application would be, what - A day or two - while we do the remapping. For the kind of information we want to pull from the database, that sort of downtime isn't really a problem. Just an inconvenience for the users, really. None of the stuff we want is *needs* to be accurate at the time of the request. When you ring up your bank, you only ever get information correct as of the previous close-of-day.
Thanks though - because you might be right. until I look at it in much more detail, this might actually turn out to be a pretty big issue. I'll just have to wait and see though.
23rd March 2009, 11:46 AM #5
Technicalities, my friend. And that's for the legal gremlins to worry about - not me :)
23rd March 2009, 11:50 AM #6
Get a better bank. I get up-to-date information from mine whenever I call, including any pending transactions and so on. I expect to get that information as well, and would be more than a little angry if I transferred money I didn't have, or paid for a bill I couldn't afford, because their information isn't up to date.
Originally Posted by Gdn
23rd March 2009, 11:54 AM #7
It's Richard from SalamanderSoft. Thanks for thinking of us, but I'm afraid that I can't help you here. All our access to Sims is through the business objects and API. In fact I think we'd be in breach of our partnership contract otherwise.
23rd March 2009, 11:56 AM #8
i am probably a useful guy to know, i have done exactly what you are doing, i have built modules for Moodle that pull information from SIMS.net via sql views.
The key is to get your head round the relationships in the db, it's hard as it not a normalised database.
if you let me know what info your trying to get out, i my have already created the views.
As for support, we are supported by our lea and capita, we have an agreement in place that if we break it, we pay to fix it.
Capita prefer to people to use there business objects but this is not possible if your not on a windows platform.
23rd March 2009, 11:57 AM #9
Right, so I gave a bad analogy. Thanks for pointing that out.
Look, I know there might be problems. I expect there will be problems. But it's my job to explore what those problems might be and to present them to the Powers. People have done this already, with varying degrees of success. I'm just exploring options. Can we maybe get a little more positivity here?
23rd March 2009, 12:07 PM #10
I really don't think a school would go for a product that would breach the Capita license \ support and cost you thounsands of pounds. Capita provide a API - I believe in C# for anyone who needs anything above the standard reports, which to be perfectly honest, gives you pretty everything you want. If you contact your LEA, they can even rename the school name, person name who wrote it with whatever you want.
23rd March 2009, 12:10 PM #11
Hey Richard. I was actually aware that you'd done what you have by using their API. But I was thinking of getting in touch about it anyway, as it's another avenue I'll have to explore in order to come up with a decent report. And what you've done for Twyneham looks awesome.
the If We Break It We Fix It approach seems perfectly reasonable to me.
Can I get in touch later today to pass you some stuff in greater detail?
23rd March 2009, 12:15 PM #12
23rd March 2009, 12:41 PM #13
yeah, you missed the bit where I get paid to give my bosses an in-depth analysis of the alternatives, complete with working examples purely because they want to look at that information - because that's what they're paid for - and not just swallow the Capita-Is-The-Best story without having all the alternative options in-front of them - no matter how true the Capita-Is-The-Best story may or may not be.
23rd March 2009, 12:41 PM #14
Originally Posted by matt40k
The business objects are only as good as the documentation, i would use the business objects but i don't have any implementation/usage documentation. I have all the information about whatt dll do what but not how to get the information out of them.
This is a bit cheeky, but any change you could post a hello world program on how to pull information from sims through the business object(dlls) not the command report tool?
23rd March 2009, 12:45 PM #15
I don't think you've been given the entire documentation package then?
Originally Posted by penfold_99
And I don't think rpwillis will be allowed to, as the NDA doesn't allow discussion of such things...
By ahunter in forum Wireless Networks
Last Post: 11th November 2008, 06:34 PM
By enjay in forum MIS Systems
Last Post: 27th February 2008, 02:55 PM
By interele in forum MIS Systems
Last Post: 2nd February 2008, 06:43 PM
By andrewsmart in forum MIS Systems
Last Post: 14th June 2007, 09:13 PM
By Norphy in forum MIS Systems
Last Post: 5th April 2007, 11:20 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread