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.

Source Qualifier Transformation: What is it?

The Source Qualifier transformation in Informatica is a crucial transformation used to extract data from various sources, such as databases, flat files, or other systems, and load it into the Informatica data flow for further processing. Here are the steps and procedures involved in using the Source Qualifier transformation:

Drag and Drop Source Qualifier Transformation:

Open the Informatica PowerCenter Designer tool and create a new mapping or open an existing one. Drag and drop the Source Qualifier transformation from the Transformation palette onto the mapping canvas.

Connect the Source to the Source Qualifier:

Connect the source (database, flat file, etc.) to the Source Qualifier transformation by drawing a data flow line between them. The Source Qualifier transformation serves as a bridge between the source and the next transformation or target in the mapping.

Configure Source Qualifier Properties:

Double-click on the Source Qualifier transformation to open its properties.

• Connection Information: Specify the database connection details or file properties, such as filename, delimiter, and file format.
• SQL Query: Provide a SQL query or select the necessary table(s) and columns from the source.
• Source Filter: If needed, you can apply filters to the data at the source level using the Source Filter property.
• Sorted Ports: If the source data is pre-sorted, you can specify the sorted ports to optimize the integration service performance.

Columns and Data Types:

Ensure that the ports (columns) in the Source Qualifier match the data types of the source table or file. If there are any discrepancies, you may need to use data conversion functions.

Source Qualifier Overrides:

In some cases, you may want to override the default behavior of the Source Qualifier transformation. You can use the Source Qualifier Overrides to change the default query or session properties, such as database hints, SQL query, and number of sorted ports.

Output to Transformation or Target:

Connect the Source Qualifier output to the next transformation or directly to the target (e.g., Target table or file). You can apply further transformations, data filtering, or aggregations in the subsequent transformations before loading the data into the target.

Validate and Run:

Validate the mapping to ensure that there are no errors or warnings. Once the mapping is validated successfully, you can run the workflow containing the mapping to execute the data extraction and transformation process.

Step 1: Start mapping designer and choose “m_emp_emp_target”.

Step 2: Double-click the “SQ_EMP” source qualifier transformation. The edit transformation property window will be opened. Then

1. Tap on the properties tab
2. An SQL editor window will appear after selecting the SQL Query Modify option.

Step 3:

1. Open the SQL editor window
2. Do the following query
3. SELECT EMPNO, ENAME, JOB, MGR FROM EMP
4. Please take note that we are just keeping the columns EMPNO, ENAME, JOB, and MANAGER in the select query because we are picking those from the source.
5. Then, Select Ok button

Step 4:

1. Select the Ports tab from the menu in the “edit transformations” box.
2. You may see every port by selecting the ports tab.
3. Delete all other ports and just keep EMPNO, ENAME, JOB, and MGR.

Step 5: Select the OK button after deleting the ports.

Once more, select the properties tab within the Edit Transformations window, and the displayed data will be limited to your previous selection.

It will launch the SQL Editor window when you click the “OK” button.

It will verify that the data you’ve chosen are accurate and prepared to be loaded into the target database.

To continue the procedure, click OK.

Error Handling and Exception Handling in Informatica Transformations

Error handling and exception handling are critical aspects of data integration workflows in Informatica. They help ensure that data processing is robust, reliable, and can gracefully handle unexpected issues that may arise during ETL (Extract, Transform, Load) operations. Informatica provides several mechanisms for error and exception handling in its transformations. Here are some of the key components and techniques used for error handling in Informatica transformations:

Error Logging:

Informatica allows you to enable error logging for transformations. When error logging is enabled, any error or exception that occurs during data processing is logged into error tables or flat files. These error logs can be reviewed later for troubleshooting and analysis.

Error Handling Strategies:

In transformations like Expression, Aggregator, Joiner, etc., you can configure error handling strategies for individual ports or the entire transformation. Common error handling strategies include “Abort,” “Fail,” “Continue,” or “Ignore.” These strategies determine the course of action when an error occurs during data processing.

Error Port:

Some transformations, such as Expression and Lookup, have an additional error output port that indicates whether any errors occurred during data processing for a specific row. By using the error output port, you can route error rows to separate paths in the mapping.

Error Thresholds:

In Aggregator transformations, you can set error thresholds to define how to handle transformation errors when they exceed specified limits. For example, you can choose to ignore rows with errors, fail the entire transformation, or continue processing despite errors.

Transaction Control:

Informatica offers transaction control options for session properties. By defining the session’s transaction control strategy, you can specify how to handle errors during the session run. Options include “Commit on Success,” “Commit on Failure,” and “Commit on Finish.”

Event-Based Error Handling:

You can configure event-based error handling to perform custom actions when specific errors occur. To address certain fault conditions, for instance, you may send an email message or run a specific script.

Reject Files:

When loading data into target systems, you can configure reject files to capture rejected records that do not meet the data quality criteria or encounter errors during loading. These reject files provide detailed information about the errors encountered.

Error Transformation:

The Error Transformation is a special transformation used to capture and handle transformation errors. You can link this transformation to the error output of a Source Qualifier or any other transformation to process error rows separately.

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.