Time Dimension Vs FACT

View previous topic View next topic Go down

Time Dimension Vs FACT

Post  hunain on Tue Jan 28, 2014 4:08 am

I have a Business Requirement to indicate student scores which are were recorded in the last year based on the current date.

I have a FACT TABLE which records the student scores e.g. A Student got TOFEL in 2011-Jan and got another one in Jan-2014.

I would like to indicate the Jan-2014 record as the currently valid one since its within the 1 year validity period.

Would it be a good practise to add a column in this fact table called ONE_YR_VALIDITY_IND and have 0's and 1's

or

Would it make sense to add a new column in the time dimension table called ONE_YR_VALID_IND and flag all such time keys to Y
and then simply join the time key on time dimension to the time key on the fact table i.e. the date on which the test was taken by the student.

Thanks guys!

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Time Dimension Vs FACT

Post  vp2014 on Tue Jan 28, 2014 7:56 am

No need to create a field in fact table. It will will be expensive to update fact table. You can join with time dimension and filter on year field.

vp2014

Posts : 1
Join date : 2014-01-28

View user profile

Back to top Go down

Re: Time Dimension Vs FACT

Post  BoxesAndLines on Tue Jan 28, 2014 8:34 am

If it has a one year validity, I would add a another relationship to the date dimension to indicate end of validity. This avoids any update logic.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Time Dimension Vs FACT

Post  nick_white on Thu Jan 30, 2014 12:27 pm

I would suggest that you don't go for any solution that hardcodes the flag value as you would then need to maintain the values every day (to check which were no longer valid and update them.
The solution of adding an additional Date Dim relationship obviously works but if you do want a flag/indicator then use a calculated column (on your table, in a view or in your BI tool if you are using one) e.g. current_flg = case when (sysdate - score_date) < 365 then 'Y' else 'N'

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Time Dimension Vs FACT

Post  hunain on Tue Feb 04, 2014 11:14 pm

Nick, Thank You Indeed! Thats what I thought, updating the dimension table everyday just to update this indicator does not make much sense. I will go with your option have a calculated or derived column on the time dimension table. Thanks!

hunain

Posts : 19
Join date : 2013-09-15

View user profile

Back to top Go down

Re: Time Dimension Vs FACT

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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