SCD3 in Informatica

SCD3 means Slowly Changing Dimension type 3. It will store current and most recent historical data.
For every possible changing column from source to target will have two columns, one is Previous and the other is Current. The SCD3 process is

Identify the new record and insert it in to the table.
Identify the changed record and update it in to the existing record.

Implementing SCD3 in Informatica with example

Create source DEPT table and drag it in to the mapping designer.
Create target DEPT_SCD table adding two columns to DEPT table one is Prev_DNAME and the other is Curr_DNAME.
Drag target tble in to the mapping designer for two times, one for update and the other is insert.
Create lookup transformation on target, and add new source port src_DEPTNO

Add lookup condition DEPTNO=src_DEPTNO.

Connect Source DEPTNO to src_DEPTNO on lookup.

Create expression transformation add all ports from lookup to expression.
Create two new ports in expression. one is NEW_REC_IND other is CHG_REC_IND
Write a expression in this ports

NEW_REC_IND=IIF(ISNULL(DEPTNO),1,0)

CHG_REC_IND=IIF(NOT ISNULL(DEPTNO)AND(Prev_DNAME!=Crr_DNAME))

Connect ports DEPTNO in lookup to DEPTNO in expression

DNAME in source qualifier to curr_DNAME in expression

curr_DNAME in llokup to prev_DNAME in expression.

LOC in lookup to LOC in expression

Create filter transformation and connect all source ports to filter transformation.
Connect NEW_REC_IND from expression to filter.
Add filter condition NEW_REC_IND
Create Update strategy transformation connect