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.
