Time elapsed between dimension attribute and fact

View previous topic View next topic Go down

Time elapsed between dimension attribute and fact

Post  scoob on Wed Mar 23, 2011 5:28 pm

Hi

I'm quite new in dimensional modeling and I'm wondering if there's a best practice for my particular case in a CRM like context :

I've got an individual dimension with a hire date attribute and some other dates attributes. It's a SCD type 2 standard dimension because some attributes may change.
I've got an Event Fact table with a lot of events each day
I need to report on the number of events for individual by time elapsed since hired.

I see 5 possibilities to do so and I don't know which to choose :
1°/ Store the time elapsed as value in the fact table (non-additive fact)
2°/ Store the hire date in the dimension and dynamically evaluate the time elapsed by subtracting the hire date to the event date on query
3°/ Store the time elapsed as range value in the Individual dimension. It will increase the Individual Dimension's size because almost every individual will have 1 row by range value as time goes by. (The loose of precision is acceptable with a logarithmical scale).
4°/ Store the time elapsed in a mini-dimension Time_Elapsed_Dimension with both range and exact value to avoid adding row in the Individual Dimension.
5°/ Use a kind of bridge table Time_Elapsed_Range_Dimension to dynamically evaluate predefined ranges with min and max value to get the range value
using the time elapsed (in addition to the 1 or 2 storing solution).

Another important thing to know is that the Individual Dimension as to be used with other fact tables like a Survey Fact table, not requiring this time elapsed information, and a Useful System Decision factless fact table which need it.

My preferred solution so far is 5 and 2 because it deals with ranges to facilitate the user querying the table and doesn’t need to calculate the time elapsed while loading fact tables. But I’m really worried with poor performance with such a solution.

Does anyone has an idea ? Or already had to deal with such a problem ?
Thx

Guillaume

scoob

Posts : 3
Join date : 2011-03-23
Age : 39
Location : France

View user profile

Back to top Go down

Re: Time elapsed between dimension attribute and fact

Post  LAndrews on Wed Mar 23, 2011 6:54 pm

I'm always in favour of keeping things simple

- Hire Date stays on the individual dimension
- Time Elapsed is a value on the fact record
- If you want to provide ranges for analytical/reporting needs, then I'd create dimension with the ranges, and add the dimension key to the fact record.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Thanks for the answer

Post  scoob on Thu Mar 24, 2011 6:39 am

I like your answer but i'll have to do the same for each fact table the information is needed.

I tried to avoid it but as it depends on the fact's date it seems fair to me.

scoob

Posts : 3
Join date : 2011-03-23
Age : 39
Location : France

View user profile

Back to top Go down

Re: Time elapsed between dimension attribute and fact

Post  Jeff Smith on Thu Mar 24, 2011 11:54 am

Not that it matters, but is Time Elapsed on the Fact Table a degenerate dimension or a measure? Maybe all measures are really just degenerate dimensions.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Time elapsed between dimension attribute and fact

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