Programatic data extraction from SIMS.net Data Base

[StArbiter]

@Klawd - we signed the NDA and didn't see any particular problem with it - I am worried now as to what you may have seen that we missed - it looked like a bog standard agreement ?

 

Currently we are using both the SDK and IMS to routinely extract the data we need. Neither has any licence cost and support comes from the LA team. We may use the business objects to write data back but just waiting for the new SIMS web parts before going down that route.

[localzuk]

We provide the SDK which many people are happy with

 

Phil, you do not provide an SDK as such - you provide a couple of command line exporting tools. An SDK is a set of tools and API calls for interacting at a relatively low level in the system - not calling a commandline application...

 

Also, please don't say that many people are happy with something as there are quite obviously a good number who aren't happy with this, and there are even those who choose to invalidate their warranty by direct SQL interaction.

 

Why is interfacing with the SQL data a warranty voiding activity? Surely you could take a more flexible and sensible stance on this - ie. interacting with the database is acceptable if it is exporting, but if it is importing then it is not supported and any resulting mess is chargeable? The current one size fits all denying access is not working. The tools provided are not suitable for the task and it causes a good amount of hassle for an industry that is well known for having limited resources (we don't have the time or money to pay for extras or third party people to create things for us).

 

For example, I have been asked to export various data from SIMS for integrating with a locker management package. I would normally write this in c# - a .net language. And as such it should be *very* easy for me to interface with SIMS.net and export that data. Instead it is an absolute mess of commandlines etc...

[dhicks]

[Webman wrote:]

> The thing about this so-called SDK is that data is not available real-time.

> Reports have to be created within SIMS in advance of when you need

> the data.

 

I don't see this as being a particular problem - just create a bunch of reports that extract various chunks of data from SIMS as XML. Okay, so the API calls become things like getAllStudents() that return a large recordset that you then have to sort through rather than getStudentByName(x). It's just like programming with most database-backed systems these days, but instead of writting a database query in SQL you set it up in SIMS. I understand you can import/export reports (sorry, don't have SIMS at hand to check at the moment), so we

could include the reports along with the API.

 

[Webman wrote:]

> I'm guessing all our data requirements differ so much it will be awkward developing a one-size-fits-all approach ourselves.

 

We can't all be that different - we all do pretty much the same job, there must be a fair bit of commonality. Anyone who wants particular functionality can simply add a report and some associated code to the API bundle.

 

[localzuk wrote:]

> Why is interfacing with the SQL data a warranty voiding activity?

 

I can sympathise with Capita's point of view here - they don't want to have to deal with every script kiddy who Learnt SQL In 24 Hours and decided to start making modifications to their SIMS database. Bearing in mind SIMS' age, and the fact that it's moved platforms / databases several times (and the fact that, if I remember correctly, it used to marketed as being made "by teachers, for teachers"...), I'm sure the database is a tad on the hairy side and takes a bit of getting used to.

 

--

David Hicks

[webman]

David, I agree on some of your points (and also entirely with localzuk...)

 

What about daily report exports into CSV/XML with the data then being inserted or updated into an existing MySQL/MSSQL database for our own use? Essentially we end up with our own stale, day-old SIMS database via a mish-mash of data exports.

 

If we hack away on something ourselves that we have to settle for, where is the incnetive for Capita to give us a proper SDK or API? Or should that be in the Blue Skies forum?

[dhicks]

> David, I agree on some of your points (and also entirely with localzuk...)

 

I think we're all agreed that what we're discussing is a somewhat second-best solution, and that a proper solution is a proper API. Note that "proper" doesn't involve NDAs. I don't have a problem with my workplace having to meet random legal requirements for me to get work done, but I'm not surrendering any personal legal rights without a fee.

 

You'd think if Capita had a nice, freely-available, API they could then market SIMS as central all-in-one solution for a school's data storage needs. They could boast that you could interface it with most anything - authentication servers, locker managements systems, etc, etc. The best part is that they probably wouldn't even need to actually do much to implement this - simply publish an API and provide a website for people to post code on.

 

> What about daily report exports into CSV/XML with the data then being

> inserted or updated into an existing MySQL/MSSQL database

 

How is running a report from CommandReporter and getting a bunch of XML data back any different than running an SQL query? Surley SIMS reports don't take that long to run?

 

> If we hack away on something ourselves that we have to settle for,

> where is the incnetive for Capita to give us a proper SDK or API?

 

We could replace "settle for" with "exactly meets our needs" - we can write an API that works however we like, as opposed to whatever Capita comes up with.

 

--

David Hicks

[webman]

Exactly, agreed

 

How is running a report from CommandReporter and getting a bunch of XML data back any different than running an SQL query? Surley SIMS reports don't take that long to run?

 

Because I would imagine SQL is native to our apps and it can work on live data. Depending on how we code our apps, we may or may not need to transform XML/CSV into another format. We also have to consider how out of date the exported data is (this will vary depending on the application).

 

How is running a report from CommandReporter and getting a bunch of XML data back any different than running an SQL query?

 

I don't believe CommandReporter is designed to run on every request made by our hypotehtical application, whereas we can utilise SQL as often as we like without the performance drawbacks of file access and XML parsing.

 

I know we both want the same outcome; but think we have different ideas on which approach would be best to take

[klawd]

@StArbiter

 

@Klawd - we signed the NDA and didn't see any particular problem with it - I am worried now as to what you may have seen that we missed - it looked like a bog standard agreement ?

 

Currently we are using both the SDK and IMS to routinely extract the data we need. Neither has any licence cost and support comes from the LA team. We may use the business objects to write data back but just waiting for the new SIMS web parts before going down that route.

 

If its the same NDA we were sent, then you have potentially already violated Paragraph 7. The NDA is full of strange clauses. I can't give examples for obvious reasons, and since you are already bound by it, there is little point....

 

I certainly would not sign it, neither was the the school management prepared in its current form.

[Tamarside]

I wish to have our SIMS data match up with our LDAP data. This involves extracting some data from SIMS. If I understand correctly, I can use the CommandReporter tool for free to extract XML data from SIMS. CommandReporter seemed to be already installed on my SIMS server, there was nothing to download or install. Other options exist (using SQL, some defined API) but involve license violations/NDAs/paying.

 

This XML tool works, but isn't an API. There's a bunch of us want the same thing, so let's just get on and write one - a simple API front end to the CommandReporter tool. I'd suggest something Windows-based that installs on the SIMS server and provides a bunch of REST function calls available over HTTP, then we can write a bunch of libraries for everyone's favourite languages to access that API.

 

Does every SIMS server have a web server (i.e. IIS) on it already? Would, therefore, ASP be the most appripriate thing to code the server-side part in? Or would it be better to provide a seperate minimal web server?

 

--

David Hicks

 

But it'd be SO much easier just extracting the data directly from the SQL DB. What's more, as you're not writing back there is no way Capita can see you've extracted data.

 

No offense, Capita, but like others I feel the need to point out that you guys may have written the app, but you don't own the data. When schools want to use the data in different ways, they should be allowed to do so without being forced down the line of scheduled exports of non-live data! What you as company suggests as feasible is a joke!

The time to start paying attention to your customers is NOW, because there ARE other MIS system around.

 

There was a time when very clever companies could dazzle schools with their all-singing, all-dancing software products. But the world has moved on since the days on Windows 3.1!

 

Almost without fail all MIS systems have now become so jaded and out of date with the needs of schools that they are a joke. They still insist on operating siloed systems that don't speak to anything else, AD integration is a rude phrase to them and identity management seems to be treated like a family scandal - best ignored and kept quiet about.

 

Capita, Serco, wake up and wake up NOW. Your products are stale, your customers are complaining, your restrictions are extreme, and your "partner status" programmes come across as little more than outright profiteering.

Edited February 5, 2009 by Tamarside
Typo

[penfold_99]

We have received different SDK's from the command line report tool, to the really unhelpful documentation on how to interface to the sims.net dll to get information.

 

Using either of the above still requires middleware to you wanted to integrate sims and an application that runs on linux.

 

By far the easiest why to interface with sims.net is by connecting to the SQL server and using custom views.

 

@Phil Neal,

 

With the caveat of if the data get corrupted we pay to fix it.

Would it be possible to have documentation about the SQL Tables and how they relate to each other, this would limit the possible issues of writing back incorrect data?

[dhicks]

But it'd be SO much easier just extracting the data directly from the SQL DB. What's more, as you're not writing back there is no way Capita can see you've extracted data.

 

Yes, but I figure that, at some point, an end-user might have to contact Capita support, and it'd be best to avoid anything that even remotely looks like it accesses any databases - you know it doesn't write back data, I know it doesn't write back data, but some random support chap might not.

 

Also, like, why bother? If Capita don't want me writing to their database then I won't, I'll write data somewhere else. Eventually all our data will be stored elsewhere and we can get rid of SIMS. Problem solved.

 

--

David Hicks

[Tonyoung]

 

No offense, Capita, but like others I feel the need to point out that you guys may have written the app, but you don't own the data.

 

This is largely true except that the MIS suppliers own the responsibility of supporting the database and the structures. It may be possible for a school to opt out of the local authority support. If they do I guess they can the do what they want with their data and database - not sure my head would go along with this.

 

The reason the suppliers use interfaces is that applications are protected from database structural changes and they do not have to document the structure with every release. It may be worse for Capita because of their market share they would need to give 12 months warning of changes to any company that links to their database

 

@ Phil - can you clarify this ?

[localzuk]

You really shouldn't work with a third party database without using an API provided by the third party. This is what would solve all these issues - a supported API for data access. It would remove the possibility of the database being messed around with, and would provide that access that people want.

[webman]

You really shouldn't work with a third party database without using an API provided by the third party. This is what would solve all these issues - a supported API for data access. It would remove the possibility of the database being messed around with, and would provide that access that people want.

 

I believe they do have one - but its cost is (conveniently) prohibitively expensive for schools.

[Tonyoung]

I believe they do have one - but its cost is (conveniently) prohibitively expensive for schools.

I thought Capita supplied the API free of charge to any school provided the SIMS license holder approved. So for must of us we need to get LA approval. There is no cost unless we write an application and then resell it commercially.

[localzuk]

I thought Capita supplied the API free of charge to any school provided the SIMS license holder approved. So for must of us we need to get LA approval. There is no cost unless we write an application and then resell it commercially.

 

I was told that the commandreporter tool was the API...

[Tonyoung]

I was told that the commandreporter tool was the API...

You may be correct and this is where I get confused. The command reporter uses a bunch of APIs to extract data from the SIMS reporting engine. However a school can get access to the "raw" API documentation through their LA. Most LAs only agree to allow the schools to read data but guess some may allow writeback.

We use the IMS utility to populate our application. It's stopped working with the SIMS learning platform some 6 months ago but works with our own module. What it doesn't do though is allow data to go back.

@phil - what's the score with IMS ?

[PhilNeal]

Schools can access the SIMS business objects layer for their own purpose FOC; these can read and write to the SIMS DB. We also provide the command line reporter to schools FOC as an easy way outputting a wide range of data to be used elsewhere. If the LA is the licence holder you will need their permission to make use of these facilities.

 

We also provide an IMS link FOC which will automatically maintain an external system with pupil names, groups and staff names. Most VLEs are either using or planning to use this link. We are in discussions about developing an IMS write-back of assessment data. SIF may or may not overtake this if Becta can reassure us that standard SIF doesn’t expose schools to data protection risks.

 

If you use SIMS business objects to write data back to SIMS we will deal with any data issues i.e. fix them in the normal way. We get about 5 cases a month of schools that have written to the SIMS DB directly and caused corruption. Typically a school in this position doesn’t mention that this is what they have done and as a result we spend days trying to find out if SIMS could have caused the data to be in the state it is in. If you write back to the database directly we will charge for any data fixing that is required.

 

We employ 4 staff that permanently assist with third party support so we do charge for the commercial use of SIMS business objects.

[SimpleSi]

Just my 2 pennies worth.

 

In my past I've been through many differernt scenarios of getting at data held on a primary system to provide input into a secondary system.

 

To make sure that no harm is done to the primary system it is very much better to provide the maximum possible amount of buffering between the 2 interfaces and to make sure the workload of the data extraction is as minimal as possible or takes place at the least sensitive time for the primary system.

 

If YOU were the provider of the primary system you would get upset/charge a lot of money if the secondary system affected yours

 

And one little mistake on your part could cause you or your school a lot of grief/expense and it will be YOUR fault

 

 

regards

 

Simon

(Not normally known for defending corporate interests )

[Tamarside]

This is largely true except that the MIS suppliers own the responsibility of supporting the database and the structures. It may be possible for a school to opt out of the local authority support. If they do I guess they can the do what they want with their data and database - not sure my head would go along with this.

 

The reason the suppliers use interfaces is that applications are protected from database structural changes and they do not have to document the structure with every release. It may be worse for Capita because of their market share they would need to give 12 months warning of changes to any company that links to their database

 

@ Phil - can you clarify this ?

 

Actually I beg to differ. READ ONLY access cannot in any way, shape or form damage the DB. And the vendors don't simply own the responsibility for support, they actually get paid for it.

 

Nobody is expecting vendors to support every app that links to their DB, but if they started a) delivering more of what their customers actually need and b) being more transparent towards their customers this whole issue can go away.

 

For example, if Capita released a proper API to dynamically read data from their DB and also provide AD integration (or some other identity management solution, e.g. Shiboleth, or others) then the DB would remain totally protected, while schools would enjoy a great deal more in terms of freedom and functionality.

 

Capita aren't the only ones behaving in this manner - Serco does exactly the same. Having said that, I also have to point out that Serco's CMIS DB is FAR cleaner and easier to work with than the SIMS one.

[Tonyoung]

We are in discussions about developing an IMS write-back of assessment data. SIF may or may not overtake this if Becta can reassure us that standard SIF doesn’t expose schools to data protection risks.

.

Phil - Who pays for the SIF infrastructure for writing back assessment data ? If the vLE supplier are implementing IMS as you say then they aren't going to be pleased to ditch their development to date for SIF. You seem to be hiding behind a vague promise of SIF delivery sometime in the future.

 

As Serco have a fully integrated learning platform with their MIS what do Capita offer ? Learnwise - see supportnet or UniServity - see VLE forums and neith erof which will ever write data back.

 

It's beyond me as to why we can't write back data to AM today, this week or this month.

[penfold_99]

Schools can access the SIMS business objects layer for their own purpose FOC; these can read and write to the SIMS DB. We also provide the command line reporter to schools FOC as an easy way outputting a wide range of data to be used elsewhere. If the LA is the licence holder you will need their permission to make use of these facilities.

 

We also provide an IMS link FOC which will automatically maintain an external system with pupil names, groups and staff names. Most VLEs are either using or planning to use this link. We are in discussions about developing an IMS write-back of assessment data. SIF may or may not overtake this if Becta can reassure us that standard SIF doesn’t expose schools to data protection risks.

 

If you use SIMS business objects to write data back to SIMS we will deal with any data issues i.e. fix them in the normal way. We get about 5 cases a month of schools that have written to the SIMS DB directly and caused corruption. Typically a school in this position doesn’t mention that this is what they have done and as a result we spend days trying to find out if SIMS could have caused the data to be in the state it is in. If you write back to the database directly we will charge for any data fixing that is required.

 

We employ 4 staff that permanently assist with third party support so we do charge for the commercial use of SIMS business objects.

 

@Phil Neal,

 

Please could you define SIMS Business Objects as this is a very generic term?

Are these Business Objects accessible from a non Microsoft OS?

[SimpleSi]

Actually I beg to differ. READ ONLY access cannot in any way, shape or form damage the DB.

 

I've ground systems to a halt by just reading from a database in an aggressive manner.

 

regards

 

Simon

[penfold_99]

I've ground systems to a halt by just reading from a database in an aggressive manner.

 

regards

 

Simon

 

You shouldn't be bale to with a well constructed query and a normalised database.

 

But as sims doesn't have the latter the former is a bit hard.

[Tonyoung]

You shouldn't be bale to with a well constructed query and a normalised database.

 

But as sims doesn't have the latter the former is a bit hard.

@Penfold are you confirming that direct reads from SIMS could have a hugely adverse affect on the performance of the database ? Could explain a lot

[Tamarside]

I've ground systems to a halt by just reading from a database in an aggressive manner.

 

regards

 

Simon

 

Actually Simon I think you'll find you took a massive performance hit, but the DB wasn't damaged, so my point stands that read-only access to a DB will not destroy it.

 

SQL optimisation is a different topic altogether.

 

Obviously you'd need to evaluate any queries run against the DB by getting a baseline of the server's performance before, then again while the query is running, so you can evaluate how much of a performance hit you'd be taking.

That should form part of your normal tasks anyhow and should slot in under your change management processes.