An apology to Capita: why we slurped directly from the database

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, 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 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, 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:

  1. 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.
  2. 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.
  3. 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.


Visualising SIMS data: How are reports changing over time?

This post is the first in a series of entries I plan to make showing how we designed a bespoke data dashboard for our school. I’ll post links here to show how each aspect of our dashboard was created, leaving us with something that looks like this:

The Problem

At our school, we have a fairly simple way of reporting attitudinal and organisational behaviour to parents:

NumberAttitudinal behaviourOrganisational behaviour
1Below expectationsBelow expectations
2Meeting expectationsMeeting expectations
3Exceeding expectationsExceeding expectations
AB and OB descriptors

Now, I know that from a data analysis point of view, this will initially look horrifying. Clearly a 1-4 scale is not suitable for any kind of analysis. However, I actually have come to quite like this system, as it is extremely simple for parents to understand. At a data analysis level, we have plenty of other indicators to tell us when a child is slipping in terms of their organisation of attitude, mainly through our Behaviour Management system. So really, if we see a ‘1’ in a report, we can use that as a warning sign that further investigation and intervention is needed.

These scores are reported on a termly basis. By the end of the year, a students simplified report card might look something like this:

SubjectTermAttitudinal BehaviourOrganisational Behaviour
Maths1.1Meeting expectationsExceeding Expectations
Maths1.2Meeting expectationsBelow expectations
Maths2.1Below expectationsBelow expectations
Maths2.2Below expectationsExceeding expectations
Maths3Meeting expectationsMeeting expectations
English1.1Meeting expectationsBelow expectations
English1.2Below expectationsBelow expectations
English2.1Meeting expectationsMeeting expectations
English2.2Meeting expectationsMeeting expectations
English3Exceeding expectationsExceeding expectations
End of year report for a student

This is system is okay when looking at a single student, but becomes quite challenging for tutors and middle leaders when trying to analyse cohort data. Typically, our SIMS manager will create a marksheet that has all the data from each subject, like this:

Now this is okay, but it lacks finesse. Firstly, we will need to open seperate marksheets for each half-terms report data. We also need to manually look around for each score. While we could prettify it a bit with conditional formatting, this really isn’t optimal.

The PowerBI solution

The key requirements for a good visualisation of this data were:

  • Show at a glance whether something was a 1, 2, 3 or 4
  • Be able to show change over time
  • Summarise the different data for each subject.

In the end, I went for a 100% stacked bar graph:

To create these, I added the following to the 100% stacked bar graph shelves:

You might notice the tooltip is going to a report page. This tooltip is a simple table showing the subjects and count of ABs, so that we get dynamic tooltips that show us which subjects gave each grade. We can also click a bar to get a list of which students were assigned that grade:

With this, we now have a much richer, filterable view of our data. A teacher can now filter our key indicators instantly, to see how aspects are changing by term, and link these back to students.

As I said at the start of this post, a 1-4 rating system is really only useful as a traffic light. Staff can use these to flag students for investigation, before using the other analytics tools the dashboard provides to start to explain why those scores were given.

In the next post, I’ll look at our behaviour reporting, and how we used hierarchies and tree-maps to gain a more initiative understanding of behaviour incidents in our school.


Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!