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

MDX calculation performance
Optimizing the performance of the Storage Engine is relatively straightforward;
you can diagnose performance problems easily and you only have two options -
partitioning and aggregation - to solve them. Optimizing the performance of the
Formula Engine is much more complicated because it requires knowledge of MDX,
diagnosing performance problems is difficult because the internal workings of the
Formula Engine are hard to follow, and solving the problem is reliant on knowing
tips and tricks that may change from service pack to service pack.
Diagnosing Formula Engine performance
problems
If you have a poorly performing query, and if you can rule out the Storage Engine as
the cause of the problem, the issue is with the Formula Engine. We've already seen
how we can use Profiler to check the performance of Query Subcube events, to see
which partitions are being hit and to check whether aggregations are being used; if
you subtract the sum of the durations of all the Query Subcube events (bear in mind
that some of these events may execute in parallel though) from the duration of the
query as a whole you'll get the amount of time spent in the Formula Engine.
Another hallmark of a query that spends most of its time in the Formula Engine is
that it will only use one CPU, even on a multiple-CPU server. This is because the
Formula Engine, unlike the Storage Engine, is single-threaded. As a result, if you
watch CPU usage in Task Manager while you run a query you can get a good idea of
what's happening internally; high usage of multiple CPUs indicates work is taking
place in the Storage Engine, while high usage of one CPU indicates work is taking
place in the Formula Engine.
Calculation performance tuning
Having worked out that the Formula Engine is the cause of a query's poor
performance then the next step is, obviously, to try to tune the query. In some cases,
you can achieve impressive performance gains (sometimes of several hundred
percent) simply by rewriting a query and the calculations it depends on; the problem
is knowing how to rewrite the MDX and working out which calculations contribute
most to the overall query duration. Unfortunately, Analysis Services doesn't give you
much information to use to solve this problem and there are very few tools out there
which can help either, so doing this is something of a black art.