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",
sum("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.
，大数据app，车险返现，云品，大数据质量，企业管理软件公司
