Overview

In the following we discuss the effect of a pruning configuration table assigned to a Hana Model (Calculation View) used in a HCPR. First we need to stress the fact that the term Pruning is also used in BW to preselect InfoProviders. This should not be confused with the pruning defined for a Hana calculation view. In general, both types can play a role when a BW query is executed.

• BW Pruning: If a HCPR/MultiProvider consists of many InfoProviders, it is useful to use 'pruning' for this Provider. The query workload is reduced by checks to determine whether an PartProvider contains any data for the selection range of the query. PartProviders that are not relevant are then not queried at all. The checks are already done in the OLAP Engine (in ABAP on the application server), for more details see MultiProvider:Pruning
• Hana Native Pruning: It is possible to assign a pruning configuration table to a calculation view which contains more projections in a Union Node. The handling of the pruning is done on the Hana database. See Hana Platform 2.0: Modling Guide 7.5.2.1 Prune Data in Union Nodes

Example

Hana Calculation View with a union of two tables:

The view uses a pruning table

with the following content:

So,

• table STPE_TAB2(Projection_2) is supposed to contain only the value M002 for the field STPE_MAT
• table STPE_TAB1(Projection_1) is supposed to contain only the values M003 and M004 for the field STPE_MAT

When we check the content of the tables we get the following result:

We can see that the content of the tables do not comply with the rules defined in the pruning table. In general, this is not recommended since the result of queries then depend on whether pruning was caried out or not. In this simple example it helps a bit to better understand how pruning works.

We run the following statement in the Hana Studio

The result when using the filter STPE_MAT = M002 is 1(and not 11). The reason is that, because of pruning only the table STPE_TAB2 was read.

The calculation view also uses the following execution hint in order to guarantee that pruning is carried out as expected:

The hint ce2qo_disable_unfolding = 1 means that the query is forced to be executed by the calculation engine (and not sql engine). More details to this parameter can be found in note 2291812.

We take a look at the planviz trace:

Pruning table STPE_PRUNING_2 is read and as a consequence only table STPE_TAB2 is accessed:

BW Query defined on HCPR based on Hana Calculation View

We create an HCPR based on the calculation view STPE:CALCVIEW_14 from above and a simple query which is again restricted to the M002:

 The value is the same as we got by running an SQL statement on the calculation view. Hence, the pruning on the database was done for the BW query as well.

When we remove the filter we get the sum for each key figure over the two tables, pruning does not take place:

Now the value for M002 is 11!

In RSRT it is possible to create an planviz trace, see PlanViz Trace generated in RSRT

This statement can then be used in the Hana Studio to create an Execution Trace, basically it looks the same as above: only the table STPE_TAB2 is read.