SIMS <--> SQL DB relationship schema

[Gdn]

Hey.

 

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,

 

-Gdn

[localzuk]

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...

[matt40k]

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.

[Gdn]

@localzuk

 

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.

[Gdn]

@matt40k

 

Technicalities, my friend. And that's for the legal gremlins to worry about - not me

[jamesb]

When you ring up your bank, you only ever get information correct as of the previous close-of-day.

 

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.

[rpwillis]

Hi Gdn,

 

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.

 

Regards,

Richard

SalamanderSoft

http://blog.salamandersoft.co.uk

[penfold_99]

@Gdn,

 

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.

[Gdn]

@jamesb

 

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?

[matt40k]

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.

[Gdn]

@rpwillis

 

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.

 

 

----------

 

 

@penfold_99

 

Cheers dude. Someone dropped your name to me a week-or-so back saying that you might be the guy to speak to. And I knew there would be some way around all this Capita support/contract/legal rights nay-saying crap. My LEA have told me as much, and no one, not even the people at Capita, have been able to give me a difinitive answer on the subject, including pertinent quotes from the support contract or Terms of Use. It all seems very hazy at best.

 

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?

[matt40k]

SIMS partners introduction - Capita Children's Services

 

Am I missing something?

[Gdn]

@matt40k

 

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.

[penfold_99]

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.

 

@matt40uk

 

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.

 

@rpwillis

 

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?

[localzuk]

@matt40uk

 

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.

 

@rpwillis

 

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?

 

I don't think you've been given the entire documentation package then?

 

And I don't think rpwillis will be allowed to, as the NDA doesn't allow discussion of such things...

[penfold_99]

I don't think you've been given the entire documentation package then?

 

Yeah, we have got this direct from capita.

 

And I don't think rpwillis will be allowed to, as the NDA doesn't allow discussion of such things...

 

Capita's documentation is covered by the NDA not the program that is created, if it was then the company would not be able to release it.

 

even a pointer in the right direction would be helpful using an example on a website which implements similar system..

[rpwillis]

@gdn

 

Sorry, I misunderstood you then. That's for the comments on what I've been working on with Twynham. I'll have to get round to blogging about it soon.

 

@penfold_99

 

localzuk is correct in that our NDA prohibits that sort of discussion. The document "Technical Guide - Interfacing With SIMS.net.doc" which comes with the API indexes gives an example though. I can download them through Support Net e.g. Resource: 14928 - 13th Jan 2009, but I don't know if they are available to schools.

 

Richard

[penfold_99]

@gdn

localzuk is correct in that our NDA prohibits that sort of discussion. The document "Technical Guide - Interfacing With SIMS.net.doc" which comes with the API indexes gives an example though. I can download them through Support Net e.g. Resource: 14928 - 13th Jan 2009, but I don't know if they are available to schools.

 

Richard

 

Thanks Richard,

 

In capita support what menu route are you taking to get the documentation?

[rpwillis]

Hi Penfold,

 

It's just on my home page in SupportNet under files. It will be the default set up of SupportNet for partners.

 

Richard

[penfold_99]

Hi Penfold,

 

It's just on my home page in SupportNet under files. It will be the default set up of SupportNet for partners.

 

Richard

 

Hi Richard,

 

Thanks for that, unfortunately i don't have access to it but have put in a request with our lea for it.

[budgester]

@Gdn,

 

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.

 

Capita prefer to people to use there business objects but this is not possible if your not on a windows platform.

 

We do a similar thing with Serco CMIS, and directly read from the database, using various technologies, e.g. C#, PHP, ODBC, to windows and apache on linux.

 

[RANT]

But having worked on the database what really strikes me is the complete mess that they call the database design.

 

Lets face it, normalisation, indexes and foreign keys are pretty much database 101.

 

As far as I can see no one at either of these companies has a clue about database design.

 

And the other thing that annoys me is the way that they want to restrict my access to my data.

 

[/RANT]

[Martin_W]

Hey Gdn,

 

I saw your post from about 1,5 year ago. I was wondering if you had made progress with the SQL queries you needed. I am trying to extract data directly from the SIMS database as well. If you have any tips/help, that would be much appreciated!

 

If you have any problems with your data, maybe I can also help you out. I have a lot of experience in extracting data from school information systems other than SIMS (directly from the source databases). I can use the tools of the company I work for to help making the data extraction easier.

 

Please let me know, if you can help.

 

Martin

[matt40k]

@Martin

What you trying to extract?

[Martin_W]

I want to extract data about grades and absences of pupils from directly from the database for management information purposes.

 

@jinnantonnix

Are there already accessible views in the SIMS SQL database that are easy to use, is that what you mean?

[Martin_W]

@jinnantonnix

Thanks for you eleborate explanation!

 

I've read about the Business Objects universes that 3rd parties can purchase. I've worked with Business Objects before, but I was wondering:

Are they actually that useful in this case. I mean if I can join together the views that I need, is the BO universe really of that much extra value?