Rank Transformation in Informatica with EXAMPLE

By : David Carter David Carter
Hours Updated

⚡ Smart Summary

Rank transformation in Informatica is an active, connected object that keeps only the top or bottom N rows of every group, assigning each surviving row a position through its automatic RANKINDEX output port.

  • 🥇 Top or bottom N: The Top/Bottom property and the Number of Ranks property together decide which rows survive the filter.
  • 🗂️ Ranking by group: Group By ports restart the ranking for every group, so each department can return its own top three.
  • 🔢 Rank port: Exactly one port carries the R flag, and that single column is the value the ranking is calculated on.
  • 📍 RANKINDEX: The Designer adds this output-only port automatically to hold the position of each row inside its group.
  • 🧪 Worked example: Eight Mapping Designer steps load the three highest paid employees per department into EMP_TARGET.
  • 💾 Cached and active: Group values and row data are held in an index and a data cache, and the row count changes.
Read More

Rank Transformation in Informatica

What is Rank Transformation?

Rank transformation is an active and connected transformation that performs the filtering of data based on group and ranks. For example, if you want to get ten records of employees having the highest salary, that kind of filtering can be done by the Rank transformation.

Rank transformation also provides the feature to do ranking based on groups. For example, if you want to get the top ten salaried employees department wise, that grouping can be done with this transformation.

Rank transformation is an active transformation, as it affects the number of output rows.

The Rank transformation has an output port by which it assigns a rank to the rows. That port is named RANKINDEX, and the Designer creates it automatically as soon as the transformation is added to the mapping.

Because the ranking has to compare every row in a group before it can decide which rows win, the Integration Service caches the incoming data. Group values go into an index cache and the rest of the row data goes into a data cache, in the same way a cached Joiner transformation holds its master pipeline.

Ports in a Rank Transformation

Before the worked example, it helps to know what each port flag on the Ports tab actually does, because two of them do the real work of this transformation.

Port type What it does
Input (I) Receives a column from the upstream transformation. Every column linked into the Rank transformation is an input port.
Output (O) Passes a column on to the next transformation or to the target.
Variable (V) Holds a local calculation inside the transformation, exactly as in an Expression transformation.
Rank (R) Marks the one column the ranking is calculated on. Only a single port in the transformation can carry this flag.
Group By Restarts the ranking for every distinct value, so a top-three filter returns three rows per group rather than three rows overall.
RANKINDEX An output-only port created by the Designer. It stores the ranking position of each row within its group and may be left unconnected.

Ranking can be done on a single port only, so a requirement such as “highest salary, then longest service” needs the tie-breaking value prepared upstream rather than a second rank port.

How to Use Rank Transformation in Informatica

Our requirement is to load the top 3 salaried employees for each department; we will implement this using Rank transformation. Work through the eight steps below in the Mapping Designer.

Step 1) Create a mapping having source EMP and target EMP_TARGET. Both definitions now sit on the canvas, as shown below.

Mapping Designer showing the EMP source definition and the EMP_TARGET target definition

Step 2) Then in the mapping

  1. Select transformation menu
  2. Select create option

The Transformation menu with the Create option is shown next.

Transformation menu opened in the Mapping Designer with the Create option highlighted

Step 3) In the create transformation window

  1. Select rank transformation
  2. Enter transformation name “rnk_salary”
  3. Select Create button

Create Transformation window with Rank selected and the name rnk_salary entered

Step 4) The Rank transformation will be created in the mapping, select done button in the window. The empty rnk_salary object now appears on the canvas.

Rank transformation rnk_salary created and placed on the mapping canvas

Step 5) Connect all the ports from source qualifier to the Rank transformation, so that every employee column is available for ranking.

All Source Qualifier ports linked into the rnk_salary Rank transformation

Step 6) Double click on the Rank transformation and it will open the “edit transformation” window. In this window

  1. Select properties menu
  2. Select “Top” option from the Top/Bottom property
  3. Enter 3 in the number of ranks

The Properties tab now reads Top with three ranks, as the screenshot shows.

Properties tab of the Rank transformation with Top selected and three entered as the number of ranks

Step 7) In the “edit transformation” window again

  1. Select ports tab
  2. Select group by option for the Department number column
  3. Select Rank in the Salary Column
  4. Select ok button

The Ports tab now carries the group flag on the department number and the R flag on salary.

Ports tab of the Rank transformation with group by set on the department number and rank set on the salary column

Step 8) Connect the ports from Rank transformation to the target table. The mapping now runs from EMP through rnk_salary into EMP_TARGET.

Ranked ports linked from the rnk_salary Rank transformation to the EMP_TARGET table

Now, save the mapping and execute it after creating a session and workflow. The source qualifier will fetch all the records, but Rank transformation will pass only records having three high salaries for each department.

RELATED ARTICLES

Rank Transformation Properties

The Properties tab used in Step 6 carries more than the two settings the example changes. The settings below control how many rows survive, how strings are compared, and where the cache files are written.

Property What it controls
Top/Bottom Whether the highest or the lowest values are kept for the ranked column. The example uses Top.
Number of Ranks How many rows are returned per group. The example uses 3, so each department returns three employees.
Case-Sensitive String Comparison Whether string comparisons honour letter case while ranking. It only matters when the rank port holds text.
Cache Directory Directory in which the index and data cache files are created. The default is the $PMCacheDir process variable.
Rank Data Cache Size Size of the data cache holding the row data. The default is Auto, which lets the Integration Service size it.
Rank Index Cache Size Size of the index cache holding the group values. The default is Auto.
Transformation Scope Applies the ranking to each transaction or to all incoming data.
Tracing Level Amount of detail written to the session log: Terse, Normal, Verbose Initialization or Verbose Data.

Cache sizing is the setting worth revisiting on large sources. When the caches are too small the Integration Service pages to disk, which is one of the first things checked during performance tuning of a mapping.

Rank Transformation vs Aggregator Transformation

Both transformations are active, both cache their input and both offer Group By ports, so it is easy to reach for the wrong one. The difference is what comes out of them.

Point of comparison Rank transformation Aggregator transformation
Purpose Returns the top or bottom N complete rows of each group Returns calculated values such as SUM, AVG, MAX or MIN per group
Rows returned Up to the Number of Ranks per group, with all their columns intact Normally one summary row per group
Extra port added RANKINDEX, holding the position of each row None
Ranked or aggregated column One port flagged as the rank port Any number of ports carrying aggregate expressions

Put simply, an Aggregator transformation answers “what is the highest salary in this department”, while Rank transformation answers “which employees earn it”. When only a subset of rows has to be discarded on a simple condition and no ordering is involved, a Filter transformation is the cheaper choice, because it needs no cache at all.

FAQs

No. Rank is connected only, so it has to sit in the data flow between two objects. The RANKINDEX port may be left unconnected, but the transformation itself cannot be called the way an unconnected Lookup is called.

Tied rows receive the same value in the rank index and the transformation then skips the next value. Two employees sharing second place therefore both show 2, and the following row shows 4 rather than 3.

The whole source is treated as one group, so the transformation returns only the Number of Ranks in total. Setting three ranks without a group port gives the three highest paid employees across the company, not per department.

Yes. Numeric, string and date/time ports can all be flagged as the rank port. For text the Case-Sensitive String Comparison property decides whether letter case affects the ordering, so check it before the session runs.

Not guaranteed. The transformation selects which rows survive, it does not promise the order they reach the target in. Add a Sorter transformation on the RANKINDEX port when the load order matters.

A SQL window function labels every row and leaves filtering to an outer query. The Informatica transformation labels and filters in one pass, and it works on flat files and other non-relational sources where no database engine is available.

Machine learning models score records on likelihood of churn, fraud or conversion, and that score becomes the ranked column. AI advisors also profile group sizes so cache settings and the number of ranks are chosen from real data rather than guesswork.

Copilot drafts the equivalent window-function query or Python groupby quickly, which is useful for validating results. It cannot read your mapping, so treat every suggestion as a draft and compare the row counts against the session log.

Summarize this post with: