Modeling Student Academic Programs

I am designing a student star schema and I have a student dimension, quarter dimension and registration fact table. I need to add to this data warehouse the student's academic programs (majors).
The attributes for the academic program are: student id, start quarter, termination quarter, college, department, major, objective, emphasis, completion quarter, completion status, termination reason code, termination reason, honors, level, dissertation defense date, college catalog year.

How would I design the academic program fact table? I need to be able to report from both registration and academic programs so I will have to join them together. Is it OK to have one record per student and academic program and update it when the information changes? Or is it better to have a record per each quarter that the major is valid? This will be more difficult to maintain. We don't have a primary major designation.
Would I pull out all the attributes for the major into a separate dimension (completion status, termination reason code, termination reason, honors, level, dissertation defense date, college catalog year)?

Hi Diana,

I think that the first approach you mention ("to have one record per student and academic program and update it when the information changes") is the right one. With this approach, the fact table granularity is the academic program instead of being "each quarter a program is valid" (this sounds conceptually strange to me - maybe not for your business users).

With regards to your second question, the academic program attributes should not be in the fact table. In any case, they could be in a separate dimension (academic program). At this stage of design, something to discuss is whether you should create dimensions for the academic program attributes or not. My suggestion is that you analyze each case separately. For instance, it may be useful for dates if you need to report on date attributes (year, quarter, month, day of the week, before vacation day, etc). Please check the requirements to take a wise decision.

If you have any further question, don't hesitate to contact me.

Best regards,