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:

[justin_molenaur2]

First, great post, gives some really good ideas on how to implement more complex scenarios and push the work down to the DB.

Assuming a different scenario - where it's more based on multiple Analytic Views being combined into a single union in a CV, and NOT performing a MTD/YTD breakup like you did.

Is possible to push the same input parameter down to multiple Analytic views to be used as a filter instead of a projection? For example, pushing a month/year filter down to several different analytic views.

I am testing this and can't get it to work in any of the front end tools (Explorer, Analysis for Office/OLAP and Excel), but it does work at the Data Preview level in HANA.

Regards,

Justin

[manna_das]

commonly asked by the end user for such kind of reports, thanks Ravindra, it will help a lot

[former_member184768]

Hi Justin,

I am sorry, but did not get your requirement completely. But did you try with defining Input parameters at each of the analytic view level and then mapping the input parameters at analytic view to the input parameters at the calc view (which contains the union). This way the Input parameters from the Calc view will be pushed down to the Analytic view level.

Regards,

Ravi

[former_member184768]

Thanks for the comments Manna. 🙂

[former_member183980]

Thanks Ravindra !!

Excellent Details provided

[former_member184768]

Thanks for the comments Sir.

Regards,

Ravi

[Former Member]

Hi Ravi,

As you mentioned that :

The filter values can be filled by the Front end Reporting tool ".

Suppose we've a Current_Date, can you please point to me a document or something, wherein I find a way to derive the Placeholders for Current Month, Current Year, YTD, LYTD e.t.c which can be be passed onto HANA Views.

Thanks & regards,

Jomy

[former_member184768]

Hi Jomy,

Please check if this document can be of any help.

Implementation of WTD, MTD, YTD in HANA using Input Parameters Derived from Table

Regards,

Ravi

[sunilkumar_reddyb]

HI Ravi,

Is it possible to do offset in SAP HANA ( Like BW offset variable )