Informatica Transformations Tutorial

Informatica Transformations

Informatica Transformations are repository Objects that are used to perform aggregations, sorting, merging, modifying, distributing, etc., while passing data through them.

The Power Center Designer provides a set of transformations in Informatica that perform specific functions. For example, Sorter transformation will sort the input fields based on a specified field.

Transformations in Informatica are objects that create. Modify or pass data to a defined target. The source data can be modified as per the target system’s requirement. These transformations in Informatica are classified into connected and unconnected transformations. When a transformation is connected to some other transformation then it is connected and when it is a standalone transformation then it is unconnected. There are two types of transformations in Informatica that are active and passive. Let us have a look at these with examples.

Classification of Transformation

Transformation is classified into two categories-the first one based on connectivity and second based on the change in several rows. First, we will look at the transformation based on connectivity.

1. Here are two types of transformation based on connectivity, such as:

• Connected Transformations
• Unconnected Transformations

In Informatica, one transformation is connected to other transformations during mappings are called connected transformations.

For example, Source qualifier transformation of Source table Stud is connected to filter transformation to filter students of a class.

Those transformations whose not link to any other transformations are called unconnected transformations.

Their functionality is used by calling them inside other transformations. And these transformations are not part of the pipeline.

The connected transformations are preferred when the transformation is called for every input row or expected to return a value.

The unconnected transformations are useful if their functionality is required periodically only or based upon certain conditions. For example, calculate the tax details if tax value is not available.

1. Here are two types of transformations based on the change in several rows, such as:

• Active Transformations
• 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 10 numbers of rows as input, and it returns 15 numbers of rows as an output, then it is an active transformation. In the active transformation, the data is modified in the row.

Passive Transformations do not change the number of input rows. In passive transformations, the number of input and output rows remains the same, and data is modified at row level only.

In the passive transformation, we cannot create new rows, and no existing rows dropped.

List of Transformations in Informatica

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

What is Filter Transformation?

Filter Transformation is an active transformation because it changes the number of records. We can filter the records according to the requirements by using the filter condition.

For example, for loading the student records having rollno equal to 20 only, we can put filter transformation in the mapping with the filter condition rollno=20. So only those records which have rollno =20 will be passed by filter transformation, rest other records will be dropped.

Step 1: Create a mapping having source “Stu” and target “Stu_target”.

Step 2: Then in the mapping

• Select Transformation menu
• Select the create option.

Step 3: In the create transformation window

• Select Filter Transformation from the list.
• Enter Transformation name fltr_rollno_20
• Select create option

Step 4: The filter transformation will be created, click on the Done button in the creative transformation window.

Step 5: in the mapping,

• Drag and drop all the source qualifier columns from the filter transformation.
• And Link the columns of filter transformation to the target table.

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

• Select the properties menu.
• Click on the filter condition editor.

Step 7: Then,

• Enter filter condition rollno=20.
• Click on the OK button.

Step 8: Again in the edit transformation window,

• We will see the filter condition in the properties tab.
• Click on the OK button.

Now save the created mapping and execute this after creating session and workflow. In the target table, only the rollno = 20 will be loaded from the record.

Transformations in Informatica with Examples

Active transformations in Informatica are those that modify rows and number of input rows while passive ones do not change a number of input rows and no new rows are created or existing dropped.

Following are the types of active transformations:

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

1. Source Qualifier Transformation

This transformation is active and connected. It is used to represent rows of the read integration service. When a flat-file or relational data source is involved at that time Source Qualifier Transformation comes into the picture. With this transformation how data is fetched and overridden can be defined.

Example: Modify source qualifier mapping ‘m_student_target’ to return only selected columns.

Step 1: Open mapping ‘m_student_target’ in the mapping designer.

Step 2: Double-click on the Source Qualifier transformation “SQ_STUD”. It will open edit transformation property window for it. Then

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

Step 3: In the SQL editor window enter the following query-

SELECT ROLLNO, STUDNAME, DEPT, HOD FROM STUD and click on OK.

Step 4: Go to “edit transformations” window,

• Select the Ports tab from the menu
• Under the ports tab, you will see all the ports. Keep only the ports ROLLNO, STUDNAME, DEPT, HOD and delete other ports. Once you delete these ports click on OK.

Step 5: Go to edit transformation again and you will be able to confirm that the data selected is correct and ready to load.

2. Aggregator Transformation

This transformation is used when a user wants to perform aggregate functions like sum, average, etc.

Example: To calculate the average marks of students department wise.

Step 1: Create a new database target table, for example, say “avg_mks_deptwise”

Step 2: Create a New mapping “m_ avg_mks_deptwise “.

In order to create a new mapping, we need a source table (STUD) and target table (avg_mks_deptwise) both in mapping designer for that we need to

• Import the target table ” avg_mks_deptwise ” in the mapping.
• Import the source table “STUD”

Step 3: In mapping, delete all columns other than marks and department. Now, create a new aggregator transformation using a toolbox menu. Click on the Aggregator icon and a new transformation will be created.

Step 4: Drag and drop marks & deptno columns from source qualifier (SQ_STUD) to the aggregator transformation

Step 5: Double-click on the aggregator transformation to open its properties, and then

• Add a new port in the transformation
• Rename the port name to AVG_MKS
• Change the data type of this new port to double
• Make this port as an output port by selecting the checkbox of the output port.
• Click on the expression option

Step 6: In the expression window

• Add expression- avg (MKS)
• Select Ok Button, this will bring back the edit transformation window.

Step 7: In the edit transformation window, select the option “GroupBy” by marking the checkbox against the deptno column and Click Ok. Link deptno and avg_mks by using aggregator transformation.

3. Router Transformation

Router transformation is used to filter source data. It also enables to include discarded data in the mapping. In addition to these multiple filters, conditions can be applied to multiple data sets.

Example:

Step 1: Create a mapping having source “STUD” and target “STUD_TARGET.”

Step 2: Create a new transformation and in create window do below:

• Select router transformation
• Enter a name for the transformation “rtr_deptno_10”
• Select Create option

Router transformation will be created in mapping.

Step 3: Drag and drop all columns from source qualifier to router transformation.

Step 4: Double-click on the router transformation, then in the transformation property of it.

• Select the group tab and enter the group name as “deptno_20” and click on the filter.
• Go to expression editor and filter deptno_20 and select OK.

Step 5: Connect the ports from the group deptno_20 of router transformation to target table ports

4. Joiner Transformation

This transformation is used when you want to create joins. The main advantage of these joins is that heterogeneous databases can be used to create these joins. Joins like master outer join, detail outer join, fuller outer join and normal join can be created.

Example: Join Stud table and Dept table.

Step 1: Create new mapping and import source tables Stud and Dept and target tables.

Step 2: Go to the transformation menu and select the create option. Enter the transformation name ‘stud_jn_dept’ and choose to create an option.

Step 3: Drag and drop all the columns from both the source qualifiers to the joiner transformation

Step 4: Double click on joiner transformation and select condition tab. Click on add new condition icon and then select deptno in master and details columns list.

Step 5: Now go to the properties tab and select normal join and click OK.

Step 6: Once this is done then go to the ports tab and select any column as a particular source and then select OK. Link all columns from joiner transformation.

5. Transaction Control Transformation

This transformation allows committing and rollback transactions during the execution of any mapping. There are five variables to handle these operations. These are TC_CONTINUE_TRANSACTION, TC_COMMIT_BEFORE, TC_COMMIT_AFTER, TC_ROLLBACK_BEFORE, and TC_ROLLBACK_AFTER.

Example: Commit data for department no 20

Step 1: Create a new transformation and then select transaction control. Enter a valid name like ‘tc_commit_dept20’ and create.

Step 2: Drag and drop all the columns from source qualifier to the transaction control transformation then link all the columns from the transaction control transformation to the target table.

Step 3: Select property tab and and click on transaction control. Enter the expression as:

“iif(deptno=20,tc_commit_before,tc_continue_transaction)” and select OK. Once this is done save the mapping.

Types of Transformations in Informatica

Informatica transformations can divide into two types: Active Transformations or Passive Transformations. We can further divide them based on the connectivity in mapping: Connected Transformations and Unconnected Transformations.

Active Transformations in Informatica

An active type of transformation in Informatica can change the number of rows that pass through the transformation. For example, if the input rows do not meet the specified expression, then those rows will not move to the target.

Passive Transformations in Informatica

If a transformation does not change the number of rows that pass through it, then the transformation is called Informatica passive transformation.

Connected Transformations in Informatica

Within a Mapping, if a transformation connected to other transformations, or if it connected directly to the target table, then it is called Informatica Connected Transformation.

All the active and passive transformation can call as connected transformations in Informatica. A connected transformation can receive / or provide multiple input ports

Unconnected Transformations in Informatica

Within the Mappings, if a transformation not connected to other transformations, then we can call it as Unconnected Transformation in Informatica. It means it neither connected to Source nor the target.

Unconnected Lookup is the best example of Informatica Unconnected Transformation.

Informatica Transformations List

Before we get into the list of Informatica transformations and their definitions, let me open my Power Center Designer to display the transformation in mappings:

and few more transformations

The following are a list of Informatica transformations that are available to transform data. I suggest you click on the URL (red color links) to navigate to the required transformation and find the example with a detailed explanation.

INFORMATICA TRANSFORMATIONS	TYPE	DESCRIPTION
Aggregator	Active / Connected	This transformation is used to perform Aggregate calculations such as SUM, MIN, COUNT etc.
Application Source Qualifier	Active / Connected	It represents the rows that the Integration Service reads from an application.
Custom	Active or Passive / Connected	This transformation is used to call Procedures from DLL or shared Library
Data Masking	Passive / Connected	Use this transformation to replace original data (sensitive data) with realistic test data. This is very useful while we are working in Non- Production environment.
Expression	Passive / Connected	This transformation helps you to write Custom Calculation using Expression Editor.
External Procedure	Passive / Connected or Unconnected	This is used to call a procedure in a Shared Library or from the Com layer of Windows.
Filter	Active / Connected	This transformation is used to Filter the data
HTTP	Passive / Connected	Use this transformation to connect with HTTP Server and read or update data.
Input	Passive / Connected	This is available in Mapplet Designer. Use this transformation to define the Input rows.
Java	Active or Passive / Connected	This is very useful to execute the code written in Java.
Joiner	Active / Connected	Use this transformation to Join data from multiple tables. There are four types of Joins: Normal Join, Master Outer Join, Detail Outer Join, and Full Outer Join.
Lookup	Active or Passive / Connected or Unconnected	This Informatica transformation is useful to look up data present in Relational databases, or flat files
Normalizer	Active / Connected	Use this transformation to normalize the data (DE normalized).
Output	Passive / Connected	This is available in Mapplet Designer. Use this transformation to define the output rows.
Rank	Active / Connected	Used to Limit the records from Top or Bottom
Router	Active / Connected	Based on the group condition, this transformation will route the data to multiple destinations or transformations.
Sequence Generator	Passive / Connected	This Informatica Transformation will generate Numeric values. We mainly use this to generate primary keys
Sorter	Active / Connected	This is used to Sort the data based on the specified Column or Key
Source Qualifier	Active / Connected	It represents the rows that the Service reads from a Flat File or Relational Database.
SQL	Active or Passive / Connected	This Informatica transformation helps you to execute SQL Queries against a Database
Stored Procedure	Passive / Connected or Unconnected	Use this transformation to call a stored procedure.
Transaction Control	Active / Connected	By using this transformation you can define Commit, and Rollback Transactions
Union	Active / Connected	This is used to combine or merge data from different databases or sources.
Unstructured Data	Active or Passive / Connected	It transforms data in a semi-structured or unstructured format.
Update Strategy	Active / Connected	This transformation is used to determine whether to Insert, Update, Delete, or reject Rows.
XML Generator	Active / Connected	It reads data from one or more ports and returns XML
XML Parser	Active / Connected	This transformation reads XML file and output the data.
XML Source Qualifier	Active / Connected	It represents the records that the Integration Service reads from an XML source.

If you want to save the above specified list of Informatica Transformations in your Mobile, then use below image

Conclusion 

Transformations in Informatica hence help data transformations and processing easier. With so many transformation options to provide Informatica will help you with your data in the best way.