Changing a slowly changing dimension

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

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.