Solve Order Hijacking in Analysis Services 2000 and Mondrian

Here’s another example of cube SOLVE_ORDER settings causing issues when interacting with query solve order.

If a calculated member is defined in a query which references a cube calculated member, the solve order of the query member can override the other calculated member.

For example, with the following query [maleMinusFemale] is defined with a SOLVE_ORDER of 3000. It’s set this way to make sure it always has higher precedence than [ratio].

Gender.override wraps maleMinusFemale, however, and sets a SOLVE_ORDER lower than [ratio]. When evaluating gender.override, the SOLVE_ORDER of maleMinusFemale relative to other calculated members no longer matters, since gender.override SOLVE_ORDER is what is considered at the point of evaluation.

Members defined in the cube:

member gender.maleMinusFemale as 'gender.m - gender.f', SOLVE_ORDER=3000, FORMAT_STRING='#.##'

member measures.[ratio] as 'measures.[unit sales] / measures.[sales count]', SOLVE_ORDER=10

------------------------------------------

Query:

WITH

MEMBER gender.override as 'gender.maleMinusFemale', SOLVE_ORDER=5, FORMAT_STRING='#.##'

SELECT {measures.[ratio],
measures.[unit sales],
measures.[sales count]} on 0,
{gender.override, gender.maleMinusFemale} on 1
FROM sales

	Ratio	Unit Sales	Sales Count
override	10.72727	-236	-22
maleMinusFemale	-0.05	-236	-22

With SSAS 2005 the solve order cannot be “hijacked” in this way. Cube members are scoped separately and are evaluated first, unless explicitly overridden in the query (with ‘SCOPE_ISOLATION=CUBE’). The example above demonstrates that SSAS 2005 solve order scoping is more sophisticated than simply shifting cube member solve order lower than query members during evaluation, since problems like this could still happen if any interaction between cube and query solve order is permitted.