Overview of Transformation:

Informatica Transformations are repository objects that generate, edit, or transfer data to specified target structures like files, tables, or any other necessary destination fields. An instruction set that specifies the data flow and loads the data into the target is all that a transformation is.

Two types of Transformation

Active Transformation:

1 In Active Transformation the number of input rows modified while passing the data through Transformation.

2. Number of input and output rows are different.

3. Active Transformation modifies the transaction boundary.

4. Row type attributes are changed in Active Transformation.

Passive Transformation:

1. The number of rows does not change when it passes through Passive Transformation.

2. Number of input rows are equal to number of output rows.

3. Passive transformation manages the transaction boundary.

4. Passive transformation maintains row type attributes.

Transformation Types and its Descriptions

Source Qualifier: When a relational or a flat file source definition to a mapping is connected to a Source Qualifier. The Source Qualifier transformation represents the rows that the informatica Integration Service reads when it runs the session. it converts relational or flat file datatypes into Informatica datatypes to process it to next step.

Aggregator Transformation: It is used to perform calculation like counts, averages, sum etc. which is applied on group of data. It is performed of a group of rows.

Expression Transformation: It is a passive and connected Informatica transformation. Row-wise manipulation is done using expression transformations. Individual record row wise is participated in this transformation.

Filter Transformation: It is used to remove unwanted records from the mapping. User can define the filter condition in the Filter transformation, and based it, the records will be rejected or passed in the mapping. The default condition for this transformation is always TRUE.

Joiner Transformation: Joiner Transformation is a connected and active transformation. Joiner transformation is used to join the source data from two heterogeneous sources residing in different locations or file systems. Joiner Transformation can also join data from same sources.

Number of joiners = Number of source -1

Lookup Transformation: This transformation is used to get data based on a specified lookup condition which is specified by user. This look up condition is used to get the data from other table based on the look up conditions.

Normalizer Transformation: The Normalizer transformation is an active transformation that transforms one incoming row into multiple output rows. When the Normalizer transformation receives a row that contains multiple-occurring data, it returns a row for each instance of the multiple-occurring data.

Rank Transformation: The Rank transformation selects the top or bottom range of data. Use the Rank transformation to return the largest or smallest numeric values in a group. You can also use the Rank transformation to return strings at the top or bottom of the mapping sort order.

Router Transformation: The Router transformation is an active transformation that you can use to apply a condition to incoming data.

In a Router transformation, Data Integration uses a filter condition to evaluate each row of incoming data. It tests the conditions of each user-defined group before processing the default group. If a row meets more than one group filter condition, Data Integration passes the row multiple times. You can either drop rows that do not meet any of the conditions or route those rows to a default output group.

Sequence Generator Transformation: It is a passive and connected transformation that generates numeric values for rows in a table. It is used to create unique primary key values, replace missing primary keys, it creates cycle of sequence of numbers.

This transformation contains two output fields, NEXTVAL and CURRVAL.

Sorter Transformation:  Sorter Transformation is active and connected Transformation which is used to sort data in Ascending or Descending order which is based on key. Key may be single or multiple.

SQL Transformation: it is used to call a stored procedure or function in a relational database or to processes SQL queries midstream in a pipeline. This transformation calls a stored procedure or function, process a saved query, or process a query that has created in the transformation SQL editor.

Union Transformation: Union Transformation is an active transformation. It merges data which is coming from multiple sources either from homogenous or from heterogeneous. It merges data in vertical roe by row format.

Update Strategy Transformation: This transformation is an active transformation. When a data warehouse is designed, user has to decide what sort of information to store in targets. As part of designing table user need to determine whether to maintain all the historic data is required or just the most recent changes required.