The scope of the document is to show how SAP HANA Exit variables are working for column selection for standard YTD reporting and delta calculation using native SQL. As a result the native SQL statements on generated calculation views work nearly similar as a SAP BW Query. By using HANA Exit variables the Query can be used within SAP BW and natively side by side. Furthermore complex SQL statements can be simplified by using HANA Exit variables.
The document should serve as an example how SAP HANA Exit variables can be used.
The first part of the document shows how to generate calculation views out of SAP BW queries and the second part of the document explains how to consume the generated calculation view including the SAP HANA Exit variables natively.
STEP 1
Enable that SAP HANA Exit variables are processed.
First of all you have to create the enhancement spot based on BAdI RSOA_VARIABLES_HANA_EXIT.
Start Transaction SE18.
Create Enhancement Spot RSROA_VARIABLES_HANA_EXIT.
For more details on SAP HANA EXIT see the SAP Documentation:
https://help.sap.com/erp2005_ehp_08/helpdata/en/b7/7f3073e9d842d6959e03e74dcefa09/frameset.htm
STEP 2
Select “Enh. Spot Element Definitions” Tab and click on the icon “Create BAdI implementation”.
Assign interface for the BAdI:
IF_RSROA_VAR_HANA_EXIT
Save and activate the interface.
Next, create an Enhancement Implementation, a Composite Enhancement Implementation and a BAdI Implementation.
Enhancement Implementation: ZXX_CL_VARIABLE_HANA
Composite Enhancement Implementation: ZXXXX_VARIABLE_HANA_EXIT
BAdI Implementation: ZXX_VAR_HANA_EXIT_BADI
STEP 3
Implement the class for the YTD variables.
The objective is to calculate from and to values for calendar day based on an input ready calendar day variable.
Input: 22.08.2015
Result calendar day from current year: 01.01.2015
Result calendar day to current year: 22.08.2015
Result calendar day from previous year: 01.01.2014
Result calendar day to previous year: 22.08.2014
To get these results we’re using a help method _CALCULATE_YTD with year as offset and an indicator for from and to values. This method encapsulates the logic for the YTD calculation and is called by the interface method process. To be able to handle shift years we’re using the script function ADD_YEARS.
In order to be able to process our HANA step-2 variables we have to store the input value of our input variable (CMVM_CALDAY_YTD) in method GET_PROPERTIES in table C_TS_VNAM_INDEX.
class ZXX_CL_VARIABLE_HANA definition public final create public . public section. interfaces IF_BADI_INTERFACE . interfaces IF_AMDP_MARKER_HDB . interfaces IF_RSROA_VAR_HANA_EXIT . PROTECTED SECTION. private section. class-methods: _CALCULATE_YTD importing value(IV_VALUE1) type RSCHAVL value(IV_VALUE_OFFSET) type INTEGER default 0 " 0=CY, 1=PY value(IV_VALUE_TO) type CHAR1 default '' " ''=FR, 'X'=TO exporting value(EV_VALUE) type CHAR8 "Day . ENDCLASS. CLASS ZXX_CL_VARIABLE_HANA IMPLEMENTATION. method IF_RSROA_VAR_HANA_EXIT~GET_PROPERTIES by database procedure for hdb language sqlscript. C_IS_ACTIVE := 'X'; if :I_VNAM = 'CHVM_CALDAY_YTD_CY_FR' or :I_VNAM = 'CHVM_CALDAY_YTD_CY_TO' or :I_VNAM = 'CHVM_CALDAY_YTD_PY_FR' or :I_VNAM = 'CHVM_CALDAY_YTD_PY_TO'THEN C_TS_VNAM_INDEX = select 'CMVM_CALDAY_YTD' as VNAM, 1 AS INDEX FROM DUMMY; END IF; ENDMETHOD. method IF_RSROA_VAR_HANA_EXIT~PROCESS by database procedure for hdb language sqlscript using ZXX_CL_VARIABLE_HANA=>_CALCULATE_YTD. if :I_VNAM = 'CHVM_CALDAY_YTD_CY_FR' THen call "ZXX_CL_VARIABLE_HANA=>_CALCULATE_YTD"( :i_var_value_1, 0 , '' , :c_value ); END IF; if :I_VNAM = 'CHVM_CALDAY_YTD_CY_TO' THen call "ZXX_CL_VARIABLE_HANA=>_CALCULATE_YTD"( :i_var_value_1, 0 , 'X' , :c_value ); END IF; if :I_VNAM = 'CHVM_CALDAY_YTD_PY_TO' THen call "ZXX_CL_VARIABLE_HANA=>_CALCULATE_YTD"( :i_var_value_1, 1 , 'X' , :c_value ); END IF; if :I_VNAM = 'CHVM_CALDAY_YTD_PY_FR' THen call "ZXX_CL_VARIABLE_HANA=>_CALCULATE_YTD"( :i_var_value_1, 1 , '' , :c_value ); END IF; endmethod. method _CALCULATE_YTD by database procedure for hdb language sqlscript. --Declare Area DECLARE lv_year INT; declare LV_MON_FR string; declare LV_MON_TO string; declare LV_DAY_FR string; declare LV_DAY_TO string; --Prepare LV_YEAR := substring( ADD_YEARS( TO_DATE( :IV_VALUE1 , 'YYYYMMDD' ) , ( 0 - IV_VALUE_OFFSET ) ) , 1, 4) ; LV_MON_TO := substring( ADD_YEARS( TO_DATE( :IV_VALUE1 , 'YYYYMMDD' ) , ( 0 - IV_VALUE_OFFSET ) ) , 6, 2) ; LV_DAY_TO := substring( ADD_YEARS( TO_DATE( :IV_VALUE1 , 'YYYYMMDD' ) , ( 0 - IV_VALUE_OFFSET ) ) , 9, 2) ; LV_MON_FR := '01'; LV_DAY_FR := '01'; --Execute EV_VALUE := LV_YEAR || LV_MON_FR || LV_DAY_FR ; if IV_VALUE_TO = 'X' then EV_VALUE := LV_YEAR || LV_MON_TO || LV_DAY_TO ; end if; endmethod. ENDCLASS.
STEP 4
Create the SAP HANA exit variables with BW Query Designer:
- CHVM_CALDAY_YTD_CY_FR – calendar day, year to date current year from
- CHVM_CALDAY_YTD_CY_TO – calendar day, year to date current year to
- CHVM_CALDAY_YTD_PY_FR – calendar day, year to date previous year from
- CHVM_CALDAY_YTD_PY_TO – calendar day, year to date previous year to
- CMVM_CALDAY_YTD – calendar day, input ready
STEP 5
For this example we activated BW Content InfoProvider /IMO/SD_M30 and transferred it to a CompositeProvider by executing program RSO_CONVERT_IPRO_TO_HCPR.
Fill in the required information:
- Source: /IMO/SD_M30
- Target: V_SD30
Open the new CompositeProvider /IMO/V_SD30 – Billing
and activate the external SAP HANA View in the CompositeProvider
STEP 6
On this CompositeProvider, just create a simple query where the HANA Exit variables will be used in the column structure within quantity and net value selections for the previous and current year and delta selection for the quantities.
Query:
/IMO/V_SD30_REP_001
Due to the fact that HANA Exit variables can only process single values, we’re using a range selection with two single value variables.
Activate the External SAP HANA View setting in the Query to provide the HANA-native consumption of BW data.
STEP 7
When the External SAP HANA View is activated, a calculation view is automatically generated out of the BW Query object.
In a next step go to transaction RS2HANA_ADMIN.
Here the SAP HANA View ID for the Query is shown, just search for the Query name /IMO/V_SD30_REP_001 .
Now we can access the generated calculation view natively.
In transaction DB50 search for the SAP HANA View:
Open the SAP HANA View */V_SD30_REP_001.
You have the two tabs here:
– Properties
– Columns
On the properties tab you see the storage type of the view which is column storage.
The columns tab shows the available objects and columns of the view.
( As an alternative you can also use the SAP HANA Modeler for this:
)
Selecting the “Send to SQL Editor” button, the SQL Editor opens and you can directly build a new query on the view.
SELECT
"0COMP_CODE",
"0COMP_CODE___T",
sum( "QUAN_PY_YTD" ),
sum( "QUAN_CY_YTD" ),
sum( "NET_PY_YTD" ),
sum( "NET_CY_YTD" ),
case when ( sum( "QUAN_CY_YTD" ) = 0 ) then 0 else ( sum( "QUAN_PY_YTD" ) *100 / ABS( sum( "QUAN_CY_YTD" ) ) ) end as "DELTA_SQL"
FROM
_SYS_BIC."system-local.bw.bw2hana.query.imov_sd30/V_SD30_REP_001"
(
'PLACEHOLDER' = ('$CMVM_CALDAY_YTD$', '20150822')
)
group by
"0COMP_CODE",
"0COMP_CODE___T"
You can easily calculate new columns using the generated calculation view in the SQL editor.
Due to the fact that the division by 0 is not generated into the calculation view we had to take care for this in the SQL statement.
case when ( sum( “QUAN_CY_YTD” ) = 0 ) then 0 else ( sum( “QUAN_PY_YTD” ) *100 / ABS( sum( “QUAN_CY_YTD” ) ) ) end as “DELTA_SQL”
Also see this link to SAP Documentation for further details:
https://help.sap.com/saphelp_nw74/helpdata/en/c4/70990abfce4796aacf7a41a27fedcc/frameset.htm
The input variable value is handed over in the placeholder section in the native SQL.
‘PLACEHOLDER’ = (‘$$CMVM_CALDAY_YTD$$’, ‘20150822’)
As you can see we achieve to get the same results as in the SAP BW Query shown in RSRT:
It is also possible to save the query and call and change it again later.
Without using the SAP HANA Exit variables the SQL query gets much more complex and you have to insert all values for the YTD calculation manually:
Select "0COMP_CODE", sum( "CY" ) as "CY", sum( "PY" ) as "PY" FROM ( SELECT "0COMP_CODE", sum( "0QUANT_B" ) as "CY", 0 as "PY" FROM _SYS_BIC."system-local.bw.bw2hana.imo/V_SD30" where "0CALDAY" >= '20150101' and "0CALDAY" <= '20150822' group by "0COMP_CODE" union all SELECT "0COMP_CODE", 0 as "CY", sum( "0QUANT_B" ) as "PY" FROM _SYS_BIC."system-local.bw.bw2hana.imo/V_SD30" where "0CALDAY" >= '20140101' and "0CALDAY" <= '20140822' group by "0COMP_CODE" ) GROUP BY "0COMP_CODE"
SAP HANA variables are a good solution for hybrid scenarios by combining the best of both worlds. So the clue is once the SAP HANA variables are created, they can be used in SAP BW Queries and of course for HANA native reporting. SAP Analytics Cloud is also able to process HANA Exit variables and input ready variables are shown as prompts.
This is a good possibility to bring both worlds together but there is also still room for improvement because the handling is not very smooth yet.
https://blogs.sap.com/2017/09/29/usage-of-sap-hana-exit-variables-for-a-standard-reporting-scenario/