Introduction
Many companies are using SAP Integrated Planning to enter data, consolidate or enhance it while within BW system. With new versions of SAP BW it is possible to use HANA database instead of Application Server. SAP calls it Planning Application KIT - PAK.
PAK may seem easy to use. There are one or 2 transactions to activate it and that's it. It lives in your system and sometimes makes a life of a planner easy. Sometimes not. There are cases especially with BW versions prior to 7.50 where there are difficulties especially with currency exchange. Or better say it is also depending on your HANA DB version.
How to turn PAK ON or OFF
When you are on the newest HANA DB and BW 7.50 or above you may feel safe. It is however only half true. PAK still has many limitations as described in OSS Note 1637199. For Planning Sequences, and Infoproviders in general SAP provided us with a nice report RSPLS_PLANNING_ON_HDB_ANALYSIS. With it you can check your advanced DSOs, Planning Functions or Planning Sequences. You can see with traffic lights and messages where is a problem. If you encounter an issue specific to your application, which run O.K. without PAK, or you just want to test it, you have in general 2 options:
- Turn PAK ON/OFF only for your user globally with a parameter in your user profile: RSPLS_HDB_SUPPORT -> HDB_OFF or HDB_ON.
- Turn PAK ON/OFF for your advanced DSO/Cube globally for all users: Enter it in Maintenance view for table RSPLS_HDB_ACT_IP with deactivated Flag
Disaggreagation on Queries with PAK
More challenging it is when it comes to Queries. In the already mentioned OSS Note 1637199 SAP mentions:
Disaggregations in the Query are executed not in the database if
- Planning model uses MultiProvider on top of an aggregation level
- A formula is used as reference key figure for disaggregation
- The key figure is restricted to multiple values for a given characteristic except several single values. E.g. intervals, or hierarchy nodes in the restriction lead to execution in the application server.
I have to add here also my very favorite BEX Variable Offsets, which I use to limit the number of Customer Exit variables I need to create for an application.
SQL-Script characteristic relationships
This all is however already mentioned. Let's try something more complicated. Your application needs characteristic relationships to derive some Characteristics automatically outside Aggregation levels. If you have target values within attributes of a characteristic inside the level or you can use some aDSO where its key is fully matched with your Aggregation level chars you are still fine. Challenge arises when you need to program it.
With classic ABAP class your PAK will never run. You need to use HANA Script within AMDP procedure. It may sound horrifying for us but it is relatively easy to accomplish with help of SAP.
Creating a class for derivation
First of all you need to create a new ABAP Class in transaction SE24. You must set superclass CL_RSPLS_CR_EXIT_BASE for it. Then it will be visible in transaction RSPLAN in characteristic relationships for your advanced DSO.
This is the second step to do. Create a characteristic relationship on your aDSO with derivation and enter all Source and Target characteristics. Save your model. It will not work yet however.
Enhancing the class with help of SAP
You are now ready to execute report RSPLS_SQL_SCRIPT_TOOL. On Sample Characteristic Relationship Tab, you can enter your aDSO name. As a Step enter the step number of your relationship with the class you created ( usually 1). Without it the report will not give you anything.
It helps you enhance your class with code you need to run it as SQL Script. It is not possible to write it in SAP GUI afterwards, so you have to switch to Eclipse or HANA Studio. Copy your code into your class:
Do your own magic
After this you have to concentrate on the custom logic within the method AMDP_DERIVE.
Example of SQL Script may look like this:
METHOD AMDP_DERIVE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
e_t_chas = select
case when ( "/BIC/SOURCE1" is NOT NULL and "/BIC/SOURCE1" <> '#' ) then
( '0' || CO_AREA || "SOURCE1")
else '' end as "/BIC/TARGET1",
case when "/BIC/SOURCE1" is NULL then '' else "/BIC/SOURCE1" end as "/BIC/SOURCE1",
case when CO_AREA is NULL then '' else CO_AREA end as CO_AREA
from :i_t_chas;
ENDMETHOD.
Test 1
Now you are prepared to run within PAK. Make sure the TARGET1 Characteristic is not in your Aggregation Level and report RSPLS_PLANNING_ON_HDB_ANALYSIS does not say PAK cannot be used and you can test it with a simple planning query with disaggreagation on the same column.
It works! ...For now.
Variable Offsets and PAK Disaggregation
Now to complicate it even more imagine another developer will be lazy and use for example BEX Variable with Offset and Disaggreagation of planned values (on itself). Example is when you have a 0CALMONTH variable to give you the first month of selected Calendar year but you want to have an additional column with February. You can do it also other way but let's imagine you use the Offset, so for February, you use your January Variable 0CALMONTH_CMOD_JAN as 0CALMONTH_CMOD_JAN+1.
All is correct Query is plannable and the customer is so far happy. What will NOT make him happy is when he goes into the aDSO to check the records and finds out TARGET1 characteristic is empty! Now it is your turn to investigate why the heck did this happen to you. In best case you also have your nice simple Query, you tested your class with and you verify that with it the TARGET1 char is always filled.
Solution with ABAP Fallback
If you are the system architect you will email all your developers to inform them not to use unsupported features of BEx. This is however sub-optimal in some cases. If someone has a simple query, which does not generate thousands of records he does not need PAK. PAK can even slow things down (as SAP mentioned few years ago) when the number of generated records is less than 1000(can be different now).
As a curious developer you google for a solution and find out that there is also ABAP Fallback. It is used when PAK is off. Either by setting or by using preventing feature. You can there use your good-ole ABAP to program again the same thing you did above with SQL Script but this time within ABAP.
For that you need to redefine the method DERIVE within your custom class from the superclass. You can do it by inserting following 2 lines at the end of class definition:
methods IF_RSPLS_CR_METHODS~DERIVE
redefinition
...
methods AMDP_DERIVE
importing
value(I_T_CHAS) type TN_T_CHAS
exporting
value(E_T_CHAS) type TN_T_CHAS .
methods IF_RSPLS_CR_METHODS~DERIVE
redefinition .
protected section.
private section.
ENDCLASS.
You can take the coding from the DERIVE method of the superclass and adjust it to fit your needs. In my example it looks like following:
METHOD if_rspls_cr_methods~derive.
*---------------------------------------------------------------------*
* <-- e_t_mesg messages
* <-> c_s_chas characteristic combination: source and target
* fields included; do not change the source
* fields
* <<- cx_rspls_failed exception
*---------------------------------------------------------------------*
*Created by Pavol Feranec
*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
* Very important to keep this code updated as well.
*In case your query contains things which prevent PAK like Variables with intervals or offsets
*PAK will not run and your derivation will be empty!!!
*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
* infrastructure needed by the buffer:
* Attention: The system also supports external buffering,
* check documentation of attribute N_USE_EXTERNAL_BUFFER
* and note 1067433
DATA: l_s_mesg TYPE if_rspls_cr_types=>tn_s_mesg,
l_is_valid TYPE rs_bool.
FIELD-SYMBOLS: <l_th_buf> TYPE HASHED TABLE,
<l_s_buf> TYPE any.
* end example code:
CLEAR e_t_mesg.
* begin of example code:
* use the buffer?
* o_use_buffer is switched on by default in the constructor
IF o_use_buffer = rs_c_true.
* yes:
ASSIGN o_r_th_buf_d->* TO <l_th_buf>.
ASSIGN o_r_s_buf->* TO <l_s_buf>.
<l_s_buf> = c_s_chas.
READ TABLE <l_th_buf> INTO <l_s_buf> FROM <l_s_buf>.
IF sy-subrc = 0.
IF o_r_is_valid->* = rs_c_true.
c_s_chas = <l_s_buf>.
RETURN.
ELSE.
IF e_t_mesg IS SUPPLIED.
APPEND o_r_s_mesg->* TO e_t_mesg.
ENDIF.
RAISE EXCEPTION TYPE cx_rspls_failed
EXPORTING
msgid = o_r_s_mesg->msgid
msgty = o_r_s_mesg->msgty
msgno = o_r_s_mesg->msgno
msgv1 = o_r_s_mesg->msgv1
msgv2 = o_r_s_mesg->msgv2
msgv3 = o_r_s_mesg->msgv3
msgv4 = o_r_s_mesg->msgv4.
ENDIF.
ENDIF.
ENDIF.
* implement your derivation algorithm here:
FIELD-SYMBOLS: <ls_chas> TYPE if_rspls_cr_types=>tn_s_chas.
*Source
FIELD-SYMBOLS <fs_SOURCE1> TYPE /BIC/OISOURCE1.
FIELD-SYMBOLS <fs_CO_AREA> type /BI0/OICO_AREA.
*Target
FIELD-SYMBOLS <fs_TARGET1> TYPE /BIC/OITARGET1.
*SOURCES
READ TABLE n_ts_chas ASSIGNING <ls_chas> WITH TABLE KEY iobjnm = 'SOURCE1'.
IF sy-subrc = 0.
IF <ls_chas>-role = 'S'.
ASSIGN COMPONENT <ls_chas>-fieldnm OF STRUCTURE <l_s_buf> TO <fs_SOURCE1>.
ENDIF.
ENDIF.
READ TABLE n_ts_chas ASSIGNING <ls_chas> WITH TABLE KEY iobjnm = '0CO_AREA'.
IF sy-subrc = 0.
IF <ls_chas>-role = 'S'.
ASSIGN COMPONENT <ls_chas>-fieldnm OF STRUCTURE <l_s_buf> TO <fs_CO_AREA>.
ENDIF.
ENDIF.
*TARGETS
READ TABLE n_ts_chas ASSIGNING <ls_chas> WITH TABLE KEY iobjnm = 'TARGET1'.
IF sy-subrc = 0.
IF <ls_chas>-role = 'T'.
ASSIGN COMPONENT <ls_chas>-fieldnm OF STRUCTURE <l_s_buf> TO <fs_TARGET1>.
ENDIF.
ENDIF.
*Derive TARGET1
if <fs_SOURCE1> is assigned and <fs_CO_AREA> is assigned.
if <fs_SOURCE1> <> '' and <fs_SOURCE1> <> '#'.
concatenate '0' <fs_CO_AREA> <fs_SOURCE1> into <fs_TARGET1>.
l_is_valid = rs_c_true.
else.
clear <fs_TARGET1>.
l_is_valid = rs_c_true.
endif.
endif.
* update the buffer with the result:
* l_s_mesg should contain a message in the 'invalid' case
* l_is_valid should indicate whether derivation was possible
* <l_s_buf> should contain the derived fields
IF o_use_buffer = rs_c_true.
o_r_is_valid->* = l_is_valid.
IF o_r_is_valid->* = rs_c_true.
INSERT <l_s_buf> INTO TABLE <l_th_buf>.
c_s_chas = <l_s_buf>.
ELSE.
IF e_t_mesg IS SUPPLIED.
o_r_s_mesg->* = l_s_mesg.
APPEND l_s_mesg TO e_t_mesg.
ENDIF.
INSERT <l_s_buf> INTO TABLE <l_th_buf>.
RAISE EXCEPTION TYPE cx_rspls_failed
EXPORTING
msgid = l_s_mesg-msgid
msgty = l_s_mesg-msgty
msgno = l_s_mesg-msgno
msgv1 = l_s_mesg-msgv1
msgv2 = l_s_mesg-msgv2
msgv3 = l_s_mesg-msgv3
msgv4 = l_s_mesg-msgv4.
ENDIF.
ENDIF.
* end of example code
endmethod.
Test 2
Within this code you can insert a BREAK-POINT to debug it. And also to test if the Query disaggregates with or without PAK. If you run your correct query without Offset it will not be stopped with the break-point and you should still have the target characteristic filled inside the aDSO. When you execute the other query with variable Offset, the break-point will stop and you can check the values derived.
Conclusion
It is good to try PAK and challenge it within your planning applications. It is still not a cure to every disease. It may even slow things down a bit often. But if your company decided to go HANA way, you as a developer should do your best to enable PAK and use it. Though it may be hard to forget your custom ABAP functions, data slices and characteristics derivations (even some cool things in Query Designer), I believe it is worth learning all this with SQL-Scripts. As always there will be someone who just makes things old fashioned and quickly in ABAP. For the sake of your data consistency you should learn to program ABAP Fallbacks, as there may come also unexpected complications to your simple queries which may force you to use the cool but prohibited things once again...
- SAP Managed Tags:
| User | Count |
|---|---|
| 3 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
