[Informatica] Lookup Transformation

Lookup Transformation

::Transformation type::
Connected / Unconnected

Lookup Transformation is used to Lookup/find data from a relational table or a flat file based on lookup keys. The PowerCenter looks-up the source file or table to find a match for based on lookup condition, override query and lookup key ports.
The PowerCenter caches the whole table whenever a Lookup is used.
Therefore, lookups must be used sparingly. Unnecessary and superflous use of Lookups can cause performance issues. Lookups are used in below cases.
-To find a related value from table or a file. Suppose you have Employee number with you and you wish to find the Name of the Employee from Employee_Details table. You can Lookup the Employee_Detail table with Employee Number as the Lookup key.
-To update Slowly changing dimensions. One can determine the need of updation of the target table by determining if a particular data is already present in the target by using a Lookup on the Target.

-Connected lookups receive input values directly from the pipeline. They can return multiple columns at a time.
-Unconnected Lookup receive input values from the result of a :LKP expression in another transformation. They return only a single column per row. The PowerCenter passes the return vale of the Lookup to the :LKP expression.

-One can specify a Lookup override to minimize the number of rows extracted from the source. This improves the performance.
-Lookup  policy on multiple macth determines what happens when the lookup transformation finds multiple matching values in source. Remember that Lookup can only return a single value. Therefore we can configure the lookup to use the FIRST returned value or use ANY value in case of multiple matches. Alternatively, you can report an error as well.
-Minimum of one lookup port / key needs to be specify in order to enable the PowerCenter Lookup the Source.
-When overriding the default lookup query, one must comment the default ORDER BY caluse which is auto-appended at run-time. Otherwise the session fails.
-The order of the ports in the override query must match the order of ports in Lookup Transformation.
-The order of the parameters while calling an Unconnected Lookup must match the order of ports in Lookup Transformation.

::Calling Unconnected Lookups::
-Unconnected Lookups are called from ports in Expression Transformation using a :LKP expression. The following syntax is used to call a Unconnected Lookup from a Expression.

-If a NULL value is passed from the Lookup input port as a key, the PowerCenter evaluates it to NULL and returns NULL.
-Informatica v9.01 onwards, Lookup becomes a Active transformation. It can return multiple rows on a match. In older version, Informatica has a option to return the first values or return any value in case of the Lookup key match.