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.
- Use the operational source table ITEMS to build a new Data Mart table, CURRENT_ITEMS, which will contain only current selling items.
- Create an Unconnected Lookup transformation object to match source items against current items in the Data Mart.
- 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.
- 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.
I. Analyze the source files
- 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
- Use the Warehouse Designer to create an automatic target definition named Tgt_CurrentItems_x using the ITEMS source definition.
- 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
- Use the Mapping Designer to create a mapping called M_CurrentItems_x. Drag source and target into the designer workspace.
- Create an Unconnected Lookup transformation to match ITEMS.ITEM_ID against Tgt_CurrentItems_x.ITEM_ID.
- Click on the Target button to select the Lookup table Tgt_CurrentItems_x . Click OK.
- Double-click on the Lookup and rename it LKP_CURRENT_ITEMS_x.
- Click the Ports tab.
- Add a new input port, ITEM_ID_IN, with the same data type as ITEM_ID.
- Make ITEM_ID the R port. The ports should appear as shown below
- Click the Properties tab.
- Verify that the database connection is set to the correct target database string. For example, $Target.
- Click the Condition tab.
- Click on the icon.
- Add the Lookup condition: ITEM_ID = ITEM_ID_IN.
- Click OK to save changes and close the Lookup transformation.
IV. Create an Update Strategy transformation
- Drag all ports from Source Qualifier into Update Strategy transformation.
- 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.
- 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
V. Load the target
- Use the Workflow Manager to create a Workflow wf_CurrentItems_x
- Session Task s_CurrentItems_x based on the M_CurrentItems_x mapping.
- Run and monitor the Workflow.
VI. Verify the results
- Using a SQL query tool, connect to the target database and verify that the CURRENT_ITEMS table now contains data.
- 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.
SELECT * FROM TGT_CURRENTITEMS_X;
The data returned from the above statement should be similar to this:
Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise.