Rank Transformation In Informatica

Rank Transformation In Informatica

 

Type: Active & Connected.

#Allows us to select Top or Bottom values from a set of data or select Top or Bottom values in different groups of data.
For ex: if we need to get top or bottom 5 values according to salary range OR if we need to get top or bottom 2 values in each department according to salary range, we can use Rank transformation.


#Ports in Rank transformation:
Input: At least 1 required

Output: At least 1 required
Variable: can be used to store temporary values
Rank: Only 1 port can act as rank port. This port can either be Input, Output or Variable port. Rank is calculated according to this port.

#Rank Index:
    PC Designer automatically creates a RANKINDEX port which holds ranking values in a group.
For ex: if we need to get top 5 salaried employees then RANKINDEX will hold values from 1 to 5.
    This port can only be used as Output port, we can not edit or delete it. We can also pass this port to other transformation or target.

#Defining Groups:
     Rank transformation allows us to rank or select top or bottom values from each group we define.
For ex: if we need to get top 3 salary records in each department then we can select department column as Group By column.
     By defining groups, we get one set of ranked values for each group. We can not define group by port which is already a rank port.

 

#Properties Of Rank Transformation:

• Cache directory: where cached values are stored (within temp files).
• Top/Bottom
• Number Of Ranks
• Case sensitive string comparison
• Rank Index Cache Size: It holds values for different groups that are selected in group by clause. For ex: if department is selected as group by columns then cache will have values like 10,20,30 etc. for example. Also all the group by columns will present in Index cache.
• Rank Data Cache Size: It holds raw data PC server completes ranking and is generally larger than Index cache.  To reduce data cache size, connects only required ports to transformation. Ports going out from rank transformation, variable port if any and Rank port are stored in data cache.

 

 

Thanks for reading...

Nitesh