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.

Usage of SAP HANA Exit Variables for a standard reporting scenario

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”.

Usage of SAP HANA Exit Variables for a standard reporting scenario

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

 

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario

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

Usage of SAP HANA Exit Variables for a standard reporting scenario

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

Usage of SAP HANA Exit Variables for a standard reporting scenario

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

Usage of SAP HANA Exit Variables for a standard reporting scenario

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

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario

Due to the fact that HANA Exit variables can only process single values, we’re using a range selection with two single value variables.

Usage of SAP HANA Exit Variables for a standard reporting scenario

Activate the External SAP HANA View setting in the Query to provide the HANA-native consumption of BW data.

Usage of SAP HANA Exit Variables for a standard reporting scenario

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.

Usage of SAP HANA Exit Variables for a standard reporting scenario

In transaction DB50 search for the SAP HANA View:

Usage of SAP HANA Exit Variables for a standard reporting scenario

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.

Usage of SAP HANA Exit Variables for a standard reporting scenario

The columns tab shows the available objects and columns of the view.

Usage of SAP HANA Exit Variables for a standard reporting scenario

( As an alternative you can also use the SAP HANA Modeler for this:

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario)

 

Selecting the “Send to SQL Editor” button, the SQL Editor opens and you can directly build a new query on the view.

Usage of SAP HANA Exit Variables for a standard reporting scenario

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"

Usage of SAP HANA Exit Variables for a standard reporting scenario

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’)

Usage of SAP HANA Exit Variables for a standard reporting scenario

As you can see we achieve to get the same results as in the SAP BW Query shown in RSRT:

Usage of SAP HANA Exit Variables for a standard reporting scenario

Usage of SAP HANA Exit Variables for a standard reporting scenario

It is also possible to save the query and call and change it again later.

Usage of SAP HANA Exit Variables for a standard reporting scenario

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"

Usage of SAP HANA Exit Variables for a standard reporting scenario

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.

 

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !