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”
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”
Aggregate Traffic Light KPI with Variable Targets in Power BI and Analysis Services Tabular
Setting up a KPI to monitor performance against a single target is simple enough in SSAS (it’s also possible directly in Power BI but I’ll only deal with the SSAS approach here). However if you have different targets for different dimension values then a bit more thought is required. As an example imagine different courses are set different targets for student withdrawals in an academic year. Continue reading “Aggregate Traffic Light KPI with Variable Targets in Power BI and Analysis Services Tabular”