Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 269

7.9 Query Performance in Analysis Services

We now briefly describe how query performance can be enhanced in Analysis
Services through several techniques.
The first step must be to optimize cube and measure group design. For
this, many of the issues studied in this topic apply. For example, it is
suggested to use cascading attribute relationships, like Day

→ Month →

Quarter Year , and define user hierarchies of related attributes within

each dimension. These are called natural hierarchies. The reason for this is
that attributes participating in natural hierarchies are materialized on disk
and are automatically considered to be aggregation candidates. Redundant
relationships between attributes must be removed to assist the query
execution engine in generating an appropriate query plan. Also, the cube
space must be kept as small as possible, only including measure groups
that are needed. Measures that are queried together must be allocated to
the same measure group since if a query retrieves measures from multiple
measure groups, it will require multiple storage engine operations. Large
sets of measures that are not queried together must be placed into separate
measure groups. Large parent-child hierarchies must be avoided, because in
these hierarchies aggregations are created only for the key attribute and
the top attribute. Thus, queries asking for cells at intermediate levels are
calculated at query time and can be slow for large parent-child dimensions.
Many-to-many dimension performance must be optimized, since it requires a
run-time join between the data measure group and the intermediate measure
group. Also, if possible, the size of the intermediate fact table underlying the
intermediate measure group must be reduced.
Aggregations are also used by Analysis Services to enhance query per-
formance. Thus, the most ecient aggregations for the query workload
must be selected to reduce the number of records that the storage engine
needs to scan on disk to evaluate a query. When designing aggregations,
we must evaluate the benefits that aggregations provide when querying,
against the time it takes to create and refresh such aggregations. Moreover,
unnecessary aggregations can worsen query performance. A typical example
is the case when a summary table matches an unusual query. This can make
the summary table to be moved into the cache to be accessed faster. Since
this table will be rarely used afterwards, it can deallocate a more useful table
from the cache (which has a limited size), with the obvious negative effect on
query. In summary, we must avoid designing a large number of aggregations
since they may reduce query performance.
The Analysis Services aggregation design algorithm does not automatically
consider every attribute for aggregation. Consequently, we must check the

→