Tuesday, 8 November 2016

Types of Transformations in Informatica with examples

Definition of Transformation:

Transformation is a predefined object in Informatica Client. These are used to develop the business logics to transform the data from one format to another format. Every Transformation receives input columns, applies specific logic to transform the data and then sends the output. Transformations play a very important role while designing the mappings. Now here comes a very important answer for one interview question because it is one of the most Frequently asked informatica interview questions in many multi national companies like Accenture and Wipro. Read this upcoming point twice. In Informatica, these Transformations are categorized into 2 types as mentioned below:

  1. Active Transformation
  2. Passive Transformation


Active Transformation:

A Transformation which changes the number of rows while moving the data from the source to target is called as an 'Active Transformation'.
Examples: Filter Transformation is active transformation. The other T/Rs that come into this category are Router T/R, Aggregation T/R and many others.


Passive Transformation:


A Transformation which does not change the number of the rows while moving the data from source to target is known as 'Passive Transformation'.
Examples: Expression Transformation, Sequence Generator, Stored Procedure and others.

In the other way, the Transformations are also classified into 2 types.
  • Connected Transformations
  • Unconncected Transformations

Connected Transformation:

A Transformation which is connected in the mapping flow or which is a part of the mapping flow is called as 'Connected Transformation'. These T/Rs will receive multiple input ports and returns any number of output ports. For example: Router is Connected Transformation because it is always used as a part in the mapping flow.


Unconnected Transformation:

A Transformation which is not connected in the mapping flow is known as 'Unconnected Transformation'. It is a stand alone(separate) in the mapping. So that we can use any number of times in the mappings and whenever we want. These T/Rs will receive multiple input ports but can return only one output port.

The following 2 Transformations can in Informatica can be used as connected as well as unconnected.
  • Lookup Transformation and
  • Stored Procedure

These 2 can be used differently depending upon our usage. All the other T/Rs are connected only.
Each Transformation has its own specific use and used for different purpose depending up on our usage. Though there are many transformations in the Informatica Powercenter, we use only around half of the existing as it depends upon the nature of the project. In some projects, only direct mapping is enough to load the data into the relational target systems or flat files.

Uses of Transformations in Informatica:

There are many uses with the transformations that are used in this Informatica Tool that are used for the ETL purpose. Each transformation has its own use and applies specific logic which is a predefined. What it does is just take the input and send the output after applying those logics. Read the following to know the specific uses.
  1. Filter transformation is used to filter the records or the input that are coming from the different types of sources or from any other transformation. The sources may be a flat file or relational sources.
  2. Router Transformation is also used to filter the records but here you can apply as many filter conditions you want to load the data into many targets. When we apply each filter condition we can load that data into target systems. The main advantage with this transformation is we can also catch the rejected which is not possible in the filter transformation. This is the slight difference. This will help in various scenarios when we don't any type of logics to apply simply.
  3. Expression Transformation is for data scrubbing and data cleansing purposes. In this we can also create variable ports to store the value of the expressions or to store the values of the other ports temporarily.
  4. 'Sorter Transformation' is used to keep the records in any order. We can sort them in any order like ascending order or in descending order. We can also sort the records depending on the characters. When we want to sort the records in alphabetical order then Informatica Server sorts based on the value of binary values.
  5. 'Rank Transformation' is used to give the ranks for the records in top wise or bottom wise. Here the rank port plays a key role in giving the ranks. Rank Index port is created by default. In this transformation we will have input port, output port, rank port, variable port and group by ports. The group by port is used whenever we want to give the ranks in the group wise. Otherwise we no need to select the group by port. Don't forget to select the number of records in Top wise or bottoms wise. Otherwise it will display all the records that are sent in to it.

No comments:

Post a Comment