Fact or Dimension
2 posters
Page 1 of 1
Fact or Dimension
I have a student enrollment table in my ERP which has data by term. For each term there will be one record per student, the problem is the ERP updates the same record and does not keep a history. If the student was enrolled yesterday, today If i withdraw the student the same record gets updated and we do not get a history. I have implemented an SCD Type 2 functionality but my question really is Should my table be called a FACT or a DIMENSIOn. Ideally speaking the table does not have any additive features which is why I think it should be dimension but then there are lot of attributes such as campus,division,major,student type etch which are stored in their respective dimensions table which needs to be included in the final table. This would mean that I need to lookups from dimension tables to get the keys. Please advise if the final table should be a fact or dimension table. My Final table has the columns EFECTIVE_DATE, EXPIRATION_DATE AND current row ind for each record.
Last edited by hunain on Sun Sep 15, 2013 1:23 pm; edited 1 time in total
hunain- Posts : 19
Join date : 2013-09-15
Re: Fact or Dimension
Guys so is this a Time Stamping Accumulating Snapshot Fact Table or an SCD Type 2 Dimension table.
What does the best practise say?
Thanks all!
What does the best practise say?
Thanks all!
hunain- Posts : 19
Join date : 2013-09-15
Re: Fact or Dimension
Facts represent events or states, dimensions provide context to facts.
The act of a student enrolling or dis-enrolling are events, the event should be recorded in a fact table. Who the student is, when did it occur, for which semester, and for what course are all contexts to the event, reflected by the related dimensions. If you want, you can include a measure of 1 and -1 to count the in and outflow of students. As each event occurs, you add a new row to the fact table, representing the history of such events.
You can then calculate the current enrollment by simply summing the rows. Optionally, you can create a summary snapshot of enrollment by course and semester.
The act of a student enrolling or dis-enrolling are events, the event should be recorded in a fact table. Who the student is, when did it occur, for which semester, and for what course are all contexts to the event, reflected by the related dimensions. If you want, you can include a measure of 1 and -1 to count the in and outflow of students. As each event occurs, you add a new row to the fact table, representing the history of such events.
You can then calculate the current enrollment by simply summing the rows. Optionally, you can create a summary snapshot of enrollment by course and semester.
Re: Fact or Dimension
Thank you your prompt reply. It does make sense to look at enrolment from a process perspective even though these are really 2 things enroll and de-enroll but its extremely important to record these activities in a fact table so that any point in time we can look at what the inflows and outflows are. Thanks very much indeed for taking time and sharing the best practises. Have a great day.
hunain- Posts : 19
Join date : 2013-09-15
Similar topics
» Dimension Design with intermediate tables between fact and dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|