Update Strategy Transformation
Update strategy defines the sources to be flagged for insert, update, delete, and reject at the targets.
DD_INSERT – 0
DD_UPDATE – 1
DD_DELETE – 2
The update strategy works only
->when there is primary key defined in the target definition.
->When you want update the target table based on the primary key.
Example 1: How to Configure Update Strategy Transformation
Sources: – PRODUCT (SDBU)
Target: – ODS_PRODUCT (TDBU)
Lookup Table: – ODS_PRODUCT (TDBU)
1. Import the Source Definition VENDOR by the Source Analyzer from SDBU schema
2. Import the Target Definition ODS_PRODUCT by the Target Designer from TDBU schema
3. Create a Mapping ―m_ods_product‖ by using the mapping designer. Click Tools >Mapping Designer to open the Mapping Designer.
4. Drag the PRODUCT and VENDOR Source Definitions to the mapping designer workspace.
5. If created automatically, delete all of the Source Qualifier Transformations.
6. Create a new Source Qualifier transformation by selecting all three sources (PRODUCT and VENDOR)
Click Transformation > Create to create Source Qualifier transformation. Select Expression, enter name as SQ_ PRODUCT_VENDOR
7. Add the sql query in the Source qualifier
SELECT PRODUCT.PRODUCT_CODE, PRODUCT.VENDOR_ID,
8. Click Transformation > Create to create Lookup transformation. Select Lookup, enter name as LKP_ODS_PRODUCT
9. Link the PRODUCT_CODE from SQ_PRODUCT_VENDOR to LKP_ODS_PRODUCT and Rename the input column to PRODUCT_CODE_in.
10. Double click on the header of lookup, in condition tab, add a new condition PRODUCT_CODE=PRODUCT_CODE_in.
11. Remove ports VENDOR_ID, VENDOR_NAME, VENDOR_STATE,PRODUCT_NAME, CATEGORY,MODEL, PRICE and FIRST_CONTACT from LKP_ODS_PRODUCT
12. Click Transformation > Create to create Lookup transformation. Select Update Strategy, enter name as UPD_ODS_PRODUCT
13. Link the PRODUCT_CODE column from LKP_ODS_PRODUCT to UPD_ODS_PRODUCT and Rename it to PRODUCT_CODE_LKP.
14. Link the columns from SQ_LINE_ITEM to UPD_ODS_PRODUCT
15. Click on the UPD_ODS_PRODUCT Properties tab. Add the following expression:
IIF(ISNULL(PRODUCT_CODE_lkp), IIF(DISCONTINUED_FLAG = 0, DD_INSERT,
DD_REJECT), IIF(DISCONTINUED_FLAG = 0, DD_UPDATE, DD_DELETE))
16. Drag the Target, Link the columns from UPD_ODS_PRODUCT to ODS_PRODUCT Click Repository > Save to save the new mapping to the repository. Validate the mapping.
17. In the Workflow Manager Navigator, create the session ―s_m_ods_product‖ that uses mapping ―m_ods_product‖ using the Task Developer.
Task Developer ->Menu->Tasks->Create
18. Provide the connection for Integration service to communicate with your source,Lookup and Target system
Session–>edit–>mapping ->Sources (LS) -> SQ_PRODUCT_VENDOR->OLTP_SOURCE
Session–>edit–>mapping ->Transformations (LS) -> LKP_ODS_PRODUCT-> OLAP_TDBU
Session—>edit–>mapping -> Target (LS) ->ODS_PRODUCT->OLAP_TDBU
19. Create the workflow ―w_ods_product‖ using the workflow designer. Assign the appropriate INT service.
20. Click Tasks > Link Task. Drag from the Start task to the Session task. Click Repository > Save to save the workflow in the repository.
21. Execute the workflow and verify the data in Target.
Example 2: Update else insert option at session level
Update else Insert, It will write a ‗select ‗statement on the target table and will compare withsource. Accordingly if the record already exists it will do an update else it will insert. On the Other hand the update strategy the operations will be done at the Informatica level itself.
Example 3: Update Target table without using Primary Key
If you want to update the target table by a matching column other than the primary key. In this case the update strategy will not work. Informatica provides feature, “Target Update Override”, to update even on the columns that are not primary key.You can find the Target Update Override option in the target definition properties tab. The syntax of update statement to be specified in Target Update Override is
SET TARGET_COLUMN1 = :TU.TARGET_PORT1,
[Additional update columns]
WHERE TARGET_COLUMN = :TU.TARGET_PORT
AND [Additional conditions]
Here TU means target update and used to specify the target ports.