Show TOC

Materializing the Intermediate Result of the QueryLocate this document in the navigation structure

In the Query Properties dialog box in the query monitor, you can change the standard settings for processing the intermediate results of existing queries.

Prerequisites

This property is available only in SAP HANA systems and not in BWA systems. Furthermore, it is relevant only if the query property Operations in SAP HANA/BWA is set to Exception Aggregations or higher.

Context

If the query property Operations in SAP HANA/BWA is set to Exception Aggregation or higher, a large part of query processing takes place in the SAP HANA database or in BWA.

The intermediate results of the processing are calculated in this processing. If the query contains many characteristics with intermediate results in the drilldown, these intermediate results can be used again during the result calculations.

It is generally quicker to recalculate the intermediate results each time, since optimizations can then be used at the level of the intermediate results. In some cases, however, it is quicker to materialized the results sets of the intermediate results.

These exception cases occur when more than one of the following properties apply:

  • A large number of characteristics in the drilldown, each characteristic with intermediate results.
  • A great reduction in data due to the standard aggregation.
  • Very little reduction in data due to filters.

Procedure

  1. Deselect the InfoProvider Setting option.
  2. You can activate or deactivate the Materialize Intermediate Results of the Query options specifically for the selected query.

Example

The InfoProvider contains two billion rows. The data includes the following:

  • 500 unique material numbers
  • 20 countries
  • Five billing types
  • 12 months

The query counts the material numbers per country, per country and billing type, and per country, billing type, and month. The set of intermediate results has the most finely grained tuples required in this query. If all combinations of characteristics exist, the size of the intermediate result is 500*20*5*12 = 600000 rows.

This intermediate results contains over 1000 times fewer records than the InfoProvider. This means that it probably cheaper to materialize and reuse the intermediate result than use the optimizations that can be applied when calculating the individual levels of the intermediate results from the InfoProvider data. In this case, therefore, the recommended action is to materialize the intermediate result.

Related Information