Health warning: this post is pretty technical!
One of the key goals for our Data Dashboard was to have up-to-date information. Initially, we were dealing with just GL standardised data (CAT4 and PASS). However, as time went by we started to want to use our internal reporting data, logged concerns, and merits / behaviour data.
When importing SIMS data to PowerBI, most schools seem to create .csv files from SIMS.net, and add them to the PowerBI model. However, that approach had the big drawback that it was a manual process. Now we could have automated it using command reporter, a tool for SIMS which can run SIMS.net reports a Windows scheduled task, but that still wouldn’t have worked for us as SIMS reports don’t let you export marksheet data. Since a key requirement of our staff is to see report data as it’s entered, we needed another approach.
Direct database access
SIMS uses a Microsoft SQL Server to store all its data. To put it bluntly… it’s ugly as sin. I’m not stranger to ugly data structures; I run greenpen.net, and our database sturcture isn’t quite this bad, but then we’re also not over 20 years old! So there’s no shame on Capita for having such a complicated table strucutre, but you should be aware of what you’re getting yourself into if you go down the direct database access route.
In the end, we decided that getting immediate data was worth the pain of having to access the SIMS Micorosft SQL Server directly. We were fortunate, however, that there were a few thigns in our favour:
- We have a full-time on-site developer, who is knowledgeable with SQL and database design principles. She wrote a series of custom SQL views for us, so that we never had write access to the database and could even use caching to reduce performance problems. This was a mammoth task for her, but once done it meant we could run nightly snapshots of SIMS to update our dashboard.
- We have a strong IT support team who can monitor server health and let us know if your queries start to degrade the performance of SIMS for the rest of our staff.
- We have self-hosted SIMS which means we get access to the servers in the first place.
If you’re not in the same position as us, don’t worry; there are still ways you can have nearly-live data. The easiest way is to use Command Reporter for most student data, and then ask your data manager to make a marksheet with as much of the report data as possible on it.
To make the most of this, you can create a shared drive and have the .csv files regularly update. PowerBI is able to pull these updated files through on a refresh schedule, keeping you up to date.