PDA

View Full Version : Re : [Mondrian] question on "circular" MDX



michael bienstein
02-04-2007, 06:00 AM
WITH SET ... is a named set. Named sets are evaluated in the default context - no slicer at all.

Michael

----- Message d'origine ----
De : John V. Sichi <jsichi (AT) gmail (DOT) com>

John V. Sichi
02-04-2007, 03:25 PM
OK, good, that matches the code in NamedSetExpr, and what I see in SQL
trace.

Then the stack overflow in the non-native case must be due to some bug
with juggling the calculated members. I'll log it.

JVS

michael bienstein wrote:[color=blue]
> WITH SET ... is a named set. Named sets are evaluated in the default
> context - no slicer at all.
>
> Michael
>
> ----- Message d'origine ----
> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
>

John V. Sichi
05-04-2007, 02:51 AM
John V. Sichi wrote:
> OK, good, that matches the code in NamedSetExpr, and what I see in SQL
> trace.
>
> Then the stack overflow in the non-native case must be due to some bug
> with juggling the calculated members. I'll log it.
>
> JVS
>
> michael bienstein wrote:
>> WITH SET ... is a named set. Named sets are evaluated in the default
>> context - no slicer at all.

Well, turns out nothing is ever that simple.

Michael's statement above doesn't match MSAS 2000 behavior. Run this query:

with
set [c] as '[Product].[All Product].[Drinks].[Flavored Drinks].children'
set [p] as '{[Store].[All Store].[USA].[CA].children}'
set [cp] as 'nonemptycrossjoin([c],[p])'
select [cp] on columns
from sales
where ([Time].[1998])

Notice that the result includes a tuple for (Skinner, San Francisco).
Now change the slicer to where ([Time].[1997]), and notice that this
tuple disappears. This is inconsistent with the named set evaluation
being slicer-independent (since membership in the named set is what
should determine whether a tuple shows up on the axis, not whether it is
empty/nonempty with respect to the slicer).

So what are the correct semantics? Mondrian currently can't make up its
mind. Run this query via cmdrunner with default settings (native
crossjoin enabled):

with
set [c] as [Product].[All Products].[Drink].children
set [p] as {[Gender].[M]}
set [cp] as nonemptycrossjoin([c],[p])
select [cp] on rows
from sales
where ([Time].[1998]);

You will get back an empty rows axis (slicer-dependent behavior). Then
disable native crossjoin and run again, and you'll get back three tuples
on the rows axis (slicer-independent behavior).

What is the "correct" behavior?

JVS
[color=blue][color=green]
>> ----- Message d'origine ----
>> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
>>

John V. Sichi
05-04-2007, 05:11 PM
Here's a quasi-official-sounding statement:

http://sqljunkies.com/WebLog/sqlbi/archive/2006/12/24/26337.aspx

It says that named sets defined in queries should be slicer-dependent,
meaning:

1) Current behavior for non-native evaluation is incorrect (since it's
coming out as slicer-independent, even though in the circular case it
still manages to loop as if there were a dependency).

2) We need a resolution on the correct semantics for the original
circular case. Julian reported that MSAS 2000 did not reject the query,
so the query processing it implements requires investigation and
comparison with the current Mondrian native evaluation behavior, where
the calculated members on the slicer are ignored in the NECJ.

JVS

John V. Sichi wrote:
> John V. Sichi wrote:
>> OK, good, that matches the code in NamedSetExpr, and what I see in SQL
>> trace.
>>
>> Then the stack overflow in the non-native case must be due to some bug
>> with juggling the calculated members. I'll log it.
>>
>> JVS
>>
>> michael bienstein wrote:[color=darkred]
>>> WITH SET ... is a named set. Named sets are evaluated in the default
>>> context - no slicer at all.
>
> Well, turns out nothing is ever that simple.
>
> Michael's statement above doesn't match MSAS 2000 behavior. Run this
> query:
>
> with
> set [c] as '[Product].[All Product].[Drinks].[Flavored Drinks].children'
> set [p] as '{[Store].[All Store].[USA].[CA].children}'
> set [cp] as 'nonemptycrossjoin([c],[p])'
> select [cp] on columns
> from sales
> where ([Time].[1998])
>
> Notice that the result includes a tuple for (Skinner, San Francisco).
> Now change the slicer to where ([Time].[1997]), and notice that this
> tuple disappears. This is inconsistent with the named set evaluation
> being slicer-independent (since membership in the named set is what
> should determine whether a tuple shows up on the axis, not whether it is
> empty/nonempty with respect to the slicer).
>
> So what are the correct semantics? Mondrian currently can't make up its
> mind. Run this query via cmdrunner with default settings (native
> crossjoin enabled):
>
> with
> set [c] as [Product].[All Products].[Drink].children
> set [p] as {[Gender].[M]}
> set [cp] as nonemptycrossjoin([c],[p])
> select [cp] on rows
> from sales
> where ([Time].[1998]);
>
> You will get back an empty rows axis (slicer-dependent behavior). Then
> disable native crossjoin and run again, and you'll get back three tuples
> on the rows axis (slicer-independent behavior).
>
> What is the "correct" behavior?
>
> JVS
> [color=green][color=darkred]
>>> ----- Message d'origine ----
>>> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
>>>

John V. Sichi
05-04-2007, 07:57 PM
John V. Sichi wrote:
> Michael's statement above doesn't match MSAS 2000 behavior. Run this
> query:
>
> with
> set [c] as '[Product].[All Product].[Drinks].[Flavored Drinks].children'
> set [p] as '{[Store].[All Store].[USA].[CA].children}'
> set [cp] as 'nonemptycrossjoin([c],[p])'
> select [cp] on columns
> from sales
> where ([Time].[1998])

Note: I was accidentally using a modified copy of FoodMart for this
test, which is why the metadata and data don't match the normal
FoodMart. You should instead be able to use the Mondrian queries I sent
out against normal FoodMart in MSAS 2000 to verify the slicer-dependency
of the named set evaluation (it should match Mondrian native results).

JVS
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian