Accumulating snapshot with SCD?

View previous topic View next topic Go down

Accumulating snapshot with SCD?

Post  kskistad on Thu Jul 16, 2009 4:14 pm

Is it common to update dimension keys within an accumulating snapshot fact table if it contains some slowly changing dimensions? Or is good practice to leave the keys alone and only use a more current key for new rows?

My inclination is to leave the keys alone to preserve history of that row, but what if the facts (measures) actually changed too? For example you have an accum. snapshot of loans, and you have a "Current balance" column that you update regularly. This row also has a sales region dimension, which is slowly changing because the region sales rep changes. If you updated the region key to the most recent, you are attaching that old loan to a new version of the region, which may or may not be what the business expects. But you definitely are updating the facts (the ones that change) day-to-day, so in some respect your loan record is a "current" snapshot, so shouldn't it also reflect the "current" region?

kskistad

Posts : 11
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Accumulating snapshot with SCD?

Post  ngalemmo on Thu Jul 16, 2009 5:19 pm

kskistad wrote:But you definitely are updating the facts (the ones that change) day-to-day, so in some respect your loan record is a "current" snapshot, so shouldn't it also reflect the "current" region?

Maybe... there are valid business reasons to look at it historically as well as current state, which is why I recommend that you maintain both a type 1 and type 2 version of a dimension if you need to implement a type 2. The fact table would have two foreign keys for the same dimensional role, one pointing to current data in the type 1 dimension and the other pointing to the historical version in the type 2 table.

Getting back to your original question... you should never routinely re-key fact table foreign keys.
avatar
ngalemmo

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

View user profile http://aginity.com

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