Lookup's in Informatica

 

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

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

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

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

Note:

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)

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

For indepth understanding of Informatica, click on

• Rank Transformation in Informatica
• Aggregator Transformation in Informatica
• Expression Transformation in Informatica
• Lookup Transformation in Informatica
• Interview Questions