[sims] Extracting Data from SIMS

[GeorgeVince]

Hi everyone,

Just started a new post at a school, looking to dig around some of the data held in SIMS to help senior level leadership.

 

Is it possible to run reports or query the SIMS database directly without interfacing with the SIMS GUI?

Right now my current workflow is to query the database using Reports -> Run Report and attaching a macro enabled excel file to manipulate data. I'm sure there's a more useful way to be doing this?

 

I've seen some posts relating to CommandReporter, could anyone point me in the direction of some documentation for this?

 

Thanks for your help!

[skunk]

Hi George

 

You can run an SQl statement against the DB and many do, but it is not supported and if you bungle it up, you're on your own.

 

The Command Reporter runs reports you have defined within SIMS from the command line. We use it automate exports, usually to XML, and then run various scripts against the data.

 

You can get all the relevant switches by running

CommandReporter /?

[PhilNeal]

It would be interesting to know what your SLT are after.

[bobsmith]

Welcome George

 

No touching the SQL!!!! (official position stated)

 

Bugs the hell out of me, with my last MIS I did a hell of a lot of work using sql queries which could do stuff in a fraction of the time that the MIS proper could do it, and I could also link things that you couldn't using the UI.

 

Command Reporter just allows you to run reports out of hours, nothing more special than that from what I can tell.

 

The other way is to buy an off the shelf package - search this forum for SISRA, 4Matrix etc for more info.

[bwfc_nottingham]

Hi everyone,

Just started a new post at a school, looking to dig around some of the data held in SIMS to help senior level leadership.

 

Is it possible to run reports or query the SIMS database directly without interfacing with the SIMS GUI?

Right now my current workflow is to query the database using Reports -> Run Report and attaching a macro enabled excel file to manipulate data. I'm sure there's a more useful way to be doing this?

 

I've seen some posts relating to CommandReporter, could anyone point me in the direction of some documentation for this?

 

Thanks for your help!

 

Welcome George

 

When I first started my job as a Data Manager (almost 3 years ago) , I was toying with the idea of trying to run SQL against the database.

 

What you would need to do so is a sensible ERD diagram showing relationships between entities / tables, but no ERD diagram exists and you will also find that SIMS data does not appear to be fully normalised. I would shy away from SQL writing as like @bobsmith said your are entering a dodgy area and maybe voiding warranties / support by doing so (especially if you have an SQL userid that allows update)

 

You can do some clever features with 'advanced reporting' where you assign an excel macro enabled template (utilising the auto open macro) to actually apply those macros automatically when you run your report.

 

There are some serious weaknesses in the reporting package though e.g. (a) not being allowed to apply parameter substitution if you run the command line or batch schedule option (b) not being allowed to apply "system date / todays date" as a parameter when running reports (e.g. a report to show me all todays behaviour logs) and © the BIGGEST weakness is that once you drop down from parent entity to report two child entities e.g. a report showing behaviour and achievement logs for students, then the report writer starts doing Cartesian joins across the children entities .... a really frustrating feature.

 

Don't go down the SQL log, just work with the tools you are given (albeit frustrating), after all this is a packaged solution and we are not developers / designers we are keppers of that data

 

Regards

 

Phil

Edited January 27, 2016 by bwfc_nottingham
enhance the test

[GeorgeVince]

An idea that's been thrown around here is some sort of system to automate email correspondence to parents when a Student has a behavior or achievement logged against them.

Of course this could be done with the advanced reporting system using an excel macro enabled template, however it would require someone to manually run the report each day.

 

Could the Command Reporter be configured to pull this data automatically at the close of play each day?

[ntm1275]

I was about to post the same question

 

I would like to query the sql database directly, but only using a read only sql login.

 

If using read only, you cannot 'bungle it up' so I would have thought this would be OK

 

I would totally understand Capita not being happy if read/write access was used.

 

Perhaps @PhilNeal can clarify whether a read only access is allowed

[bobsmith]

An idea that's been thrown around here is some sort of system to automate email correspondence to parents when a Student has a behavior or achievement logged against them.

Of course this could be done with the advanced reporting system using an excel macro enabled template, however it would require someone to manually run the report each day.

 

Could the Command Reporter be configured to pull this data automatically at the close of play each day?

 

So you want a system to do what SIMS InTouch does?

 

I think they wouldn't make any money from InTouch if you could easily get SIMS to do it directly

[GeorgeVince]

So you want a system to do what SIMS InTouch does?

 

I think they wouldn't make any money from InTouch if you could easily get SIMS to do it directly

 

Ah, I see.

I'm straight out of University and landed a role as a Data Analyst at a School and I've been looking at ways the school can use data to improve performance.

I've got the impression a lot of the data seems to be locked away in SIMS so was trying to automate the process of extracting and manipulating it.

 

The best I can currently do is pull off reports with macro enabled workbooks to generate dashboards, but none of the can be done automatically. A member of staff has to run the report manually via SIMS.

 

Am I barking up the wrong tree trying to find a different way outside of the SIMS GUI to extract data?

[bobsmith]

Not at all, there are various ways of getting SIMs to do clever stuff from within SIMS - the things you can do with the personal homescreens is pretty awesome.

 

Have a look at aspect/resultset analyses under Focus | Assessment.

 

SIMS Discover can do a lot of basic data extraction stuff, and it's almost idiot proof to use with nice primary colours! (this is free and you should have it in school already)

 

A lot of schools use 3rd party software to do the number crunching, but it's not live, there is an extraction and upload to do usually.

 

Although technically possible - it's just hugely frowned upon to go playing with the SQL DB directly, even if you are sensible and create a read only account to do it. It's overkill, but it's the way of MIS suppliers to minimise the ways we can break their products.

[GeorgeVince]

Been digging around, think this is what I'm trying to get at:

 

http://www.edugeek.net/forums/mis-systems/66215-sims-net-reporting.html#post593543

[PhilNeal]

There's no fees for reading data out of SIMS.

 

Resilience is important so in my view schools creating all sorts of add-ons are very dependent on individuals that move on and then things fail.

SIMS can do loads as @bobsmith says - I'd strongly advise investigating what it can do before resorting to coding.

[GeorgeVince]

@PhilNeal

 

Thanks, I think I can achieve all I need to with scheduled tasks and the CommandReporter.

[Barcrest]

Welcome George

There are some serious weaknesses in the reporting package though e.g. (a) not being allowed to apply parameter substitution if you run the command line or batch schedule option (b) not being allowed to apply "system date / todays date" as a parameter when running reports (e.g. a report to show me all todays behaviour logs) and © the BIGGEST weakness is that once you drop down from parent entity to report two child entities e.g. a report showing behaviour and achievement logs for students, then the report writer starts doing Cartesian joins across the children entities .... a really frustrating feature.

 

This is what I use to pass the current date,

@echo off

 

REM Format date for sims

for /f "tokens=1-4 delims=/ " %%f in ('date /t') do (

set dd=%%f

set mm=%%g

set yyyy=%%h

)

 

 

REM Call report using date formatted above.

"c:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe" /USER:USERNAMEHERE /PASSWORD:PASSWORDHERE /SERVERNAME:BSERVERNAMEHERE /DATABASENAME:DATABASENAMEHERE /REPORT:"Missing Mark Test 2" /PARAMS:" Mark dateDateMark date %yyyy%-%mm%-%dd%T15:19:16" /OUTPUT="c:\fire_test\reportname" /QUIET

 

Maybe you were referring to something else.

[GeekyDad]

There's no reason in the world you shouldn't run SQL queries against the SIMS database. It's SQL Server. That's what it's for. The clue is in the name ;-)

 

Just make sure you use a read-only 'report user' that you create specifically to run queries. Alternately, make sure you know what you're doing. SELECT = safe. UPDATE/DELETE/INSERT = not safe!

[GeekyDad]

'Hugely frowned upon' is not the same as 'you will break it if you try'. You won't. Just use a 'read only' userid.

 

You *will not* break SQL Server by running queries against it. Not unless you know how to!

 

Not using a DBMS to do what it's been built for - by the world's largest software company - & sending sensitive data offsite to do a limited set of what's possible locally makes no sense to me...especiaily if you have to pay for the privilege.

[GeekyDad]

There's no fees for reading data out of SIMS.

 

Resilience is important so in my view schools creating all sorts of add-ons are very dependent on individuals that move on and then things fail.

SIMS can do loads as @bobsmith says - I'd strongly advise investigating what it can do before resorting to coding.

 

 

Fair point. Schools should collaborate on this & share SQL queries. No point in all re-inventing the wheel.

 

In the new landscape of teaching actual *computing* & not ICT, it seems reasonable thats schools as a whole should raise their game when it comes to knowing how to run SQL queries against a product as widely used as SQL server. These are very useful skills within and outside the education sector.

[pcstru]

'Hugely frowned upon' is not the same as 'you will break it if you try'. You won't. Just use a 'read only' userid.

 

You *will not* break SQL Server by running queries against it. Not unless you know how to!

It is possible to play cripple Mr Database using only select statements and it is reasonably easily done by someone with very little experience or even by someone with a lot of experience. This is part of the reason why even SQL queries should not be developed on a production system.

 

In the new landscape of teaching actual *computing* & not ICT, it seems reasonable thats schools as a whole should raise their game when it comes to knowing how to run SQL queries against a product as widely used as SQL server. These are very useful skills within and outside the education sector.

The problem with this is that even being good at SQL will get you nowhere until you know the database schema and knowledge of the DB schema will be almost utterly useless outside of education. I've reverse engineered CMIS and Bromcom databases and have direct experience of SIMS (and bizarrely One) but it's wouldn't even appear on my CV unless it was a job in education or a supplier to education. It can also very time consuming, so you need to get a lot of value out of it before the investment is actually worthwhile.

[matt40k]

Fair point. Schools should collaborate on this & share SQL queries. No point in all re-inventing the wheel.

 

In the new landscape of teaching actual *computing* & not ICT, it seems reasonable thats schools as a whole should raise their game when it comes to knowing how to run SQL queries against a product as widely used as SQL server. These are very useful skills within and outside the education sector.

 

Agreed, but 1st rule is you don't run analysis reports against your transactional production system. It'll just just smash your indexes and kill any performance you once had. You run it against your warehouse. It also makes sense to not limit yourself to just your data - how can you predict if changing to a 2-week timetable will improve your pupils education without actually doing it? There are companies out there who have built cloud warehouses, whats the point of creating your own when you can just buy it? Especially if the cost is the same 20 days a year - thats assuming you can even find someone with the skills AND for min wage!!

[GeekyDad]

@pcstru - I'd be interested in your definition of 'cripple' when it comes to a database, and also how this state can be achieved with select queries.

 

If you are unfortunate enough to run a product join by accident simply kill the query. You *will not* do any harm to the underlying tables, no matter how hard you try. Call MS and ask how many times a select query has corrupted data in SQL Server.

 

SQL queries can and should be developed against a 'production' system, if there is a need to do so. SIMS runs schools, not banks. There simply isn't a high enough level of transaction activity for data input & SQL queries to conflict with each other. Don't believe me? Check out the average CPU busy on your server. Low isn't it?

 

FYI, banks run SQL queries against production systems, as do all big companies. Resource demand conflicts (mainly CPU) between transaction processing & queries is the main reason they try not to, not any notion of risk.

 

Schemas can be de-mystified quite readily if you are willing to put in a bit of effort. There are rare cases where schemas has been intentionally obfuscated, but these are few and far between. Start with the biggest tables and go from there. The model normally reveals itself quite quickly, even in cases with >1,000 tables. It shouldn't take more than a day to understand the core entities and how they relate together, so long as you are a reasonably experienced database developer. If there are PK:FK relationships specified a visual model can be generated very quickly.

 

@matt40k - you can run analysis reports against 'your transactional production system'. All production systems in all companies have operational reporting built in, including SIMS. Imagine not being able to run a report/query until someone built a data warehouse...

 

What does 'smash your indexes' mean? Sounds exciting ;-)

 

Building a data warehouse is an elective pursuit. You *do not* need to build copies of your transactional data for reporting purposes, unless reporting against the transactional system is not possible for some reason (and there are several). There are simply no barriers/risks to running SQL queries directly against SIMS.

 

What's the biggest row count in a single SIMS table? That's right...miniscule.

 

If anyone does want an analytic copy of their SIMS data in the cloud on SQL Server just let me know. Rather than take your money I'd point you back at SIMS and tell you to run your queries there...unless you can show me with *evidence*, and not emotive language, that it's a bad idea.

[bwfc_nottingham]

+1 Geeky Dad , cracking post

 

@pcstru - I'd be interested in your definition of 'cripple' when it comes to a database, and also how this state can be achieved with select queries.

 

If you are unfortunate enough to run a product join by accident simply kill the query. You *will not* do any harm to the underlying tables, no matter how hard you try. Call MS and ask how many times a select query has corrupted data in SQL Server.

 

SQL queries can and should be developed against a 'production' system, if there is a need to do so. SIMS runs schools, not banks. There simply isn't a high enough level of transaction activity for data input & SQL queries to conflict with each other. Don't believe me? Check out the average CPU busy on your server. Low isn't it?

 

FYI, banks run SQL queries against production systems, as do all big companies. Resource demand conflicts (mainly CPU) between transaction processing & queries is the main reason they try not to, not any notion of risk.

 

Schemas can be de-mystified quite readily if you are willing to put in a bit of effort. There are rare cases where schemas has been intentionally obfuscated, but these are few and far between. Start with the biggest tables and go from there. The model normally reveals itself quite quickly, even in cases with >1,000 tables. It shouldn't take more than a day to understand the core entities and how they relate together, so long as you are a reasonably experienced database developer. If there are PK:FK relationships specified a visual model can be generated very quickly.

 

@matt40k - you can run analysis reports against 'your transactional production system'. All production systems in all companies have operational reporting built in, including SIMS. Imagine not being able to run a report/query until someone built a data warehouse...

 

What does 'smash your indexes' mean? Sounds exciting ;-)

 

Building a data warehouse is an elective pursuit. You *do not* need to build copies of your transactional data for reporting purposes, unless reporting against the transactional system is not possible for some reason (and there are several). There are simply no barriers/risks to running SQL queries directly against SIMS.

 

What's the biggest row count in a single SIMS table? That's right...miniscule.

 

If anyone does want an analytic copy of their SIMS data in the cloud on SQL Server just let me know. Rather than take your money I'd point you back at SIMS and tell you to run your queries there...unless you can show me with *evidence*, and not emotive language, that it's a bad idea.

[matt40k]

Sigh...OK, I'll bite. So you write a SQL query against SIMS database. Let's ignore the fact you've bypassed the suppliers security (rightly or wrongly its only at the application level and you can't change that), you pulled the data for attendance for every pupil for the last 10 years, you've then pull in the status of the pupil at regular intervals - for example pupil premium, parents at same address, disabilities, no of address changes, blah blah blah, along with the all the static info like gender. Fine. Problem, only Capita hasn't indexed the query as it isn't something they've designed the database to handle. So the query takes an age, it and you forgot to put nolock in your query. You've just locked the database for writes.Comparing SIMS to a bank is like comparing the entire USA to a little village in england with a population of less then 1,000. SIMS is designed to run on SQL 2012 Express. That means it uses NONE of the enterprise features, none of the standard features, none of the 2014 or 2016 features. Banks run on Oracle, Teradata etc, that are designed for high concurrently. SQL Express is designed for a concurrently of what, 5. As for the PK:FK, you're assuming they actually use them and aren't relying on the application.

 

If anyone does want an analytic copy of their SIMS data in the cloud on SQL Server just let me know. Rather than take your money I'd point you back at SIMS and tell you to run your queries there...unless you can show me with *evidence*, and not emotive language, that it's a bad idea.

 

OMG, seriously. Your recommending someone uploads their SIMS database to a total stranger in the "cloud" so they can poke around it?

 

PS: It costs £5 per month to build a warehouse and they're doing all the hard work - Assembly

[pcstru]

@pcstru - I'd be interested in your definition of 'cripple' when it comes to a database, and also how this state can be achieved with select queries.

I was mainly thinking of performance hits but I've also had locks imposed when submitting queries - which stalled all other users. Yes I appreciate that would not be expected and should not happen but it is exactly the kind of thing that does crop up during development. I also once found a bug in MySQL that locked up the server for all of a large ISP's customers - no fix for that other than to avoid using a specific function. All of which is much better discovered on test systems rather than on a production system (I has actually tested the MySQL thing before putting it into production but the ISP was running a less up-to-date version of the DBE).

 

 

If you are unfortunate enough to run a product join by accident simply kill the query.

Assuming you have appropriate access at the time and permissions and are aware of what is going on. Someone submitting a query from a remote powershell session may not. They may not even realise they are having an unexpected impact.

 

FYI, banks run SQL queries against production systems, as do all big companies. Resource demand conflicts (mainly CPU) between transaction processing & queries is the main reason they try not to, not any notion of risk.

Everyone runs SQL against production systems - what would be the point of an SQL RDBMS unless they did that? No bank does 'development' against those production systems.

 

While a schools MIS is not generally big in the scheme of things, you can still generate very large result sets; think the multiple daily marks for attendance for students as an unconstrained join with student details and then assessment results. Here, that might generate many billions of records. I might agree it is fairly low risk but the expertise of the people at the end of the keyboard is variable. I have 30+ years experience working with databases - everything from DBase through MySQL, MsSQL, Oracle, Postgres NoSQL etc, and I still manage to make mistakes that could (and occasionally do) inconvenience people, so it seems reasonable that others with less experience might hit the same or similar issues. But perhaps that is delusion and I am just simply *r*p at it - I might be subject to Dunning-Kruger effect and simply not be equipped to know how unskilled and knowledgeable I am. Maybe.

Edited January 19, 2017 by pcstru