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

Once you've done this you can either open up your Analysis Services client tool or
you can start running MDX queries in SQL Management Studio. When you do this
you'll notice that Profiler starts to show information about what Analysis Services is
doing internally to answer these queries.

Interpreting the results of a Profiler trace is a complex task and well outside the
scope of this topic, but it's very easy to pick out some useful information relating to
aggregation and partition usage. Put simply:

• The Query Subcube Verbose events represent individual requests for data

from the Formula Engine to the Storage Engine, which can be answered
either from cache, an aggregation or base-level partition data. Each of these
requests is at a single granularity, meaning that all of the data in the request
comes from a single distinct combination of attributes; we refer to these
granularities as 'subcubes'. The TextData column for this event shows the
granularity of data that is being requested in human readable form; the
Query Subcube event will display exactly the same data but in the less
friendly format that the Usage-Based Optimization Query Log uses.

• Pairs of Progress Report Begin and Progress Report End events show that

data is being read from disk, either from an aggregation or a partition. The
TextData column gives more information, including the name of the object
being read; however, if you have more than one object (for example, an
aggregation) with the same name, you need to look at the contents of the
ObjectPath column to see what object exactly is being queried.

• The Get Data From Aggregation event is fired when data is read from an

aggregation, in addition to any Progress Report events.

• The Duration column shows how long each of these operations takes

in milliseconds.

• The Resource Usage event is fired at the end of query execution and gives a

summary of the number of disk read operations for the query, the number of
rows scanned, and the number of rows returned by the Storage Engine.

At this point in the cube optimization process you should be seeing in Profiler
that when your users run queries they hit as few partitions as possible and hit
aggregations as often as possible. If you regularly see queries that scan all the
partitions in your cube or which do not use any aggregations at all, you should
consider going back to the beginning of the process and rethinking your partitioning
strategy and rerunning the aggregation design wizards. In a production system
many queries will be answered from cache and therefore be very quick, but you
should always try to optimize for the worst-case scenario of a query running on a
cold cache.