Informatica Transformations

Informatica Transformations

Informatica transformations are the heart and soul of how data is transformed within Informatica’s enterprise suite of data integration tools. This is especially true within Informatica PowerCenter Designer. Transformations here enhance, modify, route, filter, aggregate, and manipulate business data in many different ways.

Just as with any programming language, there are many ways to accomplish the same data integration task. Of course, there are recommended best practices that should be followed for maintainability, readability, cohesion, and performance. We will get to these concepts, but first lets discuss the idea of transformation being active versus passive.

Active Transformations

In general, a transformation is considered active if it performs any of the following functions:

1. Change the number of rows that pass through the transformation.

An example of the this scenario is a router transformation which can route records to different targets or restrict a record from passing to any target.

2. Change the transaction boundary.

A Transaction Control transformation is example of this scenario because it defines a commit or roll back transaction based on an expression evaluated for each row.

3. Change the row type.

The Update Strategy transformation an example of this scenario because it flags rows for insert, delete, update, or reject.

Passive Transformations

In contrast to an active transformation, a passive transformation functions with the opposite characteristics of the active transformation:

1. Does NOT change the number of rows that pass through the transformation.

2. Does NOT change the transaction boundary.

3. Does NOT change the row type.

Now that we have had a chance to review active and passive transformations, lets quickly walk through connected and unconnected Informatica transformations.

Connected and Unconnected Transformations

A connected transformation must be connected to the path data path or data flow. Some transformation can be both connected or unconnected, but the majority of Informatica transformations must be connected.

Unconnected transformations do not have to be connected to the data path or data flow. Most unconnected transformations can be both connected or unconnected, such as a stored procedure or lookup transformation.

Creating a Informatica PowerCenter Transformation

There are a few different ways to create a Informatica transformation within PowerCenter.

1. Within the mapping designer, one can add, configure, and connect Informatica transformations.

2. Just as within the mapping designer, one can also add, configure, and connect Informatica transformations in the mapplet designer.

3. Finally, the transformation developer can be used to create and configure Informatica transformations. In order to use the tranformations, it must be implemented within a mapping or maplet as described in the first 2 steps. One major benefit to developing transformations within the transformation developer is the ability to create a common transformation that can be reused in more than one mapping or mapplet.

Informatica Transformations, Types and Descriptions

As described in Informatica PowerCenter’s transformations overview help, below is a list of Informatica PowerCenter 9.0 transformations, their types, and descriptions:

Transformation Type Description
Aggregator Active/Connected Performs aggregate calculations.
Application Source Qualifier Active/Connected Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.
Custom Active or Passive/Connected Calls a procedure in a shared library or DLL.
Data Masking Passive/Connected Replaces sensitive production data with realistic test data for non-production environments.
Expression Passive/Connected Calculates a value.
External Procedure Passive/Connected or Unconnected Calls a procedure in a shared library or in the COM layer of Windows.
Filter Active/Connected Filters data.
HTTP Passive/Connected Connects to an HTTP server to read or update data.
Input Passive/Connected Defines mapplet input rows. Available in the Mapplet Designer.
Java Active or Passive/Connected Executes user logic coded in Java. The byte code for the user logic is stored in the repository.
Joiner Active/Connected Joins data from different databases or flat file systems.
Lookup Active or Passive/Connected or Unconnected Look up and return data from a flat file, relational table, view, or synonym.
Normalizer Active/Connected Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources.
Output Passive/Connected Defines mapplet output rows. Available in the Mapplet Designer.
Rank Active/Connected Limits records to a top or bottom range.
Router Active/Connected Routes data into multiple transformations based on group conditions.
Sequence Generator Passive/Connected Generates primary keys.
Sorter Active/Connected Sorts data based on a sort key.
Source Qualifier Active/Connected Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.
SQL Active or Passive/Connected Executes SQL queries against a database.
Stored Procedure Passive/Connected or Unconnected Calls a stored procedure.
Transaction Control Active/Connected Defines commit and rollback transactions.
Union Active/Connected Merges data from different databases or flat file systems.
Unstructured Data Active or Passive/Connected Transforms data in unstructured and semi-structured formats.
Update Strategy Active/Connected Determines whether to insert, delete, update, or reject rows.
XML Generator Active/Connected Reads data from one or more input ports and outputs XML through a single output port.
XML Parser Active/Connected Reads XML from one input port and outputs data to one or more output ports.
XML Source Qualifier Active/Connected Represents the rows that the Integration Service reads from an XML source when it runs a session.

Informatica Transformations Summary

Informatica Transformations are core to PowerCenter. Without transformations we would not be able to manipulate data to fit our business need. We characterize transformations as passive or active, connected or unconnected. Understanding these objects is essential to programming in Informatica PowerCenter and adding business value to the data warehouse.

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.

Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.

Find Aaron Gendle on LinkedIn -
Aaron Gendle

Leave a Reply

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