Informatica Transformations Overview

A transformation is an object that reads, modifies, and passes the data on. It represents the set operations performed on the data. It can be categorized into two classes- Active and/Passive Informatica Transformations or Connected/Unconnected Transformations in Informatica.
nformatica Transformations

Watch this Informatica video

Informatica Transformations

Classification of Transformation in informatica

Transformations can be Connected or Unconnected to the data flow.

Connected transformation

Connected transformation is linked to other transformations or directly to a destination table in the mapping. It is used when a transformation is called for every input row and is expected to return a value. For instance, a connected lookup transformation can be used to know the names of the employees who work in a specific department by specifying the Department ID in the lookup expression.

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

Unconnected transformation

Unconnected transformation is not linked to other transformations in the mapping. It is invoked within a different transformation and gives value to that transformation. These transformations are not part of the mapping pipeline. To realize functionality of unconnected transformations, they have to be called inside other transformations like expression transformation.

Unconnected transformations are used only when their functionality is required based upon certain conditions. For example, if one wishes to perform a complicated data operation but does not wish to use Informatica transformations, they can build an external DLL or UNIX shared library with the codes to perform the operation and call them in the External procedure transformation.

Transformations can also be based on the change in the number of rows:

1. Active Transformation in Informatica

With the help of Active transformation, we can alter the no. of rows which it passes through the transformation and can alter the row type or transaction boundary.

For example, Filter Transformation in Informatica, Transaction Control Transformation in Informatica, and Update Strategy Transformation in Informatica are active Informatica Transformations.

The following is the list of active transformation in Informatica used for processing the data –

  • Source quilter transformation in Informatica
  • Filter transformation in Informatica
  • Ruler transformation in Informatica
  • Rank Transformation in Informatica
  • Sorter Transformation in Informatica
  • Joiner Transformation in Informatica
  • Union Transformation in Informatica
  • Aggregate Transformation in Informatica
  • Transaction control transformation in Informatica
  • Normalize transformation in Informatica
  • Update strategy transformation in Informatica
  • SQL Transformation in Informatica

Go through the Informatica course details to get a better understanding of this widely used Business Intelligence tool.

2. Passive transformation in Informatica

With the help of passive transformation, we cannot alter the no. of rows that go through it and maintain the row type and transaction boundary.

The following is the list of passive transformations used for processing data:

  • Expression Transformation in Informatica
  • Sequence generator transformation in Informatica
  • Stored procedure transformation in Informatica
  • Lookup Transformation in Informatica
  • XML source qualifier transformation in Informatica
  • SQL Transformation in Informatica

List of Transformations in Informatica

Some of the commonly used transformations in Informatica are:

Informatica Transformations Description
Aggregator Transformation This transformation is used to perform aggregate calculations such as averages and sums. It performs a calculation on a row-by-row basis. It is mainly used to perform calculations on multiple rows or groups. Aggregate functions like FIRST, COUNT, SUM, AVG, PERCENTILE, MAX, etc., can be used in aggregate transformation.
Expression Transformation This transformation calculates the value within a single row and tests the conditional statements. It is used to perform non-aggregate calculations. When configuring an expression transformation, the  expression field for the output of each calculation that has to be used in the data flow needs to be created. A variable field for calculations that needs to be used within the transformation will have to be created as well.
Filter Transformation It filters the rows in a mapping. All the ports are input/output in nature and rows that meet the filter conditions pass through it. The records can be filtered according to the requirements with the help of the filter condition.
Joiner Transformation This transformation joins two heterogeneous sources. It provides the option of creating joins in Informatica. When joiner transformation is used, the joins that are created are similar to the joins in databases. Sources are joined on the basis of the condition that matches one or more pairs of columns between the two sources.
Lookup Transformation It searches the data in the relational table and returns the result of the lookup to the target or another transformation. A user can use multiple lookups at a time. When the Lookup transformation is configured to return a single row, it is a passive transformation and when it is configured to return multiple rows, it is an active transformation.

What is Filter Transformation?

Filter transformation is a form of active transformation as it changes the number of records. It can help filter the records based on their condition.

For instance, to load the employee records having dept number equal to 10 only, filter transformation can be used in the mapping with the filter condition deptno=10. In this way, only those records that have deptno =10 will be passed by the filter transformation, and the other records will be dropped.

Here is how one can use filter transformation:

Step 1: Create a mapping having source “EMP” and target “EMP_TARGET”

Step 2: In the mapping, select transformation menu and create option

Step 3: Then, in the create transformation window, select Filter Transformation from the list. Enter the transformation name as “fltr_deptno_10”, and click on Create.

Step 4: The filter transformation will be created. Select “Done” in the create transformation window

Step 5: In the mapping, drag and drop all the Source qualifier columns to the filter transformation and link the columns from filter transformation to the target table.

Step 6: Double click on the filter transformation to open its properties, select the properties menu and click on the Filter condition editor.

Step 7: In the filter condition expression editor, enter filter condition – deptno=10, and click OK.

Step 8: In the edit transformation window under the Properties tab, you will see the filter condition, click on OK.

Step 9: Save the mapping and execute it after creating the session and workflow. In the target table, the records having deptno=10 only will be loaded.

In this way, one can filter the source records with the help of filter transformation.

Want to become a certified professional. Check out our Informatica Training in Bangalore!

Course Schedule

Name Date
Power BI Training 2022-07-02 2022-07-03
(Sat-Sun) Weekend batch
View Details
Power BI Training 2022-07-09 2022-07-10
(Sat-Sun) Weekend batch
View Details
Power BI Training 2022-07-16 2022-07-17
(Sat-Sun) Weekend batch
View Details

Leave a Reply

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