SCD 2 Implementation using Informatica

Desciption

Here, client have given CustomerDetails (Source Table) and CUSTOMER_DETAIL_TRG (Target Table) and client requirement implement SCD 2 on User_Name and Credit_Score. So, i implemnted SCD 2 on User_Name and Credit_Score, so in future if User_Name and Credit_Score changed so its update and also save previous history data.

Tables Desciption

Table 1: CustomerDetails (Source Table) - The customer table captures all customer details.

Column Name	Column Type
CusID	FLOAT
FNAME	VARCHAR(20)
MNAME	VARCHAR(20)
LNAME	VARCHAR(20)
ADDRESS	VARCHAR(100)
ORG_NAME	VARCHAR(10)
DESIGNATION	VARCHAR(20)
INCOME	DECIMAL(7,2)
CREDIT_SCORE	INT
APPLICATION_DATE	DATETIME

Table 2: CUSTOMER_DETAIL_TRG (Target Table) - The target customer table captures all customer details.

Column Name	Column Type
SK_ID	NUMBER
CuID	NUMBER(38)
FULLNAME	VARCHAR2(40)
ORG_NAME	VARCHAR2(20)
EMAIL	VARCHAR2(20)
INCOME	NUMBER(7,2)
CREDIT_SCORE	NUMBER
APPLICATION_DATE	DATE
DESIGNATION	VARCHAR2(20)

Process load data from source to target table:

1. First load table from source database to source designer and target database to target designer.
2. Add mapping in informatica
3. Convert source data types to informatica datatypes.
4. Add Expression transformation for add some columns.
5. Add lookup transformation on target table.
6. Add Expression transformation for add some columns.
7. Add Router transformation for divide data into insert and update groups.
8. Add Sequence transformation for generate surrodate key for target table.
9. Add Update strategy for insert and update records.
10. Add target table into mapping.
11. Add Workflow and session for relevant mapping.
12. Run particular workflow