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.

AB with BMANIMAL BIOLOGYBiological sciences

Students are enrolled on a course not a pathway so clearly there will be a many to many relationship between enrolments and courses which is not permitted in Analysis Services. Before embarking on a solution it’s worth considering how we want measures to work at the school level. If we want to count students by school there are a least three options for dealing with enrolments across schools.

    • Count a student as being in both schools. This is fair as they are studying in both schools but if we sum this measure across all schools we’ll get more than the total number of students. It also doesn’t reflect the work load the student has for the school.
    • Assign a proportion of each student to each school. If the proportions add up to one this will give a true total of students across all schools but won’t tell a school the actual number of students.
    • Choose one school to be the owner of the students for example the school running the major. This is not such an easy choice if the course is a joint!

The fact is all these approaches may be useful in different use cases so we’ll implement all three as different measures. The first step is to eliminate the many to many relationship. This is done by building a bridge table which is just a list of unique course codes. This is trivial to create in DAX as a calculated table derived from the courses dimension table:

Courses Bridge =values('Courses'[course_code])

This table the acts as a bridge between the Enrolments and Courses table – effectively converting one many to many relationship to a many to one and a one to many.

Data model with bridge table for student enrolments with multiple courses

The relationship between Courses Bridge and Course will also have to be made bi-directional. As it’s currently set up filters cannot propagate from Courses to the Enrolments fact table.

Data model with bridge table for student enrolments with multiple courses

Before we can define the measures we need to add couple of columns to the Courses dimension. Pathway FTE  and Pathway Primary will define how a student should be apportioned for cases 2 and 3 above. Both values need to sum to 1 for each Course.

CoursePathwaySchoolPathway FTEPathway Primary
AB with BMANIMAL BIOLOGYBiological Sciences0.671
AB with BMBUSINESS MANAGEMENTBusiness School0.330

The three measures can then be defined as follows.

--Count a student as being in both schools
--Count all rows in enrolments
Count Enrolments = COUNTROWS(Enrolments)

--Assign a proportion of each student to each school
--Use SUMX to apportion enrolments by the Pathway FTE
Count FTE = sumx(Courses, [Count Enrolments] * Courses[Pathway FTE]) 

--Choose one school to be the owner
--Use SUMX to apportion enrolments by the Pathway Primary
Count Primary = sumx(Courses, [Count Enrolments] * Courses[Pathway Primary]) 

Showing a student enrolment on a Major Minor course by School would give the following. Count Enrolments puts the student in both schools and the total for all schools is more than the total. Count FTE splits the student and Count Primary puts them in just one school – both measures sum up to the total number of students.

SchoolCount EnrolmentsCount FTECount Primary
Biological sciences10.671
Business School10.330

Alternative Approaches

Power BI (as of July 2018) supports many to many joins so the bridge used here can be replaced with a direct join. There are some limitations associated with this approach it is worth understanding. The measures would be defined in the same way.

Another approach would be to eliminate the many to many relationship by changing the granularity of enrolments table so the grain is enrolment per pathway rather than enrolment. The different measures can then be held on the enrolments table eliminating the need for the expensive SUMX operations. The downsides are the increase in ETL complexity, increase in the fact table size and a less intuitive grain.