SAP BW query - efficient use of filters with large data volumes, or: "How do I speed up the filters?"

As a business intelligence package, SAP BW provides many opportunities for efficient reporting – but also contains numerous barriers which significantly slow down performance. Using the example of report filters available in the application, this article shows how the smallest adaptations impair SAP BW performance, and how efficient settings for filtering options can improve results.

Through definitions of specialized and technical content, a use of SAP BW queries enables perfect preparation of interaction between users and data. What many users - especially professionally oriented power users - usually do not know, however: Any setting, however small, can sometimes have an immense impact on report performance. In the case of filters, for example, the outcome can vary from simply useless to highly efficient and fast reports.

The following example shows a filter selection from the area EC-PCA with the InfoObject "Document type" in Analysis for Office:

Filtration yes, but what is the right way?

When considering a use of report filters, the following question first arises: Where can the filtration behaviour be defined, and what are the reasons for adapting the relevant settings to one's own reporting requirements? As is usual with SAP BW, there are numerous ways of defining the behaviour of selected filters in order to maximize reusability. This can significantly facilitate work, especially for query developers who would ideally not need to change any settings. What is clear: Users should be familiar with and utilize available filtering options, because the selection predefined by SAP provides the least efficient solution and often leaves something to be desired in terms of speed.

The filter behaviour can be set in the following objects:

1. The InfoObject, and thus globally for CompositeProvider and Query:

2. The CompositeProvider, to respond to adjustments based on the explicit data model:

3. The Query, if special features require a change in the query structure:

The selection made above changes the behaviour of SAP BW queries to the database and, thus, the number of visible entries. Filters from master data can be determined at a very high rate, for example, though features without data are also displayed here, thus possibly irritating the user.

The following filter options are available

Selection	Description	Problem
Only posted values for navigation (default)	For filtration, the default mode only provides the selection of features included in the query. This setting therefore evaluates all key figures contained in the query based on the filter breakdown.	For a feature with a high cardinality, such as a document number, the query's key figures are evaluated for all document numbers (query filters are taken into account). If a query has many exception aggregations or if the number of entries in the master data is very high, display of the filter selection can take a long time, or the process might even be aborted. Furthermore, because the values are determined by the Analytic Engine, limits on the number of entries are not taken into account during determination.
Only values in InfoProvider	The setting ignores the key figures of a query (calculated and limited), and performs a join operation with the underlying InfoProviders. This can happen directly in the HANA database, and also takes into account the limitation of result rows.	This setting should be chosen if the InfoObject has a high cardinality or the calculations in a query are very complex. Taken into account here are the query filters, but not the key figures. Consequently, the filter window might display entries without data if, for example, limited key figures are used.
Values in the master data table	The InfoProviders are ignored, and only the master data table is accessed. This variant suppresses the join operation on InfoProviders, and provides the highest performance.	Master data queries are useful if all the features of an InfoObject are to be made intentionally available. In addition, a CompositeProvider can cause inefficient opening of the filter list due to joins, calculations, and other properties. This setting bypasses this aspect and all filters of a query, thereby providing the highest performance during access.
Feature relationships	This can only be selected in a query and is relevant for planning. This filter option is not discussed further here.

Practical examples

The following practical examples demonstrate the causes and solutions of potential problems:

Problem	Cause	Solution
Analysis for Office often crashes when the user opens the selection list.	This often happens when an InfoObject with high cardinality, such as the document number, is to be displayed, and exception aggregations exist in the query. The Analytic Engine must perform calculations for all documents, which often results in cancellation due to insufficient memory or runtime failure.	Change to the setting "Only values in InfoProvider" in the InfoObject. This bypasses calculation of all key figures for each entry (e.g. document number) and performs a direct join at the HANA level. This is restricted via TOP 1000 depending on the limitation in BO and is therefore efficient.
The selection window of calendar month (0CALMONTH), for example, opens very slowly.	Queries are often limited to one month to restrict data volumes. If the user changes this filter, the Analytic Engine calculates key figures for the entire data inventory (because the time filter is missing).	Change to the setting "Only values in InfoProvider" in the query. This bypasses calculation of key figures without any time limitations, and performs a direct join at the HANA level.
The selection window opens slowly, even though only values from the InfoProvider are read.	If a virtual InfoProvider with access to a preliminary system or Hadoop is used, a join operation on the data cannot be efficient either.	The setting "values in master data table" in the CompositeProvider bypasses access to transaction data and reads directly from the master data table.

Implementation in the case of third-party solutions with MDX access such as Longview

Because these settings are only considered in SAP BI clients such as Design Studio, Analysis for Office, etc., third-party tools must control behaviour themselves. How this works with Longview, for example, is described in one of our blog posts.

Conclusion

Small change, large effect – to minimize expenditure in query development, the relevant department and BI should regularly have exchanges, discuss problems with query settings and deploy the solution centrally. However, power users should also be aware here of the behaviour of filters and their effects, to be able to identify and communicate any potential for optimization.

Do you frequently encounter performance issues? Do not hesitate to contact us. In an initial analysis or our BI quick check, we identify stumbling blocks and provide you with opportunities for fast and uncomplicated self-help.