Rank Transformation in Informatica with proper explanation

April 29, 2020 by rajatupadhyaysunny

One of the most useful transformations in informatica is Rank transformation, which is used to rank the data and select top or bottom few records from the data set.

Now Question arise Rank transformation is active or passive?

This transformation is an active and connected transformation.

(In active transformation-the number of records changes at the input port or output port of the transformation and

connected transformation–it is connected to any source, target or any other transformation by at least by a link.)

• It accepts the data in row-wise manner and stores the data in cache.
• It also provides one more feature ranking based on particular group.

For Example:

When we need to fetch the top five salary of employees for each department.

Following steps are given-

Create the mapping in mapping designer->Import source and target from repository

->In the tool bar click transformantion->Create Rank Transformation->Edit Properties tab

1-Add column called serial_number in Target to store rank of employees.

2-When you create a rank transformation, a default output port RANKINDEX comes with the transformations.

Note– RANKINDEX is optional field and generates numbers indicating the rank. If you don’t want to use this RANKINDEX port you can leave the port unconnected.

3-Drag and Drop all the ports from source qualifier to rank transformations and also connect all ports.

4-We need to define either top or bottom option in properties inside edit transformation.

Also provides number of ranks according to number of record that you want to rank. You cannot rank the data on multiple ports. Here, we want salary of top 5 employees that I mentioned below-

How to define properties in rank transformation?

You can double click on the rank transformation and it will open pop-up window for edit transformation .In this window

• Select properties tab
• Select Top option from the Top/Bottom property attribute.
• Provide value 5 in the number of ranks

5-If want to do ranking based on particular group, we need to select the group by option in edit transformation inside Ports.

6-Also, connect the ports from rank transformation to the target tables.

7-Now, save the mapping and create session and workflow for this mapping then execute it.

8– Rank transformation will pass only records having top five salaries.

Like , we have records for Salary -1000,24000,3000,4500,2800,5000,80000,1200,800,6200,1500,7500

Rank transformation will select only records having top five salaries like

RANKINDEX,Salary — 80000, 24000,7500, 6200,5000.

Note– It accepts the data in a row-wise manner and stores the data in cache. Once we received data completely it checks the data based on the condition and sends the data to output port.