Skip to Content

Please note that the following model has been implemented in SPS06 (revision 60). I am not sure about the feasibility of this approach on the earlier revisions.


Secondly some of the observations on the below model are based on my personal experience and opinion. The SAP experts on this forum can provide better justification of the model behavior in terms of the performance of the query.

As mentioned in the earlier blog Implementation of WTD, MTD, YTD in HANA using Input Parameters only, let’s look into an option to populate the Input parameters for the Graphical Calc view defined in the blog, with the values derived using SQL script.

Rules of the game remain same i.e. same business requirements, assumptions, Sample data and Projections with Constant columns for Pruning, as mentioned in the earlier blog Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection.

The values for the input parameters can be derived in the SQL script by simple calculations. In some cases of complicated derivation rules, even complex SQL, with additional lookups on other tables, can be done. This option provides very good flexibility in terms of value derivation for the Input parameters and Projection filters.

Approach with SQL Script based View for deriving the Input Parameter Values:

In this approach, the Key Input parameters for the DATE_FROM and DATE_TO are defined in the Script based Calc view. Using the value of INPUT_DATE_TO Input Parameter, WEEK_DATE_FROM, MONTH_DATE_FROM and YEAR_DATE_FROM values can be derived with the same logic as mentioned in the first blog. The derived values are then passed to the Graphical Calc view Input parameters.

Please note that the syntax for the Calculated column Expression and SQL script expressions can be different for the same functionality.

The model for Script based Calc view can be represented as follows:

model.JPG

The filter defined in the Script based Calc view can be defined as;

ip_params.JPG

The script code executing the query on the Graphical Calc view can be defined as follows:

script.JPG

The query can be executed against the Script based Calc view with the 2 input parameters for the IP_DATE_FROM and IP_DATE_TO.

sql_execution.JPG

The execution plan for the above query is as follows:

exec_plan_1.JPG

The execution plan for the SQL code shows that, despite of using the earlier model of Graphical Calc view and the SIMILAR SQL statement, there is no evident filter push down observed, as it was clearly seen in the earlier Graphical model query execution.

Here the reason could be, that the Script based Calculation view executed the defined query in the script code against the graphical view with ALL columns used in the SELECT statement as defined in the script. Hence all the column joins are performed and there is no Query stripping for the columns. In case of query execution DIRECTLY on the Graphical Calc view (as mentioned in the earlier scenario of Direct Input parameters), ONLY those columns are joined to generate the result set which are required as per the SELECT statement.

Pros of the model:

  • This approach provides very flexible way of deriving the values to be passed to the projection filters of the underlying Calc view.
  • It provides the possibilities of the lookups on other table columns, which are not directly available in the current view.
  • No dependency on the Front end reporting tool to derive the Date values in case of Complex Lookups like Custom Calendar.

Cons of the model:

  • The Query stripping for the columns is not possible with Script based Calc views. Hence even if only 1 or 2 columns are selected from the SELECT statement defined in the script, the query is executed for all the columns as defined in the SELECT statement. This has negative impact on the performance.
  • The implementation and maintenance complexity is increased. Any change to the underlying Calc view has impact on the SELECT statement and the var_out structure. Since the var_out structure has to be maintained manually, the manual effort and maintenance complexity increases.

This model provides very high flexibility in terms of the value derivation for the projection filter. In case of custom Calendar or Custom Time periods for a specific organization, the Week / Month / Year Start Date may not be same as Calendar Week / Month / Year start date. In such cases, the lookups can be done on the custom calendar table to derive the Week Start date, Month Start Date and Year Start Date.

If the number of columns in the SELECT statement of the script is less, then the impact of the performance will not be very negative. But with the more number of columns in the SELECT statement, more column joins are performed impacting the performance.


Hence depending upon the complexity of the value derivation and the Reporting requirements, this model can be used with agreed performance expectations with the business users.

In the next blog, we will discuss another option of the Graphical implementation to avoid the scripted view and to benefit the Filter push down and Query stripping for columns concept for the same requirement.

Please feel free to comment / suggest on the blog. 🙂

=================================================================================

Date: 16 Oct 2013

Adding some more screenshots from the Planviz in response to the comment from Jody Hesch

The filter operation is seen in the Aggregation Pop operation before the final result is generated.

filter_CE_aggr_pop.JPG

Expanding to the lowest level (as you can see from the number of color bands on the left) in the PlanViz till we reach the Analytic search, it can be seen that it still provides the entire table content of 24 records. The further expansion of the Analytic search shows the column executions for each of the table columns, but NO BwPopSearch operation.

screen_shot_1.JPG

Regarding the exact behavior of HANA in this regard, can be explained by the development team. Unfortunately I am not privileged to have that information. Here I just posted my experience with scripted Calc view. 🙂

To report this post you need to login first.

13 Comments

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

  1. Jomy Joy

    Hi Ravi,

    You mentioned in the last section of this blog that:

    In the next blog, we will discuss another option of the Graphical implementation to avoid the scripted view and to benefit the Filter push down and Query stripping for columns concept for the same requirement.

     

    What is this other way of implementing this YTD, LYTD and so on without Script based CV, so that we get the benefit of filter push down and Query stripping ?

     

    Really appreciate your answer.

     

    Thanks & regards,

    Jomy

    (0) 
    1. Ravindra Channe Post author

      Hi Jomy,

       

      I guess, I forgot to post the last option. Please give me a day or two.. I’ll post the document.

       

      Thanks for reminding..

       

      Regards,

       

      Ravi

      (0) 
      1. Jomy Joy

        Hi Ravi,

        We are just to waiting to see that last option as unfortunately none of the following options works in one way or the other

        a) Calculation Cols approach – Cant filter the projection down and hence performance intensive

        b) Calling Graphical from Script based – Performance is really bad even though we’ve the flexibility of coding different date input parameters

        c) Input parameters only – Here we need to depend on the BO tools for derinving these input parameters.

         

        So really waiting for your last option. If possible can you please send it to me on email_id:joy.jomy@gmail.com

        since you might need to 1-2 days to make to post

         

        Let me know your thoughts.

         

        Thanks & regards,

        Jomy

        (0) 
      2. Jomy Joy

        Hi Ravi,

        It would be great, if you can post your 4th option to see to implement MTD, YTD and so on.

         

        Thanks & regards,

        Jomy

        (0) 
      1. Jody Hesch

        Hi Ravi,

         

        If I understand the modeling approach correctly, there are explicit filters in the projection nodes of the underlying graphical CalcView.

         

        Please advise if my understanding is correct – those filters are required to generate correct results (since measures are result of filtered base measures in the graphical calcview – rather than calculated columns for example).

         

        Above – you mention that the filters don’t execute at the lowest level. But how could that be, assuming your end result is correct?

         

        Also, in the above screenshot, the lowest node in the PlanViz, within sub-execution, is not yet expanded. Could you expand, and see if filter shows up?

         

        My apologies if I missed something and am causing redundant work.

         

        Thanks

        Jody

        (0) 
  2. Kumar Chidambaram

    Ravi

    I just came across this article . It is really helpful to approach and solve a very common challange for most reporting uses. Appreciate you sharing .

    Regards

    Kumar

    (0) 
  3. Tim Korba

    I have a few follow up questions:

     

    1) Does it make sense to flatten the records out in the aggregation level?  This will then create a YTD, MTD, etc…columns?

    2) What if your reporting is only concerned about MTD?  What is the impact on performance that all are still being filtered?  Is there a way to stop the other projections from occurring?

     

    Great article!

    (0) 
  4. Suresh Kumar Sellapan

    Ravi,

     

         How to handle same scenario with feed from multiple Analytic Views?. I need to calulate YTD, MTD, PYTD, PMTD from multiple analytic views(ANV_VW1, ANV_VW2, …ANV_VWn)..

     

          We are getting multiple fact tables from different sources.

     

    Regards

    Suresh

    (0) 

Leave a Reply