Skip to Content

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.

time_range.JPG

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:

HANA_Model_concept.JPG

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:

sample_data.JPG

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.

Projection_filter_expression.JPG

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

Approach with Calculated columns:

HANA_Calc_View.JPG

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.

CC_Week_Start_From.JPG

CC_Month_Start_From.JPG

CC_Year_Start_From.JPG

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

constant_columns.JPG

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.

Data_output_of_query.JPG

Query_output.JPG

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.

Execution_plan.JPG

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. 🙂

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Zain Khan
    • 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.

    Nice thread. Have a question about the above.

    Are you saying that you won’t be able to force a filter in the Node because you are creating calculated columns also in the same Node? (Filters can never be pushed if calculated columns are created also in the same node?)

    OR

    Are you saying that you won’t be able to force a filter because you are creating and using the same calculated columns as filters in the same Node (ie using the calculated columns in expression in the same node)

    (0) 
    1. Ravindra Channe Post author

      Hi Zain,

      When you use the calculated columns, the calculation is performed in the Calc engine. Hence all the data is fetched from the Analytic engine into the Calc engine. Since the filter is based on the Calculated column, it requires the value to be calculated before the filtering of the data and hence the filtering is performed in the Calc engine instead of the Analytic engine. This is what I meant with the concept that the filter is not pushed down.

      Regards,

      Ravi

      (0) 
      1. Zain Khan

        Thanks Ravindre. Just a follow up question.

        1. What if the filter is not based on calculated column (calculated column not being used in the filter). Will the engine filter first and then execute the calculated column for the filtered data only?
        2. Also, one more question. I’ve been trying to solve this issue with no success, it seems similar to your scenario. What if you want to force a filter where you only want data for the last 60 days? (ex. post_date > now() – 60). As you know we can’t use now() in expression filter. I want to force this filter to improve performance. Currently, I’m getting all the data then getting last 60 days. Any way this can be done graphically without sql?

        Thanks in advance.

        (0) 
        1. Rama Shankar

          Zain,

          I am not sure about question #1, I will have to try out this scenario.

          On question #2: you can create an additional script calculation view on top of the base calculation _SYS_BIC table for the base calculation view which will use a select condition to filter last 60 days.  I designed a similar solution recently for one of my clients that read the no.of days from a parameter table as well.

          Hope this helps!

          Regards,

          Rama

          (0) 
        2. Ravindra Channe Post author

          Hi Zain,

          I will post this option of SQL script tomorrow. The model is ready, just need to capture the screenshots. The third option is also available with Input parameters and I hope to post that solution in the next couple of days or so. Please give me a few more days and I think it will answer all your queries.

          Regards,

          Ravi

          (0) 
  2. Raj Kumar Salla

    Hi Ravi,

    As usual, a rich content from you. Good work. Came to know that there is a negative impact on performance which will be a good lesson to all from you.

    Eagerly waiting for your next approach.

    By the way I like your graphical representation skills.

    Regards

    Raj

    (0) 
    1. Suraj shirolikar

      Hi Ravi,

      Thanks for such a wonderful post.

      I have a q-

      If you want to pass the input as salesorg “RANGE” to the graphical model, will that be possible?

      I have tried doing this in my model- I am not able to pass the range for the sales org.

      With the code

      (placeholder.”$$BILL_VKORG_VBRK$$” => :salesorg) and using the salesorg input parameter with type “DIRECT”, I could not get the data for 1 salesorg.

      I want the user to run the model for all the salesorg at one time..

      Any hints?

      (0) 

Leave a Reply