Rank Transformation Overview
The 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.
During a mapping run, the Data Integration Service caches input data until it can perform the rank calculations.
The Rank transformation differs from the transformation functions MAX and MIN. The Rank transformation returns a group of top or bottom values, not just one value. For example, use a Rank transformation to select the top 10 salespersons in a given territory. Or, to generate a financial report, you might use a Rank transformation to identify the three departments with the lowest expenses in salaries and overhead. While the SQL language provides many functions designed to handle groups of data, identifying top or bottom strata within a set of rows is not possible using standard SQL functions.
You connect all ports representing the same row set to the transformation. The rows that fall within that rank, based on some measure you set when you configure the transformation, pass through the Rank transformation.
As an active transformation, the Rank transformation might change the number of rows passed through it. You might pass 100 rows to the Rank transformation, but select to rank only the top 10 rows. The top 10 rows pass from the Rank transformation to another transformation.
You can connect ports from one transformation to the Rank transformation. You can also create local variables and write non-aggregate expressions.
Ranking String Values
You can configure the Rank transformation to return the top or bottom values of a string port. The Data Integration Service sorts strings based on the sort order selected for the deployed mapping.
When you configure the application that contains the mapping, you select the sort order that the Data Integration Service uses to run the mapping. You can select binary or a specific language such as French or German. If you select binary, the Data Integration Service calculates the binary value of each string and sorts the strings using the binary values. If you select a language, the Data Integration Service sorts the strings alphabetically using the sort order for the language.
Rank Transformation Properties
When you create a Rank transformation, you can configure the following properties:
- •Enter a cache directory.
- •Select the top or bottom rank.
- •Select the input/output port that contains values used to determine the rank. You can select only one port to define a rank.
- •Select the number of rows that you want to rank.
- •Define groups for ranks, such as the 10 least expensive products for each manufacturer.