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 Wadhwa – and 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.

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

Union Node Mapping:

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

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

V_CYTM_FROM input parameter definition and its mapping:

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

Input Parameter definition and mapping details:

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:

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

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:

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

Times lines are here:

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