St Andrew’s College has trialled a number of tools that would 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 MS-SQL servers and outputting the data to pivot tables for analysis. In the end a decision was made to progress with Microsoft’s Power BI solution.
Choosing a business intelligence tool
A key criteria during the investigation was ease of access for staff. Power BI delivers on this as it’s accessible anytime/ anywhere via a web browser. Being integrated into Microsoft Office365 Education, staff usernames/passwords provided Single Sign On (SSO) into the reports, further reducing the barriers to entry for them.
The range of visualisations of data within Power BI is excellent, enabling the presentation of complex data in ways that staff and students could immediately understand. This represented a significant upgrade on data that was previously stored in spreadsheets and rows and columns of numbers. Significantly, being browser based, staff are only ever accessing the latest version of the reports, removing previous problems of out-dated spreadsheets.
Other benefits include:
- Security permissions are centrally managed based off AD users and SQL Row Level Security.
- Power BI connects to an on-premise MS-SQL Server data warehouse, allowing for automated updates (hourly/daily).
Tutor Quadrant report
- Ease of access – browser based, highly visual reporting;
- Collation – the collation of disparate data presented in an easily comprehensible format. Previously, to obtain data on attendance, NCEA results, discipline incidents, and Fortnightly Notes would have required dozens of clicks, different windows and even using different platforms; and
- Speed – the reports load very quickly in the browser.
Deployment and development
Microsoft Power BI can be utilised in a number of different ways depending on the requirements of the organisation. St Andrew’s has explored two methods of deployment:
- Manually generating reports in the free Power BI desktop app and publishing content in the Azure blob in the cloud (quick and easy, but limited security options).
- Developing an on-premise data warehouse and using ETL processes to extract data from various sources before loading into a tabular data model and connecting to the cloud via SSAS Gateway Connector. This is the method the College is progressing with because of the advanced security options available and the automated refreshing of data flows.
Further reporting
With the release of the Tutor Quadrant Report, development is already underway of further reports for both staff such as:
- Donations and financial giving analysis – the Director of Development can now access data which is automatically refreshed daily and she can drill down using the self-service elements of Power BI.
- Future Student Forecasting – visually displaying students intending to come to St Andrew’s and the various stages they are at from an initial enquiry through to being offered a place.
- Classroom Teacher Reports – a series of reports that visually display key student information to the classroom teacher.
Lastly, there’s rapid development happening on the platform with the ability to embed Power BI reports into a web browser or app looking particularly promising.
It is still the early stages of this project, yet the potential is obvious. The key with any business intelligence project is to help inform the decision-making processes and with more schools moving to Office365, they should start to consider how Power BI can help them with data analysis.
Compiled by Sam McNeill, Director of ICT at St Andrew’s College in Christchurch.
For more on Microsoft’s Power BI go to powerbi.com
Video overviews
Sam has created videos demonstrating and explaining the Power BI tool, including building a proof of concept based around analysing NCEA student achievement.
