Wednesday, 5 March 2014

Scenario 8: How to Implement SCD2 with Dynamic Lookup?

Scenario 8: How to Implement SCD2 with Dynamic Lookup?

I am assuming you already know concept of SCD2 and have idea about Dynamic Lookup.
If you want to brush up your dynamic lookup concept please visit:

While Implementing SCD2 with Dynamic Lookup the challenge is to fetch only unique records from lookup table to cache because Dynamic Lookup Cache doesn't support duplicate value and session will fail if any duplicate records is trying to insert into Lookup Cache. (Challenge 1)
Second Challenge which port or how to set associated expression for DWID column. (A column which would be primary key in SCD2) (Challenge 2)
For beginners: We are not taking source primary key as a primary key in target table(Dimension Table) we are creating new primary key.


I am implementing SCD2 with version based upon Salary and taking Oracle default table EMP.

Source Table                               Target Table (Dimension Table)
EMPNO (NUMBER)                   EMPNO (NUMBER)
JOB       (VARCHAR2)               JOB        (VARCHAR2)
MGR      (NUMBER)                  MGR       (NUMBER)
HIREDATE (DATE)                    HIREDATE (DATE)
SAL       (NUMBER)                   SAL          (NUMBER)
COMM  (NUMBER)                  COMM     (NUMBER)
DEPTNO (NUMBER)                 DEPTNO   (NUMBER)
                                                    DWID         (NUMBER)
                                                    VERSION   (NUMBER)

Step 1: We need one source and three target instance.

Note: Your target table must contain a primary key on DWID. If not at table level create it at Informatica Level.
Creating Primary key at Informatica Level
Target Designer-->Right Click on your table-->Edit-->Column Tab ( In the right corner you will see NOT A KEY select PRIMARY KEY for DWID column from drop down list)

Step 2: 
Create Lookup and follow steps below...
A) Drag and drop EMPNO, SAL from source Qualifier to Lookup.

B) Delete Ports/Uncheck all ports as Lookup Port except EMPNO, SAL, DWID

C) Lookup-->Right Click-->Edit-->Condition Tab
     Lookup Table Column   Operator  Transformation Port
                 EMPNO                =              EMPNO1

D) Lookup-->Right Click-->Edit--> Properties Tab
     Dynamic Lookup Cache (Check)
     Insert Else Update           (Check)
     Lookup SQL Override:
     SELECT SCD2.SAL as SAL, SCD2.DWID as DWID, SCD2.EMPNO as EMPNO FROM SCD2 WHERE SCD2.VERSION=1 (Challenge 1 accomplished)

E)  Lookup-->Right Click-->Edit--> PORT Tab
      Select Associated Expression from drop down list.
      Port Name         Associated Expression
      EMPNO             EMPNO1
      SAL                    SAL1
      DWID   (Change Data Type = Integer) From drop down list select Sequence-ID (Challenge 2 accomplish)

Step 3
Create Two Ports
New_Version (Output Port) = 1
Old_Version (Output Port) = 0

Note: While connecting ports to expression take all ports from Source Qualifier and take NewLookupRow, empno, DWID, SAL from Lookup.

Step 4
Create two Groups
New_Record: NewLookupRow=1
Update_Record: NewLookupRow=2

Step 5
Sequence Generator
Create Sequence Generator
(No need to change any default properties of Sequence Generator)

Step 6
Connect New_Record group ports with New_Record port to First Instance of target
Connect NEXTVAL port from Sequence Generator to DWID column of target.

Step 7
Connect Update_Record group ports with New_Record port to Second Instance of target
Connect NEXTVAL port from Sequence Generator to DWID column of target.

Step 8
Update Startegy
Create Update Strategy
Drag and Drop DWID and Old_Version port from Router Update_Record group to update strategy.
Develop Update Strategy Expression in Properties tab (DD_UPDATE)

Step 9
Connect DWID to DWID and Old_Version to Version column in Third Instance of Target.

Step 10
Create Session
Session-->Right Click-->Edit-->Properties Tab-->set
Treat Source Rows as  DATA DRIVEN

Step 11
Create workflow and Run it :-)


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.



  1. hai haresh ...

    i am narayana .i am trying this scenario but i didn't get output.

    plz explain below things

    1. had u implement version or flag?
    2.if it is scd type2 version , 8th point onwords i didn,t understand ur explanation.
    3.version is possiable or not.if possiable explain to me.

    i am waiting for ur replay.
    thank you

    1. Hi, Narayana

      Yes, we are maintaining Version. Kindly see my target table metadata in beginning.
      Step 8 onward we are developing logic to maintain version only. I will suggest one thing.. Please instead of reading do it practically try to implement in your system. It would be more clear by that way... Only reading always leads to confusion because it difficult to retain all previous steps to mind while moving forward.

      If you are still getting error feel free to drop me a mail at or If you want mapping .xml file then also you are welcome.


  2. Hi Harsh

    I did not understand from step5 onwards. When u have a sequence_ID on DWID columns then why do u need new sequence gen again? Also what is New_record port of first instance of target? I understand then we have to connect Insert_group ports(empno,ename etc) to first instance of target table.. Kindly explain..


  3. Hi Harsh,

    I want to know why we use primary key in target at informatica level. Can't we do without primary key?


  4. Here you are keeping old version as 0 and new version as 1, Not like 1,2,3 write?