In this article, we will learn how to implement RANK operation in SAP Data Services.

Implementation

Let us take the Employee table as Source. We want to RANK all the employees based on their salary. So the Employee with the highest salary will get first rank and the employee with lowest salary will get the last rank. Now in the event when many employees have the same salary then they basically share the same rank. But in case of normal ranking the immediate next record will not get the next consecutive rank. So eventually there will be gaps in RANKS generated.

1. First we create a Batch Job, say Job_Rank_Generation. Within the Job we create a Data Flow, say Df_Rank_Generation.

2. Next we create a New Custom Function from the Local Object Library. Lets name it CF_RANK_GENERATION.

Custom Function

Within the Custom Function Smart Editor, we define the Parameters and Variables as below:

Param/VariableNameDatatypeType
ParameterReturnint
Parameter$PREV_SALdecimal(7,2)Input
Parameter$CURR_SALdecimal(7,2)Input
Variable$HOLD_RANKint
Variable$FINAL_RANKint

Custom Function Definition

The Return Parameter Data type is int.

3. Next we define the Custom Function CF_RANK_GENERATION as below and Validate the same.

if( $PREV_SAL IS NULL )
begin
  $FINAL_RANK = 1;
  $HOLD_RANK = 1;
  Return $HOLD_RANK;
end

else if( $PREV_SAL = $CURR_SAL )
begin
  $FINAL_RANK = $FINAL_RANK + 1;
  Return $HOLD_RANK;
end

else
begin
  $FINAL_RANK = $FINAL_RANK + 1;
  $HOLD_RANK = $FINAL_RANK;
  Return $HOLD_RANK;
end

4. Lets go back and design the Dataflow. First of all we take the EMP table, from the Local Object Library as Source.

Data flow

5. Next we place a Query transform, say QRY_SORT_SAL_DESC to sort the employee data based salary in descending order. First we select all the columns from the Schema In of the Query transform and Map to Output. Next on the ORDER BY tab, select the EMP.SAL column of the Schema In and Sort Type as Descending

Query- Sort

6. Next we place another Query transform, say QRY_SET_PARAM to set the Parameter values that will passed as arguments to the Custom Function to get the Ranking. First we select all the columns from the Schema In of the Query transform and Map to Output. Next we create a New Output Column, say PREV_SAL with Data type as decimal(7,2) to capture the previous row value of salary and define the Mapping as below:

previous_row_value( QRY_SORT_SAL_DESC.SAL )

Query- Set Parameter

7. Next we place another Query transform, say QRY_GEN_RANK to call the Custom Function with the input parameters that we have already designed in the previous transform. First we select all the columns except PREV_SAL from the Schema In of the Query transform and Map to Output. Call the Function with the Input Parameters as below. This function will eventually return the Rank for each record based on the salary.

Create a New Function Call in Schema Out of the Query transform. Choose the Custom Functions from the Function categories and select the Function name CF_RANK_GENERATION.

Next we Define Input Parameters. We specify the inputs as below:

$PREV_SAL = QRY_SET_PARAM.PREV_SAL
$CURR_SAL = QRY_SET_PARAM.SAL

Function Input Parameters

Select the Return column as the Output Parameter.

Query- Rank

8. Finally we place a Template Table as Target in the Target Datastore.

Data Preview


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • How to implement SCD Type 3 in Data Services

    In this tutorial we will learn how to implement Slowly Changing Dimension of Type 3 using SAP Data Services. SCD type 3 design is used to store partial history. Here we are only interested to maintain the "current value" and "previous value" of an...

  • How to use Data Services Pivot Transformation

    In this article, we will learn how to use SAP Data Services Pivot Transform. The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the...

  • How to use Data Services Reverse Pivot Transformation

    In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each...

  • Getting Started SAP BODS

    This article is a step by step guide to learn the basic of SAP BODS. Starting from the Basic we will cover the essential topics like SCD implementation, Fact loading, CDC Mechanisms, Persistent Cache, Substitution Parameters, Variables and...

  • Fools Guide to BODS - Registering Repository to CMC

    In our earlier article, we have learnt how to create a repository in BODS. Once that part is done, The final process before launching the BODS application is registering the repository with...

  • SAP BODS Transforms

    This article deals with the various types of transformations available in SAP BODS. Transformations are in-built, optional objects used in dataflow to transform source data to desired output dataset objects available in Local Object Library under...

  • Data Services Scenario Questions Part 5

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • Table Comparison Transform to Implement Slowly Change Dimension (SCD) in Data Service

    In this tutorial we will learn a new SAP Data services transform, known as Table Comparison Transform and we will see how we may use this transform to implement "slowly changing dimension" (SCD) Type - I. Like before, we have added a video tutorial at the...

  • How to handle multiple input file in SAP Data Services

    In this tutorial, we will learn how we can use a single data flow to process multiple input files of same structure using SAP Data Services. Like before, we have included one hands-on video with the tutorial to help you see the technique in action

  • Using MERGE Transform in SAP Data Services

    This tutorial describes how to use MERGE transform in SAP Data Services (formerly known as BusinessObjects Data Services). A short video is also provided to provide hands-on.