In the following we discuss some technical details of how hierarchy node restrictions are processed during query runtime. The key point is the fact that, in some cases the OLAP Engine needs to resolve a node restriction into a corresponding filter containing all leaves of this node. This in turn might have an negative impact on the query performance when the number of leaves is large.
- If possible, the OLAP Engine uses temporary hierarchy tables which store all the leafs of the nodes. These tables are then joined with the corresponding InfoProvider table. When the hierarchy is in the drilldown, the DataManager can return the records on 'node level' which minimizes volume of data transferred from the database to the OLAP Engine. See Example II and Example IV.
- If a node restriction is resolved into the leafs, the system might have to handle a large list of filter values. The handling depends on the Database, in both cases the data is returned on 'leaf level':
- In case BW is based on Hana and the query uses the Hana Analytics-API, the list of values are converted into the corresponding SID values and transferred to the database. See Example V.
- In case SQL is used(e.g. when BW is based on ORACLE), the DataManager uses again temporary tables (prefix /BI0/06; they contain the corresponding SIDs instead of the values) which are then joined with the corresponding Infoprovider table. See also note 1139396 and Example IV.
|When you face a performance issue for a query with an active hierarchy, check whether the query uses corresponding temporary hierarchy tables in the SQL/Analytics-API statements(as discussed in Example I). If not, go through the list of possible causes below and check whether the node resolve can be avoided by changing the provider or query design, respectively.|
When is a Node restriction resolved?
- If the active presentation hierarchy(in the drilldown) is different of the hierarchy used to define a filter. See Example III
- In case a hierarchy node is excluded, see Example IV.
- If the query has a global and local(restricted key figure) node restriction. See Example IX.
- In case a characteristic with an active hierarchy or node restriction is used in a user exit(virtual characteristic with any mode (RRKE_C_MODE)), the corresponding filters are always resolved.
- If the query does not use the Read Mode H(see Query Property: Read Mode), the hierarchy is read on the leave level
- The BW Provider has to support this feature:
- it is not possible to push down hierarchy filters (temporary 08 tables) if NULL values can occur. Basically, this means that there shouldn't be a left outer join and that all fields are associated with an InfoObject and the setting 'User confirms Referential Integrity' is active(more precisely: actually it is sufficient to activate the setting Ref. Integrity for all characteristics with hierarchies). See note 2271658 and Example VI for further details.
- if there is a non-trivial mapping of one characteristic with hierarchies regarding a certain partprovider, statements accessing this provider cannot use temporary hierarchy tables, see Example VII.
- Virtual Providers: If a virtual provider does not support hierarchy restrictions, the OLAP Engine has to resolve the node and transfers the list of leafs to the virtual provider. See e.g. also VC based on Function. In addition, in case Virtual Providers based on HANA Models are used, node restrictions are always resolved.
- ADSO: in case a characteristic with hierarchies in the ADSO has the setting Master Data Check = "Master data Check during Reporting" or "No Master Data Check/ No Reporting", then queries based on this ADSO have to resolve all node restrictions. However, if the ADSO is part of an HCPR, it is possible to take advantage of temporary tables. For details see Example VIII(usage of temporary key-based hierarchy tables). Please note that in general it is recommended to put ADSOs in HCPRs when you want to run BW queries on ADSOs.
- Open ODS View: optimized hierarchy handling is only possible when the property "CalcScenario used" is set to true which means that the Open ODS View supports HANA Optimized Query Execution. In addition, the pushdown of hierarchy aggregation is only supported if referential integrity is set for all associated InfoObjects that have hierarchies defined. See note 2036430 for further details.
- Example I: Global Hierarchy Node Filter (usage of 02/08 tmp tables)
- Example II: Characteristic with active Hierarchy in Drilldown (records transferred on 'node level' )
- Example III: Different Hierarchies used on the same Characteristic (node restriction resolved, performance)
- Example IV: Node restriction resolved because of Exclude (database ORACLE)
- Example V: Node restriction resolved because of Exclude (database HANA)
- Example VI: HCPR based on Hana View: discussion of setting 'User confirms Referential Integrity'
- Example VII: ADSO setting Master Data Check
- Example VIII: HCPR with non-trivial mapping of one characteristic with hierarchies
- Example IX: Query with global and local restrictions
- 2674007 BW Hierarchy: Resolve hierarchy restriction in list of leaves