Rank Transformation in Informatica with EXAMPLE

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, you want to get ten records of employees having highest salary, such kind of filtering can be done by rank transformation.

Rank transformation also provides the feature to do ranking based on groups. Like if you want to get top ten salaried employee department wise, then this 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.

Our requirement is to load top 3 salaried employees for each department; we will implement this using rank transformation.

Step 1) Create a mapping having source EMP and target EMP_TARGET

Rank Transformation in Informatica

Step 2) Then in the mapping

  1. Select transformation menu
  2. Select create option

    Rank Transformation in Informatica

Step 3) In the create transformation window

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

Rank Transformation in Informatica

Step 4) The rank transformation will be created in the mapping, select done button in the window

Rank Transformation in Informatica

Step 5) Connect all the ports from source qualifier to the rank transformation

Rank Transformation in Informatica

Step 6) Double click on the rank transformation and it will open “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

Rank Transformation in Informatica

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

Rank Transformation in Informatica

Step 8) Connect the ports from rank transformation to the target table

Rank Transformation in Informatica

Now, save the mapping and execute it after creating 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.