Re: Re: MDX ORDER Riddle

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.

Advertisements

9 thoughts on “Re: Re: MDX ORDER Riddle”

  1. Thank you Boyan. I’ve updated my outdated blogroll too 🙂

    It’s not easy to comprehend what you ment under “to just pull the value for the aggregate Subcategory (in our example), which gets summed up to the related attribute upwards (Category) anyway”, but if it is what I assume (rolling up?) I can say it’s not possible, those are two different hierarchies, values “intersect”, they don’t roll-up.

    You have the right to consider the Order() function inconsistent, however, I find it performing by-design and therefore – consistent. I repeat, the second argument is supposed to be an expression. That’s a measure or similar, not a member. “Measures.DefaultMember” in that example, although not specified as such. If you want to expand that measure using a tuple, fine, but do so using both hierarchies, not just one. Using only one you, infact, made a shortcut, a very dangerous one. Look here.

    (a+b)*c = a+b*c only for special cases when a = a*c, in other words c = 1 (let’s call this unrelated hierarchies case). For other combinations of a, b and c (related case) the equation doesn’t stand anymore. Although it’s just a simple flat line, there are layers of calculations inside it. By a convention (or “design” as we might call it). It just happens to be the case that we can use a shortcut for the case of 1 by omittin the brackets. Normally, you wouldn’t even consider such an idea, right? The same is here, but the percentage of good combination vs bad is reversed (like searching for a, b and c where the expression is , not =). And because of that, we got used to shortcuts. So much that we forgot they are shortcuts. Most of the time they will work. But not always.

    I admit it’s not a greatest example but I’ve already did my best to explain it in my article, so I’m out of ideas at the moment. Oh, wait, here’s another one (that was a lie, I’m always full of ideas). In Excel, you can reference a cell using a coordinate, but you can also put $ in front of every “dimension” to fix it. And what’s the default? Relative coordinate. So that you can move your tables or copy-paste your formulas and that they still work in other tables. On some occasions you might want to fix them to a particular cell. And you can. But it’s not the default option. You have to explicitely say so. Otherwise it would make problems. The same is here. Whoever is not in the context will be RELATIVELY included. If you explicitly want something else, it’s not $ this time, it’s the “.CurrentMember” inside a tuple. Now this is a better example, you must admit, eh? Except that you see “problems” the other way around, them being caused by “incorrect default” behavior of the Order() function. I give up.

    I presume that asking for a change means more “ifs” in evaluation and parsing of MDX. That would slow things down and make them more complicated. FE (engine) would have to traverse all the way up in AST (a parsing tree, like in Mosha’s MDX studio) and pick where are the current members for each of the relative hierarchies (and so in all nodes) in order to bring them on the same level (inside that tuple) and calculate the tuple using them. Now, is it just for the Order() function or some other too? Clearly it’s not a matter of Order() function only, this is a general principle, a fundamental behavior of SSAS 2005 and 2008. How many more “ifs” are we talking about here? Maybe a complete removal of attribute relations?

    Does it make sense to you? Not to me. You see, we’re ISVs and I kind of feel compassion with SSAS developers when users come to them with requests like these. “Could you just …” is often how I start. When they (my collegues) hear the word “just”, that’s it. I can see it in their faces. And the story begins. That’s why in time I’ve developed the empathy for developers and realism in wish lists. But I’m practicing on a new vocabulary as well ;-).

    I agree the documentation is not perfect, examples in particular. I’m tempted to add a comment or two there :-). The problem is that this behavior is not limited to Order() function which means this comment doesn’t belong there.

    The follow-up (article)? Comming soon. Still thinking about the title and polishing it inside my head inbetween my regular work priorities :-).

    Regards,
    T

    Like

  2. OK Tomislav, I was either misunderstood, or I have some misconception when it comes to MDX…

    What I meant with my complaint can be summarised like this in a better (hopefully) way:

    If you ORDER Product by a measure Sales, you write something like:

    ORDER([Product].[Product].[Product].MEMBERS, [Measures].[Sales],DESC)

    Say, you have 2 products – Milk and Cheese with sales of 10 and 5. You will get an ordered set -> {Milk, Cheese}. In my view, because SSAS evaluates the set over the measure (someting like (Milk,Sales)=10, (Cheese,Sales)=5, and then the first tuple evaluates to a larger value, therefore, Milk comes first). Maybe, I am wrong, but bear with me for a bit longer..

    Now, in my understanding, if you ORDER Product by Country and Sales, and you have the same as above, but including two Countries – Bulgaria and Croatia and Sales like this: Milk in Croatia – 4, Milk in Bulgaria – 6; and Cheese in Croatia – 3, Cheese in Bulgaria – 2, you write the following to order by Sales in Croatia:

    ORDER([Product].[Product].[Product].MEMBERS, [Country].[Country].[Country].[Croatia], DESC)

    and you include [Measures].[Sales] in your context, you will get the following sets, which then gets ordered:

    {(Milk,Croatia,Sales),(Cheese,Croatia,Sales)}=(numeric){4,3), therefore – the ORDERED set will come up with first Milk, and then Cheese.

    Now, this is all good, and fairly easy to conceptualise (if correct – maybe I am assuming something wrong). However, when you want to order Product by a related attribute Category (and let’s assume we have two of those – Food and Alcohol), and both Milk and Cheese are in Food, you could write:

    ORDER([Product].[Product].[Product].MEMBERS, [Product].[Category].[Category].[Food], DESC)

    You would expect to get something exactly like in the example before:

    {(Milk,Food,Sales),(Cheese,Food,Sales)}={10,5}, and then Milk first, and Cheese second.

    However, we get:

    {(All,Food,Sales),(All,Food,Sales)}={15,15}, which does not work.

    This is why I am saying that it seems like ORDER is inconsistent in its behaviour. Now, obviously there is a mechanism which causes this in the back-end. However, on the front-end MDX this looks inconsistent. Maybe I am taking a simplistic approach to MDX and maybe I really need to understand what is happening behind the covers when it comes to related attributes…

    Like

  3. This begins to looks like a match of tennis :-). OK, here’s another backhand.

    This is fine:
    ORDER([Product].[Product].[Product].MEMBERS, [Measures].[Sales],DESC)

    This is also ok:
    ORDER([Product].[Product].[Product].MEMBERS, [Country].[Country].[Country].[Croatia], DESC)
    although you should either write like this
    ORDER([Product].[Product].[Product].MEMBERS, ([Country].[Country].[Country].[Croatia], [Measures].[Sales]), DESC)
    or put Croatia in slicer (WHERE part) and leave only the measure inside. The second is what I would recommend, using a slicer.

    This one is not ok:
    ORDER([Product].[Product].[Product].MEMBERS, [Product].[Category].[Category].[Food], DESC)

    You should write
    ORDER([Product].[Product].[Product].MEMBERS, [Product].[Category].[Category].[Food], [Measures].[Sales], [Product].[Product].CurrentMember), DESC)
    or you’ll loose that member in the tuple (because Food is already there, that’s an expression, not a member (as explained above). Inside, there are other hierarchies, hidden. Once there, Food will set the Product hierarchy immediatelly on its root member as explained several times so far, because of N:1 relations between Food and products and because the current member of Product hierarchy wasn’t exlicitely there.
    Notice also that if you put Food in the slicer instead, the result is completely different. Sorted, but reduced in size.

    Wait, now I think I understand what you want. You can not use the slicer (a working solution in both ways) because it would reduce the result. Otherwise it would be a perfect and simple solution. You just want to sort your results on various columns, whatever is there in the grid, right? And it doesn’t work so in your front-end. Does it issue an MDX for sorting the data? Our front-end would fail also. Luckily, this type of sorting we perform on grid (we don’t issue a new MDX for that because the context is not changed, to gain on perfomance). If we did, we would end up with false results also (unsorted). I can see the logic in your idea, makes sense. But, this is how SSAS works and if you want to support it, the MDX must reference the current member. Or the front-end should use grid sorting instead.
    Interesting problem. I’ll see with my collegues whether we should also support this using MDX (and current members as additional parameters in various dialogs, not just members as now) or memorize the last sort for grid when saving a report/query. True, users might want their report sorted already, not having to click on a column for sorting after the query executes. We save various things, but not that.

    T

    Like

  4. Hi Tomislav,

    Well, I actually talk theory here. I don’t have a solution or a product which behaves badly because of the issue – I just stumbled on it in the forums and thought it would make sense to discuss it…

    The actual reasoning, which I am coming from is based on the functioning of the ORDER function on its own – without a business case.

    I think that your previous comment shows that you are getting on my side too – becoming a friendly badminton game, rather than a competitive tennis match 😉

    My only problem is with:

    “Food will set the Product hierarchy immediatelly on its root member as explained several times so far, because of N:1 relations between Food and products and because the current member of Product hierarchy wasn’t exlicitely there.”

    I did not realise that this would happen, and most of all – why it would happen. So it seems to me that it is because of performance..something along the lines of – “why would we worry about any other directly related attributes under this one when we are already slicing by Food – no need to consider the Products, because they would obviously not matter and just slow down the whole processing. Let’s optimise this since we are aware of the relationship – something we can’t do if we have no direct relationship and it is too expensive to detect the particular case…”. It also seems that this may be true in most cases, but not in this one.

    Also – why does it work depending on attribute relationships – it should or should not get considered implicitly (the current member) in a consistent way…do I have to be aware of my att rels to write code? And how can a client tool be?

    Like

    1. Hi Darren,

      Thank you for the reference. I have not read it and I was trying to find something like it – unsuccessfully so.. Really good and helpful!!

      Like

  5. @Darren: Thanks for your intervention.

    @Boyan: “It is so by design” remains the answer to most of your questions. Yes, from the latter perspective it appears as unwanted behavior, because the client support is more complicated that way, though feasible (could be detected). Theoretically, it isn’t a problem. SSAS behaves as intended. This case is a very special case when two related hierarchies are found on opposite axes and a special function is applied to one of them referencing the other hierarchy. The logic behind this idea is questionable because the same result could have been done differently, using a slicer (a much faster way). Only in the case when you don’t want to lose your result is this idea justifiable. And in that situation another approach is available – grid sorting. So, yes, the strong relations mechanism is not ideal; it remains such though, IMO, as long as the case is not a bit awkward.

    Sorry for the delay in response.

    Like

    1. Tomislav, please have a look at Darren’s link – it actually explains why this is happening in a very good way and shows that this is not an isolated case.

      Like

Comments are closed.