Product Information
Improvements of BW/4HANA Analytical Engine in Q1 2018 with BW/4HANA 1.0 SP08
So far, the last main set of new features of the planning engine was shipped with BW/4HANA and some of these are also available in NW BW 7,50 SP8. Most of the development we did in the past is documented in the planning section of BPC. See as example the SDN blog on ‘First Steps from BW-IP and PAK towards SAP BPC 11.0, version SAP BW/4HANA planning with Embedded Model describing the improved YTD calculation support and dynamic grouping in Analysis Office. Another blog ‘Improvements on Query less Reporting in BPC/4HANA Embedded and BW4/HANA’ showed the work we have done together with Analysis for Office to enable business user to build reports without using or changing IT-controlled BW-queries. The reason behind publishing those in the planning section was that the requirements were very often triggered from the planning side.
However, since they are very often pure analytics feature, we now decided to move those descriptions to the BW/4HANA section and refer to them from the planning section.
But our journey to make the analytic layer stronger and add “query-less” reporting of course continued in 2017 and will continue in 2018.
Main improvements done till first quarter of 2018 are:
- Better automated HANA pushdown criteria including machine learning
- Further performance improvements through better parallelization
- New Query Cell based Documents
- Improved handling of structure elements in the formula mode of AO
- Performance Improvements in Query Catalog Search
Let us have a glimpse on each of the topics in detail.
Better automated HANA pushdown criteria including machine learning
The query runtime property: “Operations in SAP HANA” is used to determine to which degree operations are pushed down to HANA. Usually the execution in HANA is very beneficial if the ratio DBTRANS to DBSEL and is very small (see blog understanding query performance in nw-bw and bia) and DBTRANS is generell not to big. But currently for each query the administrator must go in and set the best value which is very time consuming and of high TCO. We want to ease this by having new values and administrator just chooses one of the following standard options.
- Defensive: This options corresponds to the option “Expert: Optimized access”.
- Standard (Default) and Offensive: The new Optimizer for operations in HANA decides if the query is executed with the option “Expert: Formulas calculated in SAP HANA with complex currency/unit” or “Expert: Optimized access”.
If a query is executed with the standard option of Operations in SAP HANA, then the new developed optimizer decides if the query is executed with the advanced expert option (positive decision with respect to HANA usage) or the option “Expert: Optimized access” (negative decision with respect of HANA usage).
In case of the advanced expert option the database result set should be smaller and consequently the processing time of the database result set in the analytical manager is shorter. On the other hand, additional setup costs due to the HANA orchestrations are required for advanced expert options.
Therefore, it makes sense to execute a query with an advanced expert option only if the benefit (shorter processing time for database result set) is higher than the setup costs.
The “Optimizer for operations in HANA” estimates number of lines of the database result set for the positive decision [EST_DBTRANS_POSITIVE_DECISION] and the negative decision [EST_DBTRANS_NEGATIVE_DECISION]. The estimation considers only the parts of the query that are executed in different ways in the positive and the negative decision. The estimation considers the global filters.
Then a reduction factor is calculated: [EST_DBTRANS_NEGATIVE_DECISION] / [EST_DBTRANS_POSITIVE_DECISION].
If this reduction factor is greater than or equal to the “Minimum reduction factor” and [EST_DBTRANS_NEGATIVE_DECISION] is greater than or equal to “Minimum DBTRANS (negative decision)”, then the “Optimizer for operations in HANA” assumes that the benefit of the smaller result set is higher than the setup costs for the advanced expert option. Consequently the “Optimizer for operations in HANA” decides positively to execute the query with the advanced option. Otherwise the “Optimizer for operations in HANA” decides negatively to execute the query with the option “Expert: Optimized access”.
If the “Minimum reduction factor” and “Minimum DBTRANS (negative decision)” are 0 or 1, then the “Optimizer for operations in HANA” is disabled and queries are always executed with the advanced expert option.
The new transaction RSR_LTO can be used to determine proposals for the “Minimum reduction factor” and “Minimum DBTRANS (negative decision)”.
Further performance improvements through better parallelization
In addition to the pushdown improvements, we also accelerated the internal OLAP data get calls by further parallelization for HANA optimized execution. A general estimation of this improvement is hard to give but check out your queries. In particular queries with many different intermediate sums (also caused indirect by exception aggregations) should profit.
New Query Cell based Documents
The history of various document solution is long and was cumbersome BW. It started in BeX using KPRO or Knowledge Management based storage which was a very complete solution but slow in performance. This lead to the fact that is was rarely used and not adapted by quite more modern analytical UIs like Analysis Office or Design Studio. Different How-To Guides and consulting solution like the LGD commentary solution did evolve but only lead to limited functionality and implementations with high effort and maintenance costs. Then we developed for BW based planning solutions (IP, PAK or BPC embedded) the planning on DSO in BW 7.4. Based on this we later added the planning on SID based key figures for characteristics in the data section the so-called attributes planning. A spin off was a short text solution which removed the need for the how to solutions. The restriction on the length of the comments to less than 250 characters and the problem that comments could not be assigned to hierarchy levels did however make it an uncomplete document solution. Also for intermediate results and hierarchy nodes the NO2 aggregation was used to show comments if they are equal on lower level. But it was not possible to assign a comment itself to such intermediate levels. The improvement to put comments also on hierarchies with postable nodes did not fully mitigate those limitations.
The modern analytics UIs on the other hand follow a different approach. Already with AO 1.x one could create pure Excel comments and store them with the Excel Workbook. However, the end user then need the rights to store workbooks on the BW backend or BI platform. This was lately enhanced in AO 2.4 with the possibility to store the documents separately on the BI platform independently from the workbook. Still limitations to use comments across worksheets and use them in complex selections like key figures remained.
Thus, we decided to create a new solution which combines best of both worlds. In this solution, the length of comments is unlimited and a comment can be assigned to any hierarchy level or intermediate results. You can also assign comments to restrictions and calculations. Documents can be shared queries are found again if the selection are equal. You can even find documents from drill down in selection and vice versa when the common part is equal. And all this comes now with decent performance.
To enable documents, you must create a new BW object called “Document Store”. In this “Document Store” you must assign the InfoProviders for which the store should hold the documents. All necessary fields for the document store are derived from the assigned InfoProvider(s) (plus some added fixed technical fields).
Under the hood an aDSO is generated to store the physical documents.
Tip: Do not forget to activate the new InfoObjects 0DOC* of the technical content since they are needed for the generated aDSOs.
Finally, you only must assign the document store to each query you want to use documents in the tab “Runtime Properties” of the query.
Now you can start to add documents to the query as shown here in transaction RSRT2.
With upcoming AO 2.7 release we hope this will be also usable in Analysis Office. The documents appear as normal Excel comments but they are stored in the backend now and not in the workbook or on the BI platform
You can create or change comments from the context menu of the cell.
In the example above the comment is created on customer level and on the intermediate sum for fiscal year. With a second query, where fiscal year is not in the drill down, the comment can be found on the detail level.
Or I create a comment in the first query on the detail level
And I can find it again on the restriction .
In addition, we have an administration page which is accessible from the “Document Store” editor in the BW Modelling Tools
or direct in the web browser via the correct URL (https://<server>:<port>/sap/bc/webdynpro/sap/rsdmdm_md_maintenance_app?
sapclient=000&doc_store=<store name>&sap-language=DE#)
The administrator can search for documents, view them, check the selection conditions or even delete documents.
Improved handling of structure elements in the formula mode of AO
With note 2569461 “Enablement of technical names (map names) of reusable key figures in queries”, we made some strong improvements to better support the formula mode in AO. Now when pressing the button convert to formula the cell based formula SAPGetData shows the technical ID if given to the key figure. For example, we set the technical ID AMOUNTTHISYEAR and Description AMOUNTTHISYEAR 2007 to a restricted key figure in the query we want to use.
To switch to the formula mode, you can press the button ‘Convert to Formula mode’ in the ribbon after you inserted the query as data source.
Now each cell is defined by a SAPGetData Formula. The key figure now referenced is the technical ID given before.
This even work with linked nodes being available for reusable key figures also with BW4HANA 1.0 SP8.
Let us look at the linked key figure with technical ID AMOUNTTHISYEAR_LINKE /Description AMOUNTTHISYEAR 200.
It is based on the key figure AMOUNTTHISYEAR given at the beginning of this chapter. When using this new key figure AMOUNTTHISYEAR_LINKE in the query without redefining the technical ID then the technical ID AMOUNTTHISYEAR of the root is used. This is exactly what you see in the SAPGetDATA formula above.
So, it is possible to uniquely define a key figure on different info providers using linked nodes and the technical ID of the root component. This use case is very close to having a central key figure repository in BW.
Performance Improvements in Query Catalog Search
We also improved the query and InfoProvider catalog search in AO. Check it out with AO 2.7 and BW/4HANA SP8
Hi Frank,
Thank you for the update!
Great! Finally, we will have one consolidated solution for comments without the need to create multiple planning enabled DSO’s just for comments restricted to 250 chars!
Could you please tell how does the structure of the ADSO look like and how does the Document Store merge 'Cube' like ADSO + comment ADSO for reporting? Is it also that we will have ADSO with Cube like keys and new input ready KF’s for comments in the backend?
Kind regards,
Sebastian
Hi Sebastian,
the aDSO structure does not matter. The Document Store also is based on a generated aDSO. But this need not be of interest.
It works for al aDSO types cube or direct input. So there is no restrictioin on cube like aDSO
Regards
Frank
Thank you! Are there any plans to bring the Comment Solution also to BW 7.5 ?
Hi Matthias,
there are no plans since the development was quite large and span over various groups. So it cannot be done simply with notes
Hi Frank,
So we have SP08 on our system, and I tried to create a document store. However I get the message "Creation of objects of type 'DOCA' not supported. Trying to follow your guide but the first step I am already stuck. Is there anything I need to do first?
Hello
I face the same issue in my system. Were you able to find a solution?
Thank you
No unfortunately not yet!
Hi All,
Anyone has been able to resolve this issue? I am bumping into this too on our system.
Best Regard
David Yee
Hello David
Application of note : 2652052 resolved this issue in our side, i hope it helps you too.
Regards
Hi Zerin,
You posting managed to resolve our issue. Thank you so much.
Best Regards
David Yee
Hi Frank
As we can see, the cells comments are works only for keyfigures, but doesn't works for Query formulas. In some cases we should use formulas in input ready query (for example to awoid summ if hierarchy in rows), hope your team will made comments support for formulas n the future
Hi Ilya,
also formula should work. Here you need to specify the technical ID. It will be then key to find the document.
Regards
frank
Hi Frank, thanks for the update.
Just more question, from which version of BW is this solution available?
The document solution is available from BW/4HANA 1.0 SP8 onwards
The document solution is available from BW/4HANA 1.0 SP8 onwards
Hi Frank,
I am not able to replicate the solution and wondering if you can point my mistake.
I have created Cube like ADSO - added to composite provider - Agg level - Plan query.
Under Plan query I have selected Yes for Document store but drop down for under default document store is disabled - PFA screen shot "DS query Prop.jpg"
.
In RSRT2 I see a warning but context menu is not bringing commentary options (PFA RSRT2 screenshot).
I have also tried in analysis excel 2.7 to assign document store dynamically but document store selection is disabled.
Can you please help me out.
Thanks
SK
Hello,
the composite provider has to be assigned to the document store. Then the F4 help shows the document store.
Best regards
Hans-Jürgen Schwab
Hi Frank,
Thanks for your help - I am able to do commentary planning now. May I know if commentary at Cost Centre group hierarchy level is supported (as I am able to store comments at Cost Centre level only).
Also May I know when BPC 11.1 will be out that supports BW4HANA 2.0
Appreciate your help.
Thanks
SK
HI SK,
BPC 11.1 should be out by yesterday. Please try to download it.
Regards
Frank
Thanks Frank - I can see it SAP downloads now. My other query on Commentary at SAP hierarchy nodes, it this supported? I tried to do comments at Cost Centre hierarchy node and noticed it is not storing in Document store object.
Thank you
SK
Yes Hierarchies are supported
Hello,
the composite provider has to be assigned to the document store. Then the F4 help shows the document store.
Best regards
Hans-Jürgen Schwab
Hello,
is it possible to switch on/off which characteristicas are document relevant? - e.g. in a planning cycle a comment which was entered in Version 1 should also be displayed in Version 2 so I want to set InfoObject "Version" as "Not document relevant".
Thanks and best regards
Michael Benz
Hi Michael,
I am sorry. This is currently not possible.
Regards
Frank
Hi Frank,
thanks for sharing, this commenting feature is awesome and works great for Analysis for Office. Does this solution also work with Lumira Designer or is this planned?
Best regards
Ulrich
Hi Ulrich,
I fear not yet. Please contact here the product Management of Lumira Designer. E.g. Noel Munday
Regards
Frank
Hi Frank,
Does this solution work on composite with aDSO and HANA view? We get the below error message:
Error reading the data of InfoProvider /1BW/DOAUKHQ$O
CL_RSDR_AT_QUERY >> Row: Inc: 54 Prog: WRITE_MESSAGES
Regards
Raghu
This might be a Limitation one would Need to investigate by a ticket. Please open a ticket
Hi Frank ,
I have created document store and I can see comment is enable on Analysis for office. But if I enter any comment and open query again, comment disappears. I checked in backend and there are no entries in document store. How do I store these comments so they dont disappear when I save new line or open again?
Also can we copy these comments to new version which is copied from standard copy function?
Thanks in Advance.
Saurabh
Hi Saurabh,
maybe you just forgot to save the documents. In Analysis Office one has to save the documents explicitly whereas in RSRT2 the documents are saved automatically.
Regards,
Gregor