Look Up Transformation In Detail

A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the lookup condition.

All about Informatica LookUp Transformation

A connected lookup recieves source data, performs a lookup and returns data to the pipeline;

While an unconnected lookup is not connected to source or target and is called by a transformation in the pipeline by :LKP expression which in turn returns only one column value to the calling transformation.

Lookup can be Cached or Uncached. If we cache the lookup then again we can further go for static or dynamic or persistent cache,named cache or unnamed cache .

By default lookup transformations are cached and static.

Lookup Ports Tab

The Ports tab of Lookup Transformation contains

• Input Ports:
  Create an input port for each lookup port we want to use in the lookup condition. We must have at least one input or input/output port in a lookup transformation.
• Output Ports:
  Create an output port for each lookup port we want to link to another transformation. For connected lookups, we must have at least one output port. For unconnected lookups, we must select a lookup port as a return port (R) to pass a return value.
• Lookup Port:
  The Designer designates each column of the lookup source as a lookup port.
• Return Port:
  An unconnected Lookup transformation has one return port that returns one column of data to the calling transformation through this port.
  Notes:
  We can delete lookup ports from a relational lookup if the mapping does not use the lookup ports which will give us performance gain. But if the lookup source is a flat file then deleting of lookup ports fails the session.

Lookup Properties Tab

Now let us have a look on the Properties Tab of the Lookup Transformation

• Lookup Sql Override:
  Override the default SQL statement to add a WHERE clause or to join multiple tables.
• Lookup table name:
  The base table on which the lookup is performed.
• Lookup Source Filter:
  We can apply filter conditions on the lookup table so as to reduce the number of records. For example, we may want to select the active records of the lookup table hence we may use the condition CUSTOMER_DIM.ACTIVE_FLAG = "Y".
• Lookup caching enabled:
  If option is checked it caches the lookup table during the session run. Otherwise it goes for uncached relational database hit. Remember to implement database index on the columns used in the lookup condition to provide better performance when the lookup in Uncached.
• Lookup policy on multiple match:
  While lookup if the integration service finds multiple match we can configure the lookup to return the First Value, Last Value, Any Value or to Report Error.
• Lookup condition:
  The condition to lookup values from the lookup table based on source input data. For example, IN_EmpNo=EmpNo.
• Connection Information:
  Query the lookup table from the source or target connection. In case of flat file lookup we can give the file path and name, whether direct or indirect.
• Source Type:
  Determines whether the source is relational database table,flat file or source qualifier pipeline.
• Tracing Level:
  It provides the amount of detail in the session log for the transformation. Options available are Normal, Terse, Vebose Initialization, Verbose Data.
• Lookup cache directory name:
  Determines the directory name where the lookup cache files will reside.
• Lookup cache persistent:
  Indicates whether we are going for persistent cache or non-persistent cache.
• Dynamic Lookup Cache:
  When checked We are going for Dyanamic lookup cache else static lookup cache is used.
• Output Old Value On Update:
  Defines whether the old value for output ports will be used to update an existing row in dynamic cache.
• Cache File Name Prefix:
  Lookup will used this named persistent cache file based on the base lookup table.
• Re-cache from lookup source:
  When checked, integration service rebuilds lookup cache from lookup source when the lookup instance is called in the session.
• Insert Else Update:
  Insert the record if not found in cache, else update it. Option is available when using dynamic lookup cache.
• Update Else Insert:
  Update the record if found in cache, else insert it. Option is available when using dynamic lookup cache.
• Datetime Format:
  Used when source type is file to determine the date and time format of lookup columns.
• Thousand Separator:
  By default it is None, used when source type is file to determine the thousand separator.
• Decimal Separator:
  By default it is "." else we can use "," and used when source type is file to determine the thousand separator.
• Case Sensitive String Comparison:
  To be checked when we want to go for Case sensitive String values in lookup comparison. Used when source type is file.
• Null ordering:
  Determines whether NULL is the highest or lowest value. Used when source type is file.
• Sorted Input:
  Checked whenever we expect the input data to be sorted and is used when the source type is flat file.
• Lookup source is static:
  When checked it assumes that the lookup source is not going to change during the session run.
• Pre-build lookup cache:
  Default option is Auto. If we want the integration service to start building the cache whenever the session just begins we can chose the option Always allowed.