[Mondrian] Re: Eigenbase perforce change 11758 for review
Julian Hyde
jhyde at pentaho.com
Tue Oct 28 20:02:43 EDT 2008
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
More information about the Mondrian
mailing list