[Mondrian] SOLVE_ORDER: AS2K vs. SSAS2005

[Benny Chow]

If the query in your blog does to evaluate the query calculated member with solve order 10 before the schema defined member with solve order 0, then looks like a problem with AS2K.  Are you sure the results are correct?  I ran your query in Mondrian and got 0 for the difference cell value which is the expected behavior when you evaluate the difference calculated member first.  

Benny

 

________________________________

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Matt Campbell
Sent: Friday, March 28, 2008 4:30 AM
To: Mondrian developer mailing list
Subject: [Mondrian] SOLVE_ORDER: AS2K vs. SSAS2005


I've had a persistent headache over the past several months trying to deal with solve order related issues.  We have limited control over the MDX generated by our reporting tool (Cognos), which means that in some cases the solve order of calculated members in the query is incorrect relative to the solve order of cube members. 

While this is a problem with Cognos, it is really a general issue with any reporting client.  Since the client can't really guess what the solve order of measures in the cube is, let alone what the user intends with a particular calculation, there is always the possibility of conflict.  A tool can allow specifying absolute solve order, but that means the user creating the report needs to know and understand details about cube definition that they may not have access to.

Recently I've learned more about how SSAS2005 handles solve order.  SSAS2005 uses a model where solve order is given a scope.  Members within the cube are by default evaluated before members in the query, regardless of relative solve order.  In almost all cases this makes better sense.  For the times when it doesn't SSAS allows you to specify SCOPE_ISOLATION=CUBE for a particular member to allow the AS2K behavior.  I did a write up of the differences at http://777eisenhower.blogspot.com/2008/03/analysis-services-2000-vs-2005.html if anyone is interested.  The SSAS 2005 behavior seems like it would eliminate most of the problems we've had.

Has anyone else run into similar problems with solve order?  I'm planning on creating some unit tests which demonstrate the SSAS 2005 behavior, and hopefully this can eventually be rolled into Mondrian.


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