SIMS <--> SQL DB relationship schema

[DBMan]

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:

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

[matt40k]

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.

[localzuk]

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

[vikpaw]

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.

 

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.

[GREED]

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

[vikpaw]

Greed - 8 months, what you been up to? did you take a sabbatical?

[GREED]

Vik - I left the school and am working for a Training & Consultancy company, but I missed this place!

 

Plus that and we are in contact with Capita about partnering with a few things... kinda watch this space.

 

Hows you geezer?

[vikpaw]

I knew you left, just didn't realise you weren't still active on EG.

Let us know what you might be doing with Capita, always useful to know what's going on. Especially if you are finding ways to use the BO!

I'll pm you to catch up.

[Martin_W]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

 

Does SIMS lack the proper reporting features you think, or is it not that bad?

[localzuk]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

 

Does SIMS lack the proper reporting features you think, or is it not that bad?

 

Generally, people don't pull data from SIMS via SQL. The only people who do are usually those needing the data for use in third party software, or custom written software.

 

Most of the time, the information can be pulled out via reports.

[vikpaw]

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.

 

Answering my own question it seems it's fine to access a copy, and so presumably doing a simple read even on the live data would be okay:

 

http://www.edugeek.net/forums/mis-systems/61507-query-sims-access-db.html#post555499

[DBMan]

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.

 

Personally, I've never struggled with the distinction between SELECT and DROP ;-)

 

Standard SIMS tools and business objects clearly don't keep everyone happy, as witnessed by the many threads about access to SIMS data.

[DBMan]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

 

Does SIMS lack the proper reporting features you think, or is it not that bad?

 

SQL exists as a data access 'language' and should be the first port of call when running queries/reports against a database. That's it's job. It's not hard and is widely understood by millions...there are only 4 basic commands...SELECT is the main one.

 

The data model (schema) is the only issue. Once that is understood the rest should be easy. Vendors often don't want you to understand it either because it's crap and they're embarassed by it or there is a claim of intellectual property surrounding it's design.

[matt40k]

Personally, I've never struggled with the distinction between SELECT and DROP ;-)

 

99% of SIMS users aren't verus in SQL, they want to click a few options and bang out a few fancy reports without fear of dropping data. The SIMS reports engine is excellent at doing so and so are the build in reports, Discover is going to make (assessment) reporting extremely quick and easy. Don't slate a good system.

 

Standard SIMS tools and business objects clearly don't keep everyone happy, as witnessed by the many threads about access to SIMS data.

 

Let me first say, the problem with EduGeek is anyone can register. As far as you know I'm a female in china who works in a hotel on the reception desk. I have multiple accounts using my multi email addresses and multi ISPs (I won't get into public proxies etc).

 

Now this is a problem because we get a wide range of users, let me split this into groups as far as I see it who are interested in this sort of information

 

1/ User who is SQL guru (jinnantonnix) who likes to play direct with the database who has a SIMS license.

 

2/ User who thinks they are a (SQL) guru, but aren't, see this public topic do it, then later get a problem which they think they can fix, try and end up messing the system completely up and then reliese there backups don't work and have to go cry to Capita with cap in hand (thankfully, I've not heard of Capita charging - suprising eh?)

 

3/- User who doesn't have a SIMS license, has no interest in paying capita for a license, or working with a school and has downloaded SIMS via a torrent or such.

 

I have no issue with 1, other then remember if it's public, just think about 2. No 2 I have no real issue with, so long as you do it once and you learn from your mistake. As for number 3, well the less said the better.

 

As far as I am aware the business objects cover most things (I did ask about some finance stuff, but WIP), and avalible subject to a NDA, which I believe Penfold got - I never heard him getting charged for it, as the school he works for has a SIMS license already. I could be wrong.