Improve SAP HANA calculation view performance via Derived from Table Input Parameter
Many customers use custom fiscal calendar including week start day, fiscal period start/end, and a unique date format to report sales and/or financials. For example, one customer has set MM-YYYY (e.g., 03-2020) as a standard format for internal reports. BI tools are required to query analytic models using this date format. Since such a date format may not be available in SAP ERP tables, joining on a calculated column is deemed to be an acceptable performance albeit at the expense of calculation view performance. This blog post presents an alternative method to filter base tables for different date formats to improve calculation view performance. Imagine having to work with a few thousand rows of data instead of hundreds of millions!
Derived from Table input parameter can be used to implement an alternative method to filter base tables not containing columns with required date format.
SAP HANA Calculation View
Using above tables, a typical calculation view has been created, as shown below.
A calculated column was created in each projection, namely MANDT (100). Calendar and Gross_Sales projections are joined on MANDT. Note: This is a pseudo-join and not recommended in real-world modeling. I have created this join solely to eliminate any effect of filtering on either projection.
Two input parameters were created, one on each base table.
Key items to note:
- Derived From Table Return Column (from DATES table) that is present in SALES table.
- Filter Column in DATES table (MM-YYYY) to get the corresponding value from YYYYMM column in DATES table.
- Filter value set to the other input parameter.
The above dialog box results from the first input parameter.
So What’s the Big Deal You Ask?
Technically you could have created a common date format column in Sales table, filtered on DATE table using any type of input parameter, and joined on date columns to get the same results. The difference lies in performance!
If we can filter both tables at the lowest level, we only lift minimum amount of required data from base tables, which has a significant impact on calculation view performance. Using above methodology, we’re able to filter SALES table for the requested time period, and lift only specific row(s) of data corresponding to that time period. In this case, only one (1) row of data was generated from SALES table, corresponding to the input dialog box time period, instead of 6 rows. Extrapolate this performance for over hundreds of millions of rows of data and you will start to envision the benefits of this procedure.
As you will notice:
- DATES table (left side) is filtered using MM-YYYY values
- SALES table is filtered using YYYYMM values
- Both tables return one (1) row of data
Derived from Table type of input parameter offers an alternative and powerful method to:
- Filter base tables using columns not necessarily present in a given table but can be derived from another table
- Design SAP HANA queries using custom date formats per customer calendars
Thank you for reading and I hope you find this blog post useful to advance analytics query design. Please let me know if I can be of further help on this topic.