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.

1 Answer 1


I would normalize the fact table into this form:

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

and add the level as a dimension.

  • In absence of better answers, I'll mark this as correct, as its the closest to what we got to a working solution.
    – James Love
    Apr 12, 2012 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.