Lookup Transformation is used to lookup the data in flat file, relational table, views. TheIntegration service query‘s the lookup table based on the lookup ports in transformation. It works like LEFT OUTER JOIN in SQL. Lookup returns value when ever match is found and returns NULL value whenever there is no match found.Informatica Lookups can be cached or un-cached. And Cached lookup can be either static or dynamic.A static cache is one which does not modify the cache once it is built and the data remains same during the session run.On the other hand, a dynamic cache is refreshed during the session run by inserting or updating the records in cache based on the incoming source data.By default, Informatica cache is static cache.A look up cache can also be divided as persistent or non-persistent based on whetherInformatica retains the cache even after the completion of session run or deletes it.
Example 1: Connected Lookup
In this example, we understand the following:
How to configure the Connected Lookup
How to write the query in SQ and Lookup Override
Source: – INVOICE (SDBU)
Target: – ODS_CUSTOMER_ACTIVE (TDBU)
Lookup Table: – CUSTOMER (SDBU)
1. Import the Source Definition PRODUCT by the Source Analyzer from SDBU schema
2. Import the Target Definition ODS_CUSTOMER_ACTIVE by the Target Designer from TDBU schema
3. Create a Mapping ―m_ods_customer_active‖ by using the mapping designer.
Click Tools > Mapping Designer to open the Mapping Designer.
4. Drag the INVOICE Source Definitions to the mapping designer workspace.
5. Click Transformation > Create to create expression transformation. Select
Expression, enter name as EXP_SOURCE_ANCHOR
6. Drag CUST_NO column from INVOICE Source Qualifier to Expression
7. Double click on Source Qualifier Transformation SQ_INVOICE, in Edit mode Add
the sql query as below:
SELECT DISTINCT INVOICE.CUST_NO
WHERE INVOICE.CANCELED=0 AND INVOICE.DATE_CLOSED IS NOT NULL;
Click Transformation > Create to create Lookup transformation. Select Lookup, enter name as LKP_CUSTOMER
Link the CUST_NO from EXP_SOURCE_ANCHOR to LKP_CUSTOMER
10. Double click on the header of lookup, in condition tab, add a new condition
11. Also, add lookup sql override in the properties tab.
SELECT CUSTOMER.CUST_NAME AS CUST_NAME,
CUSTOMER.FIRST_CONTACT AS FIRST_CONTACT,
CUSTOMER.CUSTOMER_STATE AS CUSTOMER_STATE,
CUSTOMER.CUSTOMER_ZIP AS CUSTOMER_ZIP,
CUSTOMER.CUSTOMER_TYPE AS CUSTOMER_TYPE,
CUSTOMER.DISCOUNT_RATE AS DISCOUNT_RATE,
CUSTOMER.CUST_ID AS CUST_ID
12. Drag the Target, Link the columns from LKP_CUSTOMER to ODS_CUSTOMER_ACTIVE Click Repository > Save to save the new mapping to the repository. Validate the mapping.
13. In the Workflow Manager Navigator, create the session ―s_m_ods_customer_active‖
that uses mapping ―m_ods_customer_active‖ using the Task Developer.
Task Developer MenuTasksCreate
14. Provide the connection for Integration service to communicate with your source, Lookup and Target system
Session–>edit–>mapping Source (LS) INVOICEOLTP_SOURCE
Session–>edit–>mapping Transformations (LS) LKP_CUSTOMEROLTP_SOURCE
Session—>edit–>mapping Target (LS) ODS_CUSTOMER_ACTIVEOLAP_TDBU
15. Choose the Target load Normal Mode and check ―truncate target table‖ option
16. Create the workflow ―w_ods_customer_active‖ using the workflow designer. Assign the appropriate INT service.
17. Click Tasks > Link Task. Drag from the Start task to the Session task. Click Repository > Save to save the workflow in the repository.
18. Execute the workflow and verify the data in Target.