Power BI in schools - how can you report on data from multiple MIS databases?

07/09/18

The real challenge for any school that wants to use a tool like Microsoft Power BI to analyse and report on data is how to get at data that is spread across different application databases - you want a solution that doesn’t involve lots of copy and pasting between different spreadsheets.

sample teacher analysis dashboard

In schools data is spread across student record systems, curriculum planning software, finance applications and other systems. Multi-academy trusts make the challenge even bigger - trustees want to drill into data that has come from more than one school.

How can you solve this problem? What are the technologies that can give a school an affordable, flexible and sustainable data reporting solution?

Here are three broad approaches:


Approach 1 (probably the ideal)

Key features -

  1. Develop a cloud based reporting database in Microsoft Azure (Microsoft's cloud hosting solution). The database sits between your source databases and Power BI. It’s organised in a way to make reporting easier - the tables in the database give a simpler, more consolidated view of important data.

  2. Use Microsoft’s data connectors to link source application databases to the reporting database. It may then be possible to do almost real-time updates.

  3. Point Power BI at the reporting database - that connection is straightforward.

  4. Build interactive dashboards in Power BI Desktop and share them with the online Power BI service.

Challenges -

  1. It may not be possible to connect directly to some of you software application databases - your software vendor may simply not allow it.

  2. A trust with say 4 schools and 3 different applications in each school needs - worst case scenario - 12 different connections; technically possible but the amount of effort required might make the solution too expensive.


Approach 2 (more manual maintenance because for some reason you can't connect directly to your source databases)

Key features -

  1. Build the cloud based reporting database as in Approach 1.

  2. Export data from your application databases as .csv or Microsoft Excel files.

  3. Import the .csv files into the reporting database - scripts can be developed to do this automatically, whenever the .csv files are changed.

  4. Point Power BI Desktop at the reporting database and build your reports.

Challenges -

  1. There’s a manual step in this approach. Somebody’s got to get the reports out of the source applications everytime the reporting database has to be updated.


Approach 3 (potentially spreadsheet hell but maybe a first step and good for prototyping)

Key features -

  1. Don’t have a reporting database - miss this step out.

  2. Export spreadsheets from your application databases and import them straight into Power Bi Desktop.

  3. Build reports and dashboards in Power BI.

Challenges -

  1. A lot of manual effort - takes time.

  2. Using Power BI Desktop to report across multiple imported spreadsheets becomes more difficult - it is much harder to keep track of how up to date things are.

A real world solution might be a combination of all three approaches. And you’d need help from a software developer, particularly for approaches 1 & 2. A final point to make is that it totally makes sense to build a reporting solution like this in phases - start with some prototype dashboards, prioritise your data sources and add new ones over time.