Thursday, March 14, 2013

Lookup Transformation in Informatica

Lookup transformation is used to look up data in a flat file, relational table, view or synonym. Lookup is a passive/active transformation and can be used in both connected/unconnected modes. From informatica version 9 onwards lookup is an active transformation. The lookup transformation can return a single row or multiple rows.

You can import the definition of lookup from any flat file or relational database or even from a source qualifier. The integration service queries the lookup source based on the ports, lookup condition and returns the result to other transformations or target in the mapping.

The lookup transformation is used to perform the following tasks:

  • Get a Related Value: You can get a value from the lookup table based on the source value. As an example, we can get the related value like city name for the zip code value.
  • Get Multiple Values: You can get multiple rows from a lookup table. As an example, get all the states in a country.
  • Perform Calculation. We can use the value from the lookup table and use it in calculations.
  • Update Slowly Changing Dimension tables: Lookup transformation can be used to determine whether a row exists in the target or not.

You can configure the lookup transformation in the following types of lookup:

  • Flat File or Relational lookup: You can perform the lookup on the flat file or relational database. When you create a lookup using flat file as lookup source, the designer invokes flat file wizard. If you used relational table as lookup source, then you can connect to the lookup source using ODBC and import the table definition.
  • Pipeline Lookup: You can perform lookup on application sources such as JMS, MSMQ or SAP. You have to drag the source into the mapping and associate the lookup transformation with the source qualifier. Improve the performance by configuring partitions to retrieve source data for the lookup cache.
  • Connected or Unconnected lookup: A connected lookup receives source data, performs a lookup and returns data to the pipeline. An unconnected lookup is not connected to source or target or any other transformation. A transformation in the pipeline calls the lookup transformation with the :LKP expression. The unconnected lookup returns one column to the calling transformation.
  • Cached or Uncached Lookup: You can improve the performance of the lookup by caching the lookup source. If you cache the lookup source, you can use a dynamic or static cache. By default, the lookup cache is static and the cache does not change during the session. If you use a dynamic cache, the integratiion service inserts or updates row in the cache. You can lookup values in the cache to determine if the values exist in the target, then you can mark the row for insert or update in the target.

No comments:

Post a Comment