Improvements on SAP HANA-optimized planning with t...

frank_klingl · ‎2013 Dec 16

Time passed fast and it is already 2 years since planning application kit was released to improve respond times in planning applications together with SAP BW on HANA with 7.30 SP 5 (see also SDN article ‘Moving from BW-IP to SAP HANA-optimized planning with the new Planning Applications Kit (PAK)’) . We did not relax meanwhile but tried to further improve the planning application kit with respect to performance and removed restrictions as far as possible. Also we tried to bring PAK into new and more flexible planning areas.
Main areas of improvement of the last two years were:

Remove general restrictions in
PAK listed in note 1637199 like general planning functions, data slices,
logging BADI etc.

Enable customer exits based on SQL script for planning
function, characteristic relationships and data slices

Planning on DSOs

Support user flexibility

Let us have a glimpse to each the topics above

Remove general restrictions in PAK

When we released the planning application kit with BW on HANA we had a number of limitations. Some were simple due to time constraints by the delivery date of BW on HANA with 7.30 support pack 5, some are due to data integrity assumptions needed to be made when orchestrating the planning buffers in HANA and some are due to limitation having different runtime language environment. We used the time to further improve and get rid of the
limitations. The improvements are listed below.

Feature	HANA Revision	BW Release/ Support Pack	SAP Notes
PAK enabling with basic planning functions copy, repost, delete, revaluate, disaggregation with reference and basic FOX	SPS 04 = Rev 28	7.30 SP 5 7.31 SP 2
PAK enabling with disaggregation in the query	SPS 04 = Rev 28	7.30 SP 5 7.31 SP 2
PAK enabling with standard characteristic relationship of type DSO and attribute	SPS 04 = Rev 28	7.30 SP 7 7.31 SP 3
FOX String operations	Rev 38	7.30 SP 9 7.31 SP 7	1792782
FOX CURC statement	Rev 40	7.30 SP 9 7.31 SP 7	1778939 and 1792782
Standard Data Slices of type selection	Rev 40	7.30 SP 9 7.31 SP7	1803016
Logging BADI BADI_RSPLS_LOGGING_ON_SAVE	Rev 40	7.30 SP 9 7.31 SP 7	1802658
Planning function “Deletion of Invalid Combinations” 0RSPL_DELETE_CR	SPS 05 = Rev 45	7.30 SP 9 7.31 SP 7	1778939
Planning function “Report on Basis of Characteristic Relationships 0RSPL_REPOST_CR	Rev 53	7.30 SP 10 7.31 SP 9	1855154
Planning function “Distribution by Keys” 0RSPL_DISTR_KEY	SPS 06 = Rev 60	7.30 SP 10 7.31 SP 9	1821899

With the single exception of the old forecast planning function, we plan all other standard function types to be HANA-optimized. At
the moment we still work on the currency and unit conversion and hope to release them soon. All others are now available and can be used for PAK. For
FOX (planning function type 0RSPL_FORMULA) we removed the restrictions on string operation and currency conversion and introduced MDEXIST statement.

We in addition we enabled the standard characteristic relationship of type DSO and attribute as well as the standard data slice of type selection.

Also now we support HANA-optimized runtime custom implemented logging in the logging BADI BADI_RSPLS_LOGGING_ON_SAVE. A standard audit
dimension with also includes PAK is delivered with BW 7.4 SP 5 see below.

We in general have also a restrictions on info objects based on own implementations. Those own implementations are based usually on customer implemented own ABAP classes and are like exits (see below). However, for standard info objects like 0SOURCESYSTM, 0INFOPROV or info object referencing
the standard time info objects like 0CALMONTH are now supported. In addition from 7.4 SP 5 onwards we also support master data based on native HANA views
(attribute or calculation views, see below). Those can be seen as HANA-optimized counterparts to master data with own implementation referencing custom ABAP
classes.

With all these improvements it was now possible, as always indicated, to change the default behavior of single InfoProvider activation in
into table view RSPLS_HDB_ACT_IP with SAP Note 1930335. With this note it is not necessary any more to enter all HANA-optimized InfoCubes or planning DSOs into
table view RSPLS_HDB_ACT_IP. Default behavior is now active. Only if you wish to run some scenario on classic IP you have to make an entry without
activation. Of course entries with activation marked do not hinder to run scenarios HANA-optimized.

We globally released now SAP Note 1637148 - BW on HANA: Activation of Planning Application Kit - with SAP NetWeaver BW 7.30 or 7.31 SP 11 or with
SAP NetWeaver BW 7.4 SP 5. Prerequisite is to import either Support Package 10 for SAP NetWeaver BW 7.30 or at least Support Package 9 for SAP NetWeaver BW
7.31 into your BW system. Since we want to avoid that customer have to implement a lot of notes we put in this high prerequisite. This usually avoids
a lot of follow up issues. However it is still possible to run PAK with lower SP doing the one line correction in SAP Note 1637148 as local modification.

All restrictions which hinder the whole scenario to run HANA-optimized can be analyzed with report RSPLS_PLANNING_ON_HDB_ANALYSIS. It shows starting
from InfoCube, planning function or sequence if the scenario can run HANA-optimized. For this and better tracing we recommend implementing SAP Notes 1824516,
1854948, 1895235, 1931179 and 1927309. With SAP Note 1927308 one even gets meaningful messages on a classical database before migrating to HANA in order
determine if any remodeling is necessary. If you run the report RSPLS_PLANNING_ON_HDB_ANALYSIS on a classical data base you will first however get red traffic light on the info provider indicating that some perquisites like general PAK activation or SAP HANA database are still missing. However clicking on the red traffic light
gives you then some detailed messages if remodeling is necessary or not. For runtime verification you can set the user parameter RS_DEBUGLEVEL to minimum value of 2 to also get warning if the function is not executed HANA-optimized.

See also article for other runtime verifications.

Enable customer exits based on SQL script

By closing the possible standard restriction topics we did not solve the issue that we see on most of the customer systems different sorts of
ABAP exits in planning like own planning function types or characteristic relationship and data slices based on exit. Only for master data with own
implementation we offer HANA views with BW 7.4 SP 5 (attribute or calculation views, see video).
To overcome this bottleneck, we offer now a way to extend existing ABAP exits with new methods which enable them to run also HANA-optimized.A similar approach is done in BW SP05 with the HANA analysis processes and the HANA based warehouse management transformations. In addition the NetWeaver Application Server ABAP also offers now ABAP managed database procedures (AMDP), which can be leveraged to code and deploy SQL script procedures from ABAP in eclipse or transaction SE80 (here only source based).

The SQL Script is executed by calculation scenario build through PAK in planning scenarios.

Prerequisites are listed below

Feature	HANA Revision	BW Release/ Support Pack	SAP Notes
Planning with PAK on Hana View	Rev 64	7.4 SP 5 only
SQLScript Exit for Planning Functions	Rev 67	7.30 SP 10 7.31 SP 9	1861395, 1870342, 1870369
SQLScript Exit for Characteristic Relationships and Data Slices	Rev 67	7.30 SP 10 7.31 SP 10	1861395, 1870342, 1877182, 1874412

Planning function Types based on SQLScript

For planning functions we extend the methods by introducing additional interface IF_RSPLFA_SRVTYPE_TREX_EXEC(_R) which have to be added to the existing
IF_RSPLFA_SRVTYPE_IMP_EXEC(_REF). Those have to be implemented by classes implementing new planning function types. The additional methods are
INIT_AND_CHECK and TREX_EXECUTE. For details how to implement those using SQL script and our helper class CL_RSPLS_SQL_SCRIPT
please refer to the standard documentation which is also attached in SAP Note 1928715. In the linked video we show how to create
SQLScript implementation in own planning functions types.

To summarize advantages and disadvantages of using SQL Script or FOX we wrote SAP Note 1928715 and repeat it here:

FOX enables you to have the same coding run in classical IP as well as HANA-optimized in PAK.

FOX debugging is possible in classic IP, and since the results have to be the same as stated above, it can be used for the HANA-optimized case as well. For SQL Script debugging the SAP HANA Studio is necessary and can only be achieved by copying the data to be processed (the plan buffer data) beforehand into a table. See also SCN article 'How to test SQL Script Planning Functions'.

FOX allows you to encode your application based on BW metadata, while in SQL Script, it might be necessary in certain cases to make use of the
metadata as available in the database layer.

FOX can make use of the BW infrastructure like e.g. currencyconversion, master data access etc.

Due to the planning focused language feature of FOX, we can betterguarantee performance of the customer exits. In addition with the ST-PI
software component of the solution manager, an additional toolset BIIPTOOLS is offered in transaction ST13 to analyze FOX coding and see pitfalls
regarding performance and others.

SQL Script offers an open language to allow for arbitrary database operations.

SQL Script based exits are used in other areas like characteristic relationships inside planning and as of 7.4 SP 5 also generally in BW on
HANA with HANA Analysis Processes and Warehouse Management Transformations.

FOX provides also the new debugging script RSPLFC_DEBUGGING_SCRIPT_FOX

Characteristic Relationships based onExit

For characteristic relationship we introduced the additional interface IF_RSPLS_CR_EXIT_HDB with the two methods GET_SQLSCRIPT_INFO
and GET_SQLSCRIPT_PARAMETERS. The interface is added to the existing characteristic relationship exit class. The Method GET_SQLSCRIPT_INFO has up to
four return parameters. E_PROCEDURE_NAME_CHECK defines the SQL Script name for the check method of the characteristic relationship while the parameter E_PROCEDURE_NAME_DERIVE is the corresponding one for the derive method. The parameter E_DB_SCHEMA_NAME is only needed when calling to a different schema. The create method is at the moment still handle on the application server since it is not so time critical.
So the parameter E_PROCEDURE_NAME_CREATE is at the moment not used but will become active and then can be used once we also delegate the create method to
HANA. With the Method GET_SQLSCRIPT_PARAMETERS one can in addition pass additional parameters to the SQL script procedure. We also offer a way to reuse
the ABAP exits for characteristic relationships as first step. Then one just leaves the name of procedure names in parameters E_PROCEDURE_NAME_CHECK and
E_PROCEDURE_NAME_CHECK empty. In this case the runtime to retrieve the relevantcharacteristic relationships is done in ABAP and the result pushed down to
HANA. Depending on the cardinality of how many data buffer entries need how many characteristic relationships this can already have almost as good results
for low cardinality cases as for full processing in HANA (see SAP Note 1956085- BW-IP (PAK): ABAP as fallback for HANA CR and DS). In the linked video we
show how this can be implemented

Data Slices based on Exit

For data slices we find we introduced theadditional interface IF_RSPLS_DS_EXIT_HDB with same two new methods GET_SQLSCRIPT_INFO and GET_SQLSCRIPT_PARAMETERS as for characteristic relationships. In the first you just pass with E_PROCEDURE_NAME_PROTECTED the SQL script name and optional
also a different schema can be provided in E_DB_SCHEMA_NAME. As for characteristic relationships additional parameters can be passed with GET_SQLSCRIPT_PARAMETERS. As for characteristic relationship one can also as first step utilize the existing ABAP exits by implementing the parameter E_PROCEDURE_NAME_PROTECTED as empty (See SAP Note 1956085 - BW-IP (PAK): ABAP as fallback for HANA CR and DS).

Planning on DSOs

As you might know, we enabled general planning on directupdate DSO for classic IP with 7.3 SP 8/7.31 SP 5. The HANA-optimized version was released half a year later in the next support packages (see table below or SAP Note 1799168). Here we also allow more than 16 keys for the direct update DSO on HANA. Main purpose of DSO planning was to plan on after images instead of deltas in the InfoCube (SAP Note 1735590).
With this we could offer feature like ‘no aggregation’ (NO2) for standard aggregation for prices and dates key figures (later with data type DEC). The price is only displayed on intermediate results if it is homogenous on lower level. This is different to average price possible in InfoCubes before by using calculated key figure and inverse formula in quota models. Also thedisaggregation then follows a simple copy pattern of the higher value to the lower level (see SAP Note 1785156). Note that due to the after image buffers we need all keys in the aggregation which cannot be derived by characteristic relationships.

Feature	HANA Revision	BW Release/ Support Pack	SAP Notes
PAK for DSO planning	SPS 05 = Rev 45	7.30 SP 9 7.31 SP 7	1799168
Logging BADI for DSO	Rev 40	7.30 SP 10 7.31 SP 9
Physical Delete	Rev 64	7.30 SP 11 7.31 SP 11

We also added missing features like supporting logging BADI in DSO. This might be very helpful also upstream processes since we do not support delta loading from this planning DSO in dataflows (SAP Note 1735590). One issue came up when using NO2 aggregation in combination with zero suppression. Since zero suppression is only handled in the front end layer it can happen that the intermediate results show inaccurate aggregation result and distribution copy also affects the suppressed rows. However with the after image buffer we were in the position to introduce physical deletion of records. With 7.30/7.31 SP11 we can support also the in addition to zeroing out records like for InfoCubes new planning functions physical deleting records on the database:

0RSPL_DELETE_DSO                      Delete DSO data physically

0RSPL_REPOST_DSO                    Repost Data and Delete DSO Data physically

0RSPL_DELETE_CR_DSO              Physical Deletion of Invalid Combinations in DSOs

0RSPL_REPOST_CR_DSO              Repost DSO Data on Basis of Characteristic Relationships
See the linked video to get some impressions.

Support user flexibility

Finally let me outline the new features only coming with 7.4 SP 5 and the further direction of the planning application kit. The main features are driven by the new BPC 10.1 unified model which is leveraging PAK. With the unified model new functionality like business process flows and work status introduced on top of PAK and can be used. One of the functionality we gained in addition for all classic IP or PAK scenarios including other user interfaces are audit dimensions in InfoCubes which can be used to display the audit trail for planning changes.

The main future direction will be however to have more flexible scenario supported as in classic BPC scenario. The first step is to have local providers input ready for planning which are only visible within a BPC environment. With BW 7.4 SP 6 we enable those local providers for planning. So it is possible to create in the line of business data marts and use them for planning functions. The linked video gives you some impression.

Feature	HANA Revision	BW Release/ Support Pack
Planning local provider in BPC embedded	Rev 64	7.4 SP 5
Audit dimensions	Rev 63	7.4 SP 5
Combined BPC on PAK planning	SPS 07 = Rev 70	7.4 SP 5