Cube-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 194

NULL only when both Store Sales and Store Cost are NULL . Therefore, you can assign the
{[Measures].[Store Sales], [Measures.[Store Cost]} set as the NON_EMPTY_BEHAVIOR
property for this calculated member.
Unfortunately, when the NON_EMPTY_BEHAVIOR property contains more than one member,
Analysis Services cannot use it while calculating cell values. Therefore, we recommend
that you use a single member in the NON_EMPTY_BEHAVIOR property if possible. For
example, if the Store Cost measure and the Store Sales measure belong to a single
measure group, their NON_EMPTY_BEHAVIOR will usually be the same. Therefore, instead of
assigning a set that contains two members to the NON_EMPTY_BEHAVIOR property, you can
assign a single member—for example, [Measures].[Store Sales] .
NOTE
Even though the NON_EMPTY_BEHAVIOR property is very powerful and can speed up your
query by an order of magnitude, be extremely careful when you use it. If you specify the
NON_EMPTY_BEHAVIOR property for a calculated member that does not always behave in
the same way as a real member, your query will produce unexpected results.
Analysis Services also uses the NON_EMPTY_BEHAVIOR property during the calculation of
cell values. If Analysis Services could detect that the cells with complex MDX expres-
sions assigned to them are empty when it calculates the results of an MDX query, it
could skip the calculation of those complex expressions and dramatically reduce the
time of query execution.
Assignments
Although calculated members extend the multidimensional space, Analysis Services
provides an assignment operator that you can use to overwrite the values of the existing
cells of the cube. You can assign a special formula, defined as an MDX expression, to a cell
or to a group of cells—a subcube .
The major difference between calculated members and cell calculations is the way that the
calculations affect aggregation behavior. Calculated members do not affect aggregations
and do not change the value of the cell referenced by the parent member. On the other
hand, if you use an assignment operator to change the value of a cell that corresponds to
a child member, the value that corresponds to its parent does change.
One example of a business use for assignment operators is in budgeting applications. For
example, assume that you plan to open new stores in Canada and want to predict the
sales you would get if the number of stores in Canada were to be five times fewer than in
the United States (see Figure 12.6). (We are taking a somewhat simplified approach and
assuming that our Canadian customers are going to love our FoodMart stores every bit as
much as our United States customers do.)