Programatic data extraction from SIMS.net Data Base

Quote:

---

Originally Posted by 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.

---

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

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

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

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.

Quote:

---

Originally Posted by 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

---

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 david.grashoff@capita.co.uk 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.

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

Quote:

---

Originally Posted by Tamarside

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.

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.

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

Quote:

---

Originally Posted by MattMitchell

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

Quote:

---

Originally Posted by MattMitchell

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

Quote:

---

Originally Posted by MattMitchell

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

Quote:

---

Originally Posted by MattMitchell

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.

Quote:

---

Originally Posted by MattMitchell

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.

Quote:

---

Originally Posted by MattMitchell

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.

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 ;-)

Quote:

---

Originally Posted by 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 ;-)

---

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

[QUOTE=penfold_99;332848]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, [QUOTE]

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?

Quote:

---

Originally Posted by apeo

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.

SIMS has an NDA..whatever next!

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