[Mondrian] Calculated Member SolveOrder&AggregateFunctionHandling

Thu May 22 16:28:37 EDT 2008

I think that the intent of this test was for the AS2005
"SCOPE_ISOLATION=CUBE" MDX property testing.  

Since Mondrian isn't supporting that property with this contribution, I
didn't spend the time figuring out the problem with that test case.  

It wasn't working for either value of SolveOrderMode.

________________________________

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Julian Hyde
Sent: Thursday, May 22, 2008 3:57 PM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Calculated Member
SolveOrder&AggregateFunctionHandling 

Submitted as change 11099. Thanks for the contribution, Timothy.

I made the default behavior scoped - i.e. compatible with AS 2005,
different than behavior in 3.0.3 and earlier. I don't expect many
applications to be affected, but the property
mondrian.rolap.SolveOrderMode exists if anyone needs backwards
compatibility.

Timothy, Can you explain why
_testOverrideOverCubeMemberHappensWithScopeIsolation is still disabled?

Julian

________________________________

	From: mondrian-bounces at pentaho.org
[mailto:mondrian-bounces at pentaho.org] On Behalf Of
timothy.lambert at thomsonreuters.com
	Sent: Wednesday, May 21, 2008 8:57 AM
	To: mondrian at pentaho.org
	Subject: RE: [Mondrian] Calculated Member Solve
Order&AggregateFunctionHandling 

	See my comments below preceded with ">>>"

________________________________

	From: mondrian-bounces at pentaho.org
[mailto:mondrian-bounces at pentaho.org] On Behalf Of Julian Hyde
	Sent: Monday, May 19, 2008 8:39 PM
	To: 'Mondrian developer mailing list'
	Subject: RE: [Mondrian] Calculated Member Solve Order
&AggregateFunctionHandling 

	Timothy,

	The change looks good, but you need to tie up a few loose ends
to be tied up before I can accept the contribution:

	1. The unit test cases need some work. I enabled the tests in
SolveOrderScopeIsolationTest, and most worked if I preceded them with a
check that SolveOrderMode==scoped. But
testOverrideOverCubeMemberHappensWithScopeIsolation still gives errors;
can't figure out whether it's to do with case sensitivity.

	>>> I think that the intent of this test was for the AS2005
"SCOPE_ISOLATION=CUBE" MDX property testing.  Since Mondrian isn't
supporting that property with this contribution, I didn't spend the time
figuring out the problem with this test case.  I left the test case in
but preceded the method with an underscore to prevent it from running.

	And, can you create some cases that test the behavior when
SolveOrderMode==absolute. This will be especially important when scoped
becomes the default behavior. Maybe make each test case into an 'if ...
else' with alternative outputs.

	>>> I implemented your suggestion of alternative output handling
when run with 'absolute'.

	2. The description of the property in mondrian.properties and
MondrianProperties.java needs a little more information, because the
average DBA would not know what SS2K behavior was (or SS2K was, for that
matter). The information in your email below would do just fine.

	>>> Done.

	3. Please put the same property information into
configuration.html.

	>>> Done.

	4. For future changes, please keep those lines to 80 characters,
and use <p> to delimit paragraphs in javadoc.

	>>> Will do.

	See attached a new change list with my modifications.

	>>> Attached are my new changes along with your additions.

	All,

	Any opinions what should be the default behavior? I'm inclined
to think that mondrian should use scoped solve-order (like Analysis
Services 2005) by default.

	Julian

________________________________

		From: mondrian-bounces at pentaho.org
[mailto:mondrian-bounces at pentaho.org] On Behalf Of Lambert, Timothy
		Sent: Monday, May 12, 2008 3:05 PM
		To: mondrian at pentaho.org
		Subject: [Mondrian] Calculated Member Solve Order &
Aggregate FunctionHandling 

		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://forums.pentaho.org/showthread.php?t=60654

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 more difficult.  There are (at least) two ways to
go about this.

		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/20080522/c61a05bb/attachment.html 