Using the Backend Query Analysis in SAP Analytics Cloud

[yoavyahav2]

Background

From QRC3 2022 and after Implementing note INA: performance analysis metrics as part of the InA response, an additional performance analysis tool have became available in SAC.

This tool enables dashboard developers with a good BW technical background, to understand better the behaviour of a specific widget (table or chart) in terms of the BW query or in the case of an import connection: the analytic or planning model and HANA tables.

This tool shows stats for widgets that have a backend runtime of more than one second and show the backend system behaviour as potential factors that might contribute to the overall runtime.

Supported Widgets

Not all type of widgets are supported, and different types of edit mode (ODE vs standard) enable different widget support

Widget type	Connection Type	Available in
Table 📋	BW/4 Live	Classic mode, ODE mode
Table 📋	Import connection	Classic mode, ODE mode
Value Driver Tree (VDT) 🌲	BW/4 Live	Classic mode, ODE mode
Value Driver Tree (VDT) 🌲	Import connection	Classic mode, ODE mode
Chart 📊	Import connection	ODE mode
Chart 📊	BW/4 Live	ODE mode

How to check the widgets 🛂

Based on the table above, the performance analysis option is available as part of the table/chart menu

Once the Show Performance Analysis menu option is picked, the backend and front end stats will be displayed as well as a detailed breakdown of the query analysis, grouped into three main categories:

• High Impact in red (influence the query runtime the most) 🔴


• Medium Impact in amber 🟠


• Low Impact in green 🟢

In case import connection is in use, e.g. planning or analytics models, the statistics will show a bit different as there is no backend BW query (well there is but since import connection is in use, the data is imported to SAC Using it's HANA backend tables and the data source is the planning or the analytical model):

For charts, in order to view the performance analysis, we first require to navigate to the Applied to chart → Warnings & Errors

Analysing the backend performance analysis results 💡

As there is no one way how to resolve performance issues of the BW query, there are several factors indicated by the backend query performance, depending in their volume, compared to the query design limits.

The traffic light matrix we see in SAC, is based on the BW query limits, that a query design should not exceed, otherwise the query might not be optimal and work as expected🚦

BW query element	Impact Level	Required action
Number of user-defined Variable Exits	High/ Medium	Can the number of user defined Variable Exits be reduced?
Number of Dimension Attributes	High/ Medium	Can the number of Dimension Attributes be reduced?
Number of Variables	High / Medium	Can the number Variables be reduced?
Parallel Processing	High/Medium	Check if this parameter can be configured as queries on an InfoProvider with non-cumulative key figures cannot be processed in parallel

So for example the factor Number of Dimension Attributes may be considered as amber (medium) to some extent:

And a high impact factor in a query containing more than 200 of dimension attributes:

So as an overall approach, the BW query analysis should be adopted when possible, with the obvious exceptions that users tend to create their own style of queries and development and with the understanding that if we have 200 dimension attributes it's not a best practice, a  parameter that we can relatively challenge and check if those 200 attributes are actually required or even more than that, are these attributes in use? (the short answer is: no… 🧪) especially when there are high doubts that all these attributed are indeed required, more firmly: how much of these attributes are actually required or in use in the corresponding widget.

To address this on a larger scale, a clear, well communicated and enforced guideline should be shared with the users, checked before a query is released through a standard check-up process

Reduce BW Query Metadata ↕️

Another way to fight the low performance crime is to use the reduce the BW query metadata (compare pre and post performance results)

This option optimizes performance in a table widget by consuming a reduced set of metadata. This limits the number of attributes to the ones that are explicitly defined at the query level.

When this option is activated, the only attributes that will be available in an SAP Analytics Cloud story are the attributes that were marked as visible in the query.

Further checks & Information in RSRT 🛠️

As mentioned, the traffic light is relying on the query structure limits, further information can be gathered through RSRT → technical information, when designing the query

In some cases, they are factors which may not be captured in the backend analysis in SAC, but are captured in the technical information

e.g. Table Sizes Optimized

Additional Information 📚

Warnings in transaction RSRT -> Technical Info

Performance Analysis Tools for SAP BPC NetWeaver/BW Reporting

Parallel Processing

Reduce BW Query Metadata