Diagnosis
When executing the query in the Business Explorer all of the fact tabledata, that is needed for all possible navigational steps in the query,is read in the main memory area of the OLAP processor at once. This cansometimes lead to very long run times in the query.
For a query, the OLAP processor can read the data in the fact table inone of three ways:
Reading all of the data
When executing the query in the Business Explorer all of the fact tabledata, that is needed for all possible navigational steps in the query,is read in the main memory area of the OLAP processor. Therefore, allnew navigational states are aggregated and calculated from the data ofthe main memory.
Reading the data on demand:
The OLAP processor only requests the corresponding fact table data thatis needed for each navigational state of the query in the BusinessExplorer. Therefore, new data is read for each navigational step. Themost suitable aggregate table is used and, if possible, alreadyaggregated on the database. The data for identical navigational statesyavigating is buffered in the OLAP processor.
Reading on demand when expanding the hierarchy:
When reading data on demand (2), the data for the entire - meaningcompletely expanded - hierarchy is requested for a hierarchy drilldown.For the read on demand when expanding the hierarchy (3), the data isaggregated by the database along the hierarchy and is sent to the startlevel of the hierarchy (highest node) in the OLAP processor. Whenexpanding a hierarchy node, the children of the node are thenrespectively read on demand.
In general, the reading of data on demand (2) provides much betterperformance than reading all the data (1). This read mode shouldespecially be considered for queries with many, free characteristics. Aquery that contains two or more free characteristics from differentdimensions (e.g. 'Customer' and 'Product'), will probably only beefficiently executable in this mode, as the aggregates can only beoptimally used when reading the data on demand.
For large hierarchies, aggregates should be created a middle level ofthe hierarchy and the start level of the query should be smaller or thesame as this aggregate level. For queries about such large hierarchies,the read on demand when expanding the hierarchy method (3) should beset.