Lookup transformation

The Lookup transformation is passive transformation.

Lookup is a transformation to look up data in a flat file or a relational table, view, or synonym.
There are two types of Lookups in Powercenter Designer, namely:

1. Connected Lookup
2. Unconnected Lookup .

We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect. We can use multiple Lookup transformations in a mapping.

Lookup transformation act as left outer joiner. If a particular null record matches found, then it returns null value.

The PowerCenter Server queries the lookup source based on the lookup ports in the transformation. It compares Lookup transformation port values to lookup source column values based on the lookup condition. Pass the result of the lookup to other transformations and a target.

For example, if we want to retrieve all the sales of a product with an ID 10 and assume that the sales data resides in another table called 'Sales'. Here instead of using the sales table as one more source, use Lookup transformation to lookup the data for the product, with ID 10 in sales table.

Different caches can also be used with lookup like static, dynamic, persistent, and shared. The dynamic cache cannot be used while creating an un-connected lookup.

We can use the Lookup transformation to perform following:

• Get a related value: EMP has DEPTNO but DNAME is not there. We use Lookup to get DNAME from DEPT table based on Lookup Condition.
• Perform a calculation: We want only those Employees who’s SAL > Average (SAL). We will write Lookup Override query.
• Update slowly changing dimension tables: Most important use. We can use a Lookup transformation to determine whether rows already exist in the target.

TIP: We use Connected Lookup when we need to return more than one column from Lookup table.