Exercise 8: Implementing SCD1

Exercise 8: How to achieve Target below?

Source

part_number  business_unit  business_group

01yp          unassigned            undefined

02yp          unassigned            undefined

03yp          unassigned            undefined

Target

part_key   part_number  business_unit  business_group

      1           01yp          unassigned            undefined

      2           02yp          HSBC                 undefined

      3           03yp          unassigned           undefined

Note: There are some other process which also runs on same target table and update business unit according to their need. so, in target you can see for part_number 02yp business unit is HSBC. It's not processed by Informatica it's processed by some other application.

Requirement Specification:

Case 1: New Part in Source insert into Target.

Case 2: If business_unit changed in source (Other than unassigned) then only update the business unit in target.

Theory/Analysis: It's a simple requirement of SCD 1 only one thing is need to keep in mind that while updating simple comparison of Target Business_Unit and Source Business_Unit will not work here because some other process has modified this column with value. We need to include one more condition while comparing...

Practically

Step 1

Drag and Drop Source and two Target instance to mapping designer.

Step 2

Lookup

Create Lookup and follow steps below...

A) Drag and drop part_number from source Qualifier to Lookup.

B) Lookup-->Right Click-->Edit-->Condition Tab

     Lookup Table Column          Operator         Transformation Port

                 part_number                =              part_number1

Step 3

Expression

Take Business_unit, Part Key, Part_Number column from lookup and all three columns from source qualifier.

Create Two Ports

New_Record=IIF(ISNULL(PART_NUMBER_TGT), 1, 0)

Upd_Record=IIF(NOT ISNULL(PART_NUMBER_TGT) AND (LOWER(BUSINESS_UNIT)!= 'unassigned' AND (BUSINESS_UNIT_TGT != BUSINESS_UNIT)), 1, 0)

Step 4

Router

Take Part_key column of Lookup and all source column including New_Record and Upd_Record to Router.

Create Two Group

New_Rec=New_Record = 1

Upd_Rec=Upd_Record = 1

Step 5

Connect New_Record group column to first target instance.

Step 6

Sequence Generator

Connect NEXTVAL port to Part_key column of first target instance.

Step 7

Update Strategy

Connect Part_key and Business_unit column from Upd_Rec group of Router to Update Strategy.

B)

Right Click-->Edit-->Properties Tab

Update Strategy Expression = DD_UPDATE

Step 8

connect part_key and business_unit port to second target instance.

Note 1: At the session level "Treat Source rows as: DATA DRIVEN" must be set.

Note 2: At target level you must have a primary key. If not available at database level then create it Informatica level.

Limitation: Solution above will work only when unassigned value is coming in Source Business_unit Column if actual value of Business_unit is missing .

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