Lookup's in Informatica

  • Share this blog:


Lookup's in Informatica

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


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


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

Maplet Types in Informatica

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



 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


Click on validate, click ok , click ok

From repository menu click on save

Lookup's in Informatica


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

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

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)

Lookup's in Informatica


Create the following stored procedure in the target data base

SQL> create procedure annual _ proc (SAL IN NUMBER , A SAL OUT NUMBER)



Asal := Sal*12;


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

For indepth understanding of Informatica, click on

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.