PDA

View Full Version : [Mondrian] bug in mdx function ORDER() ?



Paul Stoellberger
10-05-2010, 09:44 PM
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

Julian Hyde
10-05-2010, 09:54 PM
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 [mailto:paul.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/listinfo/olap4j-devel
>

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

Paul Stoellberger
10-05-2010, 10:05 PM
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 [mailto:paul.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/listinfo/olap4j-devel
>>
>

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

Paul Stoellberger
10-05-2010, 11:15 PM
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 [mailto:paul.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/listinfo/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

Julian Hyde
10-06-2010, 04:27 AM
> 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

Paul Stoellberger
10-06-2010, 08:54 AM
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

mibayer
10-06-2010, 09:29 AM
favor não enviar mais email

--------------------------------------------------
From: "Paul Stoellberger" <paul.stoellberger (AT) aschauer-edv (DOT) at>
Sent: Wednesday, October 06, 2010 8:54 AM
To: <jhyde (AT) pentaho (DOT) com>
Cc: <olap4j-devel (AT) lists (DOT) sourceforge.net>; "Mondrian developer mailing list"
<mondrian (AT) pentaho (DOT) org>
Subject: [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
>
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian