Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 224

Building aggregations manually
However good the aggregation designs produced by the wizards are, it's very likely
that at some point you'll have to design aggregations manually for particular queries.
Even after running the Usage-Based Optimization Wizard you may find that it still
does not build some potentially useful aggregations. The algorithm the wizards
use is very complex and something of a black box, so for whatever reason (perhaps
because it thinks it would be too large) it may decide not to build an aggregation
that, when built manually, turns out to have a significant positive impact on the
performance of a particular query.
Before we can build aggregations manually we need to work out which aggregations
we need to build. To do this, we once again need to use Profiler and look at either
the Query Subcube or the Query Subcube Verbose events. These events, remember,
display the same thing in two different formats - requests for data made to the
Analysis Services storage engine during query processing - and the contents of
the Duration column in Profiler will show how long in milliseconds each of these
requests took. A good rule of thumb is that any Query Subcube event that takes
longer than half a second (500 ms) would benefit from having an aggregation built
for it; you can expect that a Query Subcube event that requests data at the same
granularity as an aggregation will execute almost instantaneously.
The following screenshot shows an example of trace on an MDX query that
takes 700 ms:
The single Query Subcube Verbose event is highlighted, and we can see that the
duration of this event is the same as that of the query itself so if we want to improve
the performance of the query we need to build an aggregation for this particular
request. Also, in the lower half of the screen we can see the contents of the TextData
column displayed. This shows a list of all the dimensions and attributes from which
data is being requested - the granularity of the request - and the simple rule to
follow here is that whenever you see anything other than a zero by an attribute we
know that the granularity of the request includes this attribute. We need to make a
note of all of the attributes, which have anything other than a zero next to them and
then build an aggregation using them; in this case it's just the Category attribute of
the Product dimension.