In my previous post on Charticulator I produced a really basic bar chart. This was designed to demonstrate some features I found confusing. And to provide a chart to test the Export to Power BI Custom Visual feature of Charticulator. Having dabbled in Custom Visual development I was interested to see how it would perform. Continue reading “Charticulator Export Power BI Custom Visual”
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”
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”
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)”
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”
Power BI Custom Visual Part 5 – Formatting
Setting up the formatting options turns out to be the most fiddly bit of developing a custom visual. There are a few “special” property names that are required to trigger some behaviours, undocumented features and at least one bug that I’ve come across. In this section I’ll explain how the options are set up for the Tree Funnel visual and in an extra post I’ll talk about some of the other issues and features. Continue reading “Power BI Custom Visual Part 5 – Formatting”
Power BI Custom Visual Part 4 – Accessing the DataView
In understanding how to access the data in a custom visual I found AB Kimmel’s tutorial incredibly helpful. Here I’m just going to go through the options I need, for a broader explanation I’d recommend reading his sections on data views. There are four steps to go through. First specify what data can be added to the visual via the Fields pane – this data will then be accessible via the dataview. Then create a data model to hold this data and a function to populate it from the dataview. And finally use the data model data to draw the visual. Continue reading “Power BI Custom Visual Part 4 – Accessing the DataView”