Informatica Powercenter Express - Rank Transformation

Rank transformation is an active transformation that limits records to a top or bottom range. Use a Rank transformation to return the largest or smallest numeric value in a port or group, or use a Rank transformation to return the strings at the top or the bottom of a mapping sort order.

Rank Transformation Ports

Output Passes data to downstream transformations.

Pass-Through Passes data unchanged.

Variable Used for local variables. You can use a variable port to store values or calculations to use in an expression. Variable ports cannot be input or output ports. They pass data within the transformation.

Group by Indicates how to create groups. 

Rank Port Designates the column for which you want to rank values.

Rank Index

The Developer tool creates a RANKINDEX port for each Rank transformation. The Data Integration Service uses the Rank Index port to store the ranking position for each row in a group. After the Rank transformation identifies all rows that belong to a top or bottom rank, it then assigns rank index values. If two rank values match, they receive the same value in the rank index and the transformation skips the next value. The RANKINDEX is an output port only. You can pass the rank index to another transformation in the mapping or directly to a target.

Advanced Properties for Rank Transformation

Top/Bottom Specifies whether you want the top or bottom ranking for a column.

Number of Ranks Number of rows to include in the top or bottom ranking.

Case-Sensitive String Comparison Specifies whether the Data Integration Service uses case-sensitive string comparisons when it ranks strings. Clear this option to have the Data Integration Service ignore case for strings. 

Cache Directory Local directory where the Data Integration Service creates the index cache files and data cache files. Default is the CacheDir system parameter.

Rank Data Cache Size Data cache size for the transformation. Default is Auto.

Rank Index Cache Size Index cache size for the transformation. Default is Auto.

Tracing Level Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Scenario for Rank Transformation

Two highest paid employees in each department

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.