Skip to Content

Recently I saw some posts in SCN where people asked “how can we restrict data HANA views dynamically?” Like showing data for “Last 3 months” , “Last 13 Months” or “Last two years” etc . So, thought of listing the steps in a small blog post.

 

Requirement : Based on user input or current month , we want to restrict data in our view.  Common requirement is to show last 12/13 months data dynamically. In general , we want to go ‘n‘ number of months back from current month or whatever month user would select.

 

Solution :  From  SPS09 , HANA allows Input Parameter of type   “Derived From Procedure /Scalar Function” – and I would use this to meet this requirement. Good thing about this solution is , we can restrict the data at the first projection node and avoid bringing unnecessary data to upper nodes.  We shall write a procedure which would take month input from user ( or current month if nothing supplied)  and return  “to” and “from” month .

 

This procedure takes the input of  “desired month” and  number of months” user wants to go back.   Default value for “desired month ” is Current Month and that for “look back month” is 12 .

 

Example in Fig 1, give me 5 months range from 2016/01 i.e 2015/08 to 2016/01  . We shall see procedure coding later. Same procedure can be used for any number of look back month .

Proc_Call.PNG

Fig 1 – Procedure Call .  Here my “from month” is 2015/08 and “to month” is 2016/01

 

Detail Steps :

 

Step 1) Create a procedure with Input and output type as string ( must) . It can have only one scalar  output parameter . By this time you probably guessed why I have “to” and “from” month in same field separated by a hyphen .

 

Step 2) Create two Input Parameters , one type  “Direct” and another “Derived From Procedure/Scalar Function ” in your Calculation / Analytic View .

IP_DIRECT.PNG

Fig 2 : Input Parameter type Direct

 

IP_Procedure.PNG

Fig 3 : Input Parameter type “Derived from Procedure/Scalar Function”

 

Step 3) We need to map one input parameter with another to receive user input and return calculated month range. Click on “Manage Mapping“. This will pass user selection of month and number of months we want to go back .  For this model, I have selected  2 ( constant ) months back from user month/current month ; based on the requirement we can ask user to select number of month he/she wants to go back .  For that we need create another Input Parameter type ‘Direct’ .

Capture.PNG

Fig 4 : Mapping of Input Parameter to pass user selection to Procedure .

 

4) Now , we will use Input Parameter IP_MONTH to restrict data in projection node. Right click on the “Expression” under “Filter” folder and click “Open” .

expression1.PNG    expression.PNG

Fig 5 : Filtering Expression

 

I am using “leftstr” and “rightstr” operators to take only relevant portion from the output of procedure, i.e first 6 character and last 6 character respectively

 

Output of Procedure  201604-201606

( Leftstr (201604-201606),6 )   =  201604  and (  Rightstr ( 201604-201606) ,6 ) = 201606 .   My Calmonth would be between 201604 and 201606 .

 

Let’s see at what level Filters are getting applied  from PlanViz —  In total I am getting 703 records as per plan viz , we will check what is the total number of records in base table based on our selection from SAP end .

Plan VIz.PNG       results3.PNG

This procedure can be reused for any number of look back months. Either we need select a different constant value or ask user to select number of months he wants to go back .

 

 

Detail of Procedure :

 

We need to go to HANA Development perspective to create this procedure .

 

Open  “Repository View” —-> Right click on your Package —–> Select “New”

 

Choose “Others” —-> SAP HANA —-> Database Development —->Stored Procedure   . Provide Procedure Name and Target Schema. It would automatically take .hdbprocedure extension .  Put the attached code , save and activate the procedure .   Once activated successfully, test the procedure from SQL prompt using “call <Procedure> ” statement .  . If everything fine, follow from step 2.


*****Due to SCN Migration, attachment got missing , procedure code has been updated here *******

PROCEDURE "_SYS_BIC"."<Package_Hierarchy>::ZPROC_DYN_MONTH" (IN NUM_MON NVARCHAR(2) ,IP_CALMON NVARCHAR(6) , OUT FROM_TO NVARCHAR(13)) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	DEFAULT SCHEMA "_SYS_BIC"
	READS SQL DATA AS
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
 DECLARE v_num_mon INT ;
 
 IF :NUM_MON = '' OR :NUM_MON IS NULL 
 THEN v_num_mon := 12;
 ELSE v_num_mon := TO_INT(:NUM_MON) ;
 END IF ;

 
IF :IP_CALMON = '000000' OR :IP_CALMON = '' OR :IP_CALMON IS NULL 

THEN FROM_TO :=  left (  to_char ( to_dats(  ADD_MONTHS (TO_DATS(CURRENT_DATE), - v_num_mon))),6)|| '-' || left( to_char( to_dats( current_date)),6) ;

ELSE FROM_TO :=  left (  to_char ( to_dats(  ADD_MONTHS (TO_DATS(:IP_CALMON), - v_num_mon))),6) || '-' || left( to_char( to_dats(:IP_CALMON)),6) ;

END IF ;
END;

PS:  In case you want, you can use two different procedure to return “To” and “From” values of the month selection . In that case, you would not need to use leftstr or rightstr operators.

 

To report this post you need to login first.

28 Comments

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

    1. Ashwin S

      Hi Anindya,

      I have tried the above approach but I get this below error!

      ip.GIF

      I have tested my procedure using the CALL statement and it works as expected.

      PS : in my i/p parameter creation screen, I have parameter type option as “Derived From Procedure” and not“Derived From Procedure/Scalar function”. would this cause any concern?

      My HANA version is SP11

      Kind Regards,

      Ashwin

      (0) 
      1. Anindya Bose Post author

        Hi Ashwin

        I did it in SPS 10 . So logically it should be supported in SPS 11 also. Had this not been supported, I believe you would not get the relevant option from drop down.   Did you create the procedure from “Development Perspective” or “Modeling Perspective” ?

        What’s your HANA Studio version ?  It could be front end problem. I see this is supported even in SPS 12 .  Page number 120 .

        http://help.sap.com/hana/SAP_HANA_Modeling_Guide_for_SAP_HANA_Studio_en.pdf

        Anindya

        (0) 
          1. Anindya Bose Post author

            Please have a look at SPS11 modeling guide to confirm this is supported.  I don’t have SPS11 .

            It could be your HANA studio version problem.  I see many more settings in my studio version which is not there in your screenshot . Some of them “highlighted”

            Capture.PNG

            (0) 
        1. Ashwin S

          Hi Anindya,

          My HANA Studio version is 2.0.16

          Sure, will look into it some more and confirm. appreciate all your help

          Kind Regards,

          Ashwin

          (0) 
          1. Anindya Bose Post author

            My studio version is 2.2.16 .  It seems your problem is related to Studio version. Please upgrade and share your result .

            (0) 
            1. Ashwin S

              Hi Anindya,

              I want to change the procedure to calculate “from to” as current year to current year -1

              but what if the user doesn’t give an i/p for year? and what if I don’t have calyear in my cal view?

              Below is what I have tried, and please correct me if I’m wrong!

              I have created a calculated column “CALYEAR” using component(now(),1)

              PROCEDURE “_SYS_BIC”.”Ashwin_Test::ZPROC_DYN_YEAR” (IN NUM_YEAR NVARCHAR(2) ,IP_CALYEAR NVARCHAR(4) , OUT FROM_TO NVARCHAR(4))

                LANGUAGE SQLSCRIPT

                SQL SECURITY INVOKER

                DEFAULT SCHEMA “_SYS_BIC”

                READS SQL DATA AS

              BEGIN

              /*****************************

                Write your procedure logic

              *****************************/

              DECLARE v_num_YEAR INT ;

              IF :NUM_YEAR = ” OR :NUM_YEAR IS NULL

              THEN v_num_year := 1;

              ELSE v_num_year := TO_INT(:NUM_year) ;

              END IF ;

              IF :IP_CALYEAR = ‘000000’ OR :IP_CALYEAR = ” OR :IP_CALYEAR IS NULL

              THEN FROM_TO :=  left (  to_char ( to_dats(  ADD_YEARS  (TO_DATS(CURRENT_DATE), – v_num_year))),4)|| ‘-‘ || left( to_char( to_dats( current_date)),4) ;

              ELSE FROM_TO :=  left (  to_char ( to_dats(  ADD_YEARS  (TO_DATS(:IP_CALYEAR), – v_num_year))),4) || ‘-‘ || left( to_char( to_dats(:IP_CALYEAR)),4) ;

              END IF ;

              END;

              Kind Regards,

              Ashwin

              (0) 
              1. Anindya Bose Post author

                Hi Ashwin

                Your requirement is easier .  You can use attached statement to get current year .  I subtracted 1 to get previous year, but you can subtract n .

                year.PNG

                You might need to convert them to character before you concatenate them . In that case, use a to_char function .


                select   to_char(year( current_date)) as “Current_Year” , to_char((year( current_date) – 1 )) as “PreviousYear”  from dummy


                Anindya

                (0) 
                1. Ashwin S

                  Hi Anindya,

                  Thanks it worked 🙂

                  So, here is what I want to do and correct me if I’m wrong.

                  1. create 2 i/p parameters IP_FROM_YEAR and IP_TO_YEAR

                  2. create 2 procedures to populate the above parameters

                  3. map the procedure i/p parameter with the cal view i/p parameter

                  Kind Regards,

                  Ashwin

                  (0) 
                  1. Anindya Bose Post author

                    What I understand your requirement is to see last 3 years data.   You can have only one procedure which returns “current year – 2” and “current year” .

                    Output Like :  “2014-2016” .  i.e concatenate  “current year – 2”  , “-” and “Current Year” in one output string of length 9 .

                    Hold this value in a Input Parameter type “Derived From Procedure/Scalar Function” and in filter  expression put the syntax like below .

                    FISCYEAR >=  leftstr( IP_DERIVED,4) and FISCYEAR <= rightstr( IP_DERIVED,4)

                    (0) 
                    1. Ashwin S

                      Hi Anindya

                      no not really, my requirement is to replicate a BW year specific data model in HANA,

                      so basically I’m trying to create 4 cal views one is for current year, current year-1, current year -2 and current year -(>2 years old).

                      the reason being I dont want the query to bring back a lot of data(old data or less queried data, queries generally request current year or max current year-2) and using a I/p parameter driven by procedur makes this one time activity as the model will automatically shift for example current year will change to 2017 next year and 2016 will be current year -1 and hence forth.

                      PLease share your thoughts!

                      Kind Regards,

                      Ashwin.

                      (0) 
                        1. Ashwin S

                          Hi Anindya,

                          I want to have them as separate cal views and consume them into individual Virtual Providers (to get the nav attributes in BW) and finally use a composite provider or a Multi Provider to union.

                          Open for suggestions..

                          Below is the data flow:

                          scn.GIF

                          Kind Regards,

                          Ashwin

                          (0) 
                          1. Anindya Bose Post author

                            You can try that scenario and try another one in parallel.

                            Have 4 difference projections and  UNION  them  in calculation view itself.

                            Create composit provider on this calculation view avoiding creation of 4 virtual provider .

                            You can compare the performance of both the model .

                            Regards

                            Anindya

                            (0) 
                            1. Ashwin S

                              Sure I can try, but I need nav attributes from BW so don’t you think Virtual Provider could be a better approach?

                              Kind Regards,

                              Ashwin

                              (0) 
    1. Anindya Bose Post author

      Hi Vijay

      Unfortunately “Filter Expression” editor does have lots of function to be used at present. My feeling is , SAP will add more functions in future releases.  However, you might try with simple addition or subtraction and check if that works.  For Month or Fiscal Period it would still be difficult, because when you subtract 1 from Jan,2016 it would take you to Dec,2015.

      Regarding your question via LinkedIn, yes, we can do offset of 12 months plus . Current code would also do the same .  Just you need to change the order of months, if you care .  Otherwise , current code can be used without change.

      Capture.PNG

      If you want output  to be “201605 – 201705”, you just need to change the order in concatenation.

      Regards

      Anindya

      (0) 
  1. Govind Parmar

    Anindya

    Is it possible to change the input parameter using procedure.

    For example : input parameter A is mapped to procedure XYZ.

    Procedure XYZ is having one input parameter B and Output parameter C.

    Logic in Procedure XYZ is : C = B + 10;

    I want to pass A to B in the procedure and C should again be mapped to A with modified value.

    Is this possible ? I tried but was not able to do the mapping as A does not show up in the calculation view input parameter field.

    Thanks

    Govind

    (0) 
    1. Anindya Bose Post author

      Govind

      I am sorry, could not understand the requirement properly .  You can map one input parameter to another.   Like A and B can be mapped to pass values .

      Regards

      Anindya

      (0) 
  2. Thomas Rohmann

    Hi there,
    cool stuff, thanks for sharing. I am trying to implement the same but as soon as my SP has got more than one input parameter I am getting an error complaining about the mapping .
    Did you experience something similar? Any clue? I am on SPS12
    BR Thomas

    Error Message
    Repository: Encountered an error in repository runtime extension;Model inconsistency. Create Scenario failed:

    Referenced stored procedure “_SYS_BIC”.”TROHMANN::SP_KPI_YTD”: Parameter “IP_TIME” not found in variable mappings(scenario -> variables -> variable ($$IP_DATERANGE$$))

    The following errors occurred: Inconsistent calculation model (34011)
    Details (Errors):
    – scenario -> variables -> variable ($$IP_DATERANGE$$): Referenced stored procedure “_SYS_BIC”.”TROHMANN::SP_KPI_YTD”: Parameter “IP_TIME” not found in variable mappings.
    – scenario -> variables -> variable ($$IP_DATERANGE$$): The number of input parameters of the referenced stored procedure “_SYS_BIC”.”TROHMANN::SP_KPI_YTD” does not correspond to the number of variable mappings: Number of variable mappings ‘3’, number of input parameters of the stored procedure ‘2’.

    (0) 
  3. Anindya Bose Post author

    Hi Tom

    I do not see ip_fiscper has been defined as “Input Parameter”  ( no “IN” in front of param definition ) .  That could be a problem .

    Do not have access to SPS12 system but this should not change there either(can’t guarantee though)  .   Does it work if you put “YTD” as constant value inside the procedure ?

    Best Regards

    Anindya

     

    (0) 

Leave a Reply