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.
