Monday 9 June 2014

Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Scenario 22: How to implement SCD1 along with delete. (Insert, Update, Delete)

Let's Chat: Normal SCD1 always implemented with only logic of Insert and Update. Means new record inserted in source then insert if any record update in source then update target. What if you have to delete your record in target if any record is delete from source.
There could be multiple approach to achieve this. Here I am opting simple approach with Joiner.

Theory: 
Now it's pretty much clear that we have to implement three operation.
New Record in Source--> Insert
Record Updated in Source-->Update
Record Deleted from Source-->Delete

Ahhh... It's too easy. :-) 

Practically:

My Source Table Name is EMP and Target Table name is EMP_SCD1

Step 1:
Drag and Drop your Source EMP.
Import your target table (EMP_SCD1) as source and drag and drop it in mapping designer.
Drop your target table three times.

Step 2:
Joiner
Drag and Drop all your ports from source 1 EMP to Joiner.
Drag and Drop EMPNO and SAL from Source 2 (EMP_SCD1) to Joiner

Note: For better understanding I have renamed EMPNO and SAL from Source 2 (EMP_SCD1) to EMPNO_TGT and SAL_TGT

Join Condition: EMPNO=EMPNO_TGT

Join Type: Full Outer Join

Step 3
Expression
Create three output ports

New_Rec (Output Port) IIF(NOT ISNULL(EMPNO) AND ISNULL(EMPNO_TGT), 1, 0)

Upd_Rec (Output Port)  IIF(NOT ISNULL(EMPNO) AND NOT ISNULL(EMPNO_TGT) AND SAL != SAL_TGT, 1, 0)

Del_Rec (Output Port)  IIF(NOT ISNULL(EMPNO_TGT) AND ISNULL(EMPNO) , 1, 0)

Step 4
Router
Create three group

Insert_Rec: New_Rec=1
Update_Rec: Upd_Rec=1
Delete_Rec: Del_Rec=1

Step 5
Connect Insert_Rec group to Target Instance 1 for Insert.

Step 6
Update Strategy
Connect EMPNO and SAL from Update_Rec to Update Strategy and from Update Strategy to Target Instance 2.

Update Strategy Expression: DD_UPDATE

Step 7
Update Strategy
Connect EMPNO_TGT from Delete_Rec group to Update Strategy and from Update Strategy to Target Instance 3.

Update Strategy Expression: DD_DELETE

Check List: 

  1. Make sure your target table should have a Primary key either Database level or Informatica Level. 
  2. Make sure Treat Source Rows as set to DATA DRIVEN at session-->Properties Tab.
  3. Make sure Load Type of last two target instance is NORMAL (Though you won't get any error for this. If it is BULK integration service will revert it NORMAL while executing.)


BINGO!!!

Isn't it too easy :-) 


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

10 comments:

  1. Hi Harsh, I am new to informatica and I would like to say thanks to ur blog which is making my learning easy..
    My qtn here is this mapping cannot update other columns in the table except salary right?
    and I tried to develop this mapping by removing expressn tr and it didnt work for upadte and delete can you tell me what could be the reason?

    ReplyDelete
  2. Yes, except salary non of the column will update because we are comparing only salary for update in expression transformation.

    It's not clear why you removed expression? Which logic you are trying to implement? If you remove expression then how it will identify records for update or Insert or delete because we have develop expression over there to identify records for each operation according to data.
    Hope it's clear now.
    Thanks.

    ReplyDelete
    Replies
    1. There is no specific reason but just tried by putting the same conditions used in expression output ports in router tr group condition,Like for insert group I put a condition as in insert o/p port of exp tr. But didn't get the expected results for update and delete for insert it worked fine.. Just wanted to know if it is not a legitimate way to put those conditions in router.
      ThankU

      Delete
    2. Got it. It will work fine if you are developing expression directly in Router. No Issue. It's not working for update and delete due to some other reason. few check list...
      1) Is the primary key set at the column based upon which you are deleting or updating?
      2) Check Data Driven is set for "Treat source row as" option at session level properties tab.
      3) Make sure you are issuing commit at database level after updating/Deleting before running your mapping.

      Let me know if still you are getting error.

      Thanks

      Delete
  3. Hii Harsh, Checked evrything, forgot to set Data driven option..but still after setting it correct the delete condition is not working. Followed evrystep as described by u. Even chekd with debugger and found the data moving to the third update strategy,which has to delete the data.. not getting any error but the rows were not getting deleted from target..
    And Thank you so much Harsh for the beautiful approach to the scenarios, hope I will learn more from you.. Keep up the good work.. :)

    Regards
    Nikhil

    ReplyDelete
  4. Harish can you help on this...what if we are using sources and targets as flatfiles,and there are multiple records, hoe can we implement the update logic there.

    ReplyDelete
  5. i mean as the flatfiles do not have any prim key how can we implement the insert,update and delete logic's in expression can you give an example

    ReplyDelete
  6. i mean as the flatfiles do not have any prim key how can we implement the insert,update and delete logic's in expression can you give an example

    ReplyDelete
  7. Hi Harsh,
    Can you explain me what if the other columns updated rather than the salary what will be code if we change other columns in the source or target table.

    ReplyDelete
  8. Hi harsh,
    Can u pls explain me wat happens if we use lookup instead of joiner
    And how this is going to differ

    ReplyDelete