Mapping: SCD Type-2 When Rows are Repeated
Posted by: Sandeep Roy
A Mapping example that demonstrates how to implement the Slowly Changing Dimension (Type-2) when source data has multiple occurrence of the same dimension key.
Overview
SCD Type 2 tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys.The mapping example to apply SCD type-2 on source data.Suppose a company is maintaining a table with the employee location details. An example of the data is given below: EMPLOYEE TABLE
Now the mapping will load the historical data in SCD Type-2 dimension table. So the data will look like as shown below after it has been passed through mapping.
Note: The End_Date with 12/31/9999 in row indicates the current tuple version. Download Contents
| E_ID | E_NAME | E_DOB | E_LOCATION | START_DATE |
|---|---|---|---|---|
| 100 | Chandler Bing | 26/06/1978 | Texas | 01/01/2000 |
| 100 | Chandler Bing | 26/06/1978 | Las Vegas | 01/01/2006 |
| 100 | Chandler Bing | 26/06/1978 | New York | 01/01/2009 |
| SEQ | E_ID | E_NAME | E_DOB | E_LOCATION | E_START_DATE | END_DATE | FLAG |
|---|---|---|---|---|---|---|---|
| 101 | 100 | Chandler Bing | 26/06/1978 | Texas | 01/01/2000 | 12/31/2005 | N |
| 102 | 100 | Chandler Bing | 26/06/1978 | Las Vegas | 01/01/2006 | 12/31/2008 | N |
| 103 | 100 | Chandler Bing | 26/06/1978 | New York | 01/01/2009 | 12/31/9999 | Y |
- PowerCenter mapping XML
- Oracle scripts to create a source and target.
Features
Mapping to apply SCD type-2 on source data where you have multiple records with the same natural key in the same run where we can capture all the records. You Can apply this during our initial load of dimension table.Get all our historic data in SCD-2 type format. Accurately report on historical data from first day. This code can be modified to fit any data on which we want to implement SCD -2.We just need the natural key and the way to determine the order of records like inserted date. Same code can be used to initial load as well as normal load. Although the initial load can be slowed due to the volume of the records. Optimal use of Dynamic Lookup transformation.- Informatica PowerCenter 9.1.0 and above.