Use a Lookup transformation to retrieve data based on a specified lookup condition. For example, you 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, 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. You can configure the Lookup transformation to return a single row or multiple rows. When you configure the Lookup transformation to return a single row, the Lookup transformation is a passive transformation. When you configure the Lookup transformation to return multiple rows, the Lookup transformation is an active transformation. You can use multiple Lookup transformations in a mapping.
Perform the following tasks with a Lookup transformation:
•Get a related value. Retrieve a value from the lookup table based on a value in the source. For example, the source has an employee ID. Retrieve the employee name from the lookup table.
•Get multiple values. Retrieve multiple rows from a lookup table. For example, return all employees in a department.
•Update slowly changing dimension tables. Determine whether rows exist in a target.
You can perform the following types of lookups:
Connected or unconnected lookup
A connected Lookup transformation receives source data, performs a lookup, and returns data.
An unconnected Lookup transformation is not connected to a source or target. A transformation calls the Lookup transformation with a lookup expression. The unconnected Lookup transformation returns one column to the calling transformation.
Cached or uncached lookup
Cache the lookup source to optimize performance. If you cache the lookup source, you can use a static or dynamic cache. You can also use a persistent or non-persistent cache.
By default, the lookup cache remains static and does not change as the mapping task runs. With a dynamic cache, the task inserts or updates rows in the cache as the target table changes. When you cache the target table as the lookup source, you can look up values in the cache to determine if the values exist in the target.
By default, the lookup cache is also non-persistent. Therefore, Data Integration deletes the cache files after the mapping task completes. If the lookup table does not change between mapping runs, you can use a persistent cache to increase performance.