INFORMATICA SCD TYPE 2 DATE METHOD

Step  1 :

Create two tables like below

create table emp_daily

(eid  integer, ename varchar(30),job varchar(30),mgr  integer,jdate

date,sal integer) ;

insert into emp_daily values(1,'vinay','it',2,'01-oct-2015',30000);

 

create table emp_hist

(sno integer,eid integer,ename varchar(30),job varchar(30),mgr integer,SAL INTEGER,start_dt date,end_dt date)

Step 2:

1.       Take EMP_Daily, EMp_HIST  in the mapping

2.       Take lookup transform ,use EMP_HIST as lookup object and compare EID and IN_EID(input port)

3.       Take Expression transformation ,take all columns from source qualifier ,from lookup take these sno,eid,enm,mgr,job and connect to expression transformation.

Take new additional ports with the below data types and expressions

Opt_New_Record àStringàIIF(ISNULL(lkp_EID),TRUE,FALSE)

--IF NULL,IT IS NEW RECORD,OTHERWISE OLD/UPDATE RECORD

 

Opt_Update_RecordàStringàIIF(EID=lkp_EID AND (JOB<>lkp_JOB  OR MGR<>lkp_MGR OR SAL<>lkp_SAL),TRUE,FALSE)

--IF EID MATCHES AND THERE IS A CHANGE IN ANY OTHER ATTRIBUTE

 

Opt_Start_DateàDateàsysdate

4.       Take filter transformation, connect eid,enm,job,mgr,sal,jdate ,new_record  from expression.

Filter condition: NEW_RECORD=TRUE

5.       Take target and connect eid,enm,job,mgr,sal,Jdate to Start_date ,END _dt (con’t connect)

So that END_DT will be NULL.

For new record it will be NULL.

6.       Take Sequence Generator Transform and connect  NEXTVAL to target SNO

7.       Take one more filter transformation for UPDATE operation (Filter Condition: UPDATE_RECORD)

Connect from EXP ,eid,enm,job,sal,mgr,lkp_SNO,Update_RECORD,Start_Date

8.       Take target one more copy for UPDATE INSERT Operation

Pass EID,ENM,SAL,MGR,JOB,JDATE-START_Date,END-DT  NULL

This process is for Inserting updated record .

9.       Updating END_DT with start_date

Take update strategy transformation,take lkp_SNO and START_DATE

 

     Take a target table one more copy and connect lkp_SNO to SNO,START_DATEàEND_DT

 Take condition (DD_UPDATE)

10.   Goto lookup and use Sql Override ,where clause where end_dt is null to get only active records.

11.   Create session and workflow

SEE THE BELOW MAPPING FOR MORE INFO