Further Improvements on SAP HANA-optimized planning with the
Planning Applications Kit in 2014
Time is still passing fast since the planning application kit was released with SAP BW on HANA with 7.30 SP 5 (see also SCN article ‘Moving from BW-IP to SAP HANA-optimized planning with the new Planning Applications Kit (PAK)’). Meanwhile also BPC 10.1 with the embedded in addition to the standard model was launched (see also SCN article Unifying BPC-NW and BW-IP) leveraging PAK as the core planning engine. This triggered further improvements to the planning applications kit with respect to flexibility. In addition we improved performance and removed restrictions as far as possible. Main areas of improvement with SAP Business Warehouse 7.4 SP8 are:
- Remove general restrictions in PAK listed in note 1637199 like general planning or MIN/MAX key figures
- FOX enhancements
- Attribute Planning and Short Text Solution
- Support user flexibility
- Improve usability and support in IP and PAK
Let us have a glimpse to each the topics above
(1) Remove general restrictions in PAK
When we released the planning applications kit with BW on HANA we had a number of limitations. A lot of them we removed already in earlier support packages. The main remaining limitations are either now lifted with SAP Business Warehouse 7.4 SP8 or will stay due to main differences in the execution layer. The last lifted limitations are listed in the table below
Removed Restriction | HANA Revision | BW Support Pack |
SQL Exit CR and DS with ABAP fallback (Note 1956085) | Rev 67 | 7.30 SP 12 |
CR based Hierarchies (Note 1984344) | Rev 67 | 7.30 SP 12 |
MIN/MAX restriction | Rev 74 | 7.4 SP8 |
Currency Conversion (or Translation) | Rev 75 | 7.4 SP8 |
Unit Conversion | Rev 75 | 7.4 SP8 |
In detail we find
SQL Exit CR and DS with ABAP fallback
Implementing SQL Script seems to be very often still an issue in projects due to lack of knowledge of the new scripting language SQL Script. Also very often the SQL procedures are written in a procedural -using cursors - which shows no performance improvement with respect to ABAP. To overcome this obstacle, we offered a way to still leverage the old ABAP exits for characteristic relationship and data slices in case of low cardinality. For example you are a sales planner in Switzerland and you plan on 100.000 records in you sales organization CH1010 (being in your filter) which on the other hand derives for each record the country CH for Switzerland. Then you only have 1 active characteristic relationship for 100.000 records in the planning buffer. This is a low cardinality case where note 1956085 helps. Other examples are describes also in the note 1956085 itself. The advantage is that we can read only the fields relevant for the characteristic relationship or data slice in an aggregated way from HANA. So in our example only the column for sales organization with one single value CH10 is retrieved out of the data in HANA. This is very fast. For this one record also the check in ABAP is fast. The result sales organization CH10 and country CH is then pushed back to HANA and used to derive the correct country for all 100.000 records within HANA.
Then overall very fast and has the same performance as implementing the whole with SQL Script.
However the opposite would be true for a high cardinality like deriving for each product the correct product category or brand. Assuming for the 100.000 records have around 1000 different products we would then already read 1000 records from HANA to a derive in ABAP and push back 1000 records to HANA which might be still ok but overall takes more time.
Internal test show that number of read characteristic relationships can be even around 10.000 records, however the best trade-off has
to be verified on project base. So we recommend to first start with the ABAP workaround by implementing note 1956085 and returning empty values for the parameters
E_PROCEDURE_NAME_CHECK and E_PROCEDURE_NAME_DERIVE in the method IF_RSPLS_CR_EXIT_HDB~GET_SQLSCRIPT_INFO.
Then one should check the performance and see if a lot of time is spend during read, ABAP check and push back to HANA. If this is the case, one should still
go this way and implement a SQL Script procedure. To be on the save side one can of course always implement a SQL Script procedure at once.
Characteristic Relationships based on Hierarchies
We removed the restriction on characteristic relationships based on hierarchies (RSPLS_MTYPE = H). This was longtime an
issue since BW hierarchies are not known in HANA. But we could leverage the same trick as above for the ABAP fallback to facilitate a fast and effective
way to use BW hierarchies.
MIN/MAX restriction
With SAP Business Warehouse 7.4 SP8 or note 2011651 we lifted the restriction to use not input-able key figures of standard aggregation MIN or MAX in PAK. Now it is possible as in classic BW-IP to have key figures with standard aggregation MIN and MAX in theaggregation level. However, as in classic IP, they are not ready for input, so they can only be used for reporting purpose and one cannot enter any planning values.
Currency and UNIT Conversion
With Revision 74.01 onwards itis now possible to leverage the currency and unit conversion functionality down in HANA. Therefore we enabled the standard planning function types 0RSPL_CURR_CONV and 0RSPL_UNIT_CONV also for PAK. So in those cases we do not any longer need the workaround to read the plan buffer data in the ABAP application server execute the planning function then on the ABAP layer and push back the result to HANA. This should improve the performance where such planning function types are used. Before SP8 in case of currency conversion also a modeling workaround was possible by using FOX and the CURC statement. This workaround is not needed any longer after SAP Business Warehouse 7.4 SP8.
Remaining Restrictions
One remaining restrictions is the forecast planning function type 0RSPL_FORECASTING. Here we envision using SQL Script and embedding there the predictive analytic library (PAL). Of course this can be done in any customer project already now.
For details refer SAP help to and watch the nice video.
The other restriction are due to transient or
virtual data etc. where we in general lack information on HANA side.
(2) FOX enhancements
With support package 8 the FOX functionality is improved first for IP and once the HANA engine has also adopted those enhancements
the extension will be available in PAK. The main intention is to make customer exits using SQL Script unnecessary. In particular the translation from a
sequential implementation of an ABAP exit to SQL script results in most projects in a performance boost only if the SQL script is not programmed also
in a sequential way but using direct SQL algebra expressions instead.
The new features are listed in the table below
Feature | HANA Revision | BW Release/ Support Pack |
Internal table | Rev 74 | 7.4 SP 8 |
Read external reference data | Rev 74 | 7.4 SP 8 |
Syntax High Lightening | 7.4 SP8 |
The new features are also documented in the F1 help (info button) on the parameter section in the FOX planning function
maintenance screen in transaction RSPLAN. The Syntax highlithing requires an GUI 7.4 update.
In detail we now desribe those features
Internal Tables
In BW-integrated planning you can from now on define and use internal tables:
TABLE INTTAB { YEAR TYPE 0CALYEAR KEY, NUMBER
TYPE F, COSTCENTER TYPE 0COSTCENTER }.
Internal tables consist of fields. All data types which are permitted for variables can be used for these fields. A type followed by KEY is a key
field. Tables are sorted by key fields. There has to be at least one key field and one non-key field. There is only one entry per key possible in the table.
Tables can be filled by assigning a value:
- INTTAB.{NUMBER,2011} = 25.
- INTTAB.{ COSTCENTER, 2013} = 00004711.
Values can be accessed as follows:
{ 0VCPL_INT, 2013 } = INTTAB.{ NUMBER, 2013 }.
The following special functions are available for internaltables:
- Determine
number of rows: CNT = LINES( INTTAB ) - Specify if
value exists: CNT = EXISTS( INTTAB.{ 2013 } ). The variable CNT is type I;
it is set to the value 1 if the entry exists, or set to 0 if the entry
does not exist - Delete specific
value: DELETE( INTTAB.{ 2014 } ) - Delete all
values: CLEAR INTTAB - FOREACH loop
for values: FOREACH YEAR IN INTTAB.
The FOREACH var IN INTAB works then
similar to the existing foreach IN REFDATA/SELECTION or VARIABLE. Please
follow the Loop Construct documentation in the F1 help.
Read external reference data
In BW-integrated planning, you can now read data from any aggregation level. This data is declared using the InfoProvider statement.
Example:
INFOPROVIDER DSO_REF.
The following expression is used to access the InfoProvider:
{ 0VCPL_INT, 2013 } = DSO_REF.{ 0VCPL_QUAV, YEAR}.
The name of the InfoProvider and a period (“.”) are inserted in front of the curly brackets when the InfoProvider is accessed.
In this example, 0CALYEAR is the characteristic to be changed. All characteristics to be changed, and all characteristics that are not in the
aggregation level, must be specified in curly brackets for the InfoProvider access. The usual notation can be used to access block characteristics.
{ 0VCPL_INT, 2013 } = DSO_REF.{ 0VCPL_QUAV, YEAR| 0PLANT = PLANT }.
You cannot set values. The system can iterate using the values that are assigned to a block however.
FOREACH YEAR IN DSO_REF.
{ 0VCPL_INT, 2013 } = { 0VCPL_INT, 2013 } +
DSO_REF.{ 0VCPL_QUAV, YEAR }.
- ENDFOR.
The system calculates which data it reads from the formula (like with reference data). For example, value PLANT01 is added to the selection for
characteristic 0PLANT if the formula contains the following:
{ 0VCPL_INT, 2013 } =DSO_REF.{ 0VCPL_QUAV, YEAR | 0PLANT = PLANT01 }.
(3) Attribute Planning and Short Text Solution
With 7.4 SP8 we introduced a new way to store character like key figures or in other words store plain ASCII text (short text case without master data) or characteristic with certain allowed values (attribute planning with master data value) in DSO and use them in reporting and planning as figures. This runs as all usual keyfigure in the OLAP with NO2 aggregation. The consequence is we show texts on higher level if the are equal and * if they are not equal. The whole text processing is as fast as for normal number based key figures.
To archieve this we require a character being inserted in the data field section of planning enabled DSO for direct update.
Once you have done this you will see that you can mark in the new Section ‘Characteristic as Key Figure’ this characteristic as a key figure.
If you ask why this was done only on DSO and not on cube the answer is very simple. We need after images to store texts. Now you
can use them directly in an aggregation level you create on top of the DSO. Here you drag them into the key figure section of the aggregation level. Before
you could only drag characteristic from the key field section of the DSO into Chars. Section.
However normally you have most of your data in InfoCubes. So you would build a multiprovider and use the DSO for characterlike key
figures. Depending on how many characteristics you have in your DSO compared to the InfoCube you can store the comment on different levels of aggregation.
Once you have modeled your aggregation level you can start using the text key figures in the query. In the query designer they are treated as usually key figures. You can either have multiple comment fields in your DSO for each relevant key figure or model an account model for the comments by having the key figure as an ‘account’ dimension. Then you can restrict the comment for each key figure in the query designer.
Once you have done this you can use them in the query as shown in the first picture.
Below is a list of features and how they are implemented in this solution compared to other known document based solutions. As stated above you can store
short text or comments in plain ASCII text with restriction to maximum length of 250 characters. This is different from the old document based solution in
BexWeb where you can assign any document. However we now will support all new clients including the EPM Addon. Only the old Bex Analyzer can only display
them. At the moment Analysis Office will support it with 1.4 SP9 onwards, the Design Studio will also adapt it with a patch based on 1.4 and the EPM Client 10.0 with SP19 . The cell based comment cannot be attached to query level or any hierarchy level. Due to the tight integration into OLAP it is very fast solution where
NO2 aggregation is used. This means one can see the comments on higher level if they are the same on lower level and also make comments input ready and
distribute them with copy to lower level.
Feature | Simplified Short Text | Full comment solution |
Comment or Documents | ASCII short text | Word, pdf, ASCII, |
Attachment Anchor | Cell | Cell, Query, Characteristic Combination |
List geometry | Cell only | Intermediate results Hierarchy Nodes Restricted key figures Drill Down |
Length of Comments | <250 | Full length |
Aggregated View | Shows comment if they are all equal | |
Visibility and Maintenance | Separate Column | Excel Comment, separate column, pop up … |
Versioning | Possible via data model | inside Comment With different colors More documents |
Performance | Very good | Dependends strongly on complexity and list geometry |
Supported UI | AO, EPM, Bex Web, planned Design Studio , |
At
(4) Support user flexibility
In SAP Business Warehouse 7.4 SP 6 we started to support more flexible use cases in the planning application kit. The main
features are driven by the new BPC 10.1 embedded model which is leveraging PAK.
With the embedded model new functionality like business process flows and work status introduced on top of PAK. See blog Unifying BPC-NW and BW-IP for details. One of the functionality we gained in addition for all classic IP or PAK scenarios inc...
The main future direction will be however to have more flexible scenario supported as in the standard BPC scenario. The first step was done with BW 7.4 SP 5 to enable local providers in BPC and BW workspaces for planning.
Planning on Composite Providers
With SP8 we go one step further and start to support the CompositeProvider in addition to the MultiProvider in planning
scenarios. In the first delivery with SP8 we focus to handle the known MultiProvider use cases also in the CompositeProvider. You can in addition also
use the CompositeProvider modeled in workspaces if you only leverage normal BW info object in the local provider.
The following scenarios with CompositeProvider and planning are currently not yet supported:
CompositeProviders with joins where an input-able part provider is involved
- CompositeProviders with key figure mapping (could result in different contexts, when involving local providers for planning). This is targeted to be lifted as next step.
- CompositeProviders involving local providers with non BW info objects (thus resulting in characteristic mapping with different basis characteristic names
- CompositeProviders with a navigational attribute which is not mapped for all inputable partial providers.
Feature | HANA Revision | BW Release/ Support Pack |
Planning on composite provider | Rev 74 | 7.4 SP 8 |
Enable master data maintenance in planning scenrios | Rev 74 | 7.4 SP 8 |
Enable master data maintenance in lanning scenarios
With SAP BW 7.4 SP5 it is already possible to se the new master data maintenance UI based on WebDynp...
http://<server>:<port>/sap/bc/webdynpro/sap/rsdmdm_md_maintenance_app?IOBJNM=<info object name>&sap-language=EN.
This makes it it easier to support the entry of new master data in planning processes.
In addition we want to offer the same feature but better integrated into the planning landing page of BPC unified which is the BPC Admin Start Page. As for classic BPC one can now maintain master data directly from the BPC Admin UI.
If the dimension which are used in the models can be mainained in BW without any own implementation and the user having the
authority on the dimension with S_RS_IOMAD, the master data can be maintained directly on the BPC Admin UI
This feature was not fully finished and therefore is not rolled out with SP8. We plan to do this with notes and UI patches afterwards.
(5) Improve usability and support
With note 1694205, we improved the ability for customer to better track and localice errors in PAK.
In addition we developed several debuging scripts to analyze errors on data and FOX script more easily. This can also be very well used in customer project on
planning. The scripts we deliver are
Feature | Technical Name | BW Release/ Support Pack |
FOX Debugging Script | RSPLFC_DEBUGGING_SCRIPT_FOX | 7.4 SP 8 |
Analysis Report improvement for PAK | Rev 74 | 7.4 SP 8 |
Buffer Debugging Scripts | IP: RSPLF_DEBUGGING_SCRIPT_C_DATA PAK: RSR_DEBUGGING_SCRIPT_STORE | 7.4 SP 8 |
Script to analyze the bit for characters | RSPLS_DEBUGGING_SCRIPT_CHABIT | 7.4 SP 8 |
Using Debugging Script in general with the new ABAP debugger
When the debugger is started go to the tab‘Script’.
By pressing the large ‘Load Script’ button, a sub-screen comes up. Here you choose option
database and the Script Name you want to use.
Press ‘Enter’ and the scipt name will be visible under Name in the section Script in the field Name.
Then press the ‘Start Script’ button and the script is started.
Starting the RSR_DEBUGGING_SCRIPT_STORE
Run report RSR_DEBUGGING_SCRIPT_STORE (perquisite are notes 1822059 and 1861037). It analyzes the store object which
controls the buffer tables. When you start the script RSR_DEBUGGING_SCRIPT_STORE you have to choose the right store. The store is for
example a reference to a planning buffer in HANA.
Then one has to choose the right view on the data. Often it is the latest view on top.
You can still use the standard debug flags to set the right breakpoints for when reading or writing buffers.
You can also use the list below to find the right spots for store etc.
Starting the RSPLF_DEBUGGING_SCRIPT_C_DATA
Also for classic IP we have a debugging script for the c_DATA tables with name RSPLF_DEBUGGING_SCRIPT_C_DATA (prerequisite is note 1923579). It has the advantage that instead of C_DATA you also now see the mapping of the technical column name (containing sid) with the name of the infoobject. So no lookup to table /BI0/SIOBJNM is not need any longer.
When starting script choose the table you want to look at.
FOX Debugging Script RSPLFC_DEBUGGING_SCRIPT_FOX
Start the debugging script RSPLFC_DEBUGGING_SCRIPT_FOX with 7.4 SP6 and 7.3/7.31 SP11
This FOX debugging script can be watch variable values, active data in the buffer and reference data.
On can step through loops and used to check how the data changes. It is described in detail in the blog Debugging-a-Planning-Function-of-Type-Formula-in-BW-IP
Load and Start Script RSPLFC_DEBUGGING_SCRIPT_FOX in the new debugger. By either seetting a break-point in a script or in the class
CL_RSPLFC_FORMULA->EXECUTE
Then set break-point, and press ‘Start Debugging’
RSPLS_DEBUGGING_SCRIPT_CHABIT
Script to analyze the bit for characters (prerequisite 1923579)
- SAP Managed Tags:
12 Comments
