Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection

[Ravi_Channe]

Most of the financial KPI reporting in BI applications needs the data to be reported period wise. The common scenario is to display the KPIs for different periods based on the User Input.

The KPIs to be reporting could be:

• Based on Input Date Range i.e. between Date From and Date To
• Week to date (WTD) for i.e. from the beginning of the week in which the date falls in, till input Date (Date to)
• Month to date (MTD) for i.e. from the beginning of the Month in which the date falls in, till input Date (Date to)
• Year to date (YTD) for i.e. from the beginning of the Month in which the date falls in, till input Date (Date to)

The Conceptual HANA Information can be shown like below:

Some of the assumptions for the below model are as follows:

• The date column is based on NVARCHAR data type and values as represented in SAP tables.
• It considers standard calendar with week starting from Sunday and ending in Saturday. Hence week to date is the time period between being of the week from Sunday till the date.
• No special consideration for Factory Calendar or any organization specific Calendar functionality
• The base model is considered to be based on Analytic view consisting of Data foundation table and associated Attribute views. This can also be a Calc view which can combine different Analytic Views / Calc views / Attribute views. For the example, I built the Analytic view model directly on a sample table.
• Each Projection is based on the different instance of the SAME base model. In this example, it is based on the same table.
• The KPI reporting can be done with different time periods, which specific attribute ZPERIOD to denote the time period value.
• The Calc View has input parameters for the date ranges as “Date From” and “Date To”.
• The KPIs for WTD, MTD and YTD are derived with respect to “Date To” only.
• For simplicity, the Posting Date characteristic is considered for the time duration derivation, along with Net Sales Key Figure.

The sample data considered for this example is as follows:

There are multiple ways in which the above KPIs can be implemented. In this blog, I would like to discuss the implementation using Calculated Columns in HANA and touch upon the Pros and Cons of the approach.

The main reason for the different approaches for the Projection is due to the fact that Projection expression does not provide flexibility for the data manipulation. The filter expression provides limited functions to be operated on the input parameters and hence filters cannot be set for derived values of the Input parameters, like Start of the Month or Start of the Year date, based on the Input date.

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

Approach with Calculated columns:

The projection filters need to be defined with the following restrictions using Calculated Columns.

• Input Date Range: "POSTING_DATE" >='$$INPUT_DATE_FROM$$' and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Week to Date: "POSTING_DATE" >="CC_WEEK_START_FROM" and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Month to Date: "POSTING_DATE" >="CC_MONTH_START_FROM" and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'
• Year to Date: "POSTING_DATE" >="CC_YEAR_START_FROM" and "POSTING_DATE" <= '$$INPUT_DATE_TO$$'

The calculated columns for the Projections can be defined as mentioned below.

The UNION node can be implemented with the constant columns as shown below:

With the above Projection model, the result of the query execution for a given date range, specified by the DATE_FROM and DATE_TO Input Parameters in the above model, is shown below.

Pros of the model:

• Very simplified data modeling.
• The development is completed within the scope of HANA studio itself with standard and SAP supported methodology.
• Query pruning can be achieved with the Constant column concept.

Cons of the model:

• The filter push down does not happen due to the calculated column implementation. Due to the calculated column, all the data is pulled in the Calc engine for the filtering.
• Negative impact on the performance as the filters are not pushed down.

As you can see from the below execution plan for WTD KPI, there is No Filtering (No BWPopSearch operation) of the data happening at the Analytic Engine and all the records (11 rows) are passed to the Calc Engine.

Due to the drawback of Filter not getting pushed down, the impact on the performance is quite adverse. Hence this model is not recommended. But depending upon the data volume, required complexity in the development and maintenance, such data model can still be considered for the Period KPI reporting.

I will post another blog in next couple of day on second approach using SQL Script to implement the same requirement.

Please feel free to comment on this blog. Any recommendations / suggestions (even "Dislike" comments) are most welcome. :smile: