RANK TRANFORMATION

Rank transformation is an active and connected transformation. The rank transformation is used to select the top or bottom rank of data. The rank transformation is used to select the smallest or largest numeric/string values. The integration service caches the input data and then performs the rank calculations.

The Rank transformation differs from the transformation functions MAX and MIN, in that it lets you select a group of top or bottom values, not just one value. For example, use Rank to select the top 10 salespersons in a given territory. Or, to generate a financial report, you might also 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.

Business Case :

Consider the input from a flat file or Relational table which contains list of salary details of Employees from which we need to get the Top or Bottom N rows. The target file or table has to be populated with Ranking and Salary.