Various Transformations in Informatica

Informatica Transformations can be mainly classified into two categories. Firstly based on the connectivity(Linking in mapping) of the transformations with each other and the second is based on the change in the overall no of rows between the source and target. Let’s start by taking a look at the Informatica transformations based on connectivity.

1) Types of transformations in Informatica based on connectivity:

• Connected Transformations.
• Unconnected Transformations.

In Informatica, those transformations which are connected to one or more transformations are called as Connected transformations.

The connected transformations are used when for every input row, a transformation is called and is expected to return a value. For example, we can use a connected lookup transformation to know the names of every employee working a specific department by specifying the Department ID in the lookup expression.

Some of the Major connected Informatica transformations are Aggregator, Router, Joiner, Normalizer, etc.

Those transformations that are not connected to any other transformations are called Unconnected transformations. Their functionality is used by calling them inside other transformations like Expression transformation. These transformations are not part of the mapping pipeline.

The unconnected transformations are used when their functionality is only required based upon certain conditions. For example, As a programmer you wish to perform a complicated operation on the data, howeveryou do not wish to use Informatica transformations like expression or filter transformations to perform this operation. In such a case, you can create an external DLL or UNIX shared library with the codes to perform the operation and call them in the External procedure transformation.

There are 3 Informatica transformations viz. External Procedure, Lookup, and Stored Procedure which can be unconnected in a valid mapping (A mapping which the Integration Service can execute).

2) Types of Informatica transformations based on the change in no of rows

• Active Transformations
• Passive Transformations

Active Transformations: – An active transformation can perform any of the following actions:

• Change the number of rows that passes through the transformation: For instance, the Filter transformation is active because it removes rows that do not meet the filter condition.
• Change the transaction boundary: A transaction boundary is a boundary that encloses all the transactions before a commit is called or between two commit calls. For e.g., During a transactional operation, the user feels that after certain transactions a commit is required and calls the commit command to create a savepoint and by doing so the user changes the default transaction boundary. By default, the transaction boundary lies between the start of the file to auto commit point or EOF.
• Change the rowtype attribute: Rowtype attribute is a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetch from a pointer or pointer variable. For e.g., The Update Strategy transformation flags rowstype as 0 for inserting values, 1 for update, 2 for delete or 3 for reject.
• Aggregator, Filter, Joiner, Normalizer, etc. are a few examples of Active transformation.

Passive Transformation: A passive transformation is one which will satisfy all these conditions:

• The number of rows before and after transformation is the same.
• Maintains the transaction boundary .
• Maintains the rowtype attribute.
• Expression, ExternalProcedure, HTTP, etc. are a few examples of Passive transformation.

In the passive transformation, no new rows are created, or existing rows are dropped.

You must be wondering why passive transformations are used for if they do not change the number of rows. They are generally used to update values, calling an external procedure from a shared library and to define the input and output of maplets. A maplet is a collection of only the transformations from the mapping. For e.g, For a student database we wish to update the values of marks column to percentile instead of the percentage, this can be done by using an expression transformation which will convert the values and update in the same columns keeping the overall number of rows same after the transformations.

gregator transformation is an Active and Connected transformation. This Informatica transformation is useful to perform calculations such as averages and sums (mainly to perform calculations on multiple rows or groups). For example, to calculate the total number of daily sales or to calculate the average of monthly or yearly sales. Aggregate functions such as AVG, FIRST, COUNT, PERCENTILE, MAX, SUM, etc., can be used in aggregate transformation.

Lookup Transformation

Lookup transformation is the most popular and widely used Informatica transformation. Based on the requirement of the user has, the lookup transformation can be used as a Connected or Unconnected transformation combining it as an Active or Passive transformation. It is used to mainly look up the details from a source, source qualifier, or target in order to get relevant required data. You can also look up a ‘flat file’, ‘relational table’, ‘view’ or ‘synonym’. One can use multiple lookup transformations in a mapping.

The lookup transformation is created with the following type of ports(Logical points for transfer of information):