In this article, we will learn how to implement RANK and DENSE RANK operations on GROUP in SAP Data Services.

Implementation

Let us take the Employee table as Source. Based on the departments, we want to RANK all the employees based on their salary. So the Employee with the highest salary of a department 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 within a department, then they basically share the same rank. But in case of dense ranking the immediate next record will get the next consecutive rank. There will be no gap in RANKS generated. And for normal ranking the immediate next record will not get the next consecutive rank. So eventually there will be gaps in RANKS generated.

We will develop two Custom Functions that will help us to achieve the above scenario. The Job and the dataflow in both the case looks exactly identical. One dataflow will call the CF_RANK_ON_GROUP and the other will call CF_DENSE_RANK_ON_GROUP custom functions from Query transform. So let us take a look into the dataflow design first which will be pretty straight forward, followed by the custom function definitions.

1. Create a Batch Job and add a Data Flow in it.

2. Within the dataflow we take the EMP table from the Local Object Library as Source. The dataflow design for both the jobs are exactly the same.

Data flow

3. Next we place a Query transform to sort the employee data based on department in ascending order and within each department we sort the employee salary in descending order.

Query- Sort

4. Next we place a Query transform to set the Parameter values that will passed as arguments to the Custom Function to get the Ranking. So within this transform we create two additional Output Ports- one to capture the previous row value of department and the other for previous row value of salary.

PREV_SAL = previous_row_value( QRY_SORT.SAL )
PREV_DEPTNO = previous_row_value( QRY_SORT.DEPTNO )

Query- Set Parameters

5. Next we place a Query transform to call the Custom Function with the input parameters that we have already designed in the previous transform. Call the Function with the Input Parameters as below. This function will eventually return the Rank for each record based on the Department and salary.

$PREV_SAL = QRY_SET_PARAM.PREV_SAL
$CURR_SAL = QRY_SET_PARAM.SAL
$PREV_DEPTNO = QRY_SET_PARAM.PREV_DEPTNO
$CURR_DEPTNO = QRY_SET_PARAM.DEPTNO

Function Input Parameters

Select the Return column as the Output Parameter.

Query- Call Function

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

Data Preview- DENSE RANK

Data Preview- RANK

7. Now let us create two New Custom Functions from the Local Object Library to achive the functionality of rank and dense rank on groups.

DENSE RANK ON GROUP

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
Parameter$PREV_DEPTNOintInput
Parameter$CURR_DEPTNOintInput
Variable$HOLD_RANKint

Custom Function Definition- DENSE RANK

Next we define the custom function as below and Validate the same.

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

else if( $PREV_DEPTNO = $CURR_DEPTNO )
begin
  if( $PREV_SAL = $CURR_SAL )
    Return $HOLD_RANK;

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

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

RANK ON GROUP

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
Parameter$PREV_DEPTNOintInput
Parameter$CURR_DEPTNOintInput
Variable$FINAL_RANKint
Variable$HOLD_RANKint

Custom Function Definition- RANK

Next we define the custom function as below and Validate the same.

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

else if( $PREV_DEPTNO = $CURR_DEPTNO )
begin
  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	
end

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


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.

  • Top 50 SAP Business Objects Data Services (BODS) Interview Questions with Answers

    Learn the answers of some critical questions commonly asked during SAP BO Data Services interview.

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • Exception Handling While Reading Multiple XML Files in Data Services

    This article will demonstrate loading multiple XML files using SAP Data Services including Exception Handling.

  • Map_CDC_Operation in Data Services

    Map CDC Operation is used to support relational or mainframe changed-data capture (CDC). This transform reads the source DML changes i.e. INSERT/UPDATE/DELETE and reflects the same in the target system. So the transform maps all the source rows...

  • Why SAP Data Services

    SAP BusinessObjects Data Services delivers a single enterprise-class solution for Data Integration, Data Quality, Data Profiling, and Text Data Processing that allows us to integrate, transform, improve, and deliver trusted data to critical...

  • 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...

  • Text Data Processing using SAP Data Services

    This article deals with Text Data Processing using SAP Business Objects Data Services with the intension of Text Analytics. SAP BODS provides a single ETL platform for both Structured and Unstructured data as well as Data Quality, Data Profiling...

  • Data Services Scenario Questions Part 3

    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.

  • Data Services Scenario Questions Part 6

    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.

  • Data Services Scenario Questions Part 1

    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.