Show TOC

Pruning Queries on MultiProvidersLocate this document in the navigation structure

Concept

If a MultiProvider consists of a very large number of InfoProviders, it is useful to use pruning for this MultiProvider. The query is then divided into multiple subqueries and the workload is therefore distributed across the system. The query workload is further reduced by checks to determine whether an InfoProvider contains any data for the selection range. InfoProviders that are not relevant are then not queried at all.

When a query is executed, the system checks whether an InfoProvider contains data for the selection range. If not, it is not queried.

There are different ways in which pruning can be used for queries:

  • Based on the definition of a constant

  • Based on a restriction of metadata:

    • Semantically partitioned objects

    • Pruning based on the InfoProviders involved

  • Based on the posted data: subqueries on InfoCubes using RRKMULTIPROVHINT

Based on the Definition of a Constant

If a characteristic is set as a constant, it can be compared with the selection range in the query. If the selection range does not contain the value of the constant, this InfoProvider can be skipped when executing the query.

More information: Additional Functions in InfoCube Maintenance

Based on a Restriction of Metadata

Pruning based on metadata can be used for MultiProviders and is automatically used for semantically partitioned objects. CompositeProviders and transaction LISTCUBE also used this type of pruning.

For semantically partitioned objects:

Pruning is automatically used for semantically partitioned objects (both for InfoCubes and for DataStore objects). When you create semantically partitioned objects, you define how the data is distributed between the individual partitions. This information is stored in the tables RSLPOPART and RSLPOPARTRANGE. When you execute a query, these tables are checked and compared with the selection range of the query. InfoProviders that do not fall within the selection range are not queried.

Consistency between the selection area and the posted data is guaranteed by the corresponding filter in the DTP.

For MultiProviders, based on the InfoProviders involved:

The same principle can be applied for MultiProviders as for semantically partitioned objects. InfoCubes and DataStore objects of the types standard and SAP HANA optimized are supported. Unlike semantically partitioned objects, where the partition criteria are specified in the definition, in this case, you only define these later in table RSIPRORANGE and, if necessary, adjust them if the range changes due to data loading.

If a semantically partitioned object is entirely contained in the MultiProvider, the partition criteria are read from table RSLPOPARTRANGE when the query is executed. Restrictions that were defined in table RSIPRORANGE are not applied to complete semantically partitioned objects.

If only parts of a semantically partitioned object are used in the MultiProvider, the partition criteria from table RSLPOPARTRANGE are not read when the query is executed. In this case, you need to manually define the restrictions in table RSIPRORANGE. You can define restrictions for each InfoObject contained in the InfoProviders and these restrictions do not need to match the partition criteria of the semantically partitioned object.

More information: Administering Pruning for MultiProviders

Consideration of the Time Hierarchy

For InfoCubes, restrictions for a time characteristic can be converted to another time characteristic that is used in the query. For example, a restriction for 0CALYEAR is converted to a corresponding restriction for 0CALMONTH and the pruning is then performed.

However, not all combinations of time characteristics are converted, but rather only those that can be quickly calculated. In particular, it is not possible to convert between 0CAL and 0FISC time characteristics.

The following time characteristics are supported: 0CALDAY, 0CALMONTH, 0CALQUARTER, 0CALYEAR 0FISCPER, and 0FISCYEAR.

Based on the Posted Data

Pruning based on the posted data has been available since BW 3.x. However, it has the restriction that it is only available for InfoCubes as an involved InfoProvider.

You can activate the pruning for posted data using an entry in table RRKMULTIPROVHINT. The system then performs a prequery with the specified selection range for the InfoObjects that are stored in table RRKMULTIPROVHINT. The result is buffered.

For more information, see How to Create Efficient MultiProvider Queries in the SAP Community Network at http://scn.sap.com/docs/DOC-15981Information published on SAP site.

Note

If you are using this solution, you can continue to use it. However, the new method of pruning based on metadata is much more versatile and you should check whether it is more suitable for your use case.

If you are using an SAP HANA database, you should used pruning based on metadata rather than pruning based on posted data. Since there are no longer any dimensions when you are using an SAP HANA database, the prequery would have to be performed on the fact table , and this would lead to a long runtime for the prequery.