Skip to Content
Author's profile photo Frank Klingl

Planning Engine Improvements for BPC Embedded Model, PAK and BW-IP with BW 7.50 SP4

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 onPlanning 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 articleMoving from BW-IP to SAP HANA-optimized planning with the new Planning Applications Kit (PAK)’ or onFurther 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 }.

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

FileUpload1.jpg

 

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.

FileUpload2.jpg

 

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:

  1.   Create a 1:1 formula with exception aggregation ‘No Aggregation Along Hierarchies’ based on your attribute based key figure here simply called ‘Comment’
    Hierarchien1.jpg
  2. Create an inverse 1:1 formula back to attribute based key figure
    Hierarchien2.jpg
  3. Hide the original attribute based key figure (in our case ‘comment’)
    Hierarchien3.jpg


T
hen you can start using the short text in your query.

Hierarchien4.jpg

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.

Subset.jpg

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.

 

aDSO1.jpg

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

aDSO2.jpg

 

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

aDSO3.jpg

 

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.

 

aDSO4.jpg

 

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.

 

ALV1.jpg

 

ALV2.jpg

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. 1) Build new queries
    1. a. On existing central provider
      • aDSO, Cube, DSO
      • central composite provider
    2. b. On local provider
  2. 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. 1. Only one MultiProvider or HCPR is possible per BW Workspaces (will be lifted in next NW Release)
  2. 2. No transport possible
  3. 3. Use central Plan Queries build on ALVL as Reference Query

Assigned Tags

      26 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Joost Peeters
      Joost Peeters

      Hi Frank,

      thanks for the detailed overview.

      I wanted to check out some of the videos you refer to, but I always get redirected to this url: http://go.sap.com/index.html when clicking on any of the https://videos.sap.com/something links.

      Any idea why this happens?

      Best regards,

      Joost

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      Hi Joost,

      sorry I now changed them form unlisted to published. Can you try if this helps.

      Regards

      frank

      Author's profile photo Joost Peeters
      Joost Peeters

      Hi Frank,

      unfortunately it's still not working for me. Could be linked to my SAP User-Id authorizations I guess, but I'm not sure what I could do about that.

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      https://mdocs.sap.com/mcm/public/v1/open?shr=MMt_tMp5cCNxQU4T2cLDUDWW8B7UTLBGTy5bxxuX0XYhttps://mdocs.sap.com/mcm/public/v1/open?shr=MMt_tMp5cCNxQU4T2cLDUDWW8B7UTLBGTy5bxxuX0XY

      Hi Joost,

      I do not know what is not working, but if you give me your email address I can contact you.

      I also uploaded the videos now to the link above. Hopefully this helps.

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      Hi Joost,

      I learned that video.sap.com at the moment can only be seen inside the SAP network. So I uploaded them to youtube and now replaced the links.

      Hope everything is fine now.

      Regards

      Frank

      Author's profile photo Jens Bensby
      Jens Bensby

      Hi Frank

      Thanks for a good walk through of functionality as always.

      We have a BW 7.50 SP4 system but file upload function 0RSPL_FILE_UPLOAD_AO is not to be found in RSPLAN. Am I looking in the right place for the function type or are there other requirements for the function type to be available?

      Br.

      Jens

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

       

      Hi Jens,
      you have to activate first in planning function type in the content .
      Regards
      Frank

      Author's profile photo Bryn Evans
      Bryn Evans

      Hi Frank,
      I tried to view the video but it takes me to the main SAP.com website. Tried to find it on youtube.com but no luck.
      As this is the new standard process is there an implementation guide similar to the  excellent one done by Marc Bernard for the custom solution?
      Many Thanks
      Bryn

      Author's profile photo Former Member
      Former Member

      Hey Hi Frank,

      Thanks for the insight. Exactly what we're looking for. As mentioned by Bryan, I too am unable to view the video, instead the main SAP web page opens up. Unable to find on youtube as well. :(.

      We would appreciate if you share any documentation or the video with the community.

      Thanks again.

      Regards

      Sushant Pradhan

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      Hi sushant and Bryn,

      since this is a PoC only I did not upload the Video as the other to YouTube. But you can mail me and I will send you the Video.

      Regards

      Frank

      Author's profile photo Former Member
      Former Member

      Hi Frank,

      If possible can you please share the video with me as well? I'm having same sap web page.

      yajepenati@gmail.com.

       

      Thanks for the help.

      Yajepe

       

       

       

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      Movie sent .The integration into AO is on the roadmap of AO. Please contact product management on AO side

      Author's profile photo CARLOS GARCIA
      CARLOS GARCIA

      Hi Frank,

       

      I woukd appreciate if you could send me the video of the PoC to carlosegarcia (at) gmail

       

      Thanks,

      CarlosE

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

       

      Hi CarlosE

      meanwhile we worked on a full solution from AO. It will come with AO2.7 and BW/4HANA FP08. See https://youtu.be/kyJzxNoFsRA to get glimpse.

      Regards

      Frank

      Author's profile photo Former Member
      Former Member

      Hi Frank,

      I followed your instructions in the "Hierarchy Handling of Attribute Planning in the case of hierarchies with postable nodes" and I could not make it work, is there any other procedure in BW or QUERY?

      sorry for bad english...

      Thanks

      Eduardo Savoine

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      Hi Eduardo,

      no sorry . But you can open a ticket . then we can look into it.

      Regards

      Frank

      Author's profile photo Stephan Römer
      Stephan Römer

      Hi Frank,

      I want to use the new planning funtion 0RSPL_FILE_UPLOAD_AO. I didn't found any useful documentation about it's usage.

      How does "Row with Mapping Info" works?

      What does it mean "Variable Values from file"?

      How could I set default values for the aggregation level for data which is not contained in the flat-file? e.g. 0currency should be always set to EUR.

      Do you have a documentation or could you please post it in your blog?

      Your posted link to die PoC doesn't open a video. Please provide the correct link to this video. Many thanks in advance.

      Stephan

       

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      The integration into AO is on the roadmap of AO. Please contact product management on AO side .

      If you sent me the email I can send you a video

       

      Author's profile photo Dirk Frick
      Dirk Frick

      Hi Frank,

      thanks for the interesting post!

      We would like to use the File-Upload mentioned rather than the well-known How-To-Guide by your coplleague Marc Bernard. However, I have no clue how to integrate into AfO and am not able to open the videos you provided.

      Can you help us?

      Thanks in advance and kind regards,

      Dirk

       

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

      The integration into AO is on the roadmap of AO. Please contact product management on AO side

      Author's profile photo Sanjay Sachithanandham
      Sanjay Sachithanandham

      Hi frank,

       

      I am facing the same scenario as others , I am redirected to SAP.com when I try to access the video. can you email me the video . sachisanjay@Hotmail.com

      or send me the product road map document you are referring to . we need to provide this solution or the client is going to extend their Hyperion license. so you support would help SAP gain a business. thanks

       

       

      Thanks,

      sanjay

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

       

      Since we got many question with respect to the file upload I repeat myself that it is on the roadmap for AO and we try to bring it in as soon as possible.

      But there is also an interesting alternative with BW workspaces by uploading data in a local provider and staging it furhter see:

      https://help.sap.com/viewer/0ecf5244825c4742a7b062a89d11c2ac/7.5.9/en-US/d56c640b2d744386ae884fedfd2dc4c5.html

       

      ( Dokumentation for Workspaces generell https://help.sap.com/viewer/0ecf5244825c4742a7b062a89d11c2ac/7.5.9/en-US/f38f492432354fdc913f93e8a2bed4a6.html )

      Author's profile photo Former Member
      Former Member

      Hi Frank,

      Nice Blog. It was very useful to read the things. I am also not able to view the video, is it possible to send me the video too. My email ID vijeta_samala@yahoo.com.

       

      Thanks in Advance

      Vijeta

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

       

      Hi Vijeta,

      meanwhile we worked on a full solution from AO. It will come with AO2.7 and BW/4HANA FP08. See https://youtu.be/kyJzxNoFsRA to get glimpse.

      Regards

      Frank

      Author's profile photo Jens Bensby
      Jens Bensby

      Hi Frank

      Can you send me the video on how to integrate file upload in Analysis for Office?

      Author's profile photo Frank Klingl
      Frank Klingl
      Blog Post Author

       

      Hi Jens,

      meanwhile we worked on a full solution from AO. It will come with AO2.7 and BW/4HANA FP08. See https://youtu.be/kyJzxNoFsRA to get glimpse