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.
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.
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.
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:
|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.|
|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.
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 -
Latest posts by Aaron Gendle (see all)
- 5 Reasons to Quit Your Job Today - June 20, 2017
- The 2017, Top 50
Data Driven CEO’s - March 1, 2017
- 25+ Advanced Informatica Interview Questions and Answers - February 7, 2015