Informatica Transformations Tutorial: The Ultimate Guide [STEP-IN]

Last updated on 29th Jun 2020, Blog, Tutorials

What is Transformation?

Transformations are in Informatica are the objects which create, modify or pass data to the defined target structures (tables, files or any other target).The purpose of the transformation in Informatica is to modify the source data as per the requirement of the target system. It also ensures the quality of the data being loaded into the target.

Informatica provides various transformations to perform specific functionalities.

For example, performing tax calculation based upon source data, data cleansing operation, etc. In transformations, we connect the ports to pass data to it, and transformation returns the output through output ports.

In this tutorial- you will learn

• Classification of Transformation
• Filter Transformation

Classification of Transformation:

Transformation is classified into two categories, one based on connectivity, and other based on the change in no of rows. First we will look at the transformation based on connectivity.

Types of transformation based on connectivity

1. Connected Transformations
2. Unconnected Transformations

• In Informatica, during mappings the transformations which are connected to other transformations are called connected transformations.For example, Source qualifier transformation of Source table EMP is connected to filter transformation to filter employees of a dept.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 pipeline.
• The connected transformations are preferred when for every input row, transformation is called or is expected to return a value. For example, for the zip codes in every row, the transformation returns the city name.
• The unconnected transformations are useful when their functionality is only required periodically or based upon certain conditions. For example, calculation the tax details if tax value is not available.

Types of transformations based on the change in no of rows

1. Active Transformations
2. Passive Transformations

• Active Transformations are those who modify the data rows and the number of input rows passed to them. For example, if a transformation receives ten numbers of rows as input, and it returns fifteen numbers of rows as an output then it is an active transformation. The data in the row is also modified in the active transformation.
• Passive transformations are those who do not change the number of input rows. In passive transformations the number of input and output rows remain the same, only data is modified at row level.
• In the passive transformation, no new rows are created, or existing rows are dropped.

Following is the List of Transformations in Informatica

• Source Qualifier Transformation
• Aggregator Transformation
• Router Transformation
• Joiner transformation
• Rank Transformation
• Sequence Generator Transformation
• Transaction Control Transformation
• Lookup and Reusable transformation
• Normalizer Transformation
• Performance Tuning for Transformation
• External Transformation
• Expression Transformation

What is Filter Transformation?

Filter Transformation is an active transformation as it changes the no of records.Using the filter transformation, we can filter the records based on the filter condition. Filter transformation is an active transformation as it changes the no of records.For example, for loading the employee records having deptno equal to 10 only, we can put filter transformation in the mapping with the filter condition deptno=10. So only those records which have deptno =10 will be passed by filter transformation, rest other records will be dropped.

How to use filter transformation:

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

Step 2 – Then in the mapping

1. Select Transformation menu
2. Select create option

Step 3 – Then in the create transformation window

1. Select Filter Transformation from the list
2. Enter Transformation name “fltr_deptno_10”
3. Select create option

Step 4 – The filter transformation will be created, Select “Done” button in the create transformation window

Step 5 – In the mapping

1. Drag and drop all the Source qualifier columns to the filter transformation
2. Link the columns from filter transformation to the target table

Step 6 – Double click on the filter transformation to open its properties, and then

1. Select the properties menu
2. Click on the Filter condition editor

Step 7 – Then in the filter condition expression editor

1. Enter filter condition – deptno=10
2. Select OK button

Step 8 – Now again in the edit transformation window in Properties tab you will see the filter condition, select OK button

Now 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, you can filter the source records using filter transformation.

What is Source Qualifier Transformation?

• Source qualifier transformation is an active, connected transformation which is used to represent the rows that the integrations service read. Whenever we add a relational source or a flat file to a mapping, a source qualifier transformation is required. When we add a source to a mapping, source qualifier transformation is added automatically. With source qualifiers, we can define and override how the data is fetched from the source.

• In the following example we would be modifying the source qualifier of our mapping “m_emp_emp_target”, so instead of returning all the columns it will return only selected columns.

Step 1 – Open mapping “m_emp_emp_target” in mapping designer.

Step 2 – Double click on the source Qualifier transformation “SQ_EMP”. It will open edit transformation property window for it. Then

1. Click on the properties tab
2. Click on the SQL Query Modify option, this will open an SQL editor window

Step 3 – In the SQL editor window

1. Enter the following query
   SELECT EMPNO, ENAME, JOB, MGR FROM EMP
   Note – we are selecting the columns EMPNO, ENAME, JOB & MANAGER from the source, so we have kept only those in the select query
2. Select OK Button

Step 4 – In the “edit transformations” window,

1. Select Ports tab from the menu
2. Under the ports tab, you will see all the ports. Keep only the ports EMPNO, ENAME, JOB, MGR and delete other ports

Step 5 – After deletion of ports, Select OK Button

Now, again click on properties tab in Edit Transformations window, and you will see only those data that you have selected.

When you click on “OK” button it will open SQL Editor Window, and

1. It will confirm the data you have selected are correct and ready for loading into the target table
2. Click on OK button to process further

Conclusion:

Informatica has many transformations that help in extracting, transforming and loading data to the target as per user needs. The interface which it provides is easy to use and with basic knowledge of SQL data can be transformed and used in various reports and analysis.Hope you have found all the details that you were looking for, in this article.