Informatica Common Components lookup three relationship and flat file lookup

When you create a Lookup transformation, you can choose to use a relational table or flat file as the lookup source.

Relationship Lookup

When you use a relational table as a lookup source to create a Lookup transformation, you can use ODBC to connect to the lookup source and import the table definition as the structure of the Lookup transformation.

You can only use the following options for relationship lookups:

• If you need to add a WHERE clause or query multiple tables, you can override the default SQL statement.

• You can use the dynamic lookup cache for relationship lookups.

Flat File Lookup

When you use a flat file as a lookup source, you can use any flat file definition in the repository, or import a flat file definition. If you import a flat file lookup source, Designer calls the Flat File wizard.

The following options are only available for flat file lookups:

• Use the indirect file as a lookup source by specifying a file list to find the file name.

• Use sort input to find.

• Sorts the null value data by either high or low. For relational lookups, this requires database support.

• Use case-sensitive string comparisons for flat file lookups. For relational lookups, case-sensitive comparisons require database support.

Using sort Inputs

When you configure a flat file Lookup transformation for the sort input, you must first group the condition columns. If there are no grouping criteria columns, PowerCenter Server will not be able to cache lookups and cause the session to fail. For best cache performance, sort the criteria columns.

Informatica Common Components lookup three relationship and flat file lookup