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.
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:
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
- 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:
You can try it by calling:
call myprocedurename;
The result is:
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:
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):
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