Lookup Transformation

LOOKUP TRANSFORMATION

a) Passive Transformation
b) Lookup be Connected or Unconnected. Dynamic lookup is connected.
c) Use a Lookup transformation in a mapping to look up data in a flat file or a relational table, view, or synonym.
d) We can import a lookup definition from any flat file or relational database to which both the PowerCenter Client and Server can connect.
e) We can use multiple Lookup transformations in a mapping.

The Integration Service queries the lookup source based on the lookup ports in the transformation 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.

Perform the following tasks with a Lookup transformation:

1. 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.
2. Get multiple values : Retrieve multiple rows from a lookup table. For example, return all employees in a department.
3. Perform a calculation : Retrieve a value from a lookup table and use it in a calculation. For example, retrieve a sales tax percentage, calculate a tax, and return the tax to a target.
4. Update slowly changing dimension tables : Determine whether rows exist in a target.

LOOKUP TYPES
We can configure the Lookup transformation to one of the following types of lookups:

1. Connected or Unconnected
2. Relational or Flat File
3. Cached or Un Cached
4. Pipeline Lookup

1. Connected or Unconnected : In Connected Lookup port are connected to transformation in the pipeline. Unconnected Lookup ports are not connected to transformations in pipeline and result is send through as a result of :LKP expression.
2. Relational Lookup: When we create a Lookup transformation using a relational table as a lookup source then the resulting lookup is relational lookup. We can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.
a) We can override the default SQL statement if we want to add a WHERE clause or query multiple tables.
b) We can use a dynamic lookup cache with relational lookups.
3. Flat File Lookup: When we use a flat file for a lookup source, we can use any flat file definition in the repository, or we can import it. When we import a flat file lookup source, the Designer invokes the Flat File Wizard.
4. Cached or Un-cached Lookup: We can check the option in Properties Tab to Cache to lookup or not. By default, lookup is cached.
5. Pipeline Lookup : When we use SAP application as lookup source in that case we use pipeline lookup.

Difference Between Connected and Unconnected Lookup

Connected Lookup	Unconnected Lookup
Receives input values directly from the pipeline.	Receives input values from the result of a :LKP expression in another transformation.
We can use a dynamic or static cache.	We can use a static cache.
Cache includes all lookup columns used in the mapping.	Cache includes all lookup/output ports in the lookup condition and the lookup/return port.
If there is no match for the lookup condition, the Power Center Server returns the default value for all output ports.	If there is no match for the lookup condition, the Power Center Server returns NULL.
If there is a match for the lookup condition, the Power Center Server returns the result of the lookup condition for all lookup/output ports.	If there is a match for the lookup condition,the Power Center Server returns the result of the lookup condition into the return port.
Pass multiple output values to another transformation.	Pass one output value to another transformation.
Supports user-defined default values	Does not support user-defined default values.

Lookup Components
Define the following components when you configure a Lookup transformation in a mapping:
1. Lookup source
2. Ports
3. Properties
4. Condition

1. Lookup Source
Use a flat file, relational table, or source qualifier for a lookup source. When you create a Lookup transformation,
you can create the lookup source from the following locations:
- Relational source or target definition in the repository
- Flat file source or target definition in the repository
- Table or file that the Integration Service and PowerCenter Client machine can connect to
- Source qualifier definition in a mapping

2. Lookup Ports

Ports	Lookup Type	Number Needed	Description
I	Connected Unconnected	Minimum 1	Input port to Lookup. Usually ports used for Join condition are Input ports.
O	Connected Unconnected	Minimum 1	Ports going to another transformation from Lookup.
L	Connected Unconnected	Minimum 1	Lookup port. The Designer automatically Designates each column in the lookup source as a lookup (L) and output port (O).
R	Unconnected	1 Only	Return port. Use only in unconnected Lookup t/f only.

3. Lookup Properties
Configure the lookup properties such as caching and multiple matches on the Lookup Properties tab. Configure the lookup condition or the SQL statements to query the lookup table. You can also change the Lookup table name.

Options	Lookup Type	Description
Lookup SQL Override	Relational	Overrides the default SQL statement to query the lookup table.
Lookup Table Name	Relational	Specifies the name of the table from which the transformation looks up and caches values.
Lookup Caching Enabled	Flat File, Relational	Indicates whether the Power Center Server caches lookup values during the session.
Lookup Policy on Multiple Match	Flat File, Relational	Determines what happens when the Lookup transformation finds multiple rows that match the lookup condition. Options: Use First Value or Use Last Value or Use Any Value or Report Error
Lookup Condition	Flat File, Relational	Displays the lookup condition you set in the Condition tab.
Connection Information	Relational	Specifies the database containing the lookup table.
Source Type	Flat File, Relational	Lookup is from a database or flat file.
Lookup Cache Directory Name	Flat File, Relational	Location where cache is build.
Lookup Cache Persistent	Flat File, Relational	Whether to use Persistent Cache or not.
Dynamic Lookup Cache	Flat File, Relational	Whether to use Dynamic Cache or not.
Recache From Lookup Source	Flat File, Relational	To rebuild cache if cache source changes and we are using Persistent Cache.
Insert Else Update	Relational	Use only with dynamic caching enabled. Applies to rows entering the Lookup transformation with the row type of insert.
Lookup Data Cache Size	Flat File, Relational	Data Cache Size
Lookup Index Cache Size	Flat File, Relational	Index Cache Size
Cache File Name Prefix	Flat File, Relational	Use only with persistent lookup cache. Specifies the file name prefix to use with persistent lookup cache files.

4. Lookup Condition

The Integration Service finds data in the lookup source with a lookup condition. The lookup condition is similar to the WHERE clause in an SQL query. When you configure a lookup condition in a Lookup transformation, you can compare the value of one or more columns in the source data with values in the lookup source or cache.

For example, the source data contains an employee_number. The lookup source table contains employee_ID,first_name, and last_name.

You configure the following lookup condition:
employee_ID = employee_number

The Integration Service can return more than one row from the lookup source. You configure the following lookup
condition:
employee_ID > employee_number
The Integration Service returns rows for all employee_ID numbers greater than the source employee number.

Use the following guidelines when you enter a condition for a Lookup transformation:
1. The datatypes for the columns in a lookup condition must match.
2. You must enter a lookup condition in all Lookup transformations.
3. Use one input port for each lookup port in the lookup condition. Use the same input port in more than one condition in a transformation.
4. When you enter multiple conditions, the Integration Service evaluates each condition as an AND, not an OR.The Integration Service returns rows that match all the conditions you configure.
5. If you include multiple conditions, enter the conditions in the following order to optimize lookup performance:
- Equal to (=)
- Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)
- Not equal to (!=)
6. The Integration Service matches null values. For example, if an input lookup condition column is NULL, the Integration Service evaluates the NULL equal to a NULL in the lookup.
7. If you configure a flat file lookup for sorted input, the Integration Service fails the session if the condition columns are not grouped. If the columns are grouped, but not sorted, the Integration Service processes the lookup as if you did not configure sorted input.

Types of Lookup Caches:

1. Static Cache : By default, the IS creates a static cache. It caches the lookup file or table and Looks up values in the cache for each row that comes into the transformation.The IS does not update the cache while it processes the Lookup transformation.
2. Dynamic Cache : To cache a target table or flat file source and insert new rows or update existing rows in the cache, use a Lookup transformation with a dynamic cache.The IS dynamically inserts or updates data in the lookup cache and passes data to the target. Target table is also our lookup table. No good for performance if table is huge.
3. Persistent Cache : If the lookup table does not change between sessions, we can configure the Lookup transformation to use a persistent lookup cache.The IS saves and reuses cache files from session to session, eliminating the time Required to read the lookup table.
4. Re-cache from Source : If the persistent cache is not synchronized with the lookup table, we can Configure the Lookup transformation to rebuild the lookup cache.If Lookup table has changed, we can use this to rebuild the lookup cache.
5. Shared Cache :
•     Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the IS shares the cache by default. You can only share static unnamed caches.
•     Named cache: Use a persistent named cache when we want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

This is just a Overview of lookup Transformation I will soon write for lookup mapping and other detailed information.