Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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. Kind regards Thomas

share|improve this answer
    
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 '12 at 19:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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