Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Re : [Mondrian] question on "circular" MDX

  1. #1
    michael bienstein Guest

    Default Re : [Mondrian] question on "circular" MDX

    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>

  2. #2
    John V. Sichi Guest

    Default Re: Re : [Mondrian] question on "circular" MDX

    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>
    >

  3. #3
    John V. Sichi Guest

    Default Re: Re : [Mondrian] question on "circular" MDX

    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>
    >>

  4. #4
    John V. Sichi Guest

    Default Re: Re : [Mondrian] question on "circular" MDX

    Here's a quasi-official-sounding statement:

    http://sqljunkies.com/WebLog/sqlbi/a.../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=blue]
    > 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=green][color=darkred]
    >>> ----- Message d'origine ----
    >>> De : John V. Sichi <jsichi (AT) gmail (DOT) com>
    >>>

  5. #5
    John V. Sichi Guest

    Default Re: Re : [Mondrian] question on "circular" MDX

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.