Having defined a timetable fact table and a timetable calendar dimension in previous articles I’ll now consider a particular reporting use case. I want to identify students who are timetabled for more than a certain number of hours per day. I want two measures here. The number of students who exceed X hours teaching in any single day and the number of days where each student exceeds this total. Using a timetable like this I can summarise the data at student / day level in SQL and add it to the Power BI model or create it in DAX as a calculated table. This will let me define the measures I want. Continue reading “Reporting Timetable Exceptions in Power BI – Student Hours per Day”
A Timetabling Academic Year Calendar
There are loads of good scripts out there for creating calendar tables so I’ve never felt the need to put up my own. But since I’ve written an article that references the need for one I’ll chuck my own into the mix as well. This will be a timetabling academic year calendar. So rather than just dates it will go down to the smallest timetableable unit – 15 minutes – so each row will be a 15 minute slot. Continue reading “A Timetabling Academic Year Calendar”
Creating a Timetable Fact Table from a Schedule
In general when designing a fact table I’d always aim for the most detailed level of data (lowest grain) possible. It’s always easier to roll up data than to increase the detail level of data later so best to include it up front. For timetabling data this may mean expanding a very compact schedule record to vastly more rows. But even for millions of rows it’s still worth going as detailed as possible to make the reporting easier. Continue reading “Creating a Timetable Fact Table from a Schedule”