HANA SQL Script - Cumulate Function

171 Views

Hi experts,

I have a requirement to creat a new collumn in my Calculated View to cumulate another collumn values.

The cumulated collumn should cumulate 12 months (11 months back until the current month). So I created the following SQL Script:

(SELECT SUM(B.SALES)

FROM "_SYS_BIC"."LSA.ADM.STR/CVSTR_FIN_REP_ACTUAL_3" AS B

WHERE B.CC_FISCPER_AUX <= A.CC_FISCPER_AUX

AND B.SEGMENT = A.SEGMENT

AND B.COMP_CODE = A.COMP_CODE

AND B.CC_PLANT = A.CC_PLANT

AND B.FUNC_AREA = A.FUNC_AREA

AND B.GL_ACCOUNT = A.GL_ACCOUNT

AND B.COSTCENTER = A.COSTCENTER

AND B.COST_ELEM = A.COST_ELEM

AND B.CO_AREA = A.CO_AREA

AND B.PROFIT_CTR = A.PROFIT_CTR

AND B.VERSION = A.VERSION

AND B.CC_CURTYPE = A.CC_CURTYPE

AND B.CURRENCY = A.CURRENCY

AND B.UNIT = A.UNIT

AND B.VTYPE = A.VTYPE

AND B.CC_FISCVAR = A.CC_FISCVAR

AND B.CC_FISCPER_AUX >= ADD_MONTHS(TO_DATE(A."CC_FISCPER_AUX", 'YYYYMM'), -11)

AND B.CC_FISCPER_AUX <= A.CC_FISCPER_AUX) AS "SALES_CUM12"

FROM "_SYS_BIC"."LSA.ADM.STR/CVSTR_FIN_REP_ACTUAL_3" AS A

The calculation is working perfectly, despite the fact that it's not performing. My CV is based on FAGLFLEXT table, and it has more that 5.000.000 records.

When I run a Data Preview in my Analytic View (under FAGLFLEXT) it opens fast and easy. But my cumulative SQL Script is taking more than 3 minutes to return the results.

I was refering to HANA Business Function Library and I have found the CUMULATE function, but I coudn't understand the example that they provided there. Can you guys help me to understand how could I use CUMULATE function in this situation of mine, so I can achieve a better performance?

Thanks in advance.

Adrianon Frossard.