0

enter image description hereHow can we implement SCD 2 in informatica when no PK is defined on target table(oracle)?

Implementing SCD type 2 using Dynamic lkp concept which will tag the incoming row as 0,1,2. Ive associated the SRC ports to the lookup ports and given the conditioning by specifying the "Update Dynamic Cache Condition" ie MD5(SRC Columns)<>MD5(LKP columns) and enabling the "Output Old Value On Update" with "Insert Else Update" property SET and overriding the LKP sql.

Then in the EXP am flagging the incoming record whether to insert/update using which in Router we will define the grps.

From Router INSERT GRP(FLAG = 'INSERT' or FLAG = 'UPDATE') , 1 target instance for new insert. UPDATE GRP(FLAG = 'UPDATE') connecting to EXP where i tag the old record status as 'N' and doing the Update Override at the second Target instance since no PK defined am doing this.

Every thing is working fine while i check in debugger mode to check the flow , But when there is an updated record old record gets tagged with status 'N' and new record gets inserted in the first pipeline (first target instance) while old record when it flows from UPDATE grp of router its getting updated with the Status as N and end date am making SYSDATE-1 its working in the Expression but its again getting INSERTED in the second target instance which should get updated with status as N and end date(sysdate-1).But its getting inserted with Status Y and end date with defaukt date Can any one help me to figure whats going wrong here?

Note : in the Session level , for TARGET instance 1 its INSERT and for second instance where update should happen its "UPDATE as UPDATE"

Also tried by keeping the Update Strategy in the second pipe keeping DD_UPDATE and treat source as DATA DRIVEN at the session by specifying the PK at the target designer (Infa level not the db level) but No luck

Bastian
  • 129
  • 3
  • 13

1 Answers1

1

Steps

  1. While performing SCD, you first have to identity the history records and you have to first update the history records and then insert the new records

  2. You have to use target load plan and set, first your update flow should apply and then insert operation should happen. You will have target load plan in mapping tab

enter image description here

  1. In session task, in properties tab, change Treat source row as to Data driven

enter image description here

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • Could you plz share the Mapping Structure (note that there is no PK defined on the target table) which your are highlighting too that would be helpful – Bastian Jun 01 '20 at 11:06
  • Your mapping screen shot shows there are primary keys in your target table – Jim Macaulay Jun 01 '20 at 11:09
  • You have to use data driven options in session level. As mentioned in the second screen shot. Also can you show what expression are you using in update strategy – Jim Macaulay Jun 01 '20 at 11:15
  • I have defined the PKs at the Target designer level not at the db level so that i can use update strategy for updating the old records by keeping the DATA DRIVEV option at the session level then also its getting insert rather than updating , In Update strategy its DD_UPDATE – Bastian Jun 01 '20 at 11:18
  • Did use target load plan option? – Jim Macaulay Jun 01 '20 at 11:18
  • I havent alter the target load plan , by default its first pipeline (first target instance for New inserts) and then the second instance for update – Bastian Jun 01 '20 at 11:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/215088/discussion-between-jim-macaulay-and-bastian). – Jim Macaulay Jun 01 '20 at 11:21