SIMS <--> SQL DB relationship schema

Quote:

---

Originally Posted by localzuk

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

---

Yeah, we have got this direct from capita.

Quote:

---

Originally Posted by localzuk

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

@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

Quote:

---

Originally Posted by rpwillis

@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?

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

Quote:

---

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

---

Hi Richard,

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

Quote:

---

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

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]

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

@Martin
What you trying to extract?

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?

@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?

Quote:

---

Originally Posted by jinnantonnix

Use SQL Management Studio to browse the SIMS database. Under each SIMS database, you'll find an SQL branch called Views.

As an example, there's a view called sims.rpt.vix_report_student. If you right click this view, then select Top 1000 rows, you can see how this view neatly joins the disparate tables and presents a nice coherent view of pupil records.

If you right-click a view and choose Design, you can see how the views are built from the underlying tables.

But for goodness sake, don't modify a view or you'll do a lot of damage. Only use a SELECT with views; an UPDATE can change the underlying tables directly - not good.

The good thing about views is that even if the underlying table structures change (and they often do), the names of the views remain static and any queries you write are likely to still work after upgrades.

You can use a view as part of a query, of course.
If you wanted to see the pupil details for a particular UPN, for instance, you could use this query:

Code:

---

select * from sims.rpt_vix_report_student sr
where sr.upn = '12345454'

---

With this one simple query, we can get a whole heap of data about a pupil. The view draws in fields from multiple tables. Try it to see what you get.

We can effectively treat a view as a table in its own right.

---

Sound advice. SIMS runs on top of plain old SQL Server, which in this case is provided by the end user (school) rather than the application provider (Capita).

As it is 'just' SQL Server, you can use the standard tools to interact with the database. Management Studio can be used to poke around and find what's in there and to run queries.

As has been made clear on several posts, running SELECT queries can do no harm whatsoever...apart from maybe slowing down the system if you write a bit of bad SQL.

Why not take a copy of the database and run queries against the copy? It's your data and your DBMS software, remember!

Although the schema may be a mess in some folks eyes, using the built-in views that SIMS already uses will surely help - a view is simply a query that can be used as a data source just like a physical table. Either query the views using a SELECT query or take a copy and query it there.

DON'T MAKE ANY CHANGES TO THE SIMS DATABASE!!!!

Quote:

---

Originally Posted by DBMan

As has been made clear on several posts, running SELECT queries can do no harm whatsoever...apart from maybe slowing down the system if you write a bit of bad SQL.

---

SELECT is only a step away from DROP, thus you shouldn't publicly post information about where stuff is. You also are bypassing the auditing and security. A standard SIMS user account isn't able to login directly with SQL, so you would have to create a new SQL account. Personally, if the standard SIMS tools aren't good enough, you've got the business objects. Which keeps everyone happy.

Quote:

---

Originally Posted by matt40k

SELECT is only a step away from DROP, thus you shouldn't publicly post information about where stuff is. You also are bypassing the auditing and security. A standard SIMS user account isn't able to login directly with SQL, so you would have to create a new SQL account. Personally, if the standard SIMS tools aren't good enough, you've got the business objects. Which keeps everyone happy.

---

Security through obscurity is not security. Someone posting the structure of the SIMS .net database, and simple commands on how to get data from it is not weakening security in the slightest. If a user who could use the above commands could run them, then they'd also be able to browse the database anyway...

Is it actually the case that Capita do not disallow you from directly accessing the database, merely that they wont support you if you do?
It seems very odd, i know that it's our data and we provide the software, but i'd have thought that it would be part of the licence / terms of use that we aren't allowed to access the underlying data. I suppose they can't do that.

I have accessed copies of our database for many years, but don't do much with it, it's mostly for testing purposes.

Quote:

---

Originally Posted by Martin_W

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?

---

The Business Objects are useful because they allow you to write back to the db, and i don't think anyone would dare do that without support. Also, if you want to provide third party apps using SIMS data, it's the only way to do it and not have to fix things if they break. If you only want to extract data for internal use then you can get away with not using them.

The views make a useful method for accessing the data, that is a little more resilient to the frequent changes that occur with upgrades.

Been away for 8 months and I see the topics of choice never change!