Rank is an active and connected transformation that performs the filtering of data based on the group and ranks. The rank transformation also provides the feature to do ranking based on groups.
The rank transformation has an output port, and it is used to assign a rank to the rows.
In Informatica, it is used to select a bottom or top range of data. While string value ports can be ranked, the Informatica Rank Transformation is used to rank numeric port values. One might think MAX and MIN functions can accomplish this same task.
However, the rank transformation allows groups of records to be listed instead of a single value or record. The rank transformation is created with the following types of ports.
The port which is participated in a rank calculation is known as Rank port.
A port that allows us to develop expression to store the data temporarily for rank calculation is known as a variable port.
The variable port will enable us to write expressions that are required for rank calculation.
Ports in a Rank Transformation
Configuring the Rank Transformation
Let’s see how to configure the following properties of Rank transformation:
What is Rank Index?
The Developer tool creates a rank index port for each Rank transformation. The Data Integration Service uses the Rank Index port to store the ranking position for each row in a group.
After the Rank transformation identifies all rows that belong to a top or bottom rank, it then assigns rank index values. If two rank values match, they receive the same value in the rank index, and the transformation skips the next value.
The rank index is an output port only. We can pass the rank index to another transformation in the mapping or directly to a target.
The Rank transformation gives us group information like the aggregator transformation.
For example: If we want to select the 20 most expensive items by manufacturer, we would first define a group for each manufacturer.
Suppose we want to load top 5 salaried employees for each department; we will implement this using rank transformation in the following steps, such as:
Step 1: Create a mapping having source EMP and target EMP_TARGET
Step 2: Then in the mapping,
Step 3: In the create transformation window,
Step 4: The rank transformation will be created in the mapping, select the done button in the window.
Step 5: Connect all the ports from source qualifier to the rank transformation.
Step 6: Double click on the rank transformation, and it will open the "edit transformation window". In this window,
Step 7: In the "edit transformation" window again,
Step 8: Connect the ports from rank transformation to the target table.
Now, save the mapping and execute it after creating session and workflow. The source qualifier will fetch all the records, but the rank transformation will pass only records having three high salaries for each department.