Scenario 15: How to Implement Hybrid SCD or SCD Type 6

Scenario 15: How to Implement Hybrid SCD or SCD Type 6

Source Table Structure
EMPLOYEE_ID    NUMBER(4,0)
FIRST_NAME    VARCHAR2(10 BYTE)
LAST_NAME    VARCHAR2(10 BYTE)
DOB                     DATE
EMAIL                     VARCHAR2(30 BYTE)
PHONE_NUMBER   NUMBER(10,0)
HIRE_DATE             DATE
JOB_ID                     NUMBER(4,0)
SALARY                     NUMBER(10,2)
COMMISSION     NUMBER(10,2)
MANAGER_ID     NUMBER(4,0)
DEPARTMENT_ID    NUMBER(4,0)

Target Table Structure
DWID                         NUMBER
EMPLOYEE_ID         NUMBER(4,0)
FIRST_NAME        VARCHAR2(10 BYTE)
LAST_NAME        VARCHAR2(10 BYTE)
DOB                        DATE
EMAIL                        VARCHAR2(30 BYTE)
PHONE_NUMBER NUMBER(10,0)
HIRE_DATE                DATE
JOB_ID                        NUMBER(4,0)
SALARY                        NUMBER(10,2)
COMMISSION        NUMBER(10,2)
MANAGER_ID       NUMBER(4,0)
DEPARTMENT_ID    NUMBER(4,0)
OLD_JOB_ID        NUMBER
VERSION               NUMBER

Theory/Analysis
We all know the concept of SCD1, SCD2 and SCD3. Here we have to combine all three on single dimension table.
CASE 1: If new record then insert into target
CASE 2: If MANAGER_ID updated in Source then Update same in Target (SCD1)
CASE 3: If PHONE_NUMBER updated in Source then Insert it as a new record to target and update version for previous record in target. (SCD2)
CASE 4: If JOB_ID updated in Source then insert previous JOB_ID to OLD_JOB_ID column and insert current JOB_ID to JOB_ID column in target. (SCD3)

Practically: 
Step 1
Drag and drop one instance of source and five instance of target
(Make sure your target table does have a primary key at DWID column, If it's not there at database level create it Informatica Level)
Target Designer-->Right Click on your target -->Edit-->Column Port  (Right side of column you will see NOT A KEY flip it to PRIMARY KEY.

Step 2
Lookup
Create Lookup on your target table.
Drag and drop EMPLOYEE_ID from SQ to Lookup.
Condition Tab
EMPLOYEE_ID = EMPLOYEE_ID1
Set Lookup Policy on Multiple Match in Properties Tab = Use Last Value

Step 3
Expression
Drag and drop DWID, EMPLOYEE_ID, JOB_ID, OLD_JOB_ID, MANAGER_ID, PHONE_NUMBER from Lookup to Expression and all columns from SQ.
Create six ports.

NEW_REC  (Output Port) =IIF(ISNULL(EMPLOYEE_ID_TGT), 1, 0)

UPD_SCD1 (Output Port) =IIF(NOT ISNULL(EMPLOYEE_ID_TGT) AND (MANAGER_ID_TGT != MANAGER_ID), 2, 0)

UPD_SCD2  (Output Port) =IIF(NOT ISNULL(EMPLOYEE_ID_TGT) AND (PHONE_NUMBER_TGT !=PHONE_NUMBER), 3, 0)

UPD_SCD3 (Output Port) = IIF(NOT ISNULL(EMPLOYEE_ID_TGT) AND (JOB_ID_TGT != JOB_ID), 4, 0)

NEW_VERSION  (Output Port) = 1
OLD_VERSION  (Output Port) = 0

Note: For better understanding I have Suffixed _TGT for all ports coming from Lookup.

Step 4
Router
We need to carry only DWID, OLD_JOB_ID and JOB_ID_TGT of lookup rest of the columns are source columns.
Create four groups
NEW_REC = 1
UPD_SCD1 = 2
UPD_SCD2 = 3
UPD_SCD3 = 4

Step 5:

Sequence Generator

(No need to change any default properties.)

Step 6

Connect all Source columns ports along with New_Version from NEW_REC group to Target Instance 1

Connect NEXTVAL port from sequence generator to DWID

Step 7

Update Strategy

Drag and Drop MANAGER_ID and DWID from UPD_SCD1 group

Set Update Strategy expression = DD_UPDATE

Step 8

Connect MANAGER_ID and DWID columns from Update Strategy to Target Instance to 2.

Step 9

Connect all Source Columns ports along with New_Version and OLD_JOB_ID from UPD_SCD2 group to Target Instance 3.

Connect NEXTVAL port from Sequence generator to DWID.

Step 10

Update Strategy

Drag and Drop OLD_VERSION and DWID from UPD_SCD2 group

Set Update Strategy expression = DD_UPDATE

Step 11

Connect OLD_VERSION and DWID from update strategy to Target instance 4

Step 12

Update Strategy

Drag and drop JOB_ID_TGT and JOB_ID from UPD_SCD3 group.

Set Update Strategy expression = DD_UPDATE

Step 13

Connect JOB_ID_TGT to OLD_JOB_ID and JOB_ID to JOB_ID and DWID to DWID to Target instance 5.

Step 14:

Session-->Properties Tab

Set Treat Source Rows as: DATA DRIVEN

Don't forget to give relational connection for Lookup Transformation also.

What are you waiting for? Run it, Test it, Analyse it...

BINGO!!!

 Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com