Rank Transformation in Informatica with EXAMPLE

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

Step 2- Then in the mapping

1. Select transformation menu
2. Select create option

   

Step 3 – In the create transformation window

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

Step 4 – The rank transformation will be created in the mapping, select 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 "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

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

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 rank transformation will pass only records having three high salaries for each department.