Fact or Dimension

View previous topic View next topic Go down

Fact or Dimension

Post  hunain on Sun Sep 15, 2013 5:45 am

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

View user profile

Back to top Go down

Re: Fact or Dimension

Post  hunain on Sun Sep 15, 2013 1:22 pm

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!

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Fact or Dimension

Post  ngalemmo on Sun Sep 15, 2013 6:10 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact or Dimension

Post  hunain on Mon Sep 16, 2013 12:52 am

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

View user profile

Back to top Go down

Re: Fact or Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum