[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