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.

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • Data Services Scenario Questions Part 7

    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.

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

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.

  • SCD implementation from Source based CDC table

    Consider a scenario where we want to capture all or any changes in the source table to be maintained as history in our data warehouse dimension table. So if we consider day end batch load we will miss out any intermediate changes to the master data as...

  • Error Handling, Recoverability- SAP Data Services

    Error Handling, Recoverability are the important aspects of any ETL tool. Some of the ETL tools have some sort of in-built error-handling and automatic recovery mechanisms in place.

  • Working with Data Services Flatfiles

    In this article we will try to cover all the possible scenarios related to flatfiles in SAP Data Services.

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

  • Fools Guide to BODS - Repository Creation

    In the first chapter of this article we have learnt the very basic of BODS. In this part of the article we will begin with BODS Repository creation process