THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

MDX is like chess - Solution to the puzzle

It is time to publish the solution to the MDX puzzle from the "MDX is like chess" blog. I wrote it before going to my summer vacation. Since then I received several answers. While nobody answered correctly all 3 questions, there are still winners. In a spooky coincidence, the 3 winners are from 3 countries that I visited during my vacation, and I was actually been able to meet with 2 of them ! And the winners are

1. Oz Shal-Bar. Oz is the Panorama employee whose blog entry triggered my blog replay and the puzzle. Oz solved correctly first and third questions. When I visited Panorama office in Israel, I met with Oz and gave him the first price - the chess set with my autograph.

2. Francesco De Chirico. Francesco solved first question only, but he gave the most complete answer for it. Unfortunately, I found about his answer only upon my return from Italy, so I couldn't arrange a meeting.

3. Marius Dumitru. Marius is the only person who was able to solve the second question.

And now for the solutions themselves. You should reread the original blog for the problem setting.

1. Find yet another solution to the riddle which doesn't require neither IIF nor Filter functions.

This turned out to be the simplest question with variety of different answers. Popular answer was to use CASE operator instead, and while technically this is correct, I felt that this solution was a little bit of cheating and going against the spirit of the riddle. But several people also found the more elegant solution. This solution relies on the implicit conversion from boolean data type to integer data type. The idea is to modify the code at the end of the post and replace IIF over Condition for computing length of Head, to simply multiply the length of the set by either 0 or 1. Here is Francesco's code:

{ Head([TRUE SET], ([TRUE SET].Count * ABS(Condition))), Head([FALSE SET], ([FALSE SET].Count * ABS(NOT(Condition])))) } 

 The reason why Francesco used ABS function is because in AS2005 true maps to -1 and false maps to 0, but in AS2000 true maps to 1 and false still to 0, so use of ABS is the only way to keep solution working in both versions.

2. There is a flaw in the solution with the Head function which isn't there in the solution with Generate over Filter. Can you identify this flaw ?

This turned out to be the most tricky question. There were multiple attempts to solve it, but only Marius gave correct answer. The problem with Union of Heads happens when True Set and False Set have different dimensionalities. Even though one of the Heads will end up empty set, the MDX keeps track of dimensionality of empty sets too. And then Union will detect two sets with different dimensionalities and will raise an error.

3. In both solutions (with Head and with Generate over Filter) the Condition is evaluated twice. Is there a solution under given constraints which requires only single evaluation of solution ?

In my opinion, this was the most difficult question, and Oz solved it absolutely correctly. I copy below his answer verbatim - there is nothing I could add to it !

Let's call [Larger True] this condition: '[True Set].Count > [False Set].Count'
Now, the solution would be:

Head([True Set], Iif([Larger True], [True Set].Count, 0)),
[False Set],
Head([True Set], Iif([Larger True], 0, [True Set].Count))
Iif(Condition, Iif([Larger True], 0, [False Set].Count), Iif([Larger True], [True Set].Count, 0)),
Iif([Larger True], [True Set].Count, [False Set].Count)
First, I make a union of the true/false sets, but make sure the larger set is first. Then I use subset, from the beginning of the first set, or the beginning on the second set, depending on the condition (the only place I use the condition), and depending on which set was first. The count of the subset is the count of the larger set – that's why I had to have the larger set first
Published Tuesday, August 14, 2007 6:22 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement