For a Type 2 slowly changing dimension (SCD) such as Employee with a Parent-Child relationship, how do you handle the explosion of new rows when a high ranking employee has an attribute change?
It will require terminating the high ranking employee row and adding a new current row with a new surrogate key. Then every employee row below this employee will also have to be terminated and a new current row added with the new surrogate key of the parent.
This will have to be performed 1 level at a time so the subordinate rows will have the new surrogate key of their immediate parent.
For a large organization such as 20,000 employees, this will result in a dimension with hundreds of thousands rows in a relatively short time.
Is there a better way to model this?
View Complete Post