Firstly, a Thank You to Tomislav Piasevoli for his answer. Tomislav, I was surprised not to find you on my blogroll (a bad miss on my side) – something which I corrected a few minutes ago.
I usually prefer not to think in SQL terms when it comes to MDX but I will agree that the first part of Tomislav’s answer makes sense and does illustrate the concept quite well. I was looking for a more “programatical” explanation, which can be found in the second bit. I think that it is worth re-iterating on the concept of context. For the unaware – if you do not explicitly mention an attribute member in an intersection, and there is no reference to a member anywhere in your query, it will get included with its “default member” in that intersection. In most cases this actually defaults to the All member.
However, I think that the actual behaviour of ORDER is inconsistent in this case. We should not need to include explicitly the current member from the set being ordered, and the current member being evaluated in the SSAS internal loops should be part of the query context in all cases. This seems to be taken care of when the attributes are not directly related. I would imagine that when they are it is easier, and in most cases more efficient to just pull the value for the aggregate Subcategory (in our example), which gets summed up to the related attribute upwards (Category) anyway, however, there is no guarantee that this would be correct (again – as we can see in our example). I think that the ORDER function should either get better documented, or it should get changed to always include the currently evaluated member in the value tuple we are ordering by (seems natural to sort a set by the values for that set members).
I am still a bit unconvinced that this “by design” behaviour is actually correct, but my understanding of the SSAS ways increased a bit, so I guess I would be able to tackle similar problems a bit easier in the future. Also, Tomislav, the last paragraph sounded mysteriously interesting and I am looking forward to your next post on the subject.
Update: If you follow the comments on this post you will find a very good article (link provided by Darren Gosbell) on Attribute Relationships , which explains this issue in depth. Also, Chris Webb just posted an article on his blog about a similar issue – similar in its cause rather than its symptoms.