Lookup
Transformation Overview
(excerpt from informatica 9.1 reference material)
Use a
Lookup transformation in a mapping to look up data in a flat file, relational
table, view, or synonym. You can
import a
lookup definition from any flat file or relational database to which both the
PowerCenter Client and
Integration
Service can connect. You can also create a lookup definition from a source
qualifier. You 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:
¨ 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.
¨ Update slowly changing dimension tables. Determine whether rows exist in a target.
Configure
the Lookup transformation to perform the following types of lookups:
¨ Relational or flat file lookup. Perform a
lookup on a flat file or a relational table. When you create a Lookup
transformation
using a relational table as the lookup source, you can connect to the lookup
source using ODBC
and import
the table definition as the structure for the Lookup transformation. When you
create a Lookup
transformation
using a flat file as a lookup source, the Designer invokes the Flat File
Wizard.
¨ Pipeline lookup. Perform a lookup
on application sources such as a JMS, MSMQ, or SAP. Drag the source
into the
mapping and associate the Lookup transformation with the source qualifier.
Configure partitions to
improve
performance when the Integration Service retrieves source data for the lookup
cache.
¨ 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.
¨ Cached or uncached 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.
Lookup
Caches Overview
You can
configure a Lookup transformation to cache the lookup table. The Integration
Service builds a cache in
memory when
it processes the first row of data in a cached Lookup transformation. It
allocates memory for the
cache based
on the amount you configure in the transformation or session properties. The
Integration Service
stores
condition values in the index cache and output values in the data cache. The Integration
Service queries
the cache
for each row that enters the transformation.
The
Integration Service also creates cache files by default in the $PMCacheDir. If
the data does not fit in the
memory
cache, the Integration Service stores the overflow values in the cache files.
When the session completes,
the
Integration Service releases cache memory and deletes the cache files unless
you configure the Lookup
transformation
to use a persistent cache.
If you use
a flat file or pipeline lookup, the Integration Service always caches the
lookup source. If you configure a
flat file
lookup for sorted input, the Integration Service cannot cache the lookup 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.
When you
configure a lookup cache, you can configure the following cache settings:
¨ Building caches. You can configure the
session to build caches sequentially or concurrently. When you build
sequential
caches, the Integration Service creates caches as the source rows enter the
Lookup transformation.
When you
configure the session to build concurrent caches, the Integration Service does
not wait for the first
row to
enter the Lookup transformation before it creates caches. Instead, it builds
multiple caches concurrently.
¨ Persistent cache. You can save the
lookup cache files and reuse them the next time the Integration Service
processes a
Lookup transformation configured to use the cache.
¨ Recache from source. If the persistent
cache is not synchronized with the lookup table, you can configure the
Lookup
transformation to rebuild the lookup cache.
¨ Static cache. You can configure a static,
or read-only, cache for any lookup source. By default, the Integration
Service
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. When the lookup condition is true, the Integration
Service returns a value
from the lookup
cache. The Integration Service does not update the cache while it processes the
Lookup
transformation.
¨ Dynamic cache. To cache a table, flat file,
or source definition and update the cache, configure a Lookup
transformation
with dynamic cache. The Integration Service dynamically inserts or updates data
in the lookup
cache and
passes the data to the target. The dynamic cache is synchronized with the
target.
¨ Shared cache. You can share the lookup
cache between multiple transformations. You can share an unnamed
cache
between transformations in the same mapping. You can share a named cache
between transformations
in the same
or different mappings. Lookup transformations can share unnamed static caches
within the same
target load
order group if the cache sharing rules match. Lookup transformations cannot
share dynamic cache
within the
same target load order group.
When you do
not configure the Lookup transformation for caching, the Integration Service
queries the lookup table
for each
input row. The result of the Lookup query and processing is the same, whether
or not you cache the
lookup
table. However, using a lookup cache can increase session performance. Optimize
performance by
caching the lookup table when
the source table is large.
