Lookup trans - created from Mind Map

Passive

Active

Connected Cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. Can return multiple columns from the same row or insert into the dynamic lookup cache no match for the lookup condition, Pass multiple output values Link lookup/output ports Supports user-defined default values How its processed IS returns the default value for all output ports Receives input values directly another transformation in the from the pipeline. each input row, IS queries source or cache based on lookup ports & condition IS passes return values from the query to the next transformation dynamic cache static cache uncached IS inserts the row when it does not find the row; updates the row or leaves the row unchanged. flag-insert flag-update unchanged IS returns values from lookup query IS returns values from lookup query

Unconnected Receives input values from the result of a :LKP expression in another transformation one return port (R). Returns one column per row. is no match, IS returns NULL match, IS returns the result to return port passes value to transformation calling :LKP expression one output value Does not support user-defined default values. how its procecessed static cache Receives input values from the result of a :LKP expression in another transformation lookup source IS returns one value into the return port passes the return value into the :LKP expression all lookup/output ports in the lookup condition and the lookup/return port

source types relational flat file piplines Override the default SQL statement to add a WHERE clause or to query multiple tables Sort null data high or low, based on database support Perform case-sensitive comparisons based on the database support sorted input Use indirect files as lookup sources by configuring a file list as the lookup file name Use sorted input for the lookup. Sort null data high or low. Use case-sensitive string comparison with flat file lookups input needs sorted&grouped the lookup source and source qualifier are in a different pipeline You can create multiple partitions in the partial pipeline to improve performance. improve performance when processing relational or flat file lookup sources, create a pipeline Lookup transformation instead of a relational or flat file Lookup transformation. You can create partitions to process the lookup source and pass it to the Lookup transformation. connected unconnected IS retrieves the lookup source data in this pipeline and passes the data to the lookup cache. separate target load order group

source 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

Performance Cached lookups Uncached lookups indexing the columns in the lookup ORDER BY indexing the columns in the lookup condition.

does not change the number of rows that pass through the transformation, maintains the Transaction boundary, and maintains the row type.

pass through the transformation. Change the transaction boundary. Change the row type.

not connected to other transformations in the mapping. An unconnected transformation is called within another transformation, and returns a value to that transformation.

connect to the lookup source using ODBC and import the table definition

select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file lookup source, the Designer invokes the Flat File Wizard.

to perform a lookup on an application source that is not a relational table or flat file.  has a source qualifier as source. except on Application Multi-Group Source Qualifier

The source and source qualifier are in a partial pipeline that contains no target. The Integration Service reads the source data in this pipeline and passes the data to the Lookup transformation to create the cache

can pass rows to a Filter or Router transformation to filter new rows to the target

Lookup trans

Annotations