[Mondrian] order of measures and hierarchize()

Paul Stoellberger p.stoellberger at gmail.com
Wed Jan 12 17:27:35 EST 2011 

I can't blame you for staying out of that discussion :-) It's not a fun one.... and very complex too.

As far as i know SQLPower use the query model as well, at least at a very basic level. Anyone else?

A new implementation of Hierarchize() is actually not a bad idea and probably the best way to provide this functionality for now without touching the query model too much.
Although I'm not too happy with breaking compatibility with other OLAP providers.

What effect does Hierachize()  have on named sets? Would it be an option to define a Set of Measures and use that in the query, or would this result in the same behaviour?

-Paul

On Jan 12, 2011, at 22:14 , Julian Hyde wrote:

> I'm staying out of the model discussion. I'm a provider, not a consumer. :)
> 
> Another option is to add a new builtin function to mondrian. Hierarchize2
> (anyone have a better name?) would organizes members so that they follow
> their parent, but siblings stay in their incoming order.
> 
> The olap4j model could generate Hierarchize2 against mondrian, and explicit
> lists against all other MDX engines.
> 
> Julian
> 
>> -----Original Message-----
>> From: Paul Stoellberger [mailto:p.stoellberger at gmail.com] 
>> Sent: Wednesday, January 12, 2011 12:49 PM
>> To: jhyde at pentaho.com; Mondrian developer mailing list; 
>> olap4j-devel at lists.sourceforge.net
>> Subject: Re: [Mondrian] order of measures and hierarchize()
>> 
>> Yeah, i know that Hierarchize() orders siblings as well.
>> Unfortunately I cannot get rid of Hierarchize() as it would 
>> break the drill functionality.
>> Jpivot used to include each members (child) separately, which 
>> produced the correct order but very verbose MDX.
>> Now it works the same as the olap4j query model, where on 
>> expand of a member all children are being added (with 
>> [Member].Children, potentially along with a crossjoin to 
>> enable a "drill" / expand of a member in a specific context. 
>> In olap4j this is being represented by the 
>> Selection.selectionContext (which is just a List<Selection>). 
>> 
>> olap4j automatically sets Hierarchize() if either a 
>> selectioncontext is present or more than 1 dimension is on 
>> the same axis, which is expected behaviour.
>> 
>> So I can only see only two options:
>> 1)
>> go back to generating verbose mdx (including all members 
>> separately instead of using .Children / .Siblings / etc.) 
>> where no hierarchize() is necessary
>> 
>> 2)
>> investigate how we could improve the query model to generate 
>> the mdx more intelligently so no hierarchize() is necessary 
>> (if that is even possible)
>> 
>> in a previous email i have already suggested that we have a 
>> closer look at the query model again.
>> Is there anyone out there except us using the query model? 
>> And/or is there anyone interested in joining forces to 
>> improve the query model with us?
>> 
>> -Paul
>> 
>> 
>> On Jan 12, 2011, at 20:30 , Julian Hyde wrote:
>> 
>>> According to the spec, Hierarchize not only organizes members into
>>> hierarchies (making sure that a member occurs after all of 
>> its ancestors)
>>> but also sorts sibling members. The [Measures] dimension, like all
>>> dimensions, has a natural order, therefore Hierarchize 
>> should sort measures.
>>> 
>>> Mondrian's implementation of Hierarchize has to comply with 
>> that spec.
>>> 
>>> So the question is whether the UI should be generating a call to
>>> hierarchize. Instead of
>>> 
>>> Hierarchize(
>>> Union(
>>>   {([Measures].[Sales], [Time].[All Years])},
>>>   Union(
>>>     Crossjoin(
>>>       {[Measures].[Quantity]},
>>>       {[Time].[All Years]}),
>>>     Crossjoin(
>>>       {[Measures].[Quantity]},
>>>       [Time].[All Years].Children))))
>>> 
>>> could you instead generate without the 'Hierarchize'? I 
>> think it would
>>> produce the order you want. The Union and Crossjoin 
>> operators (and all MDX
>>> operators except Unorder) produce output in a deterministic order.
>>> 
>>> In particular, notice that <Level>.Members and 
>> <Hierarchy>.Members produce
>>> rows in natural order.
>>> 
>>> Somewhat related: I realized recently that mondrian's Order 
>> operator is
>>> doing too much sorting. If two members have the same value, mondrian
>>> currently sorts them into hierarchical order; it should 
>> just leave them in
>>> the same order that they came in. It does not make a difference for
>>> 
>>> Order(
>>>   Customer.Members,
>>>   Measures.[Unit Sales])
>>> 
>>> since Customer.Members will be in natural order Order is 
>> applied. But it
>>> will make a difference to
>>> 
>>> Order(
>>> Order(
>>>   Customer.Members,
>>>   Measures.[Store Sales]),
>>> [Measures].[Unit Sales])
>>> 
>>> The effect of this should be a compound sort key (like 
>> 'ORDER BY unit_sales,
>>> store_sales' in SQL) but at present Mondrian will wipe out 
>> the previous
>>> ordering.
>>> 
>>> Julian
>>> 
>>>> -----Original Message-----
>>>> From: mondrian-bounces at pentaho.org 
>>>> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Paul 
>> Stoellberger
>>>> Sent: Tuesday, January 11, 2011 1:46 PM
>>>> To: olap4j-devel at lists.sourceforge.net; Mondrian developer 
>>>> mailing list
>>>> Subject: [Mondrian] order of measures and hierarchize()
>>>> 
>>>> Hello,
>>>> 
>>>> the olap4j query model and since a while jpivot as well use 
>>>> hierarchize() in combination with .Children to drill position 
>>>> if more than one dimension is on the same axis.
>>>> 
>>>> While with normal dimension i would expect that Hierarchize() 
>>>> defines the order of members appearing in the result, but for 
>>>> Measures this is not so much expected.
>>>> 
>>>> So a sample query (from jpivot) would look like:
>>>> select NON EMPTY Hierarchize(Union({([Measures].[Sales], 
>>>> [Time].[All Years])}, 
>>>> Union(Crossjoin({[Measures].[Quantity]}, {[Time].[All 
>>>> Years]}), Crossjoin({[Measures].[Quantity]}, [Time].[All 
>>>> Years].Children)))) ON COLUMNS,
>>>> NON EMPTY Hierarchize(Union({[Markets].[All Markets]}, 
>>>> [Markets].[All Markets].Children)) ON ROWS
>>>> from [SteelWheelsSales]
>>>> 
>>>> I have selected Sales to be the first measure on the columns 
>>>> axis (in the jpivot OLAP navigator i specifically set the 
>>>> order of measures) but Hierarchize() is breaking that order 
>>>> and selecting Quantity as the first Measure.
>>>> In saiku (previously known as PAT) we are enabling users as 
>>>> well to define the order of Measures selected (through olap4j 
>>>> query model) but obviously we are facing the same problem there.
>>>> 
>>>> As far as I know  Mondrian considers the correct order of 
>>>> Measures to be as they are defined in the schema. So in this 
>>>> case Quantity is defined first in the 
>>>> steelwheels.mondrian.xml, thats why it appears first.
>>>> 
>>>> Would you consider this behaviour to be a bug?
>>>> If this is expected behaviour, is there any way we can 
>>>> circumvent it somehow?
>>>> 
>>>> -Paul 
>>>> 
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>> 
>>> 
>>> _______________________________________________
>>> Mondrian mailing list
>>> Mondrian at pentaho.org
>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>> 
>> 
>

More information about the Mondrian mailing list