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”
National Student Survey Data in Power BI
The national student survey results are returned as a set of Excel workbooks that breakdown the responses by a variety of criteria – each workbook providing a different breakdown. By course, by age etc. This is fine but the structure makes it difficult to visualise the data in other tools and to compare the NSS metrics with other metrics about a category. Continue reading “National Student Survey Data in Power BI”
SQL Connections, Tasks and Multiple Results Sets
There is a tendency when confronted with a bug to assume that it’s related to the most complex or least understood part of a program. While not unreasonable this can lead to missing simple and obvious problems. I recently wasted several hours convinced that I had a threading issue which predictably turned out to have a much more prosaic explanation.
Continue reading “SQL Connections, Tasks and Multiple Results Sets”
Creating a SQL Server Code Back Up
Over the last few years I’ve got into the habit of setting up an SSIS job to do a daily backup of all the procedures, views and functions on databases that I’m working on. A lot of organisations I’ve worked with don’t have any source control or release procedures on database objects and having a history of these objects can be invaluable. A few times I’ve been able to use this to restore procedures that have been updated without having to find and restore an entire database to get at the old procedure version – well worth the time required set this up. First a view for the procedure and view definitions: Continue reading “Creating a SQL Server Code Back Up”
Creating User Alerts from SSAS Tabular Model
Power BI provides some basic alerting that can be set up on dashboard tiles but for my use case there are some major issues. First they rely on the user to define the alert and I want to push notifications to users. Secondly implementing alerts based on specific users’ areas of concern (the courses or schools that they manage for example) would seem to require a specific dashboard for each user which is not tenable. Since my data is on premise in SSAS however I can go back down the stack to SQL Server where I can get a bit more control of the process. Here’s the basic functionality I’ll be delivering. Continue reading “Creating User Alerts from SSAS Tabular Model”
Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)
An accumulating fact table has rows that represent a process and typically has dates (amongst other keys) that represent the time of completion of a stage in the process. For some calculations they are also a pain to deal with and where possible I like to convert them into transactional tables as well which makes solving these a lot simpler. Transaction tables represent a single measurement at a point in time which is immutable – it will never change. To take a very simple example consider a call logging table that contains the start and end of a call. Continue reading “Converting an Accumulating Fact Table to a Transaction Table (Calculating Peak Call Volumes)”
De-duping based on Sample Files
A while back I was asked if I could carry out a contact de-duplication exercise based on samples from the actual files to be de-duplicated. My initial answer was no. Clearly checking the level of de-duplication on a sample will not give the same results as for the complete files. Given two identical files (100% duplicated) of 50 records and taking a random sample of 5 records from each the chance of getting two identical samples is a million to one against! My second more thoughtful answer was yes but it would be fiddly (more so than just using the complete files). Still I was curious enough to work out how it would be done. Continue reading “De-duping based on Sample Files”
Counting Student Enrolments in Analysis Services Tabular
A weighted bridge table approach
A classic many to many relationship problem is students enrolled on a course with multiple pathways such a joints or major minors. Say we have a major minor course where the two elements are taught in different schools it might look like this in a courses dimension. Continue reading “Counting Student Enrolments in Analysis Services Tabular”