All About Rank Transformation:

A rank transformation is an active, connected transformation.

Rank conversion allows you to select only the top or bottom rankings of your data. Returns the highest or lowest number in a port or group using rank transformation. You can also use rank transformations to return the first or last string in a session's sort order. During a session, the Integration Service caches input data until it can perform ranking calculations.

String Values in Ranking

When the Integration Service runs in ASCII data movement mode, it uses a binary sort order to sort session data.

When the Integration Service runs in Unicode data movement mode, the Integration Service uses the sort order that is configured for the session. You select the session sort order in the session properties. Session properties list all available sort orders based on the code page that the Integration Service uses.

Rank Caches in Rank Transformation

During a session, the Integration Service compares input rows to rows in the data cache. If the input rows exceed the cached rows, the Integration Service replaces the cached rows with the input rows. When you configure rank transformation to rank across multiple groups, the integration service performs incremental ranking for each group it discovers.

The Integration Service stores group information in the index cache and row data in the data cache. If you create multiple partitions in a pipeline, the Integration Service creates a separate cache for each partition.

Properties of Rank Transformation:

When creating a rank transformation, you can configure the following properties:

Enter the cache directory. Select the highest or lowest rank. Select the input/output port that contains the values ​​used to determine ranking. Only one port can be selected to define a rank. Select the number of rows to include in the ranking. Define a group of ranks, such as the 10 cheapest products from each manufacturer.

Rank Transformation Ports:

A rank transformation has input or input/output ports that are connected to another transformation in the map. Also includes variable ports and rank ports. Use ranking ports to specify which columns to rank.

Rank Index in Rank Transformation

The designer creates a RANKINDEX port for each rank transformation. The Integration Service uses rank index ports to store the ranking position of each row within a group.

For example, if you create a rank transformation that ranks the top five marks of student, the rank index numbers the student from 1 to 3.

RANKINDEX is just an output port. You can pass the rank index to another transform within the mapping, or directly to the target. It cannot be deleted or edited. RANKINDEX is just an output port. You can pass the rank index to another transform within the mapping, or directly to the target.

Defining Groups in Rank Transformation

Rank transformation allows you to group information.

For example, if you want to select her three Student in each section, first define a group by section. When configuring rank translation, one of the input/output ports can be configured as a group port. The transformation creates a group of rows that fit within the rank definition (top or bottom, and a specific number for each rank) for each unique value in the group port.

Note: If two rank values ​​match, they get the same value in the rank index and the conversion skips the next value.