[Mondrian] order of measures and hierarchize()

Paul Stoellberger p.stoellberger at gmail.com
Wed Jan 12 15:49:10 EST 2011

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:
go back to generating verbose mdx (including all members separately instead of using .Children / .Siblings / etc.) where no hierarchize() is necessary

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?


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