Based on some testing done against the foodmart database, I've come
across the following behavior in MSAS that appears anomalous. I'm
trying to understand the semantics of calculated members and solve
order when calculated member expressions in multiple dimensions
reference overlapping dimensions. In particular, the following 2
queries are illustrative...

Query 1:

with member [Measures].[cm] as '[Store Sales] + [Store Cost]',
solve_order = 2
member [Product].[m1] as 'min([Product].[Food].children, [Unit
Sales])', solve_order = 1
select {[Measures].[cm]} on columns,
{[Product].[m1]} on rows
from [Sales]

Query 1 result: 1 cell, value 3428

Query 2:

with member [Measures].[cm] as '[Store Sales] + [Store Cost]',
solve_order = 1
member [Product].[m1] as 'min([Product].[Food].children, [Unit
Sales])', solve_order = 2
select {[Measures].[cm]} on columns,
{[Product].[m1]} on rows
from [Sales]

Query 2 result: 1 cell, value 1714

The only difference between these two queries is in the SOLVE_ORDER of
the two calculated members

For query 1, the resulting value of the cell is 3428, exactly 2 times
the minimum [Unit Sales] value of the children of [Food]. Based on
this result, it appears that the expressions for [cm] and [m1] are
getting resolved by essentially replacing [Store Sales] and [Store
Cost] in [cm] expression by [m1], resulting in an expression that gets
evaluated like '[m1] + [m1]' (clearly, it is not as straightforward as
replacing [Store Sales] and [Store Cost] by [m1] in the expression,
since [Store Sales] and [Store Cost] imply an 'outer' context to the
[m1] expression during evaluation, but given this particular example
the net effect is the same). This theory is born out by switching the
expression for [cm] to be subtraction instead of addition ('[Store
Sales] - [Store Cost]'). In this case, the resulting value is 0. The
resolved expression appears to evaluate like '[m1] - [m1]'. Clearly,
in neither case does the resulting value of the cell reflect either the
[Store Sales] or [Store Cost] measures.

Query 2, however, seems to demonstrate different behavior. In
particular, the result of Query 2 is exactly the minimum [Unit Sales]
value of the children of [Food] (1714). This is true regardless of
whether or not [cm] contains addition or subtraction. Given the fact
that the answer does not change regardless of the [cm] definition, the
evaluation of the cell appears to effectively ignore [cm] altogether.
Given the behavior of Query 1, I would have expected the Query 2 cell
to evaluate to something equivalent to the following...

min([Product].[Food].children, [Store Sales] +/- [Store Cost])

However, the result is in terms of [Unit Sales], not either [Store
Sales] or [Store Cost].

My questions are the following...

What is the intended/designed behavior for these 2 cases?

Is MSAS providing expected/designed behavior for these 2 queries?

If this is the expected behavior for both queries, what are the rules
of expression resolution?

When multiple calculated member expressions contain overlapping
dimensions, what are the semantics for evaluating the result cell value?

The one with highest solve order takes precedence...or said differnetly
the one with the highest solve order is calculated last. You can
simplify your example for the purposes of understanding like this...

with
member [Measures].[cm] as '6', solve_order = 1
member [Product].[m1] as '5', solve_order = 2
select
{
[Measures].[cm]
} on columns,
{
[Product].[m1]
} on rows
from [Sales]

This ouputs a 5...if you swap the solve orders...you get 6.

If you search in Book On Line on Solve Order...the topic hit you get
will give a long and in depth explanation...on Solve Order...and to
further confuse you...another elment called Pass Order.

Thanks for the quick reply, but I understand the basics of solve_order.
The tricky bit in question is what happens when both calculated member
expressions reference different members of the same dimension (in this
case, [m1] references [Measures].[Unit Sales] while [cm] references
[Measures].[Store Sales] and [Measures].[Store Cost]). Your example
provided above does not address this question, since each calculated
member expression in that example consists solely of literals '5' and
'6' - these expressions do not reference any members explicitly, let
alone members from the same dimension.

Not sure what you mean by: "since [Store Sales] and [Store Cost] imply
an 'outer' context to the
[m1] expression during evaluation". But if you refer to the MDX
Solutions book, page 135, there are 6 cube calculation mechanisms
listed. And the first, intrinsic aggregation (as represented by
intrinsic measures like [Store Sales] and [Store Cost]), only applies if
there are no other MDX calculations applicable to that cell. Consider
this query, for which [Store Sales] and [Store Cost] both return 1714,
regardless of the solve order of [m1]:

member [Product].[m1] as 'min([Product].[Food].children,
[Unit Sales])'
select {[Measures].[Store Sales], [Store Cost]} on columns,
{[Product].[m1]} on rows
from [Sales]

So, in neither of the queries will the values of these intrinsic
measures be applied. In the first query, [cm] is calculated last, by
summing [Store Sales] and [Store Cost]. But each of these is calculated
using the [m1] expression, which returns 1714. In the second query, [m1]
is calculated last, and returns just 1714.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I think this might be getting me a little closer. However, I still
need a some clarification. I understand the first part of the last
paragraph...

"In the first query, [cm] is calculated last, by summing [Store Sales]
and [Store Cost]. But each of these is calculated using the [m1]
expression, which returns 1714."

And I understand the first part of the next sentence...

"In the second query, [m1] is calculated last"

....but the thing that is not clear to me is why the rest of that
sentence is not...

by computing an expression over [Unit Sales]. But [Unit Sales] is
calculated using the [cm] expression, which returns <some value other
than 1714>.

This is the confusing part to me. Why is the [cm] expression
calculated using [m1] in the first query (as is stated in the above
reply), yet the [m1] expression is not calculated using [cm] in the
second query?

The difference lies in the way MDX query context for dimensions works in
the 2 cases. [cm] does not explicitly specify the [Product] dimension,
so [m1] gets used as the [Product] context. But [m1] explicitly
specifies [Unit Sales] in the [Measures] dimension, so the [cm] context
gets overridden.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Ok, thanks Deepak, I think this explains it. But to be perfectly
clear, let me change the example slightly to see if what I now think is
happening actually is...

with member [Measures].[cm] as '[Store Sales] + [Store Cost]',
solve_order = 1
member [Product].[m1] as 'min([Product].[Food].children, [Unit Sales] +
[Time].[1997])', solve_order = 2
select {[Measures].[cm]} on columns,
{[Product].[m1]} on rows
from [Sales]

In this version, I've simply changed [m1] to reference '[Unit Sales] +
[Time].[1997]' instead of '[Unit Sales]' by itself. So, if my
understanding is correct, then the evaluation of this expression will
look something like...

min([Product].[Food].children, [Unit Sales] + ([Time].[1997], [cm]))

This would be because the [Unit Sales] tuple already contains a
measure, so it is not expanded with [cm], but since the other tuple in
the expression [Time].[1997] does not contain a measures member, [cm]
gets added to that tuple for the evaluation of this expression.

And this in turn would be equivalent to the following during
evaluation...

min([Product].[Food].children, [Unit Sales] + (([Time].[1997], [Store
Sales]) + ([Time].[1997], [Store Cost])))

Is all of this correct?

At first glance, sounds correct - should confirm this by examining the
detailed data in the Sales cube...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***