How to design student quarterly registration fact table
3 posters
Page 1 of 1
How to design student quarterly registration fact table
I am in the process of creating a student data warehouse for a university. I want to track the student quarterly registration. I am wondering what the best way to create the fact table is. The main dimensions should be time (quarter) and student but there are a lot of attributes that are related to the quarterly student record - like registration status, fee status, level, disciplinary status, etc – about 30 of them. Do I include them all in the fact table or do they belong to a separate dimension? If I put them in a dimension then it needs to include the same keys as the fact table – student id, time period.
Thank you for your help,
Diana
Thank you for your help,
Diana
dianaantova- Posts : 9
Join date : 2009-05-05
Re: How to design student quarterly registration fact table
Those attributes belong in a separate dimension.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to design student quarterly registration fact table
You could make a 'student status' profile dimension. This dimension would contain the attributes related to student status. It would not be keyed by student id or time period. Rather, there would be a record (with a surrogate key) for each combination of attribute values*. Each student registration fact record would then point to the surrogate key for the profile dimension record that contains the combination of attributes that currently describes that student’s status.
*With 30 attributes, creating one record for each possible combination of attributes would result in an impossibly large profile dimension with billions of rows. Instead, create records as needed only for the combinations of attributes that exist. The maximum number of rows in the dimension would then be the number of students times the number of periods worth of history in the data warehouse. You could also break the attributes down into smaller groups and create more than one profile dimension.
*With 30 attributes, creating one record for each possible combination of attributes would result in an impossibly large profile dimension with billions of rows. Instead, create records as needed only for the combinations of attributes that exist. The maximum number of rows in the dimension would then be the number of students times the number of periods worth of history in the data warehouse. You could also break the attributes down into smaller groups and create more than one profile dimension.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Similar topics
» Student Course Registration Fact and Dimension Tables : How to model
» Large Student dimension or new Student Fact table?
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Student Retention Fact Table
» Student Profile - Fact Table
» Large Student dimension or new Student Fact table?
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Student Retention Fact Table
» Student Profile - Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum