Readers of this blog will know that Power BI is a powerful tool for analysing school data. But for those with responsibility for data across a multi-academy trust (MAT) the gulf between what Power BI promises and what we can deliver, given time-constraints and technical skills, means that without substantial help from external Power BI consultancies most MATs aren’t going to be able to get the most from Power BI.
So forward thinking MIS suppliers are looking to provide pre-built Power BI reports that work ‘out of the box’, without needing bespoke Power BI consultancy and without long development lead times.
One such solution is MAT Vision by Bromcom, a company with a long history as a supplier of online school MIS services. Bromcom were early adopters of Power BI, seeing its potential several years ago, ahead of their competitors. In this post, I want to highlight the benefits of Bromcom MAT Vision and then look behind the scenes, at the way Bromcom and Power BI interface.
Bromcom have cleverly used the Power BI service to provide access to a fully functioned report that pulls data from every school in your MAT – primary, secondary, EYFS to GCSE and A level. However, when you see Vision for the first time it is not immediately obvious that Power BI is being used. But look closer and you will see the easy-to-use interface and the powerful cross-filtering capabilities.
Vision provides all the analysis you would expect of a product in this class:
- Pupil demographics, sliced and filtered by every possible vulnerability (PP, EAL, SEN, ethnicity etc)
- Attendance trends and comparisons with previous academic years.
- Fixed period and permanent exclusion
- Behaviour frequencies and patterns
- Geographical mapping by post code
If you have an assessment background, the implementation of assessment (from EYFS to KS4) is particularly impressive. Bromcom’s own assessment system has strong links to teaching groups (unlike SIMS) and this makes it easy to analyse by department, class and teacher. Attainment 8 / Progress 8 analysis is available at the MAT level for both internal and external assessments – so you can see the most recent internal assessments including the A8 and P8 values from those, and on the same page you can see the most recent external exam results, with full drill down to individual pupils in both cases. You can switch between most recent grade and target grade, compare this term’s internal assessment with last term’s.
To do all this, Vision relies on timely access to data provided by individual schools, across different phases. Implementing reliable links that standardise and transfer data in a secure way is always the hardest task for anyone writing Power BI reports for MATs. Bromcom clearly leads the way with its Power BI interface. Let’s have a look at that interface in more detail…
Bromcom’s Power BI Interface
MAT Vision is underpinned by the Bromcom databases at each school. To work reliably, data needs to be transferred from each school and brought together into an online Power BI dataset. Rather than using csv files to export the data from the MIS to Power BI, Bromcom uses a live OData feed. An OData feed includes a web URL that Power BI can connect to, with a secure password. Once connected the feed provides Power BI with access to every table and field in the Bromcom database.
This is a key advantage for Bromcom – Bromcom’s approach is a single, secure step that effectively gives direct access to all the underlying pupil data tables. As an individual school, the single feed gives you access to all the underlying tables in your school. As a MAT, the OData feed gives you aggregated access to the records of all your pupils across all your schools.
The data you have access to is extensive, but you can always create your own OData feeds to include ad hoc data from any report. Let’s have a look at some of the tables available to Power BI:
Assessment. You can see the data for either your school – or all the schools in your MAT. Each assessment result is recorded against subject, class, year group, term and assessment type columns.
Attendance. You can access the mark level data, but also access is given to weekly and monthly summaries (contrast with SIMS which gives just year to date summaries)
Behaviour. A similar approach to attendance – allowing total points at the weekly and month levels and access to individual events.
Collections. Bromcom collections are like SIMS dynamic groups. They allow rule-based definitions to create ‘collections’ of pupils. For example, ‘if a pupil has less than 90% attendance allocate the pupil to the poor attender collection’. These collections are also available to Power BI.
Statutory assessments. All statutory assessments are available EYFS to GCSE – at the level of individual results for each pupil.
The data feeds also include tables for Pre-admissions, Staff payroll, staff qualifications, CRB checks, staff absence, pupil leavers and details of each school with the MAT
At the MAT level the entire multi-school dataset is synchronised at a rate chosen the MAT, allowing data to be refreshed several times per hour, if required. Connections to single site schools are live, facilitated by the direct OData link.
Bromcom leads the way with its Power BI implementation. It is easy to see why MATs and new academies are moving to Bromcom, given the level of integration between schools and the impressive Power BI analysis built on top of it. Power BI users in SIMS schools will hope that the next version of SIMS provides something similar.