Lookup Transformation
Ø A
Lookup transformation returns values from a database table or a flat file
associated with a given input value
Ø The
Integration Service queries the lookup source based on the lookup ports in the
transformation and a lookup condition
Ø Lookup
transformation can be used in below scenarios:
·
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.
·
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.
Ø Lookup
can be defined on a Relational tables or a Flat files.
Ø
Cached
or un-cached lookup.
·
Cache the lookup source
to improve performance.
·
If you cache the lookup
source, you can 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 Integration Service inserts or updates rows in the cache.
·
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. The Lookup transformation marks
rows to insert or update the target.
Ø
A
Lookup transformation can be defined as Connected or unconnected lookup.
·
A
connected Lookup transformation receives source data, performs a
lookup, and returns data to the pipeline.
·
An
unconnected Lookup transformation is not connected to a source or
target. A transformation in the pipeline calls the Lookup transformation with a
:LKP expression. The unconnected Lookup transformation returns one column to
the calling transformation
|
Unconnected
Lookup
|
|
|
Receives input values directly from the
pipeline.
|
Receives input values from the result
of a :LKP expression in another transformation.
|
|
Use a dynamic or static cache.
|
Use a static cache.
|
|
Cache includes the lookup source
columns in the lookup condition and the lookup source columns that are output
ports.
|
Cache includes all lookup and output
ports in the lookup condition and the lookup/return port.
|
|
Returns multiple columns from the same
row or insert into the dynamic lookup cache.
|
Returns one column from each row to a
return port.
|
|
If there is no match for the lookup
condition, the Integration Service returns the default value for all output
ports. If you configure dynamic caching, the Integration Service inserts rows
into the cache or leaves it unchanged.
|
If there is no match for the lookup
condition, the Integration Service returns NULL.
|
|
If there is a match for the lookup
condition, the Integration Service returns the result of the lookup condition
for all lookup/output ports. If you configure dynamic caching, the
Integration Service either updates the row the in the cache or leaves the row
unchanged.
|
If a match occurs for the lookup
condition, the Integration Service returns the result of the lookup condition
to the return port.
|
|
Passes multiple output values to
another transformation. Link lookup/output ports to another transformation.
|
Returns one output value to another
transformation. The Lookup transformation return port passes the value to the
port that contains the :LKP expression in the other transformation.
|
|
Supports user-defined default values.
|
Does not support user-defined default
values.
|
Ø By
default a Lookup creates a static cache
and contents of a static cache does not change during the session. You cannot
insert new data or update data for existing record in static cache.
Ø With a
dynamic cache, the Integration
Service inserts or updates rows in the cache.
Ø ORDER
BY Clause in Lookup: By default, the Integration Service generates an ORDER BY
clause for a cached lookup. The ORDER BY clause contains all lookup condition
ports. The order by clause of the lookup override should be on the order of
ports used in lookup conditions. To increase performance, you can suppress the
default ORDER BY clause and enter an override ORDER BY with fewer columns.
Ø
By default a lookup transformation
performs a left outer join between input data from source and lookup source
data.
Ø
The operations which can done in joiner
transformation are Equi joins(Inner & Outer joins) where as a Lookup transformation
additionally supports Non-Equi joins( range searches like <, <= , >, >=
and !=)
Ø
Every operation implemented using Joiner
transformation can be implemented using Lookup transformation but the reverse
it not true.