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