All About Lookup Transformation

Lookup transformations allow you to search for data in flat files, relational tables, views, or synonyms. You can import search definitions from flat files or relational databases that both PowerCenter clients and integration services can connect to. You can also create search definitions from source qualifiers. A mapping can use multiple lookup transformations.

The integration service queries search sources based on the search ports and search criteria in the transform. A Lookup transformation returns search results to a target or another transformation. You can configure a Lookup transformation to return a single row or multiple rows.

Source type for Lookup transformation:

When you create a lookup transformation, you can select a relational table, flat file, or source qualifier as the lookup source.

Relational lookup

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

For relational Lookup, below mentioned options are used:

Override standard SQL statements to add WHERE clauses or query multiple tables. Sort null data up or down depending on database support. Performs a case-sensitive comparison based on database support.

Flat File Lookup

If you create a Lookup transformation using a flat file as the lookup source, select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file search source, the designer calls the Flat File Wizard.

Following option are used for Flat File Lookup

Use an indirect file as a search source by configuring a list of files as the search file name. Search using sorted input.

Sort null data up or down.

For flat file searches, use case-sensitive string comparisons.

Use an indirect file as a lookup source by configuring a list of files as the lookup file name.

Search using sorted input.

Sort null data up or down.

For flat file searches, use case-sensitive string comparisons.

Connected and Unconnected Lookups: User can configure a connected or unconnected Lookup transformation. A connected Lookup transformation is that type of a transformation that has input and output ports connect to other transformations in the mapping. An unconnected Lookup transformation appears in the mapping but is not connected to any other transformations. An unconnected Lookup transformation receives input from the result of an: LKP expression in a transformation such as an Expression or Aggregator transformation. The: LKP expressions pass arguments to and receive results from the Lookup transformation. The: LKP expressions can pass search results to another expression within the expression or to an aggregation transformation to filter the results.

Lookup SQL overrides

When a Lookup transformation is included in mapping, the mapping task needs the lookup object based on the fields and properties that user has configured in the Lookup transformation. The mapping task runs a standard lookup query when the first row of data enters the Lookup transformation. If the Lookup transformation performs a relational search, you can override the default query. A standard query contains a SELECT statement that includes all search fields in the map. The SELECT statement also includes an ORDER BY clause that places all columns in the same order as they appear in the Lookup transformation. Run the mapping task to view the default query. Standard queries appear in the log file. You can override the default query if you want to change the ORDER BY clause, add a WHERE clause, or transform search data before caching it. For example, you can use database functions to customize the data type or format of a lookup table to match the data type and format of the fields used in the mapping. Alternatively, you can override the default query to query multiple tables. Override the default query on the Lookup Transformation Details Tab. Enter the entire SELECT statement in the Lookup SQL Override field. Use aliases for each column in your query. If you change the ORDER BY clause, you must also add "--" to the end of the query to suppress the ORDER BY clause that generates the mapping task.

Lookup source filter

User can configure lookup source filters for relational Lookup transformations that have caching enabled. Add a lookup source filter to limit the number of lookups that the mapping task performs against the lookup source table. When you configure a search source filter, the mapping task performs a search based on the results of the filter statement. To configure a lookup source filter, open the Details tab for the Lookup transformation and enter the filter in the Lookup Source Filter field. Do not include the WHERE keyword in your filter conditions. For example, you may need to retrieve the last names of all employees whose ID is greater than 510. Set the following search source filter for her Employee ID field in the Lookup transformation.

Employee ID >= 570

When the mapping task reads a source row, if the value of Employee ID is greater than 570, it performs a lookup in the cache. If Employee ID is less than or equal to 570, the Lookup transformation does not retrieve the last name. When you add a search source filter to a search query in a mapping task that uses pushdown optimization, the mapping task creates a view that represents the SQL override. The mapping task runs a SQL query against this view and pushes the transformation logic to the database.