SSAS Modelling – many to many with one dimension

database-designolapssas

I'm having bit of a hard time modelling an OLAP cube.

My analogy is this: I have a list of students. Each one has one Major subject of study, and zero or more minors, up to five in total.

The raw data looks like this:

Student ID    | Major   | Minor 1    | Minor 2 | Minor 3 | Minor 4 | Minor 5
-----------------------------------------------------------------------------
12345           English   French       Null      Null      Null      Null
23456           French    Engineering  Null      Null      Null      Null
34567           Math      French       Biology   Physics   Null      Null
45678           Physics   Null         Null      Null      Null      Null
56789           French    Math         Physics   Chemistry Null      Null
67890           Math      French       English   Biology   Null      Null

Now as you can see, the subjects are all common, just that for some students, some are majors, the others are minors (where they have minors). This means I'd have a dimSuject dimension.

I can map this like so:

factStudent -> dimSubject

Such that I can drop a Measure that's a count of the Students, and see a list of Majors, with the number of students in each major.

What I'd like to do, is to be able to drop a dimMinor, which will drill down the majors into the count of each minor. Preferebly dimSubject is a unique reference of all subjects being taken, regardless if it's a major or minor. I have considered an intermediate factMinor table to store a reference to the student, and a reference to a subject, but does this maintain granularity? I couldn't also figure out how to use the intermediate fact as a dimension for drilling down.

Best Answer

I would normalize the fact table into this form:

StudentId Level    Subject
34567     Major    Math  
34567     Minor 1  French
34567     Minor 2  Biology
etc.

and add the level as a dimension.