PDA

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



michael bienstein
02-04-2007, 04:34 PM
Julian,

Check out Mosha's blog entry on why he thinks NonEmptyCrossJoin is an evil function:
http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
He mentions Mondrian in there too.

Michael








___________________________________________________________________________
D

Julian Hyde
05-07-2007, 07:56 PM
John,

I don't particularly care what the semantics are, as long as we adhere
to the standard semantics. So I'm glad that you have found the
definitive scripture on the issue.

Can you log a bug for #1: fix evaluation of calculated sets to use
slicer-dependent semantics.

Is it possible to resolve #2 before the bug is fixed?

All,

Let me know if changing to the correct semantics will break you.

Julian

> -----Original Message-----
> From: mondrian-bounces (AT) pentaho (DOT) org
> [mailto:mondrian-bounces (AT) pentaho (DOT) org] On Behalf Of John V. Sichi
> Sent: Friday, May 04, 2007 2:08 PM
> To: John V. Sichi
> Cc: Mondrian developer mailing list
> Subject: Re: Re : [Mondrian] question on "circular" MDX
>
> 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:[color=darkred]
> >> 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[color=green]
> > on the rows axis (slicer-independent behavior).
> >
> > What is the "correct" behavior?
> >
> > JVS
> > [color=darkred]
> >>> ----- Message d'origine ----
> >>> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
> >>>

John V. Sichi
05-08-2007, 02:40 AM
Julian Hyde wrote:
> John,
>
> I don't particularly care what the semantics are, as long as we adhere
> to the standard semantics. So I'm glad that you have found the
> definitive scripture on the issue.
>
> Can you log a bug for #1: fix evaluation of calculated sets to use
> slicer-dependent semantics.
Done:
http://sourceforge.net/tracker/index.php?func=detail&aid=1714738&group_id=35302&atid=414613

> Is it possible to resolve #2 before the bug is fixed?
Yes, Rushan and I will poke around with MSAS some more to see if we can
pin it down.

> All,
>
> Let me know if changing to the correct semantics will break you.

We've implemented a temporary workaround, so we'll wait a bit to hear
back from others.

> Julian
> [color=green]
>> -----Original Message-----
>> From: mondrian-bounces (AT) pentaho (DOT) org
>> [mailto:mondrian-bounces (AT) pentaho (DOT) org] On Behalf Of John V. Sichi
>> Sent: Friday, May 04, 2007 2:08 PM
>> To: John V. Sichi
>> Cc: Mondrian developer mailing list
>> Subject: Re: Re : [Mondrian] question on "circular" MDX
>>
>> 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:[color=darkred]
>>> 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[color=darkred]
>>> on the rows axis (slicer-independent behavior).
>>>
>>> What is the "correct" behavior?
>>>
>>> JVS
>>>
>>>>> ----- Message d'origine ----
>>>>> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
>>>>>