Power BI for Timetablers (Part One)
This post is part of a series. Part two can be found here.
Over the last year I've become increasingly impressed by Microsoft Power BI and how we it can be used to support timetabling processes. I intend to share some of what I've done on this site, so hopefully this will eventually form a series of posts... but I'm going to start with a simple explanation of why I've found it so useful and an introduction for those who've not used it before.
Why Power BI?
I began to explore Power BI when a number of problems arose and I realised that they could all be solved by using it.
- I wanted to have a back up plan for the start of the new academic year. If there was an IT outage and our personal timetable system went down, I needed an alternative method for sharing timetabling information across the organisation. As a reporting platform, Power BI hosts its own copy of your data and therefore fulfils this need.
- The system used for timetabling at UoM has a reporting module built on Microsoft SSRS, but it's fiddly to write a report which is anything more sophisticated than a simple table or list. Power BI comes with a whole host of fancy visualisations, and you can download even more from the marketplace.
- That same system also made it difficult to share reports with large numbers of users. Each person who wanted access to reports needed to be granted access on a case by case basis - there was no easy way to make a report available to a large group of users. Power BI works with your Microsoft 365 setup, so you can easily deploy a report to "All Staff" or "Everyone in this MS Teams group".
- Often, I was tasked with writing a report which, once produced, would prompt a whole host of additional work as people asked for the data to be sliced different ways - "Can you break it down by programme?" or "What about the semester 1 data only?". I wanted a tool which would let people explore the data themselves, without having to understand the underlying data structures. Power BI reports are interactive - tools like slicers allow users to explore the data themselves.
- We'd also had problems at the start of year with heavy reporting loads knocking our reporting server over. I needed something more robust which could handle large volumes of traffic. Power BI takes a snapshot of your data and uploads it into Microsoft's cloud which can handle pretty much anything thrown at it.
- I wanted a reporting tool which would refresh at regular intervals, rather than relying on me re-running reports when people need new information. With a premium workspace, Power BI can schedule regular refreshes of its data.
It's not a magic wand though. To make the most of it, your organisation needs to be using Microsoft 365. And all the fancy sharing features really need a premium workspace which has a cost associated with it. I've also found that getting the most from Power BI also requires the ability to write some SQL – although Power BI has tools to manipulate and combine data sets, sometimes its easier to use SQL to "shape" the data before it reaches Power BI.
Power BI Overview
The biggest challenge I had getting my head around Power BI was the terminology. I've come to understand it as three main parts, all of which are sometimes referred to as "Power BI" which can get incredibly confusing:
- Power BI Desktop is a Windows application which runs on your PC. You use this to import data and design reports to display it. If you're only interested in reports for your own use then this is pretty much all you need.
- The Power BI Service is an online service (accessed at powerbi.com). You upload ("publish") the reports you built in the desktop application to the service and they get saved in a workspace. You can grant other people access to your workspace(s) and they can also view and edit the reports. If your organisation lets you have a premium workspace, you can also schedule the data in your report to refresh at regular intervals.
- Power BI Apps are also part of the Power BI Service, but these are the "public face" of the apps in your workspace. You can create a bundle of reports and share them with a pre-defined audience. That audience can view the reports and interact with them, but they can't edit them or the underlying data.
So you write your report in Power BI Desktop, publish it to the Power BI Service and then configure permissions so that your intended audience can view the Power BI App.
My journey with Power BI
My first Power BI report was a start of year contingency. A simple report which let any staff member view the timetable for any student. The report wasn't published, but we held it in reserve at the start of year in case we needed it. If needed, we would publish the app to all staff by giving access permissions to our "all staff" group in Active Directory.
After the start of year was passed and this contingency was out of the way, we developed a more refined and GDPR compliant version – one which let any staff member view the personal timetables of the students they teach. This was a much requested report, and not something our timetable system could produce. But for Power BI it wasn't an issue, since it knows who you are when you log in, and we were able to use row level security to limit what data an individual could use.
Building on that experience, we then built another report which let any member of staff check what activities are booked in a room – very useful for checking room availability. In fact, because there's a Power BI mobile app this made checking room usage on the go very easy.
And from there we've gone from strength to strength with more reports. We've also published some more reports for all staff members, including class lists (only allowing them to view their own classes) and a "draft timetable checker". We've also created some more niche reports for our scheduling staff which allow them to quickly identify staff who have back to back classes in different buildings, identify staff and students who have a 9am start and 6pm finish on the same day, and a whole host of others. These reports are useful for quality control whilst scheduling the timetable.
Hopefully the above gives you a flavour of how powerful Power BI can be for timetabling. In my next post I'll talk in a bit more detail about how we put one of these reports together (let me know if there's a specific one you'd like to see covered!
Updated for clarity: .