Hello,

due to the fact that there is not so much information available concerning the optimization for HANA  I would like to share some experiences. First of all this is an example. There are different ways to get good performance in HANA in combination with PAK. FOX Formulas which are using ABAP fuction modules are not running in memory. The first possibility whould be to change the calls for function modules to “read for reference data” from external aggregation layers. I have also done that which the same example but that would be another blog. Another possibility is to create an own planning function type based on an AMDP class.

This is what I would like to show now. The fox formula is distributing data from montly level to daily level. But it is only doing that for working days. That means the math is relatively easy. It is value per month divided by working days * 1 (if it is a working day) or * 0 if it is not a working day.

At first the old code in fox formula (at the time where the code was created there was no possibility to use internal tables in fox – the coding is simplified – we are using more key figures and characteristics but that is not important for the idea how it is working):

DATA CALYEAR TYPE 0CALYEAR.
DATA CALMONTH2 TYPE 0CALMONTH2.
DATA CALDAY TYPE 0CALDAY.
DATA ARBT TYPE F.
DATA TAGE TYPE F.
DATA VALUETAG_YUMVISTU TYPE F.
DATA COUNTER TYPE I.
DATA DATUM TYPE 0CALDAY.
DATA HOLIDAY TYPE STRING.
DATA DUMMY TYPE F.

FOREACH CALYEAR, CALMONTH2, CALDAY.
 
IF CALDAY IS INITIAL.

      CALL FUNCTION Z_ANZ_ARBT_TAGE_MON
   EXPORTING
     I_MONAT
= CALMONTH2
     I_JAHR
= CALYEAR
   IMPORTING
     E_VAL_ARBT
= ARBT
     E_VAL_TAGE
= TAGE.

   VALUETAG_YUMVISTU = { YUMVISTU, #, CALMONTH2, CALYEAR } / ARBT.

   COUNTER = 0.
  
DO.
     COUNTER
= COUNTER + 1.

     CALL FUNCTION Z_WORKING_DAY_CHECK_OF
     EXPORTING

       MONAT = CALMONTH2
       TAG
= COUNTER
       JAHR
= CALYEAR
     IMPORTING
       HOLIDAY_FOUND
= HOLIDAY
       E_DATUM
= DATUM.

          IF HOLIDAY = ‘X’.


    
ELSE.
      
{ YUMVISTU, DATUM, CALMONTH2, CALYEAR } = VALUETAG_YUMVISTU + { YUMVISTU, DATUM, CALMONTH2, CALYEAR }.
    
ENDIF.
    
IF COUNTER = TAGE.
      
{ YUMVISTU, #, CALMONTH2, CALYEAR } = 0.

       EXIT.
    
ENDIF.

   ENDDO.
ENDIF.

ENDFOR.

Before you start creating the coding in the new modelling tools via HANA Studio / Eclipse (AMDP is not officially supported in SAP GUI), you need to generate the calender data in HANA Studio.

/wp-content/uploads/2016/05/generate_time_data_958758.jpg

/wp-content/uploads/2016/05/generate_time_data_2_958759.jpg

After that you need an attribute view which you can use as the basis for the following sql statements. To get an attribute view based on the generated time data you have to create it which is mostly done automatically:

AttributeView1.jpg

AttributeView2.jpg

You will find a more detailles description hiere: Generate Time Data in SAP HANA – Part 1 – Steps 1-6

That is the basis for the next steps. Now in the SQL Script you need to get the information which day is a working day and which day is not. Furthermore the total sum of working days per month is needed for calculation.

For testing purposes it is easier to do the first steps without an ABAP Managed Database procedure. Which means if you do have the necessary rights you can create the procedure directly on the HANA DB.

“Example” and “test” in the following code are HANA packages and “TIME” is an attribute view based on the created time data table. This results in select … from “_SYS_BIC”.“example.test/TIME”; to select the data from the table.

Example Code for that (you can find a detailed description of the coding below in the class example):

create procedure SAPSR3.myprocedurename ( ) language sqlscript reads sql data as

begin

DECLARE l_FactoryCalendarID VARCHAR(2);

DECLARE l_duration int;

l_FactoryCalendarID := ’01’;

lt_tab =

SELECT DISTINCT DATE_SAP AS DATE_SAP , DATE_SAP + 1 AS SAP_MORGEN

FROM “_SYS_BIC”.“example.test/TIME”;

lt_input = SELECT distinct

  “DATE_SAP” as “StartDate”,

  “SAP_MORGEN” as “EndDate”,

  l_FactoryCalendarID as “FactoryCalendarId”,

  l_duration as “Duration”

FROM :lt_Tab;

CALL “_SYS_AFL”.“ERPA_FACTORY_DAYS_BETWEEN_DATES_PROC”( ‘SAPSR3’, ‘SAPSR3’, :lt_input, :lt_result );

lt_result2 = SELECT “StartDate” , “YEAR” , “MONTH” , “Duration” FROM :lt_result AS A JOIN “_SYS_BIC”.“example.test/TIME” as b ON

  1. a.“StartDate” = b.“DATE_SAP”

  order by “StartDate”;

lt_result3 = Select “YEAR” , “MONTH” , SUM(“Duration”) as “Days” FROM :lt_result2 GROUP BY YEAR , MONTH;

select a.“StartDate” , a.“YEAR” , a.“MONTH” , a.“Duration” , b.“Days” , 1000 / b.“Days” * a.“Duration” as “Amount”

  FROM :lt_result2 as a join :lt_result3 as b

  on a.“YEAR” = b.“YEAR” and

        a.“MONTH” = b.“MONTH”;

end;

If you are interested in the parameters of the procedure ERPA_FATORY_DAYS_BETWEEN_DATED_PROC just take a look at it in HANA Studio:

Prameters1.jpg

Parameters2.jpg

You can try it by calling:

call myprocedurename;

The result is:

/wp-content/uploads/2016/05/result_958777.jpg

Ok, this has to combined with the montly data from the aggregation level. You can see the full coding below. After distribution the montly value should be zero. Due to the fact that we have to deliver a delta information at the end we have to negate the original value. That is basically the SQL Script part but now we do need an AMDP. The AMDP has the advantage that you can use ABAP transport system instead of using HANA transport system additionally to ABAP system which makes it more difficult to synchronize.

The is a report “RSPLS_SQL_SCRIPT_TOOL” from SAP which gives you some sample coding if you add an aggregation level and a type of a function:

/wp-content/uploads/2016/05/report_958802.jpg

As a result you will get some example coding whith the needed type for the aggregation level and some basic parts of the class which needs to be created. In this coding maybe the option OPTIONS READ-ONLY. is missing. This can result in strange error messages if you have not set the parameters in a way which allows to write data in SQL Script. If you don’t want to write data you should add the read-only option.

Now you have to create a class (in Eclipse). And than create a new planning function type in transaction RSPLAN (NW7.4):

/wp-content/uploads/2016/05/functiontype2_958811.jpg

/wp-content/uploads/2016/05/functiontype_958812.jpg

The new version as an AMDP:

CLASS ZBW_PLFU_MON_DAY DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

* This is the structure of the aggregation level

    TYPES: BEGIN OF Y_S_VXSD004X1,

             CALDAY     TYPE /BI0/OICALDAY,

             CALMONTH2  TYPE /BI0/OICALMONTH2,

             CALYEAR    TYPE /BI0/OICALYEAR,

             CO_AREA    TYPE /BI0/OICO_AREA,

             DISTR_CHAN TYPE /BI0/OIDISTR_CHAN,

             INFOPROV   TYPE RSINFOPROV,

             MATERIAL   TYPE /BI0/OIMATERIAL,

             MAT_PLANT  TYPE /BI0/OIMAT_PLANT,

             MAT_SALES  TYPE /BI0/OIMAT_SALES,

             PLANT      TYPE /BI0/OIPLANT,

             PROFIT_CTR TYPE /BI0/OIPROFIT_CTR,

             SALESORG   TYPE /BI0/OISALESORG,

             SALES_DIST TYPE /BI0/OISALES_DIST,

             SOLD_TO    TYPE /BI0/OISOLD_TO,

             VTYPE      TYPE /BI0/OIVTYPE,

             YVERSIOP   TYPE /BIC/OIYVERSIOP,

             YUMVISTU   TYPE /BIC/OIYUMVISTU,

           END OF Y_S_VXSD004X1.

    TYPES: Y_T_VXSD004X1 TYPE STANDARD TABLE OF Y_S_VXSD004X1.

    INTERFACES IF_RSPLFA_SRVTYPE_TREX_EXEC.

* If you need ref_data you need to use the interface IF_RSPLFA_SRVTYPE_TREX_EXEC_R / IF_RSPLFA_SRVTYPE_IMP_EXEC_REF

    INTERFACES IF_RSPLFA_SRVTYPE_IMP_EXEC.

    INTERFACES IF_AMDP_MARKER_HDB.

CLASS-METHODS: MON_TO_DAY IMPORTING VALUE(I_VIEW) TYPE Y_T_VXSD004X1

                              EXPORTING VALUE(E_VIEW) TYPE Y_T_VXSD004X1.

  PROTECTED SECTION.

  PRIVATE SECTION.

  ENDCLASS.

CLASS ZBW_PLFU_MON_DAY IMPLEMENTATION.

  METHOD IF_RSPLFA_SRVTYPE_TREX_EXEC~INIT_AND_CHECK.

    E_TREX_SUPPORTED  = RS_C_TRUE.

  ENDMETHOD.

  METHOD IF_RSPLFA_SRVTYPE_TREX_EXEC~TREX_EXECUTE.

    DATA: L_R_SQL_SCRIPT   TYPE REF TO IF_RSPLS_SQL_SCRIPT,

          L_PROCEDURE_NAME TYPE STRING,

          L_T_IOBJ_PARAM   TYPE IF_RSR_PE_ADAPTER=>TN_T_IOBJ_PARAM,

          Y_T_VXSD004X1 TYPE Y_T_VXSD004X1.

    L_R_SQL_SCRIPT = CL_RSPLS_SESSION_STORE_MANAGER=>GET_SQL_SCRIPT_INSTANCE( I_R_STORE = I_R_STORE ).

    DATA(METHOD) = NEW ZBW_PLFU_MON_DAY( ).

    METHOD->MON_TO_DAY(

      EXPORTING

        I_VIEW = Y_T_VXSD004X1

      IMPORTING

        E_VIEW = DATA(LT_E_VIEW)

     ) .

    L_PROCEDURE_NAME = ‘ZBW_PLFU_MON_DAY=>MON_TO_DAY’.

    R_S_VIEWVIEW = L_R_SQL_SCRIPT->EXECUTE_SQL_SCRIPT(

        I_VIEW         = I_VIEW

        I_T_IOBJ_PARAM = L_T_IOBJ_PARAM

        I_PROC_NAME    = L_PROCEDURE_NAME

        I_R_MSG        = I_R_MSG ).

  ENDMETHOD.

  METHOD MON_TO_DAY  BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.

    DECLARE l_FactoryCalendarID VARCHAR(2);

    DECLARE l_duration int;

    — Factory calender in our case NRW

    l_FactoryCalendarID := ’01’;

    — Generated time data in HANA is the basis for the further steps

    lt_tab1 = SELECT DISTINCT DATE_SAP AS DATE_SAP , DATE_SAP + 1 AS SAP_MORGEN

    FROM “_SYS_BIC”.“example.test/TIME”;

    — Prepare for HANA procedure

    lt_tab2 = SELECT distinct

    “DATE_SAP” as “StartDate”,

    “SAP_MORGEN” as “EndDate”,

    l_FactoryCalendarID as “FactoryCalendarId”,

    l_duration as “Duration”

    FROM :lt_Tab1;

    — Calculate working days between dates

    CALL “_SYS_AFL”.“ERPA_FACTORY_DAYS_BETWEEN_DATES_PROC”( ‘SAPSR3’, ‘SAPSR3’, :lt_tab2, :lt_tab3 );

    — Add Year and month and remove enddate

    lt_tab4 = SELECT “StartDate” , “YEAR” , “MONTH” , “Duration” FROM :lt_tab3 AS A JOIN “_SYS_BIC”.“example.test/TIME” as b ON

    a.“StartDate” = b.“DATE_SAP”

    order by “StartDate”;

    — Working days per month

    lt_tab5 = Select “YEAR” , “MONTH” , SUM(“Duration”) as “Days” FROM :lt_tab4 GROUP BY YEAR , MONTH;

    — Add working days per month to every single day

    lt_tab6 = select a.“StartDate” , a.“YEAR” , a.“MONTH” , a.“Duration” , b.“Days”

    FROM :lt_tab4 as a join :lt_tab5 as b

     on a.“YEAR” = b.“YEAR” and

        a.“MONTH” = b.“MONTH”;

    —  Create cartesian product about calender days per month

    —  Calculate monthly value divided by working days per month * 1 if working day, otherweise * 0

    e_view = select b.“StartDate” as “CALDAY” , “CALMONTH2” , “CALYEAR” , “CO_AREA” , “DISTR_CHAN” ,

                  “INFOPROV” , “MATERIAL” , “MAT_PLANT” , “MAT_SALES” , “PLANT” , “PROFIT_CTR” ,

                  “SALESORG” , “SALES_DIST” , “SOLD_TO” , “VTYPE” , “YVERSIOP” ,

                  “YUMVISTU” / b.“Days” * b.“Duration” AS “YUMVISTU”

            FROM :I_VIEW AS A JOIN :LT_TAB6 AS B ON

                   A.“CALYEAR” = b.“YEAR” and

                   a.“CALMONTH2” = b.“MONTH”

             WHERE a.CALDAY = ‘00000000’

             UNION

    —       Negate the values which are distributed from montly level (the result of the procedure has to be the delta)

             select “CALDAY” , “CALMONTH2” , “CALYEAR” , “CO_AREA” , “DISTR_CHAN” ,

                    “INFOPROV” , “MATERIAL” , “MAT_PLANT” , “MAT_SALES” ,

                    “PLANT” , “PROFIT_CTR” , “SALESORG” , “SALES_DIST” ,

                    “SOLD_TO” , “VTYPE” , “YVERSIOP” ,

                    “YUMVISTU” * –1 AS “YUMVISTU”

             from :i_view WHERE CALDAY = ‘00000000’;

  ENDMETHOD.

ENDCLASS.


As you can see ERPA_FACTORY_DAYS_BETWEEN_DATES_PROC is used for getting the information if it is a working day or not (instead using a functional module). Basically it is also possible to debug the procedure or the AMDP in Eclipse tools.


On Oracle the fox coding took around 300 seconds for 25.000 as the input with around 350.000 records output. With the optimized code on HANA this takes 12 seconds and that is for copying 25.000 records and distributing from month to day together! Not to bad in my opinion. Without improving the coding the hana combined with a new application server is faster then the oracle db but our experience is that it is often just 2 or 2,5 times faster without code pushdown. This is maybe only valid for our system but it gives an impression on performance impact.

To report this post you need to login first.

2 Comments

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

  1. Daniel N

    Hello Torsten – 

    Thanks you so much for the post. It’s very helpful. We are now in BW 7.4 SP14. Is running function in memory (PAK) already available in BW 7.4 SP14?

    (0) 
    1. Torsten Wirth Post author

      Hello Daniel,

      yes this is possible in 7.4 SP14 – given the fact that you have licenced PAK.

      You have to use HANA optimized Cubes as a basis and there are some other prerequirements which are described in note 1637199.

      Regards,

      Torsten

      (0) 

Leave a Reply