Informatica Transformations are repository Objects that are used to perform aggregations, sorting, merging, modifying, distributing, etc., while passing data through them.
The Informatica Power Center Designer provides a set of transformations that perform specific functions. For example, the Sorter transformation will sort the input fields based on a specified field.
Types of Transformations in Informatica
A transformation is a part of Mapping, which transforms or modifies the data as per the selected transformation. This section covers the list of available ones with practical examples.
Informatica transformations can be divided into two types: Active or Passive. We can further divide them based on the connectivity in mapping: Connected and Unconnected Transformations.
Active Transformations in Informatica
An active type of transformation can change the number of rows that pass through it. 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
The passive transformation does not change the number of rows passing through it.
Connected Transformations
Within a Mapping, if a transformation is connected to another or connected directly to the target table, it is called Connected.
All the active and passive can call connected transformations in Informatica, and they can receive / or provide multiple input ports.
Unconnected Transformations
Within the Mappings, if the transformation does not connect to others, we can call it Unconnected. It means it is neither connected to the Source nor the target.
- Unconnected Lookup Transformation is the best example of Informatica Unconnected.
Informatica Transformations List
Before we get into the list of transformations and their definitions, let me open my PowerCenter Designer to display the available list in mappings:
and few more
The following is a list of Informatica transformations that are available to transform data. I suggest you click on the URL (blue color links) to navigate to the required transformation and find the example with a detailed explanation.
| Transformations | Type | Description |
|---|---|---|
| Aggregator Transformation | Active / Connected | It is useful 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 | It is useful to call Procedures from DLL or shared library |
| Data Masking | Passive / Connected | Use this to replace original data (sensitive data) with realistic test data. This is very useful while we are working in a non-production environment. |
| Expression Transformation | Passive / Connected | It helps you to write Custom calculations using Expression Editor. |
| External Procedure | Passive / Connected or Unconnected | This is useful for calling a procedure in a Shared Library or from the Com layer of Windows. |
| Filter Transformation | Active / Connected | Used to Filter the data |
| HTTP | Passive / Connected | Use this to connect with the HTTP Server and read or update data. |
| Input | Passive / Connected | This is available in Mapplet Designer. Use this one to define the Input rows. |
| Java Transformation | Active or Passive / Connected | This is very useful for executing the code written in Java Programming Language. |
| Joiner Transformation | Active /Connected | 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 Transformation | Active or Passive / Connected or Unconnected | This Informatica transformation is useful for looking up data in Relational databases or flat files. |
| Normalizer Transformation | Active /Connected | Use this to normalize the data (DE normalized). |
| Output | Passive / Connected | This is available in Mapplet Designer. Use this to define the output rows. |
| Rank Transformation | Active /Connected | Used to Limit the records from Top or Bottom. Also, refer to the Informatica Rank with Group By option. |
| Router Transformation | Active /Connected | Based on the group condition, it will route the data to multiple destinations or trans. |
| Sequence Generator Transformation | Passive / Connected | This Transformation will generate Numeric values. We mainly use this to generate primary keys. |
| Sorter Transformation | Active /Connected | This is used to Sort the data based on the specified Column or Key. Please refer to the Remove Duplicates using Informatica Sorter article as well. |
| Source Qualifier Transformation | Active /Connected | It represents the rows that the Service reads from a Flat File or Relational Database. |
| SQL Transformation | Active or Passive / Connected | It helps you to execute SQL Server Queries against a Database |
| Stored Procedure Transformation | Passive / Connected or Unconnected | Use this to call the SQL Server stored procedure. |
| Transaction Control Transformation | Active / Connected | By using this, you can define Commit and Rollback Transactions |
| Union Transformation | Active / Connected | This is useful to combine or merge data from different databases or sources. |
| Unstructured Data | Active or Passive / Connected | It transforms data into a semi-structured or unstructured format. |
| Update Strategy Transformation | Active /Connected | Used to determine whether to Insert, Update, Delete, or reject Rows. Also try the Informatica Update Strategy using Session Properties option and a complex Update Strategy Example. |
| XML Generator Transformation | Active /Connected | It reads data from one or more ports and returns XML |
| XML Parser Transformation | Active /Connected | Reads XML file and outputs the data. |
| XML Source Qualifier Transformation | 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 Transformations on your Mobile, then use the below image.
