Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: [Mondrian] bug in mdx function ORDER() ?

  1. #1
    Paul Stoellberger Guest

    Default [Mondrian] bug in mdx function ORDER() ?

    Hello,

    i'm trying to get my head around the Order() functionality in the olap4j query model... but something seems wrong which could be caused by mondrian.
    When I execute this MDX:

    select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    NON EMPTY Crossjoin({[Product].[All Products]}, Order( {[Order Status].[All Status Types],[Order Status].[All Status Types].Children}, [Order Status].CurrentMember.Name, DESC))
    ON ROWS
    from [SteelWheelsSales]

    The order status is order by name correctly!

    But when I remove the [Order Status].[All Status Types] from the set thats being ordered.... the result is wrong... its just like as if the Order() has no effect at all:

    select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    NON EMPTY Crossjoin({[Product].[All Products]}, Order( {[Order Status].[All Status Types].Children}, [Order Status].CurrentMember.Name, DESC))
    ON ROWS
    from [SteelWheelsSales]

    I looked at the SQL but there was no sign of ORDER BY status DESC ... that means mondrian must resolve that internally.

    Am I misunderstanding something here or is this a bug in mondrian?

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

  2. #2
    Julian Hyde Guest

    Default [Mondrian] RE: [Olap4j-devel] bug in mdx function ORDER() ?

    Paul,

    Does the query produce expected results if you use BDESC rather than DESC?

    Remember that with DESC (and ASC), the Order function will only re-order
    siblings within the same parent. You need to use BDESC (or BASC) to break
    the hierarchy.

    It's not surprising that you are not seeing SQL generated. Mondrian will
    convert a call to the Order function to SQL only in certain particular
    circumstances involving sorting by a cell value. Member name is, as far as
    mondrian is concerned, an 'arbitrary expression' and has to be done
    in-memory.

    Julian

    > -----Original Message-----
    > From: Paul Stoellberger [mailtoaul.stoellberger (AT) aschauer-edv (DOT) at]
    > Sent: Tuesday, October 05, 2010 5:45 PM
    > To: Mondrian developer mailing list;
    > olap4j-devel (AT) lists (DOT) sourceforge.net
    > Subject: [Olap4j-devel] bug in mdx function ORDER() ?
    >
    > Hello,
    >
    > i'm trying to get my head around the Order() functionality in
    > the olap4j query model... but something seems wrong which
    > could be caused by mondrian.
    > When I execute this MDX:
    >
    > select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    > NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    > {[Order Status].[All Status Types],[Order Status].[All Status
    > Types].Children}, [Order Status].CurrentMember.Name, DESC))
    > ON ROWS
    > from [SteelWheelsSales]
    >
    > The order status is order by name correctly!
    >
    > But when I remove the [Order Status].[All Status Types] from
    > the set thats being ordered.... the result is wrong... its
    > just like as if the Order() has no effect at all:
    >
    > select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    > NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    > {[Order Status].[All Status Types].Children}, [Order
    > Status].CurrentMember.Name, DESC))
    > ON ROWS
    > from [SteelWheelsSales]
    >
    > I looked at the SQL but there was no sign of ORDER BY status
    > DESC ... that means mondrian must resolve that internally.
    >
    > Am I misunderstanding something here or is this a bug in mondrian?
    >
    > - Paul
    > --------------------------------------------------------------
    > ----------------
    > Beautiful is writing same markup. Internet Explorer 9 supports
    > standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
    > Spend less time writing and rewriting code and more time
    > creating great
    > experiences on the web. Be a part of the beta today.
    > http://p.sf.net/sfu/beautyoftheweb
    > _______________________________________________
    > olap4j-devel mailing list
    > olap4j-devel (AT) lists (DOT) sourceforge.net
    > https://lists.sourceforge.net/lists/...o/olap4j-devel
    >


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

  3. #3
    Paul Stoellberger Guest

    Default [Mondrian] Re: [Olap4j-devel] bug in mdx function ORDER() ?

    No that doesn't change anything.

    If I include the [Order Status].[All Status Types] into the Set again, it works with BDESC too. So it's the same behaviour.

    Paul



    On 6 Oct 2010, at 01:54, Julian Hyde wrote:

    > Paul,
    >
    > Does the query produce expected results if you use BDESC rather than DESC?
    >
    > Remember that with DESC (and ASC), the Order function will only re-order
    > siblings within the same parent. You need to use BDESC (or BASC) to break
    > the hierarchy.
    >
    > It's not surprising that you are not seeing SQL generated. Mondrian will
    > convert a call to the Order function to SQL only in certain particular
    > circumstances involving sorting by a cell value. Member name is, as far as
    > mondrian is concerned, an 'arbitrary expression' and has to be done
    > in-memory.
    >
    > Julian
    >
    >> -----Original Message-----
    >> From: Paul Stoellberger [mailtoaul.stoellberger (AT) aschauer-edv (DOT) at]
    >> Sent: Tuesday, October 05, 2010 5:45 PM
    >> To: Mondrian developer mailing list;
    >> olap4j-devel (AT) lists (DOT) sourceforge.net
    >> Subject: [Olap4j-devel] bug in mdx function ORDER() ?
    >>
    >> Hello,
    >>
    >> i'm trying to get my head around the Order() functionality in
    >> the olap4j query model... but something seems wrong which
    >> could be caused by mondrian.
    >> When I execute this MDX:
    >>
    >> select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    >> NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    >> {[Order Status].[All Status Types],[Order Status].[All Status
    >> Types].Children}, [Order Status].CurrentMember.Name, DESC))
    >> ON ROWS
    >> from [SteelWheelsSales]
    >>
    >> The order status is order by name correctly!
    >>
    >> But when I remove the [Order Status].[All Status Types] from
    >> the set thats being ordered.... the result is wrong... its
    >> just like as if the Order() has no effect at all:
    >>
    >> select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    >> NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    >> {[Order Status].[All Status Types].Children}, [Order
    >> Status].CurrentMember.Name, DESC))
    >> ON ROWS
    >> from [SteelWheelsSales]
    >>
    >> I looked at the SQL but there was no sign of ORDER BY status
    >> DESC ... that means mondrian must resolve that internally.
    >>
    >> Am I misunderstanding something here or is this a bug in mondrian?
    >>
    >> - Paul
    >> --------------------------------------------------------------
    >> ----------------
    >> Beautiful is writing same markup. Internet Explorer 9 supports
    >> standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
    >> Spend less time writing and rewriting code and more time
    >> creating great
    >> experiences on the web. Be a part of the beta today.
    >> http://p.sf.net/sfu/beautyoftheweb
    >> _______________________________________________
    >> olap4j-devel mailing list
    >> olap4j-devel (AT) lists (DOT) sourceforge.net
    >> https://lists.sourceforge.net/lists/...o/olap4j-devel
    >>

    >


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

  4. #4
    Paul Stoellberger Guest

    Default Re: [Mondrian] Re: [Olap4j-devel] bug in mdx function ORDER() ?

    Just realised that i forgot to mention that this effect is only showing up when i do it in a crossjoin

    That means this query will work, although the all status types member is not included in the set:

    select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    NON EMPTY Order({[Order Status].[All Status Types].Children}, [Order Status].CurrentMember.Name, DESC) ON ROWS
    from [SteelWheelsSales]

    -paul


    On 6 Oct 2010, at 02:05, Paul Stoellberger wrote:

    > No that doesn't change anything.
    >
    > If I include the [Order Status].[All Status Types] into the Set again, it works with BDESC too. So it's the same behaviour.
    >
    > Paul
    >
    >
    >
    > On 6 Oct 2010, at 01:54, Julian Hyde wrote:
    >
    >> Paul,
    >>
    >> Does the query produce expected results if you use BDESC rather than DESC?
    >>
    >> Remember that with DESC (and ASC), the Order function will only re-order
    >> siblings within the same parent. You need to use BDESC (or BASC) to break
    >> the hierarchy.
    >>
    >> It's not surprising that you are not seeing SQL generated. Mondrian will
    >> convert a call to the Order function to SQL only in certain particular
    >> circumstances involving sorting by a cell value. Member name is, as far as
    >> mondrian is concerned, an 'arbitrary expression' and has to be done
    >> in-memory.
    >>
    >> Julian
    >>
    >>> -----Original Message-----
    >>> From: Paul Stoellberger [mailtoaul.stoellberger (AT) aschauer-edv (DOT) at]
    >>> Sent: Tuesday, October 05, 2010 5:45 PM
    >>> To: Mondrian developer mailing list;
    >>> olap4j-devel (AT) lists (DOT) sourceforge.net
    >>> Subject: [Olap4j-devel] bug in mdx function ORDER() ?
    >>>
    >>> Hello,
    >>>
    >>> i'm trying to get my head around the Order() functionality in
    >>> the olap4j query model... but something seems wrong which
    >>> could be caused by mondrian.
    >>> When I execute this MDX:
    >>>
    >>> select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    >>> NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    >>> {[Order Status].[All Status Types],[Order Status].[All Status
    >>> Types].Children}, [Order Status].CurrentMember.Name, DESC))
    >>> ON ROWS
    >>> from [SteelWheelsSales]
    >>>
    >>> The order status is order by name correctly!
    >>>
    >>> But when I remove the [Order Status].[All Status Types] from
    >>> the set thats being ordered.... the result is wrong... its
    >>> just like as if the Order() has no effect at all:
    >>>
    >>> select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
    >>> NON EMPTY Crossjoin({[Product].[All Products]}, Order(
    >>> {[Order Status].[All Status Types].Children}, [Order
    >>> Status].CurrentMember.Name, DESC))
    >>> ON ROWS
    >>> from [SteelWheelsSales]
    >>>
    >>> I looked at the SQL but there was no sign of ORDER BY status
    >>> DESC ... that means mondrian must resolve that internally.
    >>>
    >>> Am I misunderstanding something here or is this a bug in mondrian?
    >>>
    >>> - Paul
    >>> --------------------------------------------------------------
    >>> ----------------
    >>> Beautiful is writing same markup. Internet Explorer 9 supports
    >>> standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
    >>> Spend less time writing and rewriting code and more time
    >>> creating great
    >>> experiences on the web. Be a part of the beta today.
    >>> http://p.sf.net/sfu/beautyoftheweb
    >>> _______________________________________________
    >>> olap4j-devel mailing list
    >>> olap4j-devel (AT) lists (DOT) sourceforge.net
    >>> https://lists.sourceforge.net/lists/...o/olap4j-devel
    >>>

    >>

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


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

  5. #5
    Julian Hyde Guest

    Default [Mondrian] RE: [Olap4j-devel] bug in mdx function ORDER() ?

    > No that doesn't change anything.
    >
    > If I include the [Order Status].[All Status Types] into the
    > Set again, it works with BDESC too. So it's the same behaviour.


    Two more things to try. Does the problem go away if you remove NON EMPTY
    from the axis or set mondrian.native.nonempty.enable=false?

    Julian

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

  6. #6
    Paul Stoellberger Guest

    Default [Mondrian] Re: [Olap4j-devel] bug in mdx function ORDER() ?

    if i remove the NON EMPTY from the axis, the sort works

    mondrian.native.nonempty.enable=false is a default value in the biserver setup. and thats where i executed that mdx

    Paul



    On 6 Oct 2010, at 08:27, Julian Hyde wrote:

    >> No that doesn't change anything.
    >>
    >> If I include the [Order Status].[All Status Types] into the
    >> Set again, it works with BDESC too. So it's the same behaviour.

    >
    > Two more things to try. Does the problem go away if you remove NON EMPTY
    > from the axis or set mondrian.native.nonempty.enable=false?
    >
    > Julian
    >


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

  7. #7
    mibayer Guest

    Default Re: [Mondrian] Re: [Olap4j-devel] bug in mdx function ORDER() ?

    favor n

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.