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

Block Computation versus Cell-by-Cell
When the Formula Engine has to evaluate an MDX expression for
a query it can do so in one of two basic ways. It can evaluate the
expression for each cell returned by the query, one at a time, an
evaluation mode known as 'cell-by-cell'; or it can try to analyze the
calculations required for the whole query and find situations where
the same calculation might be repeated for multiple cells and instead
do it only once, an evaluation mode known variously as 'block
computation' or 'bulk evaluation'. Block computation is only possible
in some situations, depending on how the code is written, but is often
many times more efficient than cell-by-cell mode. As a result, we
want to write MDX code in such a way that the Formula Engine can
use block computation as much as possible, and when we talk about
using 'efficient' MDX functions or constructs then this is what we in
fact mean. Given that different calculations in the same query, and
different expressions within the same calculation, can be evaluated
using block computation and cell-by-cell mode; that there's no way of
knowing which mode is used when; and that in some cases Analysis
Services can't use block mode anyway, it's difficult to tell whether we
are writing good MDX or not. One of the few indicators we have is the
Performance Monitor counter MDX\Total Cells Calculated , which
basically returns the number of cells in a query that were calculated in
cell-by-cell mode; if a change to your MDX increments this value by a
smaller amount than before, and the query runs faster, you're doing
something right.

A reasonably good guide for Analysis Services MDX best practices exists in the
Books Online topic "Performance Improvements for MDX in SQL Server 2008
Analysis Services", available online here: http://tinyurl.com/mdximp , although
some of the issues it mentions have already been dealt with at the time of writing in
the very latest builds of Analysis Services 2012. For example, the use of named sets
inside functions like Sum() or Aggregate no longer cause problems, as detailed here:
http://tinyurl.com/chrissets2012 . There are a few general rules that are worth
highlighting though:

• Don't use the Non_Empty_Behavior calculation property in Analysis Services

2012, unless you really know how to set it and are sure that it will provide a
performance benefit. It was widely misused with Analysis Services 2005 and
most of the work that went into the Formula Engine for Analysis Services
2008 was to ensure that it wouldn't need to be set for most calculations.
This is something that needs to be checked if you're migrating an Analysis
Services 2005 cube to a more recent version.