Lookup transformation in Informatica

Use a Lookup transformation in your mapping to look up data in a relational table, view, or synonym. Import a lookup definition from any relational database to which both the Informatica Client and Server can connect. You can use multiple Lookup transformations in a mapping.

The Informatica Server queries the lookup table based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup table column values based on the lookup condition. Use the result of the lookup to pass to other transformations and the target.

You can use the Lookup transformation to perform many tasks, including:

a) Get a related value:

For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data easier to read.

b) Perform a calculation:

Many normalized tables include values used in a calculation, such as gross sales per invoice or sales tax, but not the calculated value (such as net sales).

c) Update slowly changing dimension tables:

You can use a Lookup transformation to determine whether records already exist in the target.

You can configure the Lookup transformation to perform different types of lookups. You can configure the transformation to be connected or unconnected, cached or uncached:

a) Connected or unconnected:

Connected and unconnected transformations receive input and send output in different ways.

b) Cached or uncached:

Sometimes you can improve session performance by caching the lookup table. If you cache the lookup table, you can choose to use a dynamic or static cache. By default, the lookup cache remains static and does not change during the session. With a dynamic cache, the Informatica Server inserts rows into the cache during the session. Informatica recommends that you cache the target table as the lookup. This enables you to look up values in the target and insert them if they do not exist.