[Mondrian] Re: Eigenbase perforce change 11758 for review

[Julian Hyde]

On Tue, 2008-10-28 at 14:46 -0700, Rushan Chen wrote:

> Hi Julian,
> 
> We have in fact discussed the exact same comment as yours regarding 
> CollationKey. The wiki section below is now expanded with some more details:
> http://pub.eigenbase.org/wiki/MondrianOrderFunctionExtension#Syntax_Options
> 
> In short, CollationKey is eventually not chosen because
> (1) Existing sort orders by several keys implicitly, which can not  be 
> replicated using CollationKey(or Order_Key as in your suggestion).


I think that you can sort by several keys simultaneously using ORDER.

Here is an example that sorts customers by the name of their state (but
not country), then by the name of their city, then by the amount of beer
they purchased in 2007.

ORDER(
  [Customer].MEMBERS, 
  BASC,
  Ancestor([Customer], [Customer].[State]).ORDER_KEY,
  BASC,
  Ancestor([Customer], [Customer].[City]).ORDER_KEY,
  BASC,
  ([Measures].[Unit Sales], [Product].[Beer], [Time].[2007]),
  BDESC)



Nation   State City    Name     Unit Sales
-------- ----- ------- -------- ----------
USA      AR            Fred           100
Russia   GA    Tblisi  Vladimir       200
USA      GA    Atlanta Bill           300
USA      GA    Savanah Eric           400
USA      GA    Savanah Jane           350


If someone wanted to sort customers by their city hierarchically (i.e.
city within state within nation) then they should be able to write

ORDER(
  [Customer].MEMBERS,
  BASC,
  Ancestor([Customer], [Customer].[City]).ORDINAL,
  ([Measures].[Unit Sales], [Product].[Beer], [Time].[2007]),
  BDESC)

For [USA].[GA].[Atlanta], ORDINAL would return the compound object
{"Atlanta", "GA", "USA"}.

Can you give me a counterexample that cannot be represented using ORDER
with multiple sort expressions, and properties .ORDER_KEY and .ORDINAL?


> (2) Hierarchical comparison can not be supported. Client will use 
> BASC/BDESC on all ancestor levels to mimic ASC/DESC.
> 

I agree that it doesn't make sense if the expressions have different
'break' behavior. The above example would be confusing if it was say
{BASC, ASC, DESC} instead of {BASC, BASC, BDESC}. The ORDER function
should give an error if ASC/DESC are mixed with BASC/BDESC.


> Regarding the MDX design principle of not evaluating member to (the 
> same) member, I think the violation can be avoided using 
> LevelExpressions(as in the very first proposal):
> 
> OrderSet(<set>, [Dimension].[level], ..., [Measures].[m1])


Yes, but it is simpler if all we ever sort on is expressions. I think I
have demonstrated that, with the new .ORDER_KEY and .ORDINAL properties,
expressions are sufficient for all sorting behavior.

Sorting by levels would be troublesome because they are not sufficiently
different from expressions. Although mondrian does not implicitly
convert levels into members (unlike hierarchies and dimensions), a lot
of people expect it to treat them like expressions. And I believe that
AS2005 does convert levels into expressions.

Julian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20081028/ebcd8eea/attachment.html