Informatica Transformations

Informatica Transformations are the repository objects which create, modify, or pass the data to the defined target structures like files, tables, or any other target fields required. A transformation is nothing but a set of instructions that define the data flow and load the data into the target. Informatica offers multiple transformations for performing specific functionalities. 

To understand Informatica transformations more precisely, let’s know what mapping is?

A mapping is nothing but a collection of source and target objects that are linked by a set of transformations. Hence the purpose of transformation is to indicate the operations that the integration service will perform on the data during workflow execution.

If you want to enrich your career and become a professional in Informatica, then visit Mindmajix - a global online training platform: "Informatica Certification Training"  This course will help you to achieve excellence in this domain.

Table of Contents: Informatica Transformations

• Classification of Informatica Transformations
• Unconnected Transformations
• Source Qualifier Transformation
• Transaction Control Transformation
• XML transformation
• SQL Transformation
• HTTP Transformation
• Union Transformation

Classification of Informatica Transformations

Transformations in Informatica are classified into two categories, one based on the connectivity of the transformations with each other, and the second based on the number of rows between the source and target.

Types of transformations in Informatica based on connectivity:

• Connected Transformations
• Unconnected Transformations

Connected Transformations

The transformations which are connected to one or more other transformations during mappings are called Connected transformations.

The connected transformations are mostly used when for every input row, transformation is called or is expected to return a value. 

Unconnected Transformations

The transformations that are not connected to any other transformations are called Unconnected transformations. These transformations are not part of the mapping pipeline.

The unconnected transformations are useful only when their functionality is used periodically or based on certain conditions. 

Types of transformations in Informatica based on the number of rows:

1. Active Transformation

2. Passive Transformation

Active Transformation

These transformations perform the following functions:

• It modifies the number of input rows and data rows that passed through the transformation.

• It modifies the transaction boundary.

• It changes the row type attributes.

Passive Transformation 

These transformations perform the following functions:

• It doesn’t change the number of input rows before and after transformation.

• It maintains the row type attributes.

• It manages the transaction boundary.

Passive transformations are generally used for updating values by calling an external procedure from a shared library and defines the input and output of maplets. A maplet represents the collection of transformations from the mapping.

If a transformation is being used as a passive transformation, there is no restriction that it cannot be used as an active transformation later. Similarly, unconnected transformation can be used as a connected transformation based on requirements.

In this blog, you’ll explore all the possible types of transformation that can belong.

Source Qualifier Transformation

The source qualifier transformation is an Active and Connected transformation used for representing the number of rows that the integration service reads during a session. While adding a flat-file or relational source in mapping, a source qualifier is used to connect it. Source qualifier transformation is added automatically when a source is added to a mapping. It converts the source data types to the Informatica native data types. You can also define and override the fetched data from the source.

Checkout Our  Informatica Tutorials

Aggregator Transformation

Aggregator transformation is an Active and Connected transformation used to perform aggregate calculations like averages, sums, counts, etc., on groups of data. The integration service stores data group and row data in the aggregate cache. For example, to calculate the average monthly sales or yearly sales of employees.

Router Transformation

Router Transformation is an Active and Connected transformation used to filter the source data.

It is similar to filter transformation, but the only difference is in filter transformation you can define only one condition and drop the rows that do not meet the condition, whereas in router transformation you can specify more than one condition and provide the ability to route the data that match the test condition. We can use this transformation to test the corresponding input data on multiple conditions. Router Transformation has input, output, and default groups.

Joiner Transformation

Joiner Transformation in Informatica is an Active transformation as well as a Connected transformation. It is used to create joins in Informatica. The joins created using this are similar to joins in databases. These joins are used to join two heterogeneous sources (different databases). There are two sources in joiner transformation, which we will be using for joins they are master source and detail source.

Join the output of the joiner transformation with another source to join more than two sources in mapping. You need n-1 joiner transformations to join the n number of sources in a mapping. 

The following are the joins supported by Joiner transformation:

• Detail outer join: In this, only the matching rows are returned from the detail source and all rows from the master source are returned.

• Master outer join: In this, all the records from the detail source are returned by the join, and only the matching rows from the master source are returned. It also discards the unmatched rows from the master source.

• Normal join: In this, only matching rows are returned from both the sources. It discards all the rows that do not match the join condition. 

• Full outer join: In this, all records from both master and detail sources are returned. 

Rank Transformation

Rank transformation in Informatica is an active and connected transformation that helps you to perform the filtering of data based on groups and ranks. For example, to select the top 5 salaried employee details department wise, then grouping can be done using this transformation.

Transaction Control Transformation

Transaction control transformation is an active and connected transformation used for rollback or commits transactions during the execution of mappings. Rollback and Commit operations represent the availability of data. Transactions can be defined based on a varying number of rows. For example, you can define a transaction on group rows in the student’s data using the section Id as a key.

 There are five in-built variables available in this transformation to handle commit and rollback operations. They are:

• TC_COMMIT_BEFORE: In this, a commit is performed before the processing of the current row.

• TC_CONTINUE_TRANSACTION: In this, there are no operations performed and data loading continues as it is.

• TC_ROLLBACK_BEFORE: In this, rollback is first performed, then data is processed to write.

• TC_COMMIT_AFTER: In this, the current row is processed, then a commit is performed.

• TC_ROLLBACK_AFTER: In this, the data is processed, then the rollback is performed.

Normalizer Transformation

Normalizer is used for converting a single row into multiple rows and vice versa. It is an active and connected transformation and also one of the most widely used Informatica transformations, primarily with COBOL sources. It represents your data in a more organized manner. In a particular row, if there is repeating data in multiple columns, then it can be divided into multiple rows. 

Union Transformation

Union transformation is an Active and Connected Informatica transformation used to merge multiple datasets from various streams/pipelines into a single dataset. This Informatica transformation works similar to the UNION ALL command in SQL. It doesn’t remove any duplicate rows. Instead, we can use an aggregator to remove duplicates which not expected in the target. 

XML transformation

XML transformation is an Active and Connected Informatica transformation. Informatica Power Center has powerful in-built functionality to process XML data. You can create an XML definition in Informatica from an XML file, flat file definition, DTD file, XML schema, or relational table definition.

There are three types of XML transformations:

• XML Source Qualifier Transformation: This is an Active transformation as well as a Connected transformation used only with an XML source definition. It describes the data elements that the Informatica Server reads while executing a session with XML sources. It possesses one input or output port for each column in the source. If you remove an XML source definition from mapping, the Designer also eliminates the corresponding XML Source Qualifier transformation.

• XML Generator Transformation: XML Generator is an Active as well as Connected transformation. We can use it to create XML inside a pipeline. This transformation outputs XML through a single output port and reads the data from one or more input ports.

• XML Parser Transformation : XML Parser Transformation is an Active transformation as well as Connected transformation. This transformation is used for extracting XML inside a pipeline and passing this to the target. XML is extracted through source systems such as databases or files. This transformation writes data to one or more output ports and reads XML data from a single input port.

Lookup Transformation

Lookup transformation is the most extensively used Informatica transformation. Based on the user requirement, it can be used as a Connected or Unconnected transformation combining it as an Active or Passive transformation. It is mainly used to lookup the required data present in the source, source qualifier, or target. You can also lookup data present in Relational tables, Flat File, and Views. Users can also use multiple lookup transformations in a mapping.

The Lookup transformations are created using the following ports:

• Output port (O)

• Input port (I)

• Return port (R) (only in case of Unconnected lookup)

• Look up ports (L)

The differences between Connected and Unconnected Lookup transformations are as follows:

SQL Transformation

SQL transformation in Informatica is a Connected transformation used to write or use SQL queries in the middle of the transformation. Using this transformation, you can insert, delete, retrieve,  and update rows from the database. 

The SQL transformation processes the external SQL queries or scripts created in the SQL editor. The following are the list of SQL statements that are used in the SQL transformation:

• Data definition statements (create, drop, alter, rename, truncate)

• Data manipulation statements (insert, delete, merge, update)

• Data retrieval statement (select)

• Data control language statements (revoke, grant)

• Transaction control statements (rollback, commit)

Expression Transformation

Expression Transformation in Informatica is a Passive and Connected transformation used for making non-aggregate calculations on the source data. Using this transformation, you can perform any type of manipulation you wish to perform on an individual record. It accepts the row-wise data, manipulates it, and moves it to the target.

Java Transformation

Java transformation in Informatica is defined as both Active or Passive Connected transformation. Using this transformation, you can generate multiple rows for a single input row based on the condition. 

Application source qualifier Transformation

Application source qualifier Transformation is defined as actively connected transformation which signifies the rows which the integration service reads from the application just like ERP source whenever it runs a session. The designer builts an application source qualifier by default when you append an Oracle E-Business Suite source to mapping.

Custom Transformation

Custom Transformation in Informatica is defined as both Active or Passive connected this will permit you to build the transformation logic within a procedure. We can build a few PowerCenter transformations with the help of custom transformation. Custom transformation calls any procedure within DLL or a shared library.

Data masking Transformation

Data masking Transformation is a passive connected transformation. Data masking transformation is used to replace the actual/original data with the test data. Data masking transformation is very helpful when we are working within a non-productive environment.

External Procedure Transformation

External procedure transformation is both passive connected or unconnected. By using External procedure transformation we can call a procedure within a shared library or else from the Windows Com layer.

Filter Transformation

Filter transformation is an actively connected transformation. This filter transformation filters out the data from the data flow based upon a particular filter condition. If you want to remove the irrelevant data from the data flow and to increase the performance you just need to place the filter transformation near the mapping sources.

Frequently Asked Informatica Interview Questions & Answers

HTTP Transformation

HTTP transformation is a passive connected transformation. We use this transformation to connect with the HTTP server and read or update data. 

• Read data from an HTTP server: Whenever the Integration Service reads the data from an HTTP server, it reclaims that data from the HTTP server plus transfers that data to the destination or a downstream transformation within the mapping.   

• Update data on HTTP server: Whenever the Integration Service writes over an HTTP server, it posts the data over the HTTP server also transfers HTTP server responses into the destination or else a downstream transformation within the mapping. 

Input Transformation

Input transformation is a passive connected transformation. The input transformation gives an input port to transfer the data into the mapplet whenever we use the mapplet within the mapping. Every port within the Input transformation connected over another transformation within the mapplet becomes any mapplet input port. Input transformations can get data of any single active source. 

Output Transformation

Output transformation is a passive connected transformation. This output transformation is available within mapplet designer we can utilize this transformation to determine the output rows.

Sequence Generator Transformation

Sequence Generator Transformation is a passive connected transformation which generates numeric values we essentially use this sequence generator transformation to generate primary keys.

Sorter Transformation

Sorter Transformation is an actively connected transformation that is used to sort the data based upon the particular key or a particular column.

Stored procedure Transformation

Stored Procedure Transformation means a passive transformation. Stored procedure transformation can be utilized within both connected also unconnected modes. Stored procedures are stored also run inside the database.  

Union Transformation

Union transformation is an actively connected transformation. With union transformation, we can merge from various sources or databases.

Unstructured Data Transformation

Unstructured Data Transformation is an active/passive connected transformation that transfers the data in an unstructured or semi-structured format.

Update Strategy Transformation

Update Strategy Transformation is defined as an actively connected transformation. We can utilize this transformation to determine whether to insert, update, reject or delete rows.