SAP planning: Flexible data slices in SAP BPC with ABAP and SQL script

A frequent requirement in SAP planning, be it Business Planning and Consolidation (BPC) or BW Integrated Planning (BW IP), is the ability to use flexible data slices. The objective is to enable the business department to lock and unlock data centrally. This way, you can ensure the consistency of data in the various planning steps. In this article you will learn how to implement this scenario using a customer exit.

In our example, we want to lock a company code for planning. In doing so, the users have the option of entering data in DSO using a query. For example, a 1 or 0 is entered for the company code.

This information is read out via the customer exit. If there is a company code with the flag 1, this company code is considered as locked. In our example, company code 1000 is considered as locked.

Basics of the SQL Script implementation

The implementation of the customer exit with SQL Script allows the performance advantages of HANA to be fully exploited. In case of disaggregations or planning functions with a lot of data, calculations are processed in the SAP HANA database. You benefit from the increased performance, because the data does not have to be transferred to the ABAP environment first, but can be processed directly in the database using SQL Script.

However, in addition to the SQL script implementation, the corresponding implementation in ABAP must also be available. If input-ready queries are used, the SAP system uses the ABAP environment. Another exception is the forecasting type planning function. In all other cases, the SQL script implementation is used.

Create ABAP class

As you have already learned, the exit must be available in both languages, ABAP and SQL Script. Therefore, we create the ABAP class first.

You can use the existing example class CL_RSPLS_DS_EXIT_BASE as a template. This class contains the interfaces and predefined methods required for the ABAP implementation.

The sample class is located in the package RSPLS. To make the class easier to find, you can add the package to favorites.

Select the class CL_RSPLS_DS_EXIT_BASE and right-click. Then select Duplicate from the context menu.

Then determine the package and the name of the new class.

In the new class, select the method IS_PROTECTED. This method checks whether a data record is protected against changes.

In our example we use a buffer and an additional method to read the DSO. If an entry with the flag 1 is found for a company code, it is considered as locked.

Code Snippets ABAP

Method: IS_PROTECTED

Paste the following code:

METHOD IF_RSPLS_DS_METHODS~IS_PROTECTED.
*---------------------------------------------------------------------*
* --> i_s_data data record, the values for infoobjects from
* n_ts_fields are set, the rest is initial
* <-- e_t_mesg messages
* <-- e_noinput flag, records is protected or not
*---------------------------------------------------------------------*

FIELD-SYMBOLS:
<l_th_buf> TYPE HASHED TABLE,
<l_s_buf> TYPE any.

CLEAR e_t_mesg.

ASSIGN o_r_th_buf->* TO <l_th_buf>.
ASSIGN o_r_s_buf->* TO <l_s_buf>.
<l_s_buf> = i_s_data.
READ TABLE <l_th_buf> INTO <l_s_buf> FROM <l_s_buf>.
IF sy-subrc NE 0.
* This record is not checked before
* Now we check if the record is locked
CALL METHOD me->get_locked_entries
EXPORTING
i_s_data = i_s_data
IMPORTING
e_s_mesg = o_r_s_mesg->*
e_noinput = o_r_protected->*.

INSERT <l_s_buf> INTO TABLE <l_th_buf>.
ENDIF.
e_noinput = o_r_protected->*."fix pointer to <l_s_buf>-protected
IF e_noinput = rs_c_true AND e_t_mesg IS SUPPLIED.
* o_r_s_mesg is a pointer to '_S_MESG' in the buffer workarea
APPEND o_r_s_mesg->* TO e_t_mesg.
ENDIF.

ENDMETHOD. 

Methode: Get Locked Entries

First we have to define the parameters in the DEFINITION part:

METHODS get_locked_entries
IMPORTING
!i_s_data TYPE any
EXPORTING
!e_s_mesg TYPE if_rspls_cr_types=>tn_s_mesg
!e_noinput TYPE rs_bool .

In the IMPLEMENTATION part we add the following logic:

METHOD get_locked_entries.
DATA:
l_s_mesg TYPE if_rspls_cr_types=>tn_s_mesg,
lv_compcode TYPE /bi0/oicomp_code.

FIELD-SYMBOLS:
<fs_compcode> TYPE /bi0/oicomp_code.

ASSIGN COMPONENT:
'COMP_CODE' OF STRUCTURE i_s_data TO <fs_compcode>.


CHECK sy-subrc EQ 0.
CLEAR lv_compcode.
SELECT SINGLE comp_code FROM /bic/azd011lock7
INTO lv_compcode
WHERE comp_code = <fs_compcode>
AND /bic/zdflag = 1.

IF lv_compcode IS NOT INITIAL.
e_s_mesg-msgid = 'ZDR'.
e_s_mesg-msgno = '000'. "Company Code &1 is locked against changes.
e_s_mesg-msgty = 'W'.
e_s_mesg-msgv1 = <fs_compcode>.
e_noinput = rs_c_true.
ELSE.
e_noinput = rs_c_false.
ENDIF.

ENDMETHOD. 

Better performance with external buffer

If the exit is called often, you will get better performance with the external buffer. To do this, delete the existing buffer logic in the CONSTRUCTOR method and define the following:

*use external buffering
if_rspls_dataslice~n_use_external_buffer = abap_true.

Create the data slice

After we have created the class, we create a new data slice of type Exit. Select the previously defined class as the exit class.

Planning Tools compared - SAP BW IP vs. BPC vs. SAC

Implementation in SQL Script

The HANA-specific interface IF_RSPLS_DS_EXIT_HDB is required to use the data slices at the level of the SAP HANA database. This interface contains two methods GET_SQLSCRIPT_INFO and GET_SQLSCRIPT_PARAMETERS. The first method, GET_SQLSCRIPT_INFO, determines the names of the SQL script procedure needed to process the exit. This tells the system which method to call in order to determine the locked company codes in our example. This method is implemented in the class created earlier as AMDP (ABAP Managed Database Procedure).

The method GET_SQLSCRIPT_INFO contains the following parameters:

• E_DB_SCHEMA_NAME - Name of the database schema containing the SQL script procedures to be executed. If this parameter is not filled, the database schema of the SAP system is used by default.
• E_PROCEDURE_NAME_PROTECTED - Name of the SQL Script procedure that contains the implementation of the lock check. If no procedure is passed, the system calls the ABAP implementation as a fallback solution.
• E_PARAMETER_NAME - Name of the structure containing additional parameters that are passed to the respective SQL Script procedures at runtime. This structure must first be created in the ABAP Dictionary. If this parameter is not set, no additional information is sent from the application server.
• E_HAS_SQL_MESG - Boolean operator, whether the SQL script procedure outputs messages.

You can use the GET_SQLSCRIPT_PARAMETERS method to pass additional parameters, such as the ABAP system date or user name, to the SQL Script method. In our example we do not need any additional parameters, therefore we do not use this method. An empty implementation is sufficient in this case.

The program RSPLS_SQL_SCRIPT_TOOL delivered by SAP offers assistance in the implementation of SQL script methods. Call transaction SE38 in the SAP GUI and select RSPLS_SQL_SCRIPT_TOOL as the program. Start the program with the Execute button or with the F8 key.

Then switch to the Sample Characteristic Relationship/Data tab. Then select the InfoProvider and the number of the relevant data slice. Also select theWith coding hints option.

When you then click on Execute, the system generates a proposal. You can use the generated source code directly at the relevant places in your class. The fields marked in yellow are for navigation purposes. They allow you to jump directly to the respective InfoProvider, class or InfoObject.

Follow the generated comments and insert the code suggestions into your ABAP class.

So we add the following code to the PUBLIC section of the class:

* the public section of your class ZDRCL_RSPLS_DS_EXIT_BASE has to contain the following lines:
INTERFACES if_amdp_marker_hdb.
INTERFACES if_rspls_ds_exit_hdb .

types:
begin of tn_s_data,
COMP_CODE type /BI0/OICOMP_CODE,
end of tn_s_data.
types
tn_t_data TYPE STANDARD TABLE OF tn_s_data
WITH DEFAULT KEY.

METHODS AMDP_GET_PROTECTED_RECORD
IMPORTING
VALUE(i_t_data) TYPE tn_t_data
EXPORTING
VALUE(e_t_data) TYPE tn_t_data.

In the IMPLEMENTATION section we define the AMDP method to be executed.

METHOD if_rspls_ds_exit_hdb~get_sqlscript_info.
e_procedure_name_protected = 'ZDRCL_RSPLS_DS_EXIT_BASE=>AMDP_GET_PROTECTED_RECORD'.
ENDMETHOD.

In our example we do not use any parameters, so nothing has to be passed.

Then we define the logic of the AMDP method in SQL Script. Thereby we read out the checked company codes from the ADSO. If an entry with the flag 1 is found, these company codes are considered as locked.

METHOD amdp_get_protected_record BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING /bic/azd011lock7.

e_t_data = select it.COMP_CODE
from :i_t_data as it
LEFT JOIN "/BIC/AZD011LOCK7" as lookup
ON it.COMP_CODE = lookup.COMP_CODE
where lookup."/BIC/ZDFLAG" = 1;

ENDMETHOD.

To output messages from your SQL script logic you need to make the following adjustments. In DEFINITION part we define the table for the messages and extend the AMDP method:

TYPES:
BEGIN OF tn_s_mesg,
msgid TYPE syst-msgid,
msgno TYPE syst-msgno,
msgty TYPE syst-msgty,
msgv1 TYPE syst-msgv1,
msgv2 TYPE syst-msgv2,
msgv3 TYPE syst-msgv3,
msgv4 TYPE syst-msgv4,
END OF tn_s_mesg,
tn_t_mesg TYPE STANDARD TABLE OF tn_s_mesg
WITH NON-UNIQUE DEFAULT KEY.

METHODS amdp_get_protected_record
IMPORTING
VALUE(i_t_data) TYPE tn_t_data
EXPORTING
VALUE(e_t_data) TYPE tn_t_data
VALUE(e_t_mesg) TYPE tn_t_mesg.

In the IMPLEMENTATION part we specify that the method outputs messages.

METHOD if_rspls_ds_exit_hdb~get_sqlscript_info.
e_procedure_name_protected = 'ZDRCL_RSPLS_DS_EXIT_BASE=>AMDP_GET_PROTECTED_RECORD'.
e_has_sql_mesg = abap_true. "Flag: SQLscript returns messages
ENDMETHOD.

In the ADMP method itself, we fill the message table. Please note that all four variables must be passed, even if they do not have any values.

METHOD amdp_get_protected_record BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING /bic/azd011lock7.

e_t_data = select it.COMP_CODE
from :i_t_data as it
LEFT JOIN "/BIC/AZD011LOCK7" as lookup
ON it.COMP_CODE = lookup.COMP_CODE
where lookup."/BIC/ZDFLAG" = 1;

e_t_mesg = select
'ZDR' as MSGID,
'000' as MSGNO,
'W' as MSGTY,
COMP_CODE as MSGV1,
'' as MSGV2,
'' as MSGV3,
'' as MSGV4
from
:e_t_data;

* alle vier Variablen muessen uebergeben werden
ENDMETHOD.

Test your data slice

Now we can test our implementation. The best way to do this is to use a planning sequence, since the ABAP environment is used for query execution (except for disaggregation). You should use a planning function that can be executed in the SAP HANA environment, such as the copy function.

If you use a planning sequence, you can test the ABAP and SQL implementations separately. If you start the planning function with the Execute Step button, the SQL implementation is used. If, on the other hand, you use the Execute Step with Trace button, the ABAP implementation is executed.

Summary

Now you know how to create data slices quite flexibly using ABAP or SQL. This procedure can be extended at will by the programming freedom you have in both worlds. For example, you could easily and quickly activate the lock only on certain days. Furthermore, thanks to the implementation with SQL Script you have the full potential of the HANA database.

Do you need help defining your planning strategy or are you looking for experienced developers with SQL Script know how? Please do not hesitate to contact us.