Programatic data extraction from SIMS.net Data Base

[PhilNeal]

There are a number of points raised above – hopefully this answers them:

 

• Inefficient code will cripple a database but read only access shouldn’t corrupt it.
   
  
• The business objects are functions written in C#; they are what our developers use to read and write to the database.
   
  
• We have offered to write a write back IMS link if someone would like to finance it; to date nobody has and I am hesitant to invest more in this until the SIF agenda is resolved. DCSF/Becta have said that they expect SIF to be the way forwards. For over 24 months we have been pointing out that standard SIF could cause schools to break data protection law. The ZIS manufacturers have begun to address some of the issues we have raised by introducing data filters that control the level of access to the student data object that applications have. Under SIF if you can see a student’s name you can also see their most private details and that isn’t correct.
  

[penfold_99]

There are a number of points raised above – hopefully this answers them:

 

• Inefficient code will cripple a database but read only access shouldn’t corrupt it.
   
  
• The business objects are functions written in C#; they are what our developers use to read and write to the database.
   
  
• We have offered to write a write back IMS link if someone would like to finance it; to date nobody has and I am hesitant to invest more in this until the SIF agenda is resolved. DCSF/Becta have said that they expect SIF to be the way forwards. For over 24 months we have been pointing out that standard SIF could cause schools to break data protection law. The ZIS manufacturers have begun to address some of the issues we have raised by introducing data filters that control the level of access to the student data object that applications have. Under SIF if you can see a student’s name you can also see their most private details and that isn’t correct.
  

 

@Phil Neal,

 

Thanks for the clarification on the points above, and the problems with SIF re data protection.

 

Will you be reliant on MS implementation of ZIS? or will be be compaible with Open ZIS - Open Source Zone Integration Server (ZIS) - Zone Integration Terminal (ZIT).

 

With regards to the IMS write back link, what is the rough cost?

 

Would it be possible to get documentation on the database tables? As i would like to write a module for moodle which transferred quiz results back to SIMS.net.

 

We currently have a test sims.net installation in place for our in-house development and updated testing.

 

If the documentation was forth coming it would remove the burden of development costs for you.

[localzuk]

@StArbiter

 

 

 

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.

 

If point 7 is one of those 'this NDA is covered by this NDA' clauses, it is an unenforceable clause. ie. you can't be covered by a clause unless you sign it...

[PhilNeal]

Penfold in answer to your queries:

• SIF is independent of MS technology
  
• IMS write-back I haven't got a firm price but £50K is not unlikely
  
• we aren't geared up to support direct table access. We support access via BOs because it means we are free to alter the underlying data structures without worrying about affecting third party access
  

[penfold_99]

Penfold in answer to your queries:

• SIF is independent of MS technology
  
• IMS write-back I haven't got a firm price but £50K is not unlikely
  
• we aren't geared up to support direct table access. We support access via BOs because it means we are free to alter the underlying data structures without worrying about affecting third party access
  

 

@Phil Neal,

 

Thanks for the reply.

 

I am aware SIF is a specification but will you be using MS technology to implement the specification?

 

With regards to the BO's is it possible to get some usage examples of how to to pass information to the dll's and information from them?

[PhilNeal]

Penfold, I don't really understand your MS question. To build an agent we will use our normal programming tools which are MS based - there are also kits available that we will use that are not. A third party won't interface with our agent but with whatever ZIS has been deployed.

 

If you lodge a query through your service desk you can get the complete documentation on our API.

[penfold_99]

Penfold, I don't really understand your MS question. To build an agent we will use our normal programming tools which are MS based - there are also kits available that we will use that are not. A third party won't interface with our agent but with whatever ZIS has been deployed.

 

If you lodge a query through your service desk you can get the complete documentation on our API.

 

@Phil Neal.

 

Thanks for the explanation it answers my question.

 

A better way for me to put across my point, why not develop for LDAP instead of Active Directory, as AD is derived from LDAP people with out Active Directory would still be able to use SIMS.net.

 

It's a bad analogy but hope you can follow it.

 

Does the request have to come from our LA or can it come direct from us?

 

Sorry for the million and one questions but we are trying to maximise the benefit of sims.net by integrating into every area of the school, and displaying data in different formats and not necessarily on a windows platform.

[PhilNeal]

The SIMS licence holder is the one that can request the documentation so I suspect that is your LA. Your support unit will also want to know that you are calling the BOs from your own software. Edited February 16, 2009 by PhilNeal

[ArchersIT]

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.

 

Hi there,

 

Could you help me in knowing how to get access to start using the Business Objects Layer (or even the command line reporter tool)? Reading the rest of the thread, I have contact capita support, who passed me on to professional services (which sounded like I was going to get charged). They say I have to speak to a particular person - who is unfortunately not available until next week. If I am understanding it correctly the tools are already present, it is just getting access to the documentation so that one knows what to call and how.

 

You description makes it sound as if getting access to these should be easy and straightforward, but either I am not using the right terminology or the people I have spoken to are not familiar with them.

 

I would really appreciate it if you were able to give me a hint on exactly what I should be asking for.

 

Many thanks

 

Jonathan

Edited February 16, 2009 by ArchersIT
clarity (hopefully!)

[PhilNeal]

Ask for the CommandLine Reporter documentation in the first place - I suspect you go through Chelmsford so I will alert them.

 

Basically any report that you can create in our Report Generator can be called through the Command Line Reporter from your own program. You shouldn't need Professional Services to get this going.

[ArchersIT]

Ask for the CommandLine Reporter documentation in the first place - I suspect you go through Chelmsford so I will alert them.

 

Basically any report that you can create in our Report Generator can be called through the Command Line Reporter from your own program. You shouldn't need Professional Services to get this going.

 

Ok - thanks for this (was off yesterday so did not try it yesterday).

 

In case you are interested, the first line person had never heard of it so asked a colleague. This resulted in a case being raised and escalated to second level who will give me a ring back. You were correct in that they were Chelmsford.

 

While the commandline reporter will probably do what I need to do right now, I would be interested in finding out more about the business objects layer for reading (and possibly writing) more directly. Could you clarify the correct way of getting hold of this documentation as well?

 

Many thanks for your help.

 

Cheers

 

Jonathan

[PhilNeal]

It's through the same root but you are likely to need some consultancy to get you started.

[ArchersIT]

I had good response on getting the third party documentation, but I have been less successful with the business object layer documentation.

 

The final response I have had (today!) is that I need to request this through the licence holder. This transalates to my needing to ring up our county support desk and try to convince someone there that even though we do not pay them for support, they need to ring Capita up on our behalf. I think that it is unlikely that this will be successful, but I will give it a go.

 

I really dont understand why this needs to go through the licence holder. If there is no licencing cost for schools (as was said earlier) then this just seems an unnecessary step. I could understand needing to go through the county support desk if we got our support from county, but we go direct to Capita for support.

 

Is this the correct process?

 

Cheers

 

Jonathan

[Tonyoung]

it may be a requirement of your end user agreement for SIMS or the LA contract with Capita - do you have a copy you can look at?

If you are writing back to the data base it seems sensible that the license holder is at least aware if there is a clause that says they should be notified.

[penfold_99]

I had good response on getting the third party documentation, but I have been less successful with the business object layer documentation.

 

The final response I have had (today!) is that I need to request this through the licence holder. This transalates to my needing to ring up our county support desk and try to convince someone there that even though we do not pay them for support, they need to ring Capita up on our behalf. I think that it is unlikely that this will be successful, but I will give it a go.

 

I really dont understand why this needs to go through the licence holder. If there is no licencing cost for schools (as was said earlier) then this just seems an unnecessary step. I could understand needing to go through the county support desk if we got our support from county, but we go direct to Capita for support.

 

Is this the correct process?

 

Cheers

 

Jonathan

 

I think this is make you county support desk aware that if there are data issues it could be caused by writing back to the database.

 

All your county team need to do is email AFAIK [email protected] about it, you then sign a NDA and you get the documentation.

 

I have been through this process, once you have the documentation good luck as there in no example on how to use the business objects or how they relate to each other. The buisness object are dll files.

 

it would be good if there was a hello world program showing you how to get student details.

 

i would be interesting in how you get on. I am looking at making a business object webservice or a interface i can use via ssh. A i would like to write back information from moodle which is on a linux server.

[ArchersIT]

Thanks for the comments - I will continue to post how I get on.

 

As to the informing/consulting the county support desk - this would be fine if we used them for any support. As we don't I am not sure of the value of them having any input or potentially a veto on whether or not we do anything with the business objects layer.

 

Cheers

 

Jonathan

[budgester]

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.

 

Please don't say this, I am moving from a CMIS school to a SIMS school in a month, and I was hoping the SIMS database might have been a bit better written than the CMIS one, in fact I didn't think you could get much worse.

[MattMitchell]

OK, so my rough understanding of the situation is:

 

- Directly accessing the SQL database used to store SIMS data violates the licence

- Reading is also not allowed - partly because of the difficulty (and prohibitive cost) of working out who's at fault in case of a problem, and partly because the underlying data structure may change

 

- People who want a simple data export/custom report feature can use a commandline tool to output reports (presumably aimed chiefly at regular scheduled tasks)

 

- There is a .NET-based API that allows access to the data, called Business Objects (no relation to SAP)

- Read-access using BO is free for licenced users, comes with a 5k price tag for developer firms

- Write access has an associated cost because of support issues

 

So there IS an api to access the data. Surely that's the important thing people are after?

 

Although writing data back in (to store VLE results, etc) sounds like a good idea to many, I don't think that it does: just as Capita don't want users to corrupt the SIMS database, surely users with custom data stored don't want SIMS to edit/change/drop tables during an upgrade. I'm working on the assumption that customisers aren't wanting to add extra fields to existing tables to store the new data...

 

Personally, I could live with storing new data in a separate database - after all, there is an API to access the SIMS data and records all have a unique identifier, so why not use that in a separate database for stuff like the VLE data? I can see the potential benefits of storing online assessment results directly into SIMS, but I don't see it as an essential way of implementing a link either.

 

Or have I got it all wrong? I'd be very curious to know as this impacts on what I'm wanting to do by the looks of things.

[MattMitchell]

One thing tha does spring to mind though:

 

I'm presuming that analysing data in a SIMS database data dump, outside of the SIMS database, is acceptable? Obviously this would have to be done by restoring a backup into a different database.

 

Trying to think outside the box a little here...

[penfold_99]

- Directly accessing the SQL database used to store SIMS data violates the licence

- Reading is also not allowed - partly because of the difficulty (and prohibitive cost) of working out who's at fault in case of a problem, and partly because the underlying data structure may change

 

Directly accessing the sql doesn't invalidate your licence but you need to be prepared to pay for capita to fix it it you break it, if you are the best thing to do is notify your LA and Capita you are.

 

We have been reading data out of the sims database for over a year and not had any issues.

 

As long as your not writing data back for sql and the data structure changes all that will happen is your queries will stop working.

 

- People who want a simple data export/custom report feature can use a commandline tool to output reports (presumably aimed chiefly at regular scheduled tasks)

 

Yes you can, but you are limited but the reports you can generate in sims.net, you not able to do complex joins. The command reporter tool only runs on windows, so unless you do what dhicks has done a create a web service to read csv files created by sims you can't have your vle on linux without using sql to the sims database.

 

- There is a .NET-based API that allows access to the data, called Business Objects (no relation to SAP)

- Read-access using BO is free for licenced users, comes with a 5k price tag for developer firms

- Write access has an associated cost because of support issues

 

Yes these is an api, good luck trying to get some usable documentation out of capita. It's very limited on examples of how to get data.

 

The 5k for developers is to have a copy of sims. There is also a per school cost of £140 but this anti competitive against open source software. As for Write access there are mixed messages coming out of capita as the Business Objects documentation i have says they don't support it, but capita partners are able to offer it.

 

 

So there IS an api to access the data. Surely that's the important thing people are after?

 

Only if you where able to use it, without paying capita thousands in consultancy to help you.

 

Although writing data back in (to store VLE results, etc) sounds like a good idea to many, I don't think that it does: just as Capita don't want users to corrupt the SIMS database, surely users with custom data stored don't want SIMS to edit/change/drop tables during an upgrade. I'm working on the assumption that customisers aren't wanting to add extra fields to existing tables to store the new data...

 

The data can written back to assessment marksheets as these would be unaffected but capita's upgrades.

 

If you need extra fields just create extra aspects.

 

Personally, I could live with storing new data in a separate database - after all, there is an API to access the SIMS data and records all have a unique identifier, so why not use that in a separate database for stuff like the VLE data? I can see the potential benefits of storing online assessment results directly into SIMS, but I don't see it as an essential way of implementing a link either.

 

This is how we link moodle and sims everything is linked by UPN.

 

Data is best stored in one location, you could then use sims.net individual reports to pull the data from any aspect. these aspects could be filled in by your VLE or any other data collection process.

[geekulike]

We're currently looking at various packages for our school, including SIMS.

 

As SIMS runs on SQL Server, which is licensed to the school separately, there seems to be little Capita can do to stop custom report development or data extracts against the SQL Server database that SIMS requires.

 

It's just a SQL Server database, after all.

 

Writing back to the SIMS SQL Server database is clearly a no-no, but why would we want to?

 

As there are no trial, demo, or crippleware versions of SIMS available, how do we assess the complexity of the schema to decide if it will hinder custom report development, without actually buying the product? This is a key deciding factor for us.

 

Business Objects and the commandreporter 'API' are not the answers we're looking for ;-)

[LosOjos]

We're currently looking at various packages for our school, including SIMS.

 

As SIMS runs on SQL Server, which is licensed to the school separately, there seems to be little Capita can do to stop custom report development or data extracts against the SQL Server database that SIMS requires.

 

It's just a SQL Server database, after all.

 

Writing back to the SIMS SQL Server database is clearly a no-no, but why would we want to?

 

As there are no trial, demo, or crippleware versions of SIMS available, how do we assess the complexity of the schema to decide if it will hinder custom report development, without actually buying the product? This is a key deciding factor for us.

 

Business Objects and the commandreporter 'API' are not the answers we're looking for ;-)

 

This has been covered here many times, but in summary, the SIMS SQL database is extremely complex, it'd take an age to figure out where you needed to be looking for certain data, then when an update comes along it could all change, hence the reason most people use commandreporter (I know that's not what you wanted to hear, but I'm afraid it's the truth...)

[apeo]

Directly accessing the sql doesn't invalidate your licence but you need to be prepared to pay for capita to fix it it you break it,

 

Exactly what do you mean by this? Do you mean you somehow alter the database while working on it directly (creating a query or sometihng), and it breaks? or if the Sims stops working properly on its own and you need them to look at the data to see whats wrong?

[localzuk]

Exactly what do you mean by this? Do you mean you somehow alter the database while working on it directly (creating a query or sometihng), and it breaks? or if the Sims stops working properly on its own and you need them to look at the data to see whats wrong?

 

Our SIMS database gets sent off to Capita relatively regularly (I can think of a few different incidents since i've been at the school). If the access of the DB is shown to be any way related to the data having gone wrong, Capita would charge for repairing it - compared with them just doing it now.

 

Also, to the user who said that 'there is an api so surely this is what is important' or similar earlier, you missed off the NDA which prevents you from developing anything that could compete with any functionality within SIMS, prevents you talking about the NDA, etc... Lots of conditions.

[geekulike]

SIMS has an NDA..whatever next!

 

What does that cover...or is it a state secret?!?!?!