Updated March 21, 2023
Introduction to Transformations in Informatica
Transformations in Informatica are the built-in features with Informatica products such as, Informatica PowerCenter that are used to transform and validate the source data as part of the ETL process flow. Transformation in Informatica is majorly two categories, known as active transformations and passive transformation. There are several business scenarios such as filtering of inputting data, routing the data, or shorting the Informatica mappings’ input data to develop the business requirements. Also, Informatica supports both connected and unconnected type of transformation functionalities as part of the product offering. Some of the common transformations in Informatica are Source qualifier; the Informatica developers widely use router, joiner and aggregator transformations.
Types of Transformations in Informatica
Transformations are mainly of two types. They can be connected or unconnected. The transformations connected to other transformations are known as connected transformations while the ones that are not connected to any other transformation are unconnected transformations. Similarly, there can be active transformations or passive transformations. Active transformations are those which modify data rows and the input which is passed to it. Passive transformations, on the other hand, do not change the number of rows. The input and output rows remain the same and data is modified at row level only.
Let us have a look at the different transformations provided by Informatica. They are listed as below:
- Source Qualifier Transformation
- Aggregator Transformation
- Router Transformation
- Joiner transformation
- Rank Transformation
- Sequence Generator Transformation
- Transaction Control Transformation
- Lookup and Re-usable transformation
- Normalizer Transformation
- Performance Tuning for Transformation
- External Transformation
- Expression Transformation
We will have a look at a few of these in detail as below.
1. Joiner Transformation
This transformation is active and connected. When two sources are to be joined on certain conditions, then this transformation is used. These can be any two relational sources, a relational source and a flat file, a relational source, and an XML file or two flat files. There should be at least one port that is common between both these sources. While joining these two sources, you must specify a condition. Once could be a master and the other detail.
Example of joiner transformation:
This is a joined where conditions are applied on below ports:
The master port is BE and Activity_code. These are present in both sources. The data is filtered and joined on the above conditions. The other properties of these joins can be found in the properties tab as below:
The joiner can have following joins.
- Normal join: This join will remove all records which will not match the joiner condition.
- Master outer join: This will discard all records in the master source which do not match, and all rows from detail source will be taken into the output.
- Detail Outer join: In this join, all records from the master source will be considered while only matching rows of detail source will be taken into consideration.
- Full outer join: This will keep all rows from both master and detail tables.
2. Lookup Transformation
This transformation is used to look up for data into a relational source. The data can be looked up into either source or target. These transformations can either be connected or unconnected. The connected lookup will take inputs indirectly from any mapping. At the same time, unconnected will receive its inputs from lookup expression or some other transformation. Connected lookups will return multiple columns while unconnected one will return only one column from each row.
The below transformation is being used to check for existing records in the target table CL1_PA_HIST.
The ports that are being looked up are given in L Column. The ones which are checked are being looked up in the target. The conditions on these ports can also be specified in the condition tab. This can be done as below.
3. Router Transformation
The router transformation is active and connected. The router transformation is similar to filter transformation. It will consider the data which captures data where the condition does not match. In the filter condition, the data where the condition matches are considered. Router, on the other hand, has the data where the condition does not match are stored. It is useful to check multiple conditions. The below transformation can be used to insert and update records. This router is used to group the records based on the return value of lookup if the return value is not null and mark it as an update else insert.
The filter condition can be applied to groups. These can be done by going to the Groups tab as below:
Here the conditions are applied on four columns, and data is traversed ahead on these conditions as multiple conditions can also be specified. The update conditions are also specified in this transformation.
The data can be easily segregated and routed to the next transformations. There is also a default group that can have records sets that do not satisfy the condition.
4. Expression Transformation
This transformation can be used to calculate values present in a single row before it is passed on to the target. The transformation can also be used to pass through the data from one transformation to another. Expressions can be user-defined and be specified as per user.
The below example is of expression transformation, which sends the data to the next transformation. All ports are sent as it is.
5. Update Strategy Transformation
This transformation is used to update data that is present in the target table. This may include maintaining the history of data or data about changes that are done recently. It can also be specified which rows are to be treated to update source rows.
The below transformation Marks records for Update or Insert according to GG_OP_TYPE.
The strategy is mentioned in the update strategy expression.
Conclusion
Informatica has many transformations that help extract, transform, and load data to the target as per user needs. The interface provides is easy to use, and with basic knowledge of SQL data can be transformed and used in various reports and analysis. Informatica is a widely used tool because of the wide variety of transformations it provides. All transformations are beneficial and can be used as per the needs of the user.
Recommended Article
This has been a guide to Transformations in Informatica. Here we discuss an introduction to Transformations in Informatica and different types along with examples. You can also go through our other suggested articles to learn more –
