I’m getting used to search and find relevant information within Informatica Powercenter official documentation website, and one of the good resources I like to use it, go to a transformation page and download the PDF guide. The downloaded file will contain all about transformation you need to know.
For instance, if you go to Joiner Transformation overview, and click on “download guide” it will download a full 504 pages with pretty much everything you need to know about transformation.
Here in this post I want to remark all that is written in this guide and that do not belong to any specific transformation.
Transformation Overview
What is a transformation?
Transformation is an object that you can you in your Powercenter Designer mappings. Those objects can set rules and behaviors to handle data in multiple ways, like lookups, aggregate data, replace data, cleanse data, etc. This way, we can say that a Transformation is an object within a mapping that is basically used to represent a set of rules, which define the data flow and how the data is loaded into our targets.
Why Transformations are important?
This is more of a data modeling question than a Informatica one, but transformations are important so we can get data from heterogeneous sources and bundle them together by structuring it and normalizing it, by normalizing I mean, cleaning, cleansing, switching it all to the same datatype, etc. By doing that we can have data coming from sources like Microsoft SQL, Oracle, CSV files, Excel files, different applications, etc., and get them all together in the same data warehouse.
Transformations can be of which type?
Transformations can be broadly classified under two major factors: Connectivity and the Changing in number of rows.
Connectivity means if a transformation is connected, within the mapping, to another object, like from source to target. Unconnected means that the transformation is not connected to any other object. Lookup transformation can be connected or unconnected, as explained here.
Change in number of rows means that a transformation can affect the number of rows that will leave source and be registered on target. For instance, if a Filter transformation is used, than a source with 100 rows can be filtered down to 20 rows, and only those 20 rows be inputted on target, because of that we can Filter an Active transformation. In the other hand, if the transformation just modifies a row, but do not alter it, then we call it Passive transformation, like Expression transformation, where you can concatenate strings, but that won’t change the number of rows to record in your target.
Why do we need unconnected transformations?
Unconnected transformations are mainly used when we need specific data that meets a conditions. An unconnected transformation is called within another transformation, and returns a value to that transformation.
We don’t always want to connect a transformation to our targets because that would require to pass along that port/attribute to the target as well, so, in cases like these we make use of unconnected transformations.
There are only very few unconnected transformations: External Procedure (can be connected or unconnected), Lookup (can be connected or unconnected), Stored Procedure (can be connected or unconnected).
What is an active transformation?
An active transformation can perform any of the following actions:
- Change the number of rows that pass through the transformation.
- Change the transaction boundary. For example, the Transaction Control transformation is active because it defines a commit or roll back transaction based on an expression evaluated for each row.
- Change the row type. For example, the Update Strategy transformation is active because it flags rows for insert, delete, update, or reject.
Can multiple active or active and a passive transformation be connected to the same target?
No. Multiple active transformations or an active and a passive transformation can not be connected to the same target or downstream transformation. That is because Informatica Integration Service can not process all active transformations at the same time, they have to follow an order.
For example, one branch in a mapping contains an Update Strategy transformation that flags a row for delete. Another branch contains an Update Strategy transformation that flags a row for insert. If you connect these transformations to a single transformation input group, the Integration Service cannot combine the delete and insert operations for the row.
The Sequence Generator transformation is an exception to the rule. The Designer does allow you to connect a Sequence Generator transformation and an active transformation to the same downstream transformation or transformation input group.
A Sequence Generator transformation does not receive data. It generates unique numeric values. As a result, the Integration Service does not encounter problems concatenating rows passed by a Sequence Generator transformation and an active transformation.
What about Passive transformations?
A passive transformation does not change the number of rows that pass through the transformation, maintains the transaction boundary, and maintains the row type.
You can connect multiple transformations to the same downstream transformation or to the same transformation input group when all transformations in the upstream branches are passive. The transformation that originates the branch can be active or passive.
What are Native or Non-native transformations?
Native transformations are a set of transformations that the Designer provides. Non-native transformations are transformations that you create using the Custom transformation.
The Designer also provides some non-native transformations such as Java, SQL, and Union transformations.
Transformations Descriptions
For a complete list of transformations, their types and description, check https://docs.informatica.com/data-integration/powercenter/10-4-0/transformation-guide/working-with-transformations/transformations-overview/transformation-descriptions.html
Pedro Carvalho 
One thought on “Informatica Powercenter – Transformation Overview”