top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Difference between Relational Lookup and Flat File Lookup Transformation in Informatica

+2 votes
5,974 views

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

Relational Lookup Transformation: When you create a Lookup transformation using a relational table as a lookup source, you can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.

Configure following options with Relational lookup Transformation:

  • 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.

Configuring Relational Lookups in a Session: When you configure a relational lookup in a session, configure the connection for the lookup database on the Transformation View of the Mapping tab. Choose the Lookup transformation and configure the connection in the session properties for the transformation.

Choose from the following options to configure a connection for a relational Lookup transformation:

  • Choose a relational or application connection.
  • Configure a database connection using the $Source or $Target connection variable.
  • Configure the session parameter $DBConnectionName or $AppConnectionName, and define the session parameter in a parameter file

Flat File Lookup Transformation: When you create a Lookup transformation using a flat file as a 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 lookup source, the Designer invokes the Flat File Wizard.

Configure following options with flat file lookup Transformation:

  • Use indirect files as lookup sources by configuring a file list as the lookup file name.( example using DEPT1.txt , DEPT2.txt )
  • Use sorted input for the lookup.
  • Sort null data high or low.
  • Use case-sensitive string comparison with flat file lookups.

Using Sorted Input for Flat File Transformation: When you configure a flat file Lookup transformation for sorted input, the condition columns must be grouped. If the condition columns are not grouped, the Integration Service cannot cache the lookup and fails the session. For optimal caching performance, sort the condition columns.

For example: A Lookup transformation has the following condition:

Order_ID = in_OrderID
Customer_ID = in_CustID

In the following flat file lookup source, the keys are grouped, but not sorted. The Integration Service can cache the data, but performance may not be optimal.
enter image description here

The keys are not grouped in the following flat file lookup source. The Integration Service cannot cache the data and fails the session.
enter image description here

Configuring Flat File Lookups in a Session: When you configure a flat file lookup in a session, configure the lookup source file properties on the Transformation View of the Mapping tab. Choose the Lookup transformation and configure the flat file properties in the session properties for the transformation.
enter image description here

posted Apr 28, 2014 by Shatark Bajpai

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

...