Lookup Transformation in Informatica

Lookup Transformation in Informatica

The Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data. You can look up the flat file and relational tables. The Lookup transformation in Informatica works on similar lines as the joiner, with a few differences. For example, lookup does not require two sources. Lookup transformations can be connected and unconnected. They extract the data from the lookup table or file based on the lookup condition.

This is the type passive transformation which allows you to perform the lookup on a relational table. Flat files, synonyms, and views.

When the mapping contains the work up transformation, the integration service queries the lock up data and compares it with lookup input port values.

The lookup transformation is created with following type of ports

Input port (I)

Output port (O)

Look up Ports (L)

Return Port (R)

The lookup transformation supports horizontal merging such as equijoin and nonequijoin.

Informatica Lookup Transformation Uses

Lookup transformation is used to return following tasks. - get a related value - in updating slowly changing dimension

Connected Lookups in Informatica

For each input row, the Integration Service queries the lookup source or cache based on the lookup ports and the condition in the transformation.

If the transformation is uncached or uses a static cache, the Integration Service returns values from the lookup query.

If the transformation uses a dynamic cache, the Integration Service inserts the row into the cache when it does not find the row in the cache. When the Integration Service finds the row in the cache, it updates the row in the cache or leaves it unchanged. It flags the row as insert, update, or no change.

The Integration Service passes return values from the query to the next transformation. If the transformation uses a dynamic cache, you can pass rows to a Filter or Router transformation to filter new rows to the target.

If there is no match for the lookup condition, the Integration Service returns the default value for all output ports.

Informatica UnConnected Lookups 

An unconnected lookup is not part of mapping data flow

It is neither connected to the source nor connected to the target

An unconnected look up transformation can receive the multi input ports but returns single

output port which is designated by return port (R)

An unconnected look up transformation is commonly used when the look is not needed for every record.

An unconnected look up transformation that supports to write expression

The look up function used in conditional statement 

[sociallocker id=14714]

Difference between connected and unconnected look up

[/sociallocker]

Note:

The condition is evaluated from each row, but the look up function is only call if the condition evaluates to true.

IFF(ISNULL(ITEM ID),:LKP.ABC(order _id),item Id)

Business purpose:

A source table (or) file may have a percentage of records with incomplete data

The holes in the data can be filled by performing a look up to another table (or) table.

 As only a percentage of the rows are affected, it is better to perform the look up on only those rows that need it and not the entire data set.

Procedure:

• Create a source definition with the name emp
• Create a target definition with the name EMP _LKp
• Emp no, ename, job, sal, deptno, dname
• Create the mapping with the name M_unconnected _LKP
• Drop the source and target definitions
• Create the transformation type look up which performs a look up on dept table
• Create the transformation type expression
• From source, qualifier copy the required ports to expression transformation
• Double click on the look up transformation select the ports tab from tool bar click on add a new port

• For a port name a dname select return port(R)
• Select properties tab

• Select the condition tab from tool bar click on add a new condition look up table column.

• Click Apply and click ok
• Double click on expression transformation select the port tab from tool bar click on add a new ports

• Click  apply and click ok

From express transformation connect the ports to target

Reusable transformations

   A reusable transformation is a reusable meta data object created with business logic using single transformation There are 2 different ways to create reusable transformations

1. Converting a non-reusable transformation into a reusable transformation
2. Using transformation developer tool

Limitation Source qualifier transformation can’t be used as reusable T/R

Procedure

• Converting nonreusable transformation into a reusable T/R select the mapping, drop to the mapping designer workspace
• Double click on the transformation, from the transformation tab
• Select make reusable, click on yes
• Click apply, click ok

Using transformation developer

• From tools menu select transformation developer tool
• From transformation, menu select create
• Select the transformation type sequence generator
• Enter the name click on create and done
• From repository menu click on save

User defined function

A user defined function is a power center object which is created using a set of built in function to meet specific requirements. A user defined function created using power center designer client.

Built – in – function

LTrim                Trim                  LTrim (RTrim (arg1)) Rtrim

Procedure

• From repository navigation window select the user-defined function sub folder.
• From tool menu select user defined functions to click on new
• Enter the name Trim, select the type public
• From arguments click on add new argument

• Click on count editor, develop the following express
• LTRIM(RTRIM(ARG1))
• Click on validate, click ok, click ok
• From repository menu click on save

Note: A user-defined function is defined with the following identifier count _ UDF

Unconnected stored procedure

• An unconnected stored procedure is not part of mapping data flow
• It is neither connected to the source nor connect to the target
• An unconnected stored procedure can receive the multiple but return single output port by default.
• An unconnected stored procedure act as a function that can be set in a transformation that supports to write an expression
• An unconnected stored procedure can be set with the following expression
• :SP. Stored procedure transformation name(:SP.SPTR NAME)

Step1: Create the following stored procedure in the target database

SQL> create procedure annual _ proc (SAL IN NUMBER , A SAL OUT NUMBER)
Is
Begin
Asal := Sal*12;
END;

• Create the source and target definitions
• Empno, ename, job,sal, annual,deptno
• Create a mapping with the name M_ unconnected _proc
• Drop the source and target definition
• Create the transformation type stored procedure and expression
• From the source, qualifier copy the required ports  to the expression transformation
• Double click on expression transformation select the ports tab

• Click Apply, click ok
• Double click on unconnected stored procedure transformation
• Select the properties tab

• Click Apply, click ok

Lookup Transformation Examples

• Create a source definition with the name emp
• create a target definition with name emp-aggr-LKP deptno, dname, sum(sal)
• create a mapping with the name m-aggr-lookup
• drop the source and target definition
• create the transformation type sorter
• from source qualifier copy the following ports to the sorter transformation (deptno, sal)
• double click on sorted transformation select the ports tab
• for a port name Dep no slece Key
• click apply
• click ok
• create the transformation type aggregator
• from sorter transformation copy the ports the aggregator
• double click on aggregator transformation select he ports tab
• for a port name deptno select group by
• for a port, name salary uncheck output port
• from tool bar click on a new port

• select the properties tab

Transformation Attribute Value   

• sorted input
• click apply click ok
• From aggregator, transformation connect the ports the target
• From transformation, menu select create
• select the transformation type lookup
• enter the name LKP-source
• click on create
• Select the source select the table dept click ok from aggregate transformation copy the port dept no to the lookup transformation
• Double click on the lookup transformation select the condition tab
• from tool bar click on odd a new condition

• From lookup transformation connect the dame port to the target

Lookup Transformation Run-time Properties

Lookup Caching Enabled - When It is enabled, the Integration Service queries the lookup source once, caches the values, and looks up values in the cache. When you disable caching, each time a row passes into the transformation, the Integration Service issues a select statement to the lookup source for lookup values. The Integration Service always caches flat-file lookups.

• Lookup Data Cache Size -Maximum size the Integration Service allocates to the data cache in memory. Default is Auto.
• Lookup Index Cache Size -Maximum size the Integration Service allocates to the index in memory. Default is Auto.
• Cache File Name Prefix -Prefix for the cache file. You can specify the cache file name prefix for a persistent lookup cache.
• Pre-build Lookup Cache -Allows the Integration Service to build the lookup cache before the Lookup transformation receives the data.
• Lookup Cache Directory Name - Directory used to build the lookup cache files when you configure the Lookup transformation to cache the lookup source.
• Re-cache from Lookup Source - Rebuilds the lookup cache to synchronize the persistent cache with the lookup table.

Lookup Transformation Advanced Properties