Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Changing a slowly changing dimension

2 posters

Go down

Changing a slowly changing dimension Empty Changing a slowly changing dimension

Post  svmayor 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
svmayor
svmayor

Posts : 3
Join date : 2013-03-06

Back to top Go down

Changing a slowly changing dimension Empty Re: Changing a slowly changing dimension

Post  umutiscan 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 : 41
Location : Istanbul, Turkey

Back to top Go down

Back to top

- Similar topics

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