Example: HCPR based on ADSOs with non-trivial mappings
As explained in Hierarchy Node Restriction resolved into Leaves, in case node restrictions cannot be handled with the help of temporary tables, the performance of queries might be worse. In the following we discuss a case where node filters have to be resolved into leaves because of a non-trivial mapping of one characteristic with hierarchies. With non-trivial mapping we mean that the two characteristics are not identical and also do not refer to each other. If such a mapping exists for a certain partprovider, node restrictions(regarding any characteristic with hierarchies) are resolved for this partprovider. This restriction does not exists for BW4Hana systems(as of SP8).
HCPR with 3 ADSOs
You can see that the Infoobject STPE_CUST is mapped with STPECUST2 for the partprovider STPE_AJ09(regarding the other two ADSOs, the mapping is trivial: STPE_CUST→ STPE_CUST). STPECUST2 is technically defined the same way as STPE_CUST, however the two Infoobjects do not refer to each other.
The characteristic STPE_CUST has hierarchies:
Query with Node Restriction
The test query is very simple, important for our discussion is the following node filter:
We check the query in Transaction RSRT: Query Monitor. Before we run the query we can take a quick look at the so called n_sx_report which contains the query definition:
We are interested in the internal table PARTPROV and the field SVSUPPHIER:
You can see that for the partprovider STPE_AJ09(Provid 63978), the flag SVSUPPHIER is not set. This means that the Analytic Engine cannot take advantage of the usage of temporary hierrachy tables(see Hierarchy Node Restriction resolved into Leaves).
Now we run the query in Transaction RSRT: Query Monitor and use the debug option 'Display SQL/Hana Query'
We get three statements displayed(since the BW system is based on Hana, the HANA Analytics-API is used), one for each partprovider: The first two use temporary hierarchy tables as explained in Example II, the last one refers to the partprovider STPE_AJ09 and has the corresponding leaves(of the filter node) in the WHERE clause.
As discussed in Example II, in general this way of handling node filters is not optimal in terms of query performance.