Skip to end of metadata
Go to start of metadata

Introduction/Overview

If a MultiProvider or HCPR is based on many PartProviders, it is useful to use 'pruning' for this Provider. The query workload is then reduced by checks to determine whether an PartProvider contains any data for the selection range of the query. InfoProviders that are not relevant are then not queried at all. In case of SPOs(Semantically Partitioned Objects) and Semantic Groups, the pruning is done automatically according to the definitions of these objects. Please see the following link in the SAP Online Documentation:

There are different ways how pruning can be used for queries:

  • Based on the definition of a constant (InfoObject Level, Provider Specific Property of Cube and DSO)
  • Based on a restriction of metadata:
    • Semantically partitioned Objects: SPO
    • Semantic Groups
    • ADSO where InfoObjects are restricted by so called 'Criterias'
    • Pruning based on the InfoProviders involved: report RSPP_PART_MAINTAIN
  • Based on the posted data: subqueries on InfoCubes using RRKMULTIPROVHINT

In case prunig does not work as expected for a certain query, follow the check list of note 2228499. 

Please note that the term Pruning is (often) also used when BW Providers are partitioned on database level. A query can then exclude partitions by comparing the query filters with the definition of the partitions. E.g. see example Pruning on Database Level and Inconsistent Time Dimension. In addition, when BW is based on Hana, it is good to know that there is also a native Hana pruning when working with Hana Calculation Views. See BWonHana chapter below.

Pruning based on the Definition of a Constant

For InfoCubes and DSOs it is possible to define constants for characteristics by using the 'Provider-Specific Properties of the InfoObject'. When doing so, a fixed value is assigned to the characteristic which means that the characteristic is available on the database side (e.g. posted in the fact table) but is no longer report relevant (no aggregation / drilldown of this characteristic). In case such a Provider is used in an Multiprovider(HCPR), pruning can take place in case there are corresponding restrictions in the query.

  • Table where you can find the constants: RSDICHAPRO
  • Pruning Table: RSIPRORANGE

Simple Example

Pruning based on posted Data: RRKMULTIPROVHINT

Pruning based on the posted data has been available since BW 3.x(see notes 911939 and 1156681). However, it has the restriction that it is only available for InfoCubes as a PartProvider. 'Based on posted data' means, that during query runtime it is checked (regarding the 'reference characteristic' maintained in the table RRKMULTIPROVHINT) which values are booked in the corresponding dimension of the cube. In case there is no intersection with the query restriction, this partprovider isn't accessed at all. Please note, that due to performance reasons, the data in the fact tables is not checked. Hence, you need to assure that there are no values in the corresponding dimension for which no transactional data exists in the fact tables. This might happen e.g. if data is deleted from an InfoCube since the dimension tables are not adapted during this process.

Example: Characteristic Costcenter is used in table RRKMULTIPROVHINT. All records with Costcenter=1000 were deleted from one of the partproviders. A query based on a corresponding Multprovider with a global restriction Costcenter=1000 would still access this parptovider as long as the dimension isn't cleaned up.

Therefore, it is recommended to remove unused values in affected dimension tables (after data deletions) using transaction RSRV:  'Elementary Tests' -  "Transaction Data" - "Unused Entries in Infocube Dimension"

If you are using an SAP HANA database, it is recommended to use pruning based on metadata rather than pruning based on posted data. The reason is that there are no longer any dimensions when you are using HANA optimized InfoCubes, the prequery would have to be performed on the fact table , and this would lead to a long runtime for the prequery.

If you use RRKMULTIPROVHINT, it is possible to deactivate the buffering of the table entries. Since in most cases the benefit of the buffering is low, we recommend to switch it off unless you notice a observeable performance decrease. In particular, when there is an issue regarding pruning, set the NOBUFFER flag as X in the table RRKMULTIPROVHINT for all affected multiproviders.

Example P1: Soft filter taken for pruning

Fields of table RRKMULTIPROVHINT:

  • POSIT: For a MultiProvider, where there is more than one characteristic entered in the table RRKMULTIPROVHINT, the field POSIT field determines the sequence of the checks
  • MAXOR: With this field, you can determine for each characteristic how many rows the WHERE condition of the test reading may have. The default of 20 is sued in case it is initial.
  • NOBUFFER: X means that the buffering is switched off
The Multiprovhint won't take effect when:
  • The reference/hint characteristic is in a line item dimension: since there is no D table in this case, pruning does not take place.
  • There are obsolete entries in the D table: since the OLAP Engine assumes that there is valid transaction data (in the cube) for all values in the dimension, prunig does not work correctly for these obsolete values. With transaction RSRV, D-table can be cleaned according to current cube data status. (RSRV -> All Elementary Tests -> Transaction Data -> Entries Not Used in the Dimension of an InfoCube).
  • There is no proper restriction defined in the query regarding the reference/hint characteristic: the system takes into account all relevant fems0 (global) selections and the superset of all relevant local restrictions, see Example P5:

Pruning based on a Restriction of MetaData

Semantically Partitioned Objects (SPOs)

Pruning is automatically used for semantically partitioned objects. The definition of the individual partitions 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.

  • For InfoCubes(and DSOs, see Example P8), restrictions for a time characteristic can be converted to another time characteristic that is used in the query. For example, a restriction for 0CALMONTH is converted to a corresponding restriction for 0CALYEAR and the pruning is then performed. In order to guarantee a correct query result, the corresponding time dimension must be consistent. Hence, in case of inconsistencies (see Inconsistent Time Dimension) the pruning for the affected partprovider is switched off (see Example P8).
  • In general, the system takes into account both, hard and soft filters (see Filter Values). See the following Examples P2.
  • In case there are relevant local restrictions, the system calculates the superset of all local restrictions and then the intersetction with the global filters. See Example P5.

If you want to check whether a certain query only accesses the relevant providers you can run it in RSRT and use the debug function 'Do not suppress messages'. In this case RSRT displays messages like the message DBMAN 157 ''number' InfoProvider(s) excluded because of pruning' if some Partproviders were excluded. For further details please use the debug function 'Explain MultiProvider' which also provides the technical names of the excluded providers: Example P3

  • Example P4: Query defined on a Multiprovder which contains one SPO(based on DSO) and one basis DSO(Standard). The basis DSO  does not contain the characteristic (Costcenter) which is used to define the partitions for the SPO. The query uses key figures restricted to costcenters and one with the filter 0INFOPROV='basis DSO'.
  • Example P7: Query based on a HCPR which contains a SPO.

Report RSPP_PART_MAINTAIN

Unlike semantically partitioned objects, where the partition criteria are specified in the definition, in this case, you only define these later in table RSIPRORANGE. You can use the program RSPP_PART_MAINTAIN to create or change entries in this table. These entries control which characteristics are used with which criteria for pruning. See SAP Online Documentation Administering Pruning for MultiProviders

  • Example P5: Multiprovider with two Infocubes; Local Restrictions used for Pruning
  • Example P8: HCPR with two DSOs; Discussion of 'Time Derivation of Time Restrictions'

BWonHana

Hana Native Pruning 

In the context of BWonHana(BW4Hana) it is important to mention that the term Pruning is not only used in BW to preselect Infoproviders but also in Hana Calculation Views. This should not be confused, in general, both types can play a role when a BW query is executed. See the following examples

HCPR based on Hana View with Pruning Table

Pruning Example: HCPR based on ADSO and Hana Model

Examples
  • Example P8: report RSPP_PART_MAINTAIN - HCPR with two DSOs; Discussion of 'Time Derivation of Time Restrictions' 
  • Example P7: Query based on a HCPR which contains a SPO.

BW4/HANA

Overview

Pruning can take place on partprovider level and database level(regarding database partitions).

PartProvider Level

Pruning based on a restriction of metadata

  • Semantic Groups
  • ADSO where InfoObjects are restricted by so called 'Criterias'
  • Pruning based on the InfoProviders involved: report RSPP_PART_MAINTAIN

If you use the report RSPP_PART_MAINTAIN to define the restrictions, the system checks whether they comply with the data of the provider. If you load then new data into this provider, there is, in contrast to Semantics Groups and ADSO Criterias, no automatic check regarding this restriction.

Database Level

Pruning is done regarding Partitions(with Data Tiering Optimization DTO) defined for an ADSO. In case a time characteristic is used to define the partitions, time derivations(if possible) are carried out in case the filter of the query is defined for another time characteristic. See example below.

Examples

SAP Online Documentation

  • No labels