This blog is about to discusses the issues identified due to the usage of RANK node in SAP HANA modeling & provide solutions to achieve the same result with different approaches with less time.

SAP HANA has graphical based approach to create RANK node in calculation view which usually takes very less time to develop but some time it cause performance issue in production on high data volume

So I would recommend whenever data volume is high then crate Table function and use ROW_NUMBER() instead of rank function. I will drastically improve the performance of the calculation view.

Graphical based RANK function in calculation view it will take more time.

**Below is the example where I have used 2 Rank function which is taking more time during data preview.**

Output for Graphical based Rank Function.

**Scripted based table function which will give better performance of the calculation view. Below is the sample code as an example.**

**CREATE** **FUNCTION** “NA_PLM”.”MY.NA.PLM.INTERNAL::TF_BOM_PERFORMANCE_TEST” ( )

**RETURNS** **TABLE**

(

“SUBID” **NVARCHAR**(12),

“IDENT” **NVARCHAR**(132),

“MATNR” **NVARCHAR**(18),

“AENNR” **NVARCHAR**(18),

“VALID_TO”** NVARCHAR**(32)

“MATERIAL”** NVARCHAR**(18),

“COMPAVG” **DECIMAL**(10,4),

“COMPLOW” **DECIMAL**(10,4),

“COMPUPP” **DECIMAL**(10,4),

“EWAHR” **DECIMAL**(3),

“ROW_NUM_2” **BIGINT** )

**LANGUAGE** SQLSCRIPT **SQL** SECURITY INVOKER **AS**

**BEGIN**

/*****************************

Write your function logic

*****************************/

VAR_OUT = **SELECT**

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”,

s**um**(“COMPAVG”) **AS** “COMPAVG”,

**sum**(“COMPLOW”) **AS** “COMPLOW”,

**sum**(“COMPUPP”) **AS** “COMPUPP”,

**sum**(“EWAHR”) **AS** “EWAHR”,

ROW_NUMBER() OVER(**PARTITION** **BY** “POSNR”,”SUBID”,”MATNR”, **ORDER** **BY** “VALID_TO” **DESC**) **AS** “ROW_NUM_2”

**from**

(

**SELECT**

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”,

**sum**(“COMPAVG”) **AS** “COMPAVG”,

**sum**(“COMPLOW”) **AS** “COMPLOW”,

**sum**(“COMPUPP”) **AS** “COMPUPP”,

**sum**(“EWAHR”) **AS** “EWAHR”,

ROW_NUMBER() OVER(**PARTITION** **BY** “SUBID”,”VALID_TO”,”MATERIAL” **ORDER** **BY** “AENNR” **DESC**) **AS** “ROW_NUM”

**FROM** “_SYS_BIC”.MY.NA.PLM.INTERNAL/TF_TEST”

**GROUP** **BY** “SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”

)

**Where** ROW_NUM=1

**Group** **by**

“SUBID”,

“IDENT”,

“MATNR”,

“AENNR”,

“VALID_TO”,

“MATERIAL”

**RETURN** :VAR_OUT;

**END**;

__ ………………………………………………………………………………………………..__

__Output:-__

Graphical based RANK function is taking more time like 15 Dec

ROW_NUMBER() in table function is taking very less time compare to Graphical based RANK function- Its taking only 5 Sec.

**Note :- In my case, Development system does not have much data but still graphical Rank is taking 15 sec so if we move same in production then it will directly impact performance of the report due to high volume of data.**

So this approach will improve performance of the report.