• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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 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 lookup on relational table. Flat files, synonyms and views.

When the mapping contain the workup 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 support horizontal merging such as equijoin and non equijoin.

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

In Connected lookup transformation, we connect input and output ports  to other transformation in the mapping.

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 need for every record.

An unconnected look up transformation that supports to write expression

The look up function used in conditional statement 

Note:

The condition is evaluated from each row but the look up function is only call if the condition evaluates tot 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.

Lookup Transformation in Informatica

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 perform 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

 

Port Name Data type Precision Scale IOV
In- deptno Decimal 2 0

 

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

Transformation Attribute

Value
Connection Information BSR – Reader

 

 

 

  • Select the condition tab from tool bar click on add a new condition look up table column.
Transformation Attribute operator T/R port
deptno = IN- Deptno
  • Click apply and click ok
  • Double click on expression transformation select the port tab from tool bar click on add a new ports
Port Name Data type Precision Scale I O V Expression
DName string 15 :LKP.LKP-Dept(Deptno)
  • 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 non reusable transformation into a reusable T/R select the mapping, drop to the mapping designer work space
  • Double click on the transformation, from 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 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 user defined function sub folder.
  • From tool menu select user defined functions click on new
  • Enter the name Trim, select the type public
  • From arguments click on add new argument
Name Data type Precision
Arg1 String  10

  

 

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

Screenshot_8

Note:

A user defined function is defined with following identifier count _ UDF

Unconnected stored procedure

  • An unconnected stored procedure is not part of mapping data flow
  • It is neither connect 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 an transformation that supports to write expression
  • An unconnected stored procedure can be set with the following expression
  • :SP. Stored procedure transformation name(:SP.SPTR NAME)

12

Step1:

Create the following stored procedure in the target data base

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 source qualifier copy the required ports  to the expression transformation
  • Double click on expression transformation select the ports tab
Port Name Data type Precision Scale I   O    V Value
ANUAL Decimal 2 :SP .USP(Sal , proc – Result)

 

 

 

  • Click apply, click ok
  • Double click on unconnected stored procedure transformation
  • Select the properties tab
Transformation Attribute Value
Connection Information Batch _ Trg – Writer
  • 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
Portname    Datatype Precession Scale I O V   Expression
sum(sal) decimal ? 2 sum(sal)
  • select the properties tab
Learn Informatica by Tekslate - Fastest growing sector in the industry. 
Explore Informatica Training Online  and course is aligned with industry needs & developed by industry veterans. 
Tekslate will turn you into  Informatica Expert.

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
look up table column operator T/T port
Deptno      = Dept no 1
  • 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

Lookup Cache Persistent
– Indicates whether the Integration Service uses a persistent lookup cache.Case Sensitive String Comparison
– The Integration Service uses case-sensitive string comparisons when performing lookups on string columns when the lookup source is a flatfile.
Null Ordering
– You can choose to sort null values high or low. By default, the Integration Service sorts null values high for flatfile. For relational lookups, null ordering is based on the database default value.
Tracing Level – Sets the amount of detail that appears in the log for this transformation.
Update Else Insert
– Applies to dynamic lookup cache only. The Integration Service updates the row in the cache if the row type entering the Lookup transformation is update, the row exists in the index cache, and the cache data is different than the existing row. The Integration Service inserts the row in the cache if it is new.
Insert Else Update
–  Applies to dynamic lookup cache only. The Integration Service inserts the row in the cache if the row type entering the Lookup transformation is insert and it is new. If the row exists in the index cache but the data cache is different than the current row, the Integration Service updates the row in the data cache.
Output Old Value
– on Update The Integration Service outputs the value that existed in the cache before it updated the row. Otherwise, the Integration Service outputs the updated value that it writes in the cache.
Update Dynamic Cache Condition
– Applies to dynamic lookup cache only. An expression that indicates whether to update the dynamic cache. The Integration Service updates the cache when the condition is true and the data exists in the cache. Default is true.
Connection
– Connection to the relational database that contains the relational lookup source.
Sorted Input
– Indicates that input data is presorted by groups.
Datetime Format
– Define a datetime format and field width.
Thousand Separator
– Value is None. This field is read-only.
Decimal Separator
– Value is a period. This field is read-only.
*The last four properties are applicable only for flatfiles.

Lookup Transformation in Informatica Video Tutorial:

You Might Like:

Summary
Review Date
Reviewed Item
Informatica Lookup Transformations
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Lookup Transformation in Informatica"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.