Friday, April 5, 2013

Update Strategy Transformation

Update Strategy is an Active and Connected Transformation.

The Mapping still now we have seen are only insert the records/rows into target table. But if you want to update, delete or reject the records/rows that are coming from source exist in target table.

When you design a data warehouse, you need to decide what type of information to store in targets. As part of the target table design, you need to determine whether to maintain all the historic data or just the most recent changes.

Example:
We have a target table T_CUSTOMERS that contains customer data. When a customer address changes you may want to update the address or keep both the old and the new address in the table. In case you update the new address with old address the target table will have always current history or data. In case if you want to maintain both new and old address, we would create a new row containing the updated address and preserve the original row with the old customer address into target table.

The model you choose determines how you handle changes to existing rows.
In PowerCenter, you set the update strategy at two different levels:
  • Within a session. When you configure a session, you can instruct the Integration Service to either treat all rows in the same way (for example, treat all rows as inserts/update/delete/data driven), or use instructions coded into the session mapping to flag rows for different database operations.
  • Within a mapping. Within a mapping, you use the Update Strategy transformation to flag rows for insert, delete, update, or reject.
Note: You can also use the Custom transformation to flag rows for insert, delete, update, or reject.

Flagging Rows Within a Mapping
For the greatest degree of control over the update strategy, you add Update Strategy transformations to a mapping. The most important feature of this transformation is its update strategy expression, used to flag individual rows for insert, delete, update, or reject.

The following table lists the constants for each database operation and their numeric equivalent:
Operation
Constant
Numeric Value 
Insert
DD_INSERT
0
Update
DD_UPDATE
1
Delete
DD_DELETE
2
Reject
DD_REJECT
3

The Integration Service treats any other value as an insert.

Forwarding Rejected Rows
You can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. By default, the Integration Service forwards rejected rows to the next transformation. The Integration Service flags the rows for reject and writes them to the session reject file. If you do not select Forward Rejected Rows, the Integration Service drops rejected rows and writes them to the session log file.

If you enable row error handling, the Integration Service writes the rejected rows and the dropped rows to the row error logs. It does not generate a reject file. If you want to write the dropped rows to the session log in addition to the row error logs, you can enable verbose data tracing.

Specifying an Operation for All Rows
When you configure a session, you can select a single database operation for all rows using the Treat Source Rows As setting.

The following table displays the options for the Treat Source Rows As setting:
Setting
Description
Insert
Treat all rows as inserts.
Note: The row violates a primary or foreign key constraint in the database, the Integration Service rejects the row.
Delete
Treat all rows as deletes. For each row, if the Integration Service finds a corresponding row in the target table (based on the primary key value), the Integration Service deletes it.
Note: The primary key constraint must exist in the target definition in the repository.
Update
Treat all rows as updates. For each row, the Integration Service looks for a matching primary key value in the target table. If it exists, the Integration Service updates the row.
Note: The primary key constraint must exist in the target definition.
Data Driven
Integration Service follows instructions coded into Update Strategy and Custom transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject.
If the mapping for the session contains an Update Strategy transformation, this field is marked Data Driven by default.
If you do not choose Data Driven when a mapping contains an Update Strategy or Custom transformation, the Workflow Manager displays a warning. When you run the session, the Integration Service does not follow instructions in the Update Strategy or Custom transformation in the mapping to determine how to flag rows.

Specifying Operations for Individual Target Tables

Once you determine how to treat all rows in the session, you also need to set update strategy options for individual targets. Define the update strategy options in the Transformations view on Mapping tab of the session properties.

You can set the following update strategy options:
  • Insert: Select this option to insert a row into a target table.
  • Delete: Select this option to delete a row from a table.
  • Update: You have the following options in this situation:
o    Update as Update: Update each row flagged for update if it exists in the target table.
o    Update as Insert: Insert each row flagged for update.
o    Update else Insert. Update the row if it exists. Otherwise, insert it.
  • Truncate table: Select this option to truncate the target table before loading data.
Sample Mapping:
1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer
3. Select Mappings --> Create.

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_dept_update
4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Table definitions.
Note:
a) To update target table 1st we need to look up on target table and then we need to check whether that record exist in target or not, based on that only will be routing records using router for insert/update.

5) Please follow the link to Create/Import target Lookup.
Drag required ports from 'SQ_EMP_DEPT' Source Qualifier to 'LKP_Employee_Dept' Lookup as below.
Select 'LKP_Employee_Dept' lookup in workspace and edit it by right click --> go to Condition tab and defined lookup condition.
Click on 'Apply' and 'OK'

6) Please follow the link to create expression transformation.
Drag required port from 'LKP_Employee_Dept' lookup to 'EXP_Employee_Dept_Flag' expression transformation.
Select 'EXP_Employee_Dept_Flag' expression in workspace and edit it by right click --> go to Ports tab and Add a port by Name 'Flag' as below.
Click 'OK' to close Expression Edit.
Click on 'Apply' and 'OK'

Note: Here we are validating the Lookup values is Null or Not based on the we will flag the input records for 'Insert' or 'Update' target table.

7) Please follow the link to create router transformation.
Drag required port from 'EXP_Employee_Dept_Flag' expression to 'RTR_Employee_Dept' Router transformation to route 'Insert/Update' records flaged.
Select 'RTR_Employee_Dept' router in workspace and edit it by right click --> go to Group tab and Add two Groups 'Insert' and 'Update' as below.
Click 'Insert Group' on Open Browse for Expression Editor as below and defined condition Flag='I' for insert
Click on 'Update Group' on Open Browse for Expression Editor as below and defined condition Flag='U' for update
Click on 'OK' to close Expression Edit.
Click on 'Apply' and 'OK'

8). Select 'Transformation' from Menu --> Create
a) That will appear you 'Select the transformation type to create:'
b) Select ' Update Strategy' from drop down and 'Enter a new name for this transformation:' as "upd_Employee_Dept"
c) Click 'Create' and 'Done'

or
 a) Click on Update Strategy Transformation icon marked below in below snapshot.
b) Click in the workspace in Mapping Designer.
c) Select UPDTRANS in workspace and Right Click --> Edit.
d) In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "upd_Employee_Dept"
e) Click on 'OK'
f) Click on 'Apply' and 'OK'.
9) Take another copy of target instance, one for insert' Employee_Dept_Ins' and other for update ' Employee_Dept_Upd'
10) Drag required columns from 'Insert Group' in Router transformation to 'Employee_Dept_Ins' target instance.
11) Drag required columns from 'Update Group' in Router transformation to 'upd_Employee_Dept' transformation.
a) Select 'upd_Employee_Dept' Update Strategy transformation in workspace and right click 'Edit'.
b) Go to 'Properties' tab.
c) Click on 'Open Browser' for Expression Editor.
entry: dd_update
Note: See that 'Forward Rejected Rows' is checked. So that Forward Rejected Rows to Next Transformation/Written to Rejected/Bad File. If not it will write it to session log file.

d) click on 'OK', Click on 'Apply' and Click on 'OK'.

12) Drag required columns to update from 'upd_Employee_Dept' Update Strategy Transformation to 'Employee_Dept_Upd' target instance for update.

13) Select 'Mapping' from Menu --> Validate.
14) Select 'Repository' from Menu --> Save.

30 comments:

  1. Great work, thanks

    ReplyDelete
  2. Thank you so much, this is great help :)

    ReplyDelete
  3. awesome..thank you so much bro....u Rocked.!!

    ReplyDelete
  4. Great Job...!! Very very Useful...!! Thank you for such a nice explanation..!!

    ReplyDelete
  5. Chitaka gottav anna...

    ReplyDelete
  6. Thanks for such a Great help !! :-) :-)

    ReplyDelete
  7. thank you so much for such a wonderful information!!

    ReplyDelete
  8. this is seriously great work...

    ReplyDelete
  9. Super !!!! Neat expalnation

    ReplyDelete
  10. great one thank you :)

    ReplyDelete
  11. nice explanation ,really helpful to freshers

    ReplyDelete
  12. Thank u...... good expltn...

    ReplyDelete
    Replies
    1. good work better to add some more real time examples

      Delete
  13. Great man , I have 5 years of data modelling experience and I have just started to learn Informatica , this was greatly useful.

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Hi can u give some real time sceniors on scd. It will be so helpful.. thanks in advance...

    ReplyDelete
  16. i gone through many article but this one is the outstanding one...update strategy explained in SCD is wonderful way..Thanks dude..no words :)

    ReplyDelete
  17. Thank you so much for this explanation..:)

    ReplyDelete
  18. Explanation is nice.I think this example we can solve without filter and router transformations.

    ReplyDelete
  19. Nice Examples and easy to learn . Thank you very much for your great work.

    ReplyDelete
  20. Great post!!! But I want to mention one thing...Instead of using Router and expression can't we directly use update strategy and USE IFF(ISNULL(EMPNO),DD_INSERT,DD_UPDate) as acondition in it?

    ReplyDelete
    Replies
    1. Yes using the Expression is the best way of doing it. It will reduce the risk of table locking by Router loading data simultaneously in same table.

      Delete
    2. But in this way you will not be able to identify the number of inserted rows and number of updated rows separately for audit purpose also you will not be able to populate the columns like last updated date and last insert date.

      Delete
  21. Hi i have to inset update in same table how to do that

    ReplyDelete
  22. Hi i have to inset update in same table how to do that

    ReplyDelete