Lookup Transformation in Informatica
Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data.
It is a kind of join operation in which one of the joining tables is the source data, and the other joining table is the lookup table.
The Lookup transformation is used to retrieve data based on a specified lookup condition. For example, we can use a Lookup transformation to retrieve values from a database table for codes used in source data.
When a mapping task includes a Lookup transformation, then the task queries the lookup source based on the lookup fields and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation.
We can configure the Lookup transformation to return a single row or multiple rows. This is the passive transformation which allows performing the lookup on the flat files, relational table, views, and synonyms.
When we configure the Lookup transformation to return multiple rows, the Lookup transformation is an active transformation. The lookup transformation supports horizontal merging, such as equijoin and nonequijoin.
When the mapping contains the work up transformation, the integration service queries the lock up data and compares it with lookup input port values.
The lookup transformation is created with the following type of ports, such as:
- Input port (I)
- Output port (O)
- Look up Ports (L)
- Return Port (R)
Perform the following tasks using a Lookup transformation, such as:
- Get a related value: Retrieve a value from the lookup table on the basis of a value in the source. For example, the source has a student rollno. Retrieve the student name from the lookup table.
- Get multiple values: Retrieve the multiple rows from a lookup table. For example, return all students in a class.
- Perform a calculation: Retrieve any value from a lookup table and use it in a calculation. For example, retrieve the marks, calculate the percentage, and return the percentage to a target.
- Update slowly changing dimension tables: Determine the rows that exist in the target.
Configure the Lookup Transformation
Configure the Lookup transformation to perform the different types of lookups, such as:
- Relational or flat-file lookup: Perform a lookup on a flat file or a relational table. When we create a Lookup transformation by using a relational table as the lookup source, we can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.
When we create a Lookup transformation by using a flat-file as a lookup source, the Designer invokes the Flat-file Wizard. - Pipeline lookup: Perform a lookup on the application sources such as JMS or MSMQ. Drag the source into the mapping and associate the Lookup transformation with the source qualifier. When the Integration Service retrieves source data for the lookup cache then configure the partitions to improve performance.
- Connected or unconnected lookup: A connected Lookup transformation receives source data, performs a lookup, and returns data to the pipeline. Or the unconnected Lookup transformation is not connected to a target.
A transformation in the pipeline calls the Lookup transformation with a :LKP expression. The unconnected Lookup transformation returns one column to the calling transformation. - Cached or uncached lookup: Cache the lookup source to improve performance. We can use static or dynamic cache for caching the lookup source.
By default, the lookup cache remains static and does not change during the session. With a dynamic cache, the Integration Service inserts or updates rows in the cache. When we cache the target table as the lookup source, we can look up values in the cache to determine if the values exist in the target. The Lookup transformation marks rows to insert or update the target.