Rank Transformation in Informatica , is a connected and active /Passive transformation which select top/bottom rows of input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows (which are not a part of top/bottom threshold).
Must check : Please also check how to create Aggregator Transformation
How Rank Transformation works in Informatica?
It first cache all the input data and then performs the rank calculation per group , filter out the unwanted records.
Steps to create an Rank transformation:
- In the Mapping Designer, open a Mapping.
- Click Transformation > Create. Select Rank transformation.
- Enter a name and click Done.
- You will see one port RANKINDEX port already there. This port store the ranking of each record and can be used to populate target as well
- Add all additional port from source input which are going to be use in following transformation.
- Open the port tab and first check the Group by option for desired column ( for example deptno in our case)
- Also check the Rank (R) option for the port which you want to do ranking. For example salary in our case.
Must Read : Lookup Transformation in informatica
Note: We can define Group by indicator for multiple port, but Ranking can be done on single port only.
- Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as per need.
- Click OK.
- Connect output ports of Rank Transformation to other transformation or target
Must Read : Learn more about informatica coding standard
Example of Rank Transformation in Informatica:
A good example of Rank transformation with different type of join can be found here.
For more explanation on Rank transformation , you can read it here and also check here for informatica interview question on Rank transformation
Incoming Search Terms :-
