Data Manipulation Using Update Strategy in Informatica PowerCenter

  Level2 Training , Transformations

It is obvious that we need data manipulation such as Insert, Update and Delete in an ETL job, Informatica PowerCenter provides Update Strategy transformation to handle any such data manipulation operations. Lets understand Update Strategy Transformation in detail.

Lets consider a real time scenario for the demonstration.

The operational source system that supplies data to your data mart tracks all items that your company has ever sold, even if they have since been discontinued. Your Sales Department wants to run queries against a Data Mart table that contains only currently selling items. They don’t want to use views or SQL, and they want this table updated on a regular basis.

Solution

1. Use the operational source table ITEMS to build a new Data Mart table, CURRENT_ITEMS, which will contain only current selling items.
2. Create an Unconnected Lookup transformation object to match source items against current items in the Data Mart.
3. Create an Update Strategy transformation to test the result of the lookup and determine the appropriate row action to take on the first and subsequent runs of the session.
4. New current items will be inserted, discontinued items will be rejected, current items already in the target will be updated, and current items already in the target but discontinued since the last session run will be deleted.

Mapping Layout

I. Analyze the source files

1. Use the Source Analyzer to analyze the ITEMS table from the operational source database. If the source table has already been imported and analyzed, it is not necessary to reanalyze it.

II. Design the target schema

1. Use the Warehouse Designer to create an automatic target definition named Tgt_CurrentItems_x using the ITEMS source definition.
2. Create the table in the target database using your student ID and password. The table should appear as below.

III. Create the Mapping and Transformations

1. Use the Mapping Designer to create a mapping called M_CurrentItems_x. Drag source and target into the designer workspace.
2. Create an Unconnected Lookup transformation to match ITEMS.ITEM_ID against Tgt_CurrentItems_x.ITEM_ID.
3. Click on the Target button to select the Lookup table Tgt_CurrentItems_x . Click OK.
4. Double-click on the Lookup and rename it LKP_CURRENT_ITEMS_x.
5. Click the Ports tab.
6. Add a new input port, ITEM_ID_IN, with the same data type as ITEM_ID.
7. Make ITEM_ID the R port. The ports should appear as shown below
8. Click the Properties tab.
9. Verify that the database connection is set to the correct target database string. For example, $Target.
10. Click the Condition tab.
11. Click on the icon.
12. Add the Lookup condition: ITEM_ID = ITEM_ID_IN.
13. Click OK to save changes and close the Lookup transformation.

IV. Create an Update Strategy transformation

1. Drag all ports from Source Qualifier into Update Strategy transformation. 
2. Test the result of the lookup and determine the appropriate row action to take on the first and subsequent runs of the session. The logic is that new current items will be inserted, discontinued items will be rejected, current items already in the target will be updated, and current items already in the target but discontinued since the last session run will be deleted.
3. The pseudo code for the logic is as follows
if (the record doesn’t exist in the target table) then
    if (the discontinued flag is not set) then INSERT else REJECT
        else if (record exists) if (the discontinued flag is not set) then UPDATE
            the record else DELETE the record

4. Create an expression for the above pseudo code and enter it in the Update Strategy expression editor. The expression will call the Unconnected Lookup transformation.

5. Completed expression will look like in the below image.





6. Map all the columns from the update strategy to the target table.

V. Load the target

1. Use the Workflow Manager to create a Workflow wf_CurrentItems_x
2. Session Task s_CurrentItems_x based on the M_CurrentItems_x mapping.
3. Run and monitor the Workflow.

VI. Verify the results

1. Using a SQL query tool, connect to the target database and verify that the CURRENT_ITEMS table now contains data.

SELECT * FROM TGT_CURRENTITEMS_X;
The data returned from the above statement should be similar to this:
2. After the session is run once the items table can be modified to simulate changes. You should run the session again to see the results of the logic code in the Update Strategy transformation.

Video Tutorial

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise.

Share with your friends