LookupTransformation in IICS – IDMC

Lookup Transformation is used to Look-up on a relational table or a file and get the related values to target.

Usage:
• Lookup Transformation is used to get a related value. Retrieve a value from the lookup table based on a value in the source
• Used to get multiple values. Retrieve multiple rows from a lookup table.
• Used to update slowly changing dimension tables. Determine whether rows exist in a target.

Types of Lookup Transformation:
1. Connected Lookup
2. Un Connected lookup
3. Dynamic Lookup (Cache)

Connected Lookup Transformation:
1. By default, lookup is passive Transformation.
2. By default, Lookup will act as a Left outer join oracle.
3. To Lookup any Table/file, there should be at least one matching column between source and lookup table/file.
4. When you Run a Mapping Task, First Lookup Transformation will write a default SQL query to create a lookup cache in the log as shown in the below.

Lookup Writes a Default SQL query based on the below syntax:

Select “The columns which are going out of the lookup”, “the columns which are involving in joining condition” from “Lookup Table” order by “the Condition columns”

Example: Select Postal_code , City, Location_id, cust_id from OT_Src_Dev1.Locations_src order by location_id , cust_id

5. Lookup will Executes the above default SQL Query in the Lookup Database (Based on the Connection given for the lookup) and copy the query results to Lookup Cache.
6. The source Records will try to Match with Lookup cache records, based on the Lookup condition, If the Match is found Lookup will give related values to the next transformation/Target, If the Match is not found, Lookup will give NULL values to next transformation/Target.
7. When Source Transformation Columns and Lookup columns names are same, then you Need to take care of Filed mapping conflicts, To Resolve the Filed mapping conflicts, prefix all the columns which are in source as “SRC_” as shown in below.

Lookup as a Passive Transformation:

1. If the Lookup transformation returns a single row, then lookup will act as a passive transformation.
2. When Lookup finds Multiple matches in the lookup table, by default look will Return any row. from the multiple matches in the lookup table. In this case Lookup can send any value as an output.
   If you select Multiple matches as Return First row or Return Last Row, or Return any row, lookup will act as a passive transformation.

Lookup as an Active Transformation:

1. If the Lookup transformation returns multiple rows, then lookup will act as an Active transformation.
2. When you Select Multiple matches as Return all rows, lookup will return multiple rows for each multiple match record, hence in this case Lookup will act as an Active transformation.

Important Properties in Lookup Transformation:

There are few Lookup Properties which are important as mention below.

1. Lookup SQL Override
2. Lookup Source Filter

Lookup SQL Override:

• When Lookup is a relational Table, you can override the default SQL by using Lookup SQL override to restrict the lookup cache.
• You can Add a where condition By overriding SQL Query As shown in below.