Knowing the distance between your staff and students’ locations and your institution has innumerable uses. From timetable planning to calculating the CO2 impact of your organisation. Given a person’s postcode calculating their straight line distance from an address is straightforward but not that useful. Actual driving, cycling or walking distances would be better. Here the Google Maps APIs can help. Continue reading “Using the Google Maps Distance Matrix API in .Net”
Auto Create Views from the Tribal SITS data dictionary
As I’ve discussed the Tribal SITS Data Model does not have the nicest table and fields names and has a huge number of lookup tables. In this situation I like to build a layer of views over the base tables to provide a friendlier interface to build queries on. Still this is a bunch of work – could I auto create these views from the SITS data dictionary? Short answer: Yes. But they’ll probably need a bit of manual tidying up. That said I can produce working views that will still save a lot of effort. Continue reading “Auto Create Views from the Tribal SITS data dictionary”
Navigating the Tribal SITS Data Model
The Tribal SITS database doesn’t have the worst data model I’ve ever used but it’s still not the easiest to work with. The table and column names are logically organised but most are just four characters long. So they’re effectively codes the meaning of which is not always clear. I typically build a view layer for the tables I use frequently to add in more verbose table and column names as well as adding in all the lookup tables of which there are hundreds. Another issue is there are no foreign key constraints on the database. Aside from the fact that referential integrity is not enforced this makes finding table relationships difficult. On the up side SITS does maintain its own data dictionary which helps with these and some other issues. Continue reading “Navigating the Tribal SITS Data Model”
Reporting Timetable Exceptions in Power BI – Student Hours per Day
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”
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”
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”
Room Usage Map using Power BI Shape Map Visual
In a previous article on the PowerBI shape map I noted that you can use this visual to create a map of any shapes you like, for example, a campus building map, rooms withing buildings or pretty much anything else. All you need is a shape file that defines the regions of the objects you want to map. Here I’ll show how you could create a visual showing room utilisation. Continue reading “Room Usage Map using Power BI Shape Map Visual”