Skip to Content

Modeling CM/YTM/LYTM Comparison in Calculation Views using Input Parameters derived from Scalar Procedure

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

2 Comments
You must be Logged on to comment or reply to a post.
  • 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$$’

    • 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