# [Mondrian] Calculated Member Solve Order & Aggregate Function Handling

timothy.lambert at thomsonreuters.com timothy.lambert at thomsonreuters.com
Mon May 12 15:05:17 EDT 2008

```Calculated Member Solve Order & Aggregate Function Handling

SSAS2005 vs. SSAS2000 vs. Mondrian

Background

This proposal was seeded by the R&D of others.

Following are links to some of that work...

http://777eisenhower.blogspot.com/2008/03/analysis-services-2000-vs-2005
.html

SOLVE_ORDER Calculated Member Property

Definition:  The order of evaluation (from highest to lowest solve
order) and calculation (from lowest to highest solve order) for
calculated members, custom members, custom rollup formulas, and
calculated cells in a single calculation pass of a multidimensional
cube. Solve order is used to determine formula precedence when
calculating values for cells in multidimensional cubes, but only within
a single calculation pass.

SSAS2000 Behavior:  The SOLVE_ORDER value is absolute regardless of
where it is defined; e.g. a query defined calculated member with a
SOLVE_ORDER of 1 always takes precedence over a cube defined value of 2.

SSAS2005 Behavior:  By default, cube calculated members are resolved
before any session scope calculated members, and session scope members
are resolved before any query defined calculation.  The SOLVE_ORDER
value only applies within the scope in which it was defined.

Achieving SSAS2000 Behavior on SSAS2005:  Using the SCOPE_ISOLATION=CUBE
property in a query calculated member definition will put the query
defined member into cube scope.  So effectively solve_order is treated
like an SSAS2000 absolute value since all members are treated as if they
were defined in the same scope.

Aggregate Function

Definition:  Returns a number that is calculated by aggregating over the
cells returned by the set expression.

The Aggregation function is designed to be used against base measures.

SSAS2000 Behavior:  The SOLVE_ORDER value must be manually applied such
that aggregate values are solved before other related calculated
members.

SSAS2005 Behavior:  The aggregate function is always applied to base
members; i.e. as if solve_order was defined to be the lowest value in a
given evaluation in a SSAS2000 sense.

Current Mondrian Behavior

In regard to solve_order and Aggregate function handling, Mondrian
behaves like SSAS2000.

During the evaluation of a given cell, if there are multiple calculated
members in the evaluation context then the member with the highest
solve_order is evaluated.  The other calculated members with lower
solve_order values stay within the evaluation context, and are evaluated
during the processing of the calculated member with the higher
solve_order.

Through this recursive process, the calculated members with lower
solve_order values get calculated before those with higher values.

Mondrian Issue

For use with certain reporting clients (e.g. Cognos), it is better for
Mondrian to behave like SSAS2005 rather than SSAS2000 in regard to solve
order and aggregate function evaluation.

Note that some reporting clients (e.g. Cognos) do not take advantage of
the SSAS2005 SCOPE_ISOLATION property, so support for it is not
considered critically important.

Proposed Mondrian Changes

High Level

The proposal involves enhancing Mondrian to optionally behave like
SSAS2005 rather than SSAS2000 in regard to solve order.

The option is controlled using the MondrianProperties entry
SolveOrderMode=absolute|scoped.  The current Mondrian solve order
behavior remains the default; 'absolute'.  The SSAS2005 behavior can be
turned on with the value of 'scoped'.  One can achieve SSAS2005
SCOPE_ISOLATION=CUBE semantics by using 'absolute' mode.

While preserving old behavior as a default is usually desirable, in this
case it is recommended that Mondrian adopt the SSAS2005 behavior as the
default.

The implementation of the proposal is based on the same sort of logic
one uses when manually applying the solve_order property to get the
desired SSAS2005-like behavior with current Mondrian.

The 'scoped' solve order mode code determines the highest solve order
based on...

-   If the calculated member is involved with an aggregate function.

Aggregation function based calculations are calculated first.

-   The location of the calculated member definition; i.e. its scope.

Cube scope members are calculated after Aggregate function based
calculations.

Query scope members are calculated after cube scope calculations.

-   The solve_order property.

This property is only used to order calculations within a given scope.

Code Changes

Attached to this email - the archive was built with the packChange
script.

The RolapEvaluator.peekCalcMember method now invokes one of two methods
for determining the highest solve order for two or more calculated
members.   One of these methods implements the "absolute" algorithm and
the other implements the "scoped" algorithm.

Determining cube and query scope is easy since there is an already
defined class method for determining if a member is defined in a query.
If it's not defined in the query then it must be defined in the cube.

Determining relative solve_order is also trivial since that property is
readily available for a given calculated member.

Determining if the member is part of an aggregate function is a little

In the proposed solution, there is a new RolapEvaluator method that
traverses the expression graph associated with a calculated member
explicitly looking for an aggregate function.

An alternate solution performed a full evaluation of the calculated
member in order to look for an aggregate function.  After the
evaluation, a flag was checked to see if an aggregate function was
found.

Unit Tests

A JUnit test (SolveOrderScopeIsolationTest) was recently contributed to
the Mondrian project.  These test cases expect AS2005 behavior and as
such fail with the current Mondrian.  So this test is not normally setup
to run with the regular regression suite.

When this test is enabled, the proposed implementation with the new
AS2005 solve order behavior passes all test cases.

Note there is one exception in which Mondrian fails regardless of
SolveOrderMode.  There seems to be an MDX syntax problem in one of the
test cases.

Also note there is one test case that passes for both the old and new
behavior.

There is another test case that passes for current Mondrian but fails
for the new solve order behavior.  The test case is
Mondrian.test.NamedSetTest.testOrderedNamedSet.  The test case
explicitly expects SSAS2000 behavior and as such should fail with the
new AS2005 behavior.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080512/2da7e2e2/attachment.html
```