Increasingly, most organisations are seeking to “do more” with the data they collect and store and in this respect, St Andrew’s College is no different. For the last two years I have been looking at a number of tools that would allow us to easily collect, analyse, display and share critical information amongst key stakeholders.
As part of this investigation we have looked at tools such as Crystal Reports, Tableau and Microsoft Excel connected via MS-Query to our MS-SQL server and outputting pivot tables for analysis. Here are some examples of this:
A custom Crystal Report showing a student’s Fortnightly Notes score.
A chart in Tableau comparing the performance of StAC students with Decile 8-10 schools in a Year 11 Maths standard
A PowerPivot Map that plots home addresses by gender
In the end, we decided to progress with Microsoft’s PowerBI solution, which is described on their website as:
Power BI transforms your company’s data into rich visuals for you to collect and organize so you can focus on what matters to you. Stay in the know, spot trends as they happen, and push your business further.
Some of the reasons we selected this solution include:
- It’s browser based – you can access it from “anywhere” and see live data. You can also bookmark certain reports in your browser for near instant access.
- There is also an app available (iPhone/iPad/Android/Windows10) so the data is accessible anytime / anywhere
- We can tweak reports / visuals quickly and easily, based off feedback from stakeholders
- Being browser based, you don’t need a local file on your computer that is “out of date” once a new version with improved features is built. What you see is always the “latest version”
- It’s part of our existing Office365 Suite, so our existing username/password logs you into the reports.
- Security permissions are centrally managed based off AD users and role based groups.
- It connects to our on-premise MS-SQL Server, allowing for scheduled data updates (hourly / daily).
To best demonstrate the power of this tool, we built a proof of concept based around analysing NCEA student achievement, in particular University Entrance requirements and course/subject endorsement. Here is a screencast walking through the tool:
Note: identifiable data such as student names / ID numbers have been blurred out in this video.
To accelerate the development of some of this reporting, we have:
- Partnered with DataCom New Zealand and are getting expert advice from their Business Intelligence team in terms of configuring the ETL process via Microsoft SSIS, building a tabular data model and connecting to PowerBI in the cloud for presenting the data to staff.
- Hired a new staff member to join the ICT Services team in the role of Business Intelligence Report Writer. The responsibilities for this role will be to interface with the various business units in the College (e.g. Academic Data, Enrolments, Development, Communications etc), understand their reporting requirements and then build the reports in PowerBI.
The key with any Business Intelligence project is to help inform the decision making process and not just be contented with pretty visualisations. To that end, a robust conversation and scoping of what is required to be seen by the stakeholder needs to be established. However, with a wide range of visualisations being added regularly to PowerBI, there is a number of ways to present data in an easily comprehensible format. One of my favourites in a 3D, interactive globe that significantly improves on the PowerMap in Excel (see above):
This visualisation could be very useful in mapping where our current students or Old Collegians live or identifying where donations are coming from globally mapped either by volume or value for example.
We are in the very early stages of this project, yet the potential is very obvious to the leadership teams at St Andrew’s. The focus over the next few weeks will be configuring the backend infrastructure: the ETL processes (Extraction, Transformation, Loading), the Data Warehouse and the connectivity into PowerBI. Subsequently, the rapid development of reporting dashboards will proceed.
If this interests you, please do check back regularly on the blog for updates or drop a comment below to discuss further.