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.

Analysis Services 2000 vs. 2005: SOLVE_ORDER

Analysis Services 2000

With Analysis Services 2000 the solve order of members defined within a query and the solve order of members in the cube are evaluated at the same time. This means that if you have a calculated member in an MDX query that has a lower solve order than a member defined in the cube then the query member would be evaluated first. For example, consider the following query.

WITH
MEMBER Time.Difference as '[Time].[1997].[Q2]-[Time].[1997].[Q1]', SOLVE_ORDER=10
SELECT
{Measures.[Store Sales Ratio]} on 0,
{Time.Difference, [Time].[1997].[Q2], [Time].[1997].[Q1]} on 1
FROM
Sales

Where [Store Sales Ratio] is defined as '[Measures].[Store Sales Net]/[Measures].[Store Sales]' in the cube and given a SOLVE_ORDER of 100.

	Store Sales Ratio
Difference	0.6
Q2	0.6
Q1	0.6

Looking at these results, you may expect Difference to evaluate to 0, since the Q2 value minus the Q2 difference should be subtracting the two ratios. Since the [Store Sales Ratio] solve order is higher, however, it is evaluated after the [Difference] calculation, i.e.

(( [Unit Sales], Q2 ) – ( [Unit Sales], Q1 ) )/ (( [Store Sales], Q2 ) – ( [Store Sales], Q1 ))

This is probably not what is desired, since you would expect a difference calculation to typically be evaluated on the final value of the measure, not on the components.

To get the desired value in AS2K, the only real option is to change either the cube or the query solve order. Unfortunately, if you’re using a client which automatically generates MDX changing the query may not be feasible. And there may be other forces which dictate particular solve order of cube members. On more than one occasion this has left us with no good solution.

Analysis Services 2005

With SSAS 2005 the notion of “scope” was added to solve order. The solve order of the cube is in its own scope, and is evaluated before any of the calculation in the query. So running the same query above in SSAS 2005 produces the expected result:

	Store Sales Ratio
Difference	0
Q2	0.6
Q1	0.6

For the occasions where you actually want to have evaluation interact with the cube solve order, you can specify “SCOPE_ISOLATION=CUBE” within the query members definition.

See http://msdn2.microsoft.com/en-us/library/ms144787.aspx for more details about scope isolation and when you would use it.

Aggregate() changes

A related change is related to the Aggregate() function. In AS2K if the Aggregate() function had a higher solve order than what it was evaluating it would throw an error. It did this because there is no generic way of determining how to aggregate over a calculated member. Aggregation is really something that applies to base measures.

The fact that Aggregate members were required to have lower solve order produced headaches in my environment, because some client tools will specify a higher solve order for a member which uses Aggregate(). To counteract this we would set cube members to have solve orders higher than what we would ever expect the client to choose, which was a hacky and unreliable alternative (and also produced some of the unexpected values mentioned earlier).

In SSAS2005, on the other hand, Aggregate() has been enhanced to always apply to the base components of a expression, regardless of solve order. This is exactly the sort of behavior we would expect, and greatly reduces the constraints around solve order assignment within the cube.