[Mondrian] proposal for Order function with key specification list
John V. Sichi
jsichi at gmail.com
Sat Sep 6 17:29:25 EDT 2008
Excellent, thanks. (Amusingly, the first review on Amazon for this book
starts with "The first edition of MDX Solutions is without a doubt the
Currently native NonEmptyCrossJoin and Filter (and maybe others) are out
of conformance, and native is enabled by default, but conformance can be
achieved by disabling native.
Julian Hyde wrote:
> 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