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 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
- a. On existing central provider
- aDSO, Cube, DSO
- central composite provider
- b. On local provider
- a. On existing central provider
- 2) Extend central IT query
- a. Reporting Query
- 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
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
Hi Joost,
sorry I now changed them form unlisted to published. Can you try if this helps.
Regards
frank
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.
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.
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
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
Hi Jens,
you have to activate first in planning function type in the content .
Regards
Frank
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
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
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
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
Movie sent .The integration into AO is on the roadmap of AO. Please contact product management on AO side
Hi Frank,
I woukd appreciate if you could send me the video of the PoC to carlosegarcia (at) gmail
Thanks,
CarlosE
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
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
Hi Eduardo,
no sorry . But you can open a ticket . then we can look into it.
Regards
Frank
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
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
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
The integration into AO is on the roadmap of AO. Please contact product management on AO side
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
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 )
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
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
Hi Frank
Can you send me the video on how to integrate file upload in Analysis for Office?
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