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:
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
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)
Create Calculated Column (From Month)
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)
"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