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. 

/wp-content/uploads/2014/09/blog1_535962.jpg

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.

/wp-content/uploads/2014/09/blog2_535963.jpg

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:

  1. INTTAB.{NUMBER,2011} = 25.
  2. 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 }.

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

/wp-content/uploads/2014/09/blog3_535989.jpg

 

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.

 

/wp-content/uploads/2014/09/blog4_535992.jpg

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.

/wp-content/uploads/2014/09/blog5_535993.jpg

 

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.

/wp-content/uploads/2014/09/blog6_536000.jpg

 

 

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.

 

/wp-content/uploads/2014/09/blog7_536001.jpg

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 including other user interfaces is the audit dimension 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 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.

 

 

 

 

/wp-content/uploads/2014/09/blog8_536002.jpg

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 WebDynpro. It is accessible as
before from RSD1 or direct via URL
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.

 

 

/wp-content/uploads/2014/09/blog9_536039.jpg

 

 

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

/wp-content/uploads/2014/09/blog_10_536040.jpg

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.

/wp-content/uploads/2014/09/blog_11_536041.jpg

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.

/wp-content/uploads/2014/09/blog_12_536045.jpg

Then one has to choose the right view on the data. Often it is the latest view on top.
/wp-content/uploads/2014/09/blog_13_536046.jpg

You can still use the standard debug flags to set the right breakpoints for when reading or writing buffers.
/wp-content/uploads/2014/09/blog_14_536047.jpg

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.
/wp-content/uploads/2014/09/blog_15_536051.jpg

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’

/wp-content/uploads/2014/09/blog_16_536052.jpg

 

/wp-content/uploads/2014/09/blog_17_536053.jpg

RSPLS_DEBUGGING_SCRIPT_CHABIT

Script to analyze the bit for characters (prerequisite 1923579)

 

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Jomy Joy

    Hi Frank,

    Thanks for this post. This is really interesting and real value-add.

    I’m on BW 7.4 SP6. I’ve created a planning enabled DSO but couldn’t find the section “characteristic as key figures”

    Can you please let me know what I might be missing to make use of this awesome feature ?

    Is it for SAP BW7.4 SP6 or only from SP8 onwards. Is there some note which can be applied incase we are on SP6 or SP8 instead of full upgrade to use this feature ?

    Thanks & regards,

    Jomy

    (0) 
    1. Frank Klingl Post author

      Hi Jomy,

      as mentioned in the Blog. This feature is available with BW 7.4 SP8. The RTC will by end of this week  on the 15.09.2014. Then when you move a characteristic to the data field and not to the key field section as now this new segement will come up.

      Regards

      Frank

      (0) 
  2. James Xie

    Hi Frank,

    Thanks for the summary. What we would be interested to achieve is planning on top of a composite provider which is a union of a real-time infocube (plan data) and an Open ODS View(actual data) accessing a HANA Live Reuse View using SDA (Virtual). Please advise if this scenario is supported in SP8. I tried this but the planning function always complained that my Open ODS View was not a real-time infocube.

    (0) 
    1. Frank Klingl Post author

      Hi Tianjian,

      this is certainly a edge scenario. Scenarios which are not equivalent to old multi provider scenarios were not target on the SP8 delivery. However we want to improve into such scenarios. Currently you have to check on the technical restricitons


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

      We got a union case with native HANA access (Analytical View and ODS view on ODP) and a real time cube working. Here you need in additon note 2072212. If a part provider is a ODS view you at least have to map all characterisitc to BW info objects. (Otherwise you violate  CompositeProviders involving local providers with non BW info objects).

      So simple scneario are already working, since we want ot evolve into this direction, I suggest to implement note 2072212 and build your own scnenarios.  If you have issue, open a medium ticket on BW-PLA-IP with the scenario you want to have and we have a look. Just let us the number know.

      Regards

      Frank

      (0) 
  3. Tor Haabrekke

    Hi Frank, I am SAP Retail Presales and also covers SAP Planning for Retail. Many of the retail clients run the latest BPC and would like to feed financial targets from BPC to the SAP Planning for Retail. Is it possible for the planner in the SAP Planning for Retail to just use a planning function “Get targets from BPC”.

    Can also a planner in BPC pull targets from SP4R into BPC?

    Kind regards

    Tor

    (0) 
    1. Frank Klingl Post author

      Dear Tor,

      sorry I just saw your quesiton very late. This depends on the Model you use in BPC. If you use the embedded model this should be possible. If you use the standard model it is harder. you have also there a cube generated in the BPC namespace in BW but you have to solve the mapping from the account model to a BW model. I am aware of customers doing it with own planning funciton types in the write back BADI of BPC standard models. But a normal planning function copy is most of the time semantical not enough.

      Regards

      Frank

      (0) 
  4. Alexander Sobolev

    Hello, Frank

    Do you have examples of using internal tables functionality during implementations?

    At current moment there is no information about working with tables with two or several keys: how to fill such type of table.

    (0) 
    1. Frank Klingl Post author

      This might be an example:

      TABLE INTATB{

             YEAR TYPE 0CALYEAR KEY,

             CON TYPE 0IP_CON KEY,

             KF TYPE I }.

      DATA CNT TYPE I.

      DATA YEAR TYPE 0CALYEAR.

      *Internal Table contains two records.

      INTATB.{ KF, 2005 , ‘ASIA’}   = 1000.

      INTATB.{ KF, 2005 , ‘EUROPE’} = 2000.

      * Access the record from internal table which exist. Value = 30000 + 1000 => 31000  

      {0IP_PRICE , 2005, ‘ASIA’ } = 30000 + INTATB.{ KF, 2005 , ‘ASIA’}.

      *Access the record from internal table which does not exist.  Value = 30000 + 0 => 30000 

      {0IP_PRICE , 2006, ‘EUROPE’ } = 30000 + INTATB.{ KF, 2006 , ‘EUROPE’}.      

      (0) 
  5. Harald Büsching

    Hello Frank,

    thank you for good explanations. I have implemented some planning on comments and it was the solution, what was needed by business. Anyhow for reporting I have two problems:

    1. MDX in combinations with those key-figures does not yet work. See message 907590 / 2015.

    2. Reporting in Bex Web via query designer does not yet work on the multiprovider but it does work on the aggregation level, which is built on that multiprovider. See message 1020344 / 2015.

    Do you have any idea, if and when this will be fixed? We are currently of SP11 7.40.

    Regards

    Harald.

    (0) 
    1. Frank Klingl Post author

      Hi Harald,

      thanks for the feedback .

      What comes with BW7.5 SP4 will be to store comments hierarchy level on hierarchy with postable nodes. Then use FAGGR ‘no aggregation along hierarchy and  1:1  inverse formula. We will comment this in an upcoming blog.

      Regards

      Frank

      (0) 

Leave a Reply