Changing a slowly changing dimension

View previous topic View next topic Go down

Changing a slowly changing dimension

Post  svmayor on Mon May 20, 2013 9:27 pm

Hi All,

So, let's say that you have an SCD type2 dimension table with these columns . . .

column_a,
column_b,
column_c,
column_d
column_f

column_a, column_b and column_c make up the changed record indicator for the table. But column_d and column_f are not tracked historically.

Then two years later, the business users decide they want to also track column_d as part of the SCD type 2 determinate.

How do you rebuild the dimension and re-sync the fact table with the dimension keys?

Thanks in advance.

Chuck
avatar
svmayor

Posts : 3
Join date : 2013-03-06

View user profile

Back to top Go down

Re: Changing a slowly changing dimension

Post  umutiscan on Tue May 21, 2013 3:41 am

Hi,

Can you find the history of those type 1 attributes? Most of the time that's impossible, because operational systems do not store the history.

If you can find the history, you can rebuild your dimension and then rebuild fact tables that refer to your dimension.

To rebuild the dimension, you have to join your existing dimension with the historical values that you want to add your dimension. You generate your dimension with new surrogate keys and new VALID_FROM / VALID_TO values.

And finally yo need to rebuild all fact tables that refer to your dimension because you changed the surrogate keys in your dimension. Join the old fact table to the old dimension to get the natural dimansion keys and generate an initial dataset. Join your initial dataset to the new dimension to get new surrogate key values.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

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