Running QTD, YTD in SAP Datasphere using SQL View

[james_lim]

There is a blog about running QTD and YTD that is posted by @Sefan_Linders 

https://community.sap.com/t5/technology-blogs-by-sap/running-year-to-date-ytd-on-fiscal-periods-with...

My customer wants to implement this on top of flipping sign functionality that I posted earlier.

https://community.sap.com/t5/technology-blogs-by-sap/sap-datasphere-how-to-flip-sign-the-measure-val...

To achieve this, I tried to implement the solution and used a Large Language Model (LLM) for optimizing the SQL statement. The LLM suggested that using window functions is more efficient than using subqueries or self-joins, as it improves readability and allows calculations without altering the query structure.

Here is the optimized SQL query provided by the LLM:

SELECT ACCT, Date, Version, CostCenter,  Amount,

   SUM(Amount) OVER (PARTITION BY ACCT, CostCenter ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTD,

   SUM(Amount) OVER (PARTITION BY ACCT, CostCenter, YEAR(Date), QUARTER(Date) ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTD

FROM Fact

ORDER BY ACCT, CostCenter, Date, Version.

I hope this helps someone who wants to implement QTD and YTD in SAP Datasphere.

[albertosimeoni]

Did the model even suggest that when during a fiscal period there are no posting on one particular account, with the window function the cumulated value for that account do not appear in that specific period? (=> wrong totals)

Since LLM are trained "generally", while the query optimization depends heavily on the specific DBMS it is executed, are you really sure that the query given is the optimal for HANA that is an in memory column store database ? (did you test it with PlanViz/ SQL Analyzer? the last time I check performances were the same)

"using window functions is more efficient than using subqueries or self-joins":

- is totally not trivial as untill like yesterday window function were executed in ROW engine leading to OOM issues due to row format Itab.

- range joins operators were added to HEX to improve non equi joins performances.