Name of Solution: Mapping: SCD Type-2 When Rows Are Repeated
Business Requirement: PowerCenter mapping example that demonstrates how to implement the Slowly Changing Dimension (Type-2) when source data has multiple occurrences of the same dimension key.
Solution URL: https://community.informatica.com/solutions/1972
Supported Versions: Informatica PowerCenter 9.1 and above.
Description: 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 E_ID 100 100 100
E_NAME Chandler Bing Chandler Bing Chandler Bing
E_DOB 26/06/1978 26/06/1978 26/06/1978
E_LOCATION Texas Las Vegas New York
START_DATE 01/01/2000 01/01/2006 01/01/2009
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. SEQ E_ID E_NAME E_DOB E_LOC S_DATE E_DATE 101 100 Chandler Bing 26/06/1978 Texas 01/01/2000 12/31/2005 102 100 Chandler Bing 26/06/1978 Las Vegas 01/01/2006 12/31/2008 103 100 Chandler Bing 26/06/1978 New York 01/01/2009 12/31/9999 Note: The End_Date with 12/31/9999 in row indicates the current tuple version.
Download file contents:
PowerCenter mapping XML Oracle scripts to create a source and target.
F N N Y
Steps to implement the solution: 1. Compile the script in the database to create source and target tables. 2. Import workflow using the Repository Manager. Select the appropriate folder from the repository and resolve the conflicts by choosing suitable option. 3. In the PowerCenter Designer, study the mapping and how transformations are designed.
4. Observe the logic of Router transformation as shown below.
5. Open the workflow in Workflow Manager. Assign the integration service in Workflow > Edit -> Integration Service. 6. Edit session and assign valid connection object for the source and target. Make sure that the lookup transformation also gets the valid connection. 7. Execute the workflow and observe the output in the target table. The SCD type 2 is implemented when rows are repeated.
Other Useful links:
Mapping Bundles Workflow Bundles Informatica Tools Bundles Informatica for Social Media Bundles Debugging Tools Bundles Visio Templates Bundles B2B Templates and Projects Bundles Data Quality Packs and Plans Bundles Script Bundles