07

Connected Lookup Transformation

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
Transformation EXP_SOURCE_ANCHOR.
7. Double click on Source Qualifier Transformation SQ_INVOICE, in Edit mode Add
the sql query as below:
SELECT DISTINCT INVOICE.CUST_NO
FROM
INVOICE
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
CUST_ID=CUST_NO.
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
FROM CUSTOMER
WHERE CUSTOMER.CUSTOMER_TYPE=’Active’;
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.