[Mondrian] proposal for Order function with key specification list

[Rushan Chen]

Hi Julian,

Yes to do what was proposed in my previous email was really hard with 
existing MDX; and that too comes with the poor performance of 
multi-passes at sorting.

Using member expression is a good idea and in fact works better with the 
parser. However, member ordinal might not be as reliable as member order 
key which will be used if CompareSiblingsByOrderKey is set.

I added some examples here, together with the updated syntax:
http://pub.eigenbase.org/wiki/MondrianOrderFunctionExtension

There is also a note on order preservedness (after the MDX examples) of 
set functions. Currently there is no guarantee from Mondrian, although 
the implementations can be order preserving. MDX does not have a way to 
"hint" this property. I would like to know if any other Mondrian 
applications depend on this property for set functions(Generate and 
Exists to start with).  If that is the case, then perhaps this can be 
formalized in documentation.

Thanks,

Rushan

Julian Hyde wrote:
> I could support that change. I am not usually support extensions to MDX, but
> this seems to be backwards compatible with SSAS and it is functionality
> difficult to provide otherwise.
>
> Like Zelaine, I would like to see some examples. 
>
> I am not sure that we should allow sort by <Level>, because that can be
> coerced into an expression. Is it not sufficient to sort by a member
> expression, such as <Hierarchy>.CurrentMember or perhaps
> Ancestor(<Hierarchy>.CurrentMember, <Level>)? Members ought to collate by
> ordinal, if they do not already.
>
> Julian
>
>   
>> -----Original Message-----
>> From: mondrian-bounces at pentaho.org 
>> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Rushan Chen
>> Sent: Tuesday, September 02, 2008 12:08 AM
>> To: Mondrian developer mailing list
>> Subject: [Mondrian] proposal for Order function with key 
>> specification list
>>
>> Mondrian implements the standard ORDER function as specified here:
>> http://msdn.microsoft.com/en-us/library/ms145587.aspx
>>
>> It allows sorting of a set using one sort order based on a  value or 
>> string expression for each cell.
>>
>> In our client, we would like to be able to sort a set using 
>> not just the 
>> value_expression for a cell but also the level coordinates, 
>> similar to 
>> the SQL order by clause. For MDX, it looks something like this:
>>
>> ORDER(<set> {, <key_specification>}...)
>> <key_specification> ::= {<level>|<value_expression>} [, ASC | DESC | 
>> BASC | BDESC]
>> <value_expression> ::= <numeric_value_expression> | 
>> <string_value_expression>
>>
>> If <level> is specified, cells are compared using the ordinal column 
>> value (if CompareSiblingsByOrderKey is set) for that level.
>>
>> I would like to know if this is something useful to everyone, and 
>> whether for this it makes sense to extend Mondrian MDX beyond 
>> standard MDX.
>>
>> Thanks!
>>
>> -- 
>> Rushan Chen
>>
>>
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>>
>>     
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>   


-- 
Rushan Chen

www.lucidera.com<http://www.lucidera.com/>

Gartner: LucidEra is a business intelligence "Cool Vendor
<http://www.lucidera.com/company/press_releases/cool_Vendor_leading_analyst_firm_9apr.php>

Beagle Research: LucidEra is a CRM WizKid
<http://www.lucidera.com/company/press_releases/lucidera_crm_wizkid_awards_26mar.php>

JMP Securities: LucidEra is in the Hot 100
<http://www.lucidera.com/company/press_releases/jmp_hot_100_best_apr2.php>

-------------------------------------------------------------------------------
This message and any files or text attached to it are intended only for
the recipients named above, and contain information that may be
confidential or privileged. If you are not an intended recipient, or you
have reason to believe that you received this message in error, you must
not read, copy, use or disclose this communication. Please also notify
the sender by replying to this message, and then delete all copies of it
from your system. Thank you.