[Mondrian] proposal for Order function with key specification list
jhyde at pentaho.com
Sat Sep 6 16:53:22 EDT 2008
I did some more reading.
I just read "MDX Solutions" by George Spofford, and he says explicitly that
several functions preserve order, including <Hierarchy>.Members, Distinct,
Except, Filter, Head, Union.
Spofford says that Generate "iterates over each tuple in set1, and for each
element in set1, it puts every element specified by set2 into the result
set". Not quite watertight, but given that MDX 'sets' are ordered, I think
he means that ordering is preserved.
Regarding Crossjoin, Spofford is not explicit, but Microsoft is explicit in
both AS2000 and AS2008 http://msdn.microsoft.com/en-us/library/ms144816.aspx
: "The order of tuples in the resulting set depends on the order of the sets
to be joined and the order of their members".
Spofford does not say that the Order function is stable. "Note that there is
no explicit way to sort a set based on more than one criterion. For example,
you want to sort a set based primarily on a string member property and
secondarily on a numerical value, no good way is available for specifying
this." He's a smart guy, so if he thought Order was supposed to be stable,
he would have suggested nested calls to Order.
However, the AS2000 online doc
http://msdn.microsoft.com/en-us/library/aa178200.aspx says that Order is
stable: "When the input set has two elements for which the <String
Expression> or <Numeric Expression> has the same value, the input order is
Sadly the AS2005 and AS2008 doc is not so explicit about the behavior of
It might be that Microsoft is backing off specifying ordering in order to
make MDX easier to implement by a SQL DBMS. That's a shame, because MDX is
intended for display of data, so ordering is more important than for SQL.
People have written apps assuming the implicit ordering properties, and they
will be justifiably annoyed if the behavior changes.
Regardless of what Microsoft's de facto MDX standard says, I am going to
declare that mondrian functions which return sets preserve order in the
obvious way. If 'obvious' is not obvious, let's discuss in these forums.
This may mean that certain operations are less efficient than they could be.
In particular, the hash-based implementation of exists that you spoke of
would not be valid under these rules. Its use would be valid only if the
result was surrounded by a call to Unorder() or by a call to Order() which
fully specified the order. The Unorder() function is not currently
> -----Original Message-----
> From: Julian Hyde [mailto:jhyde at pentaho.com]
> Sent: Friday, September 05, 2008 2:55 PM
> To: 'John V. Sichi'; 'Mondrian developer mailing list'
> Cc: 'Rushan Chen'
> Subject: RE: [Mondrian] proposal for Order function with key
> specification list
> > Regarding the discussion on input/output order stability
> > below, Rushan
> > was also asking about other functions such as Generate and
> > Exists (not
> > just Order). The MS docs are silent for these functions,
> > implying that
> > MDX query authors should not make any assumptions about the result
> > order. This also implies that implementations are free to use
> > optimizations which might destroy order (for example, Zelaine has
> > prototyped a HashMap-based implementation of Exists).
> Not so fast. You, a SQL guy, might infer that order is
> unspecified, but a lisp guy (or girl) would not. They don't
> specify that <Hierarchy>.Members returns the members in
> order, but every MDX user assumes that.
> The Microsoft online documentation is not canon. Actually,
> there is no canon for MDX. I would seek a second opinion from
> one of the better MDX books (e.g. George Spofford, Mosha
> Pasumansky) and see what they say about the ordering of
> various functions.
> And the fact that they introduced Unorder is an indication
> that elsewhere in MDX, order matters.
More information about the Mondrian