SCD Type 2 in Informatica

SCD -2 Implementiation in informatica

1. We have a flat file as shown below

2. Pull the source (emp.txt) to Source Analyzer

3. Develop the Mapping

Go to Mappings > Wizards > Slowly Changing Dimensions

Enter the New Mapping Name as M_EMP_SCD_TYPE2 and

choose 2nd Option Type 2 Dimension – Keep a full history of changes in the target click next

SCD Type 2 in Informatica

Select the Source file (FlatFile:emp) & enter the New Target Table Name as EMP_SCD_T

SCD Type 2 in Informatica

Click Next

Move the emp_id to Logical Key Fields

Move the Other columns to Field to compare for changes

SCD Type 2 in Informatica

Click Next

Choose the Third Option – Mark the dimension records with their effective date range

SCD Type 2 in Informatica

Click Finish

The Mapping will be created as below

SCD Type 2 in Informatica

Give the Connection Information to lookup table here (LKP_GETDATA)

4. Generate and Execute the target table (EMP_SCD_T) in Target Analyzer

5. Save the Record

6. Generate the Session and Workflow for this mapping

7. Run the Workflow

We got Output like

SCD Type 2 in Informatica

Now in Flatfile change the location for emp_id 5003

SCD Type 2 in Informatica

Run the Workflow,

Output

SCD Type 2 in Informatica

Here new primary key is assigned for new record (5003) and also end date is set for old record

Datawarehouse Architect