How to create transactions SCD2 from many SCD tables

[savi176]

Hello,
During the design of a HR data mart I had a problem that was repeated in every dimension:
All the dimensions are SCD type 2 and their sources are few operational tables that each table has a history records.
For example:
Dim Employee is built from T_phone, T_address, and 8 more tables in the same structure. . This is an exaple
EMP_KEY PHONE_NUMBER FROM DATE TO_DATE
1 111 01/01/2009 31/03/2009
1 222 01/04/2009 31/07/2009
1 333 01/08/2009


EMP_KEY ADDRESS FROM DATE TO_DATE
1 AAA 01/01/2009 31/01/2009
1 BBB 01/02/2009 30/06/09
1 CCC 01/07/2009


I will have to create this table

EMP_KEY TRAN KEY TRAN TYPE FROM DATE TO_DATE ADDRESS PHONE
1 1 1 ( phone change) 01/01/2009 31/01/2009 111 AAA
1 2 2 (address change) 01/02/2009 31/03/2009 111 BBB
1 3 1 01/04/2009 30/06/2009 222 BBB
1 4 2 01/07/2009 31/07/2009 222 CCC
1 5 1 01/08/2009 333 CCC


And this is should be done to 9 tables…..


Any ideas?


Thank you

Avi

[ngalemmo]

Take it one step at a time...

You will need to do multiple merge passes building a consolidated history two tables at a time. Start with two tables, say phone and address, sort and merge them by employee and effective date, create whatever rows are necessary to accurately combine the data with the correct effective and expiration dates (this is not very hard when you are only dealing with two sets of dates) and write them to a new table. Take the next source table and sort/merge it with the table created in the first pass. Apply the same logic to resolve dates and add that data. Keep repeating untill you have merged in all the other data.

You could do this in a single pass but the process must be structured with multiple merge steps of two data streams, with the next step feeding off the data stream of the previous merge and one new source. This can be achived in some ETL tools with a little bit of creative work.

If you try to merge more than two sets of dates at the same time, you would go nuts trying to resolve the different permutations...