Skip to Content

The motivation for this exercise is based on the approach Uladzislau Pralat  presented here.

Other approaches are also presented by Justin Molenaur , Ravindra Channe & Guneet Wadhwaand can be referred following the below links:

http://scn.sap.com/community/hana-in-memory/blog/2013/07/26/implementation-of-wtd-mtd-ytd-in-hana-using-script-based-calc-view-calling-graphical-calc-view

http://scn.sap.com/community/hana-in-memory/blog/2014/03/10/how-tocalculate-ytd-mtd-anytd-using-date-dimensions

http://scn.sap.com/community/hana-in-memory/blog/2015/01/09/simple-example-of-year-to-date-ytd-calculation-in-sap-hana

The difference in the approach, I am discussing here, lies in leveraging the Input Parameter’s “Derived from Procedure/Scalar Function” option to deduce the Year_To_Month (CYTM), Last_Year_Current_Month (LYCM) and Last_Year_To_Month (LYTM) values as input parameters and can further be applied as Filters. I am using ‘Procedures returning scalar values’ to compute the month. This is similar to the approach we adopt in BW/BEx reports where the CYTM,LYTM variables are populated using CMOD exit.

The demo here is based SAP HANA SP10.

Approach:

  • Create a base “reusable” calculation view having sales table and M_TIME_DIMENSION table joined on the required date field.
  • Create one base input parameter which accepts user entered Calendar Month. This is used further as an input for the scalar procedures to calculate CYTM, LYCM, LYCM.
  • Create a calculation view (may treat it as a Reporting View) having the above created base view and apply the input parameters as filters on CALMONTH field.

Development:

  • Create a base Calculation View CA_SUPERSTORE_SALES_REUSE.

/wp-content/uploads/2016/03/1_910351.png

  • Reuses this view in another Calculation view and use Union on all the four nodes corresponding to CM, CYTM, LYCM, LYTM Projection nodes.

/wp-content/uploads/2016/03/2_910388.png

Union Node Mapping:

12A.png

  • Observe the filters on each projection node corresponding to the four period categories getting calculated under the union node.
    • CM : is straight forward, CALMONTH filtered on the User direct input calendar month input parameter.

/wp-content/uploads/2016/03/3_910400.png

    • CYTM : CALMONTH is filtered based on V_CYTM_FROM input parameter derived using the scalar procedure taking V_CM as the input;

4A.png

V_CYTM_FROM input parameter definition and its mapping:

/wp-content/uploads/2016/03/5_910410.png

/wp-content/uploads/2016/03/6_910411.png

The code snippet evaluating CYTM_FROM is:

CREATE PROCEDURE “_SYS_BIC”.“PR_SUPERSTORE_SALES_CYTM_SCALAR”(IN IN_CALMONTH VARCHAR(6), OUT OUT_CALMONTH VARCHAR(6))

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

AS

BEGIN

  DECLARE V_CALMONTH VARCHAR(6);

  DECLARE V_YEAR VARCHAR(4);

  V_YEAR := LEFT(IN_CALMONTH,4);

  V_CALMONTH := CONCAT(:V_YEAR,’01’);

  SELECT V_CALMONTH INTO OUT_CALMONTH FROM DUMMY;

END;

    • LYCM: Filter details

7A.png

Input Parameter definition and mapping details:

8A.png

Code snippet for LYCM is:

CREATE PROCEDURE “_SYS_BIC”.“PR_SUPERSTORE_SALES_LYCM_SCALAR”(IN IN_CALMONTH VARCHAR(6), OUT OUT_CALMONTH VARCHAR(6))

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

AS

BEGIN

  DECLARE V_CALMONTH VARCHAR(6);

  DECLARE V_MONTH VARCHAR(2);

  DECLARE V_YEAR VARCHAR(4);

  V_YEAR := LEFT(IN_CALMONTH,4);

  V_YEAR := V_YEAR – 1;

  V_MONTH := RIGHT(IN_CALMONTH,2); 

  V_CALMONTH := CONCAT(:V_YEAR,:V_MONTH);

  SELECT V_CALMONTH INTO OUT_CALMONTH FROM DUMMY;

END;

    • LYTM:

/wp-content/uploads/2016/03/9_910414.png

Input Parameter definition and mapping details: First screen shot gives the ‘From’ value corresponding to Last Year and the second one gives the ‘To’ value

/wp-content/uploads/2016/03/10_910418.png

/wp-content/uploads/2016/03/11_910419.png

Code snippet for LYTM_FROM:

CREATE PROCEDURE “_SYS_BIC”.“PR_SUPERSTORE_SALES_LYTM_FROM_SCALAR”(IN IN_CALMONTH VARCHAR(6), OUT OUT_CALMONTH VARCHAR(6))

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

AS

BEGIN

  DECLARE V_CALMONTH_FROM VARCHAR(6);

  DECLARE V_YEAR VARCHAR(4);

  V_YEAR := LEFT(IN_CALMONTH,4);

  V_YEAR := V_YEAR – 1;

  V_CALMONTH_FROM := CONCAT(:V_YEAR,’01’);

  SELECT V_CALMONTH_FROM INTO OUT_CALMONTH FROM DUMMY;

END;

Code Snippet for LYTM_TO:

CREATE PROCEDURE “_SYS_BIC”.“PR_SUPERSTORE_SALES_LYTM_TO_SCALAR”(IN IN_CALMONTH VARCHAR(6), OUT OUT_CALMONTH VARCHAR(6))

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

AS

BEGIN

  DECLARE V_CALMONTH_TO VARCHAR(6);

  DECLARE V_YEAR VARCHAR(4);

  DECLARE V_MONTH VARCHAR(2);

  V_YEAR := LEFT(IN_CALMONTH,4);

  V_YEAR := V_YEAR – 1;

  V_MONTH := RIGHT(IN_CALMONTH,2);

  V_CALMONTH_TO := CONCAT(:V_YEAR,:V_MONTH);

  SELECT V_CALMONTH_TO INTO OUT_CALMONTH FROM DUMMY;

END;

  • And finally, the output:

/wp-content/uploads/2016/03/13_910420.png

  • The PlanViz: looking at the plan visualization, we can observe various filters in action

/wp-content/uploads/2016/03/14_910424.png

Times lines are here:

14A.png

I would like to thank deepak hp for providing me tip to fix the procedure issue; you may refer here for details;

– Prasad A V

To report this post you need to login first.

2 Comments

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

  1. Uladzislau Pralat

    Hi Prasad,

    there is simply a better way to create ranges avoiding Table and Scalar Function e.g. calculated columns and keeping all calculations within the Calculation View itself.

    Create Input Parameter (To Month)

    /wp-content/uploads/2016/03/image001_912072.png

    Create Calculated Column (From Month)

    /wp-content/uploads/2016/03/image002_912073.png

    /wp-content/uploads/2016/03/image003_912074.png

    /wp-content/uploads/2016/03/image005_912077.png

    /wp-content/uploads/2016/03/image006_912078.png

    if     ( (
    rightstr  (
    ‘$$Month$$’,2) = ’01’ ) , string( int
    (leftstr (
    ‘$$Month$$’,4)) – 2 ) + ’12’    ,


    string( int (leftstr (
    ‘$$Month$$’,4)) – 1 ) + lpad (  ( string( int(rightstr (‘$$Month$$’,2)) –1 )  ) ,2,‘0’)

       )

    Restrict
    Data To Selection Range (From and To Months)

    /wp-content/uploads/2016/03/image011_912079.png

    “CALMONTH”>= “Month_From”
    AND
    “CALMONTH”<=‘$$Month$$’

    (0) 
    1. Prasad AV Post author

      Hi Uladzislau Pralat,

      Thanks for your suggestion. In fact, I did this initially using calculated columns only and it was working, I totally agree with you that using Calculated column will restrict the entire execution in CE engine.

      But as I mentioned in my blog, want to try in a different way.

      Moreover, “dummy” calculated columns were somehow not appealing elegant as well.

      Besides all this, the Calculated column method is taking bit more time Vis-a-Vis Input Parameters with derived procedure method for the same set of data. ( Calculated Column method – 686.78 ms where as Input Parameter with Derived Procedure – 293.50 ms).

      May be both these methods will become obsolete in case Input Parameters Expression Editor supports more functionality in future, like deriving one parameter based on another parameter;

      Once again thank you very much;

      Prasad A V

      (0) 

Leave a Reply