Informatica Transformations

What is Informatica Transformations?

Informatica Transformations are repository objects which facilitate reading, modifying or passing data to the defined target structures. The defined target structures can be either tables, files etc. A Transformation typically refers to the set of rules that define data flow and the way the data will be loaded into the specific targets. Now, 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.

Watch this Informatica video

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.

Now since we have discussed the 2 broad categories of Informatica Transformations, let us discuss the various types of transformations:

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.

Router Transformation

In cases where the same input needs to be tested multiple times, the Router transformation is used. It is basically an active transformation that can be used to apply conditions to input data. In Router transformation, each row gets evaluated through data integration and the conditions of each user-defined group is tested before the default group is processed. Data integration passes multiple times through a row when the row matches more than one group filter condition.

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.

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.

Normalizer Transformation

This type of transformation is used to transform one incoming row into multiple outgoing/output rows. The Normalizer transformation is basically an active transformation that normally receives a row that contains multiple-occurring data and returns one single row for each multiple-occurring data instance. Now, one thing that needs to be noted here is that while returning multiple rows from one input row, the Normalization Transformation returns duplicate data for all the single-occurring input columns.

Rank Transformation

Rank Transformation is used to select the top or the bottom rank of data. It is basically a type of Active and Connected transformation. Rank Transformation returns the largest or smallest numeric values in a group and also returns the strings at the top or bottom of the mapping sort order. Now the reason behind why it is called an Active Transformation is that it can change the number of rows passing through it. For example, if you need to select top 10 cities where the profit volume is very high or lowest 10 cities where the profit is low, you need to use Rank Transformation.

Union Transformation

The Union Transformation helps in merging multiple datasets collected from different streams or pipelines into one single dataset. Since it unites multiple datasets into one, the name Union has been given to it. Again, Union Transformation is also a type of Active and Connected Informatica Transformation and its working is quite similar to SQL’s UNION ALL command. While working with Union Transformation, it is usually advisable to use aggregators in order to remove the duplicates.

Update Strategy Transformation

Update Transformation is used for inserting, deleting, rejecting and updating records in a given target table. It is typically an Active and Connected Informatica Transformation and is mostly used along with Lookup Transformation. In Update Strategy Transformation, the source qualifier row is compared with the Lookup Transformation row and then a record is inserted or updated in the flagged table. A history of the records is also maintained in the form of a source table.

Active and Passive Transformation

Active Transformation: 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

Passive Transformation: With the help of passive transformation, we cannot alter the number 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: