Planning Engine Improvements for BPC Embedded Mode...

frank_klingl · ‎07-21-2016

The last main set of new features of the planning engine was shipped with the release of SAP Business Warehouse 7.5. I just want to recall the FOX enhancements on reference Data Selection and Modularization (see SDN blog on ‘Planning Engine Improvements for BPC Embedded Model, PAK and BW-IP with BW 7.50).

Basic new features like planning on aDSO in connection with B/4HANA in the cube like scenario were done.

Of course we also support our journey to make BPC embedded more flexible for planning use cases and combine BPC standard features with the central IT governed BW models.

Main improvements are

File Upload and PAK Improvements

Hierarchy Handling of Short Text/Attribute Planning in case of Hierarchies with postable nodes.

Delta Rendering

Characteristic Relationships on DSO with projections

aDSO Planning for direct update templates

Aggregation Level Maintenance in the BW Modelling Tool

Local Queries in BPC embedded

Dynamic Restrictions for End User

Let us have a glimpse to each of the topics in detail.

File Upload and PAK Improvements

PAK improvement

We now have reached a good functional rounded feature set for PAK and BPC embedded as explained also in older blogs on PAK article ‘Moving from BW-IP to SAP HANA-optimized planning with the new Planning Applications Kit (PAK)’ or on ‘Further Improvements on SAP HANA-optimized planning with the Planning Applications Kit in 2014’) and BPC blogs Unifying BPC-NW and BW-IP).

You might remember that with BW 7.4 SP8 (see Further Improvements on SAP HANA-Optimized Planning with the Planning Applications Kit in 2014) we introduced in FOX the ability to access data from a second aggregation level. At this time FOX scripts containing this feature did only run in the ABAP application layer and for PAK we had to pull the data from the planning buffer to the application layer, process the FOX in the ABAP layer, and push back the results to HANA. So it was running with PAK but not HANA optimized. With HANA revision 102.4 we now also support such kind of FOX with optimized execution in HANA. At the beginning we still have some exceptions here: when using a (programming-) variable of type key figure name (TYPE KEYFIGURE_NAME) and using this together with the INFOPROVIDER statement (like in FOREACH loops where InfoProvider and the variable is used) we still use the ABAP runtime.

All other should cases now run smoothly HANA optimized. A typical example is

DATA PROD TYPE 0IP_PROD.

DATA SEGMNT TYPE 0IP_SEGMNT.

INFOPROVIDER ANY_ALVL.

SEGMNT = VARV( SEG ).

FOREACH PROD.

{ PRICE, PROD } = ANY_ALVL.{ PRICE, SEGMNT, PROD }.

ENDFOR.

In addition with BW 7.5 SP1 we do now support the HANA optimized runtime with PAK for cube like aDSO planning as well as for local providers. Also we did some improvments on HANA enablement of create combinations in planning function.

File Upload

The goal of the new file upload planning function type is to replace the HowTo paper for file upload named “How to Load a File into BI-Integrated Planning (NW7.0)” and accesible via url. The new planning function type is named “0RSPL_FILE_UPLOAD_AO”. In addition you can implement the BADI RSPLFA_FILE_UPLOAD to get your own mapping.

Above you see the parameter screen that is diplayed before the actual upload (after the variable prompt) when running a planning sequence containing an upload planning function which. You can specify basic format settings of your csv file like the row where the data starts, the header line and the separator sign. With this popup you can overrule the default settings that you have specified when defining the planning function.

We do not have yet an integration from AO side, but a small PoC was done and can be seen on following video.

Hierarchy Handling of Attribute Planning in case of Hierarchies with postable nodes

In order to setup the comments on hierarchies with postable notes you have to do following steps in the query modelling:

Create a 1:1 formula with exception aggregation ‘No Aggregation Along Hierarchies’ based on your attribute based key figure here simply called ‘Comment’

Create an inverse 1:1 formula back to attribute based key figure

Hide the original attribute based key figure (in our case ‘comment’)

Then you can start using the short text in your query.

To get an impression, see also the small video

Delta Rendering

An input-ready query contains a few input-ready base key figures and maybe some input-ready formulas but in addition many formulas (e.g. KPIs) based on actuals that does not change at run time. If this 'reporting part' of the query dominates the size of the result set it usually also has a big impact on the time to compute the result set. In this case it may be possible only to recalculate the input-ready part in the server roundtrip since the reporting part does not change.

With the switches described in note 2336160 it is possible use the following optimization: The system determines if a manual data change in one input-ready query is simple enough to by-pass the analytic engine and to rebuild the result set locally just based on information contained in the result set. This infrastructure only computes the changed data cells and does not touch anything else. The clients may then consume only the changed and recalculated cells.

If the switches are set the system checks whether delta computation is possible (in each server round trip). If delta computation is possible the changed cells will be computed and the client will be notified that a call of the analytic engine might not be necessary to rebuild the result set. The client might still not consume the changed cells only because of some reasons mentioned below. If this is the case the client calls the OLAP to rebuild the result set. But if the query is simple enough the client might consume only the changed and recalculated cells to update the result set. Particular in wide area networks we expect significant performance improvements. In order to enable delta rendering, run report SAP_RSADMIN_MAINTAIN. Insert object RSBOLAP_DELTA_MODE with value X. Then with AO 2.3 delta rendering is triggered as long as the prerequisites above are fulfilled.

To enable also

•BI/BW Java (WJR) insert also the objects RSWR_BICS_RS_DELTA_UPDATE with value X

•ABAP BICS RSRT insert also the objects RSRTBICS_ALLOW_DELTA_RENDERING with value X

Alternatively for test purposes you can also use the user SET/GET parameter RSBOLAP_DELTA_MODE to enable it for some test users.

See note 2336160 for the detail of following query features limiting delta rendering.

Usage of 'lookup variables', i.e. formula variables

Usage of formulas with exception aggregation

Usage of nested formulas (input-ready or not) based on NO2 key figures; usage of input-ready NO2 key figure is supported

Unsupported standard aggregation, supported standard aggregation is SUM, NO2

Usage of input-ready % formulas, %XT, %YT, %GT

Overlapping KIDs (i.e. structure elements of type SEL)

Unsupported input-ready formula, usually because of an operand that cannot be send to the OLAP 'pocket' calculator

Unsupported reporting formula, usually because of an operand that cannot be send to the OLAP 'pocket' calculator

Back-end cell locking used

Cells in new lines were created

Old result set was empty

Mixture of BW hierarchies and sub totals on at least one axis

List calculation used

Condition used

InMemory (PAK) aggregation failed after InMemory disaggregation

A new reporting formula was created in the user session

Reporting formula with % functions: %XT, %YT, %CT, %GT, %RT

Characteristic Relationships on DSO with projection on subsets

With Subsets

The methods CHECK or CREATE of a relation will only be called if all characteristics of the relation are known or requested, respectively. The setting 'With Subsets' allows to call the methods CHECK or CREATE even if a non-empty set of the relations characteristics are known or requested, respectively.

Use the setting 'With Subsets' can be used in relations without derivation based on a DataStore-Object. Then all key fields of the DataStore-Object should be used in the relation.The valid records for a non-empty subset of the relation characteristics are defined by the projection from the DataStore-Object records to the subset.

aDSO Planning for direct update templates

As from SAP BW 7.4SP 9 onwards the new basic InfoProvider aDSO was introduced combining cube and DSO advantages in one InfoProvider type with HANA. See SDN article on aDSO for more details. With the shipment of SAP BW 7.5 we enabled planning on aDSO in case of cube like scenario(flag ‘All Charachterisitcs are Key, Reporting on Union of Inbound and Active Table’ is marked). Also see the article Planning Engine Improvements for BPC Embedded Model, PAK and BW-IP with BW 7.50. As mentioned above we have enabled the HANA optimized planning of the cube like aDSO as performance improvement with the first support package of SAP BW 7.5. Now with the feature pack of SP 4 we also enable the direct update like aDSO for planning and in the same time also enable it for HANA optimized runtime.

The main difference to the old direct update DSO are often hidden to the enduser and also to administrator at first glance. However, for the aDSO we have

No Navigational attributes are present at aDSO level and can only be introduced by CompositeProviders modelled on top

Enhanced InfoObject Flexibility
- InfoObjects and Fields
- Per InfoObject: SID generation during reporting or writing
- Planning currently only support SID generation during writing

In general the transport (TLOGO) object type does not determine the behaviour of planning as it was in the past with InfoCubes planning on deltas and direct update DSOs doing the same on after images. Now the aDSO can do both. So we need the subtype to determine the concrete behaviour.

When creating a direct update type aDSO you have also to maintain the key fields.

As for real time InfoCubes we still have some features in aDSOs that are available in the general case but not in planning scenarios. However when using an aDSO for direct update we could get ride of some of these limitations: in this case fields (instead of InfoObjects) are allowed as well as InfoObject of high cardinality.

Remaining restrictions are
- all characteristics have to be mapped to BW InfoObjects
- constant target should not be filled

The rest of the modelling of the planning artifacts is also very similar to the case when uisng an InfoCube. For example you use attribute for planning e.g. for short texts as in the direct update DSO.

Also transaction RSPLAN can still be used to enriche aDSO with planning meta data like assigning characteristic relationsships and data and build planning functions on top.

Aggregation Level Maintenance in the BW Modelling Tools

We always hear a lot of complaints about the number of tools a person who creates the planning applications has to to know. While for BPC standard model all modelling can be done within the BPC Web Admin, in BPC embedded, which is strongly integrated in the BW world, the user has to use transaction RSA1 and RSPLAN and the Query Designer in order to create a working input ready query. However in the BW reporting and staging all main objects like InfoObject, aDSO, composite provider or query can now be modelled with the eclipse based BW modelling tools. With BW 7.5 SP4 you can now create aggregation levels for planning as the first planning meta data object in the BW modelling tools. With this a user can now model everything which is need for input ready queries in the BW modelling tools (remember that also the query designer is available in the BW modelling tools). No change of tool is needed any longer. You start with the planing enabled aDSO, then create an aggregation level on top, and finally you model the query, just using the BW modelling tools.

Dynamic Restrictions for End User

Beginning with Analysis Office 2.3 patch 1 it should be able to create restrictions as end user. So far it was only possible to create them at design time e.g. from the central IT in the query designer as restriction or RKF. In the first version we had have restrictions on hierarchy selection, plan queries and reuse in worksheet created calculations. Those will be lifted in upcoming AO patches and backend note. Open restrictions at the moment are the input readiness of those restrictions on the fly and the possibility to edit them. Here you need to delete and recreate them. A small demo is shown in video.

Local Queries in BPC embedded

As mentioned above the creation of the basic provider in the BPC embedded model is not done by the line of business (LOB) in the BPC admin client but by the central IT administration. To facilitate the modelling of LOB owned providers we very soon introduced the local provider. However, one still had to use the BEx Query Designer to create queries on top or the provider. With BW 7.5 SP4 you now can model local queries on top of local provider in the BPC web tool. The creation of local queries on local provider is only one of many scenarios also usable for building fast prototypes. The main purpose is to leverage central object like provider and queries and extend them only locally. So a user can pick a central InfoProvider like InfoCube, aDSO or composite provider as a model. This then can be extended in the BPC embedded workspace query designer (WQD) with local basic key figures and a local queries. In this case also central queries could be leveraged.

1) Build new queries
1. a. On existing central provider
  - aDSO, Cube, DSO
  - central composite provider
2. b. On local provider

2) Extend central IT query
1. a. Reporting Query
2. b. Plan Query

The example of use case 1 a on the central composite provider is shown in video.

The story for local provider was so far in BPC 10.1 incomplete. One could upload csv into a local provider, but further consumption with queries within the LOB environment was limited. Only in AO on could build some layout and query view features on top of the default query (see videos Report Direct On Excel and Planning Direct On Excel ). Now with the local query one can further enhance the consumption possibilities by adding filters, planning enabled key figures etc. (see Planning On Exce With local Query )

The example of use case 2a is shown in the following Local enhance central designed query. Here one goes on step further

Known Limitations

1. Only one MultiProvider or HCPR is possible per BW Workspaces (will be lifted in next NW Release)

2. No transport possible

3. Use central Plan Queries build on ALVL as Reference Query