[Mondrian] order of measures and hierarchize()

Julian Hyde jhyde at pentaho.com
Wed Jan 12 15:30:18 EST 2011


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
> 




More information about the Mondrian mailing list