Implementation of WTD, MTD, YTD in HANA using Input Parameters only

[former_member184768]

Continuing the discussion, let’s look into an alternative to implement the required functionality as mentioned in the blog Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection.

We will consider the same business requirements and assumptions, as mentioned in the earlier blog for Calculated Columns.

As we observed in the implementation with the Calculated Columns approach, the performance is impacted due to the filters not getting pushed down. To ensure the filters are pushed down, it is advisable to avoid such calculations in Calc engine and let HANA filter the data as earlier in the execution as possible. In this blog, I would like to highlight the performance improvement with the approach. The performance from this option can be set as the benchmark for all the other possible options.

The Projections can be defined with Constant columns to effectively use Pruning of queries.

Approach with individual Filters for High and Low date Range:

The model for Individual filters can be shown as below:

The projection filters need to be defined with the following restrictions:

• Input Date Range: "POSTING_DATE" >='$$INPUT_DATE_FROM$$' and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Week to Date: "POSTING_DATE" >='$$INPUT_WEEK_DATE_FROM$$' and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Month to Date: "POSTING_DATE" >='$$INPUT_MONTH_DATE_FROM$$' and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Year to Date: "POSTING_DATE" >='$$INPUT_YEAR_DATE_FROM$$' and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'

All the filters are defined in the Calc View as mentioned above.

The filter values can be filled by the Front end Reporting tool where the values for the Input Parameters '$$INPUT_WEEK_DATE_FROM$$', '$$INPUT_MONTH_DATE_FROM$$' and '$$INPUT_YEAR_DATE_FROM$$' need to be derived by the same logic as the mentioned in the calculated columns in the earlier blog.

The result of the query execution for the above model is shown below.

The query execution plan shown below clearly shows the filter getting pushed down to the Analytic Engine.

As clearly seen in the above screenshot, the BwPopSearch is executed in the Analytic engine, filtering the data at the very beginning. This way the filtered data set is passed to the Calc engine for further processing. Due to the reduced data set exchanged between the engines, the performance is very good for such query executions. Also, the above plan shows the query pruning with Constant column as only ONE projection data flow is executed with WTD value for the Constant column.

Pros of the model:

• This approach provides Filter push down possibility providing very good performance.
• Each of projections using the filters, along with Constant column, provide very effective Query pruning.

Cons of the model:

• Many Input parameters can lead to multiple User input Prompts in some of the Reporting Tools.
• The front end reporting tool may have to implement logic to populate the values  for '$$INPUT_WEEK_DATE_FROM$$', '$$INPUT_MONTH_DATE_FROM$$', '$$INPUT_YEAR_DATE_FROM$$'

I want to highlight this model for the benchmark performance. If the front end reporting tool can avoid the User Input prompts for the above mentioned 3 input parameters and derive the values for these based on other input parameter, then this option for implementing Period reporting can provide very optimized performance.

In the next blog, we will look into filling these input variables using Script based approach and Pros and Cons of populating the Input Parameters with the Script based approach.

As always, comments and suggestions are most welcome. :smile: