SSAS Bug: EXCEPT with nested FILTER functions on MEMBERVALUE
Last week while spending some time on the forums, I stumbled upon a thread where the user had presented 2 queries with seemingly identical logic but produced very different results. After a bit of troubleshooting I was stumped and fired off an S.O.S. tweet with the #ssashelp hashtag. Within the hour I had received a response from Chris Webb (b | t) who was able to reproduce the issue and agreed that it was a bug. Per Chris’ recommendation, I opened a connect item. In the meantime, Chris was able to work the side-channels and receive a workaround from one of his “contacts”.
In this post I will layout the issue and confirm the workaround.
Problem & Analysis
To get us started, here is a base query showing the internet tax amount by order line item number:
As you can see, of all the internet sales orders in the Adventure Works 2012 DW database, the max number of lines on any one order is 8.
But let’s say we want to filter this result set so that we can focus on the internet tax amount for line items 3, 4, and 5:
Looks good…a simple filter statement using the MEMBERVALUE function gives us what we want.
Now, let’s say we want to see the reverse of this filter…so we’re interested in internet tax amount for all line items EXCEPT 3, 4, and 5. One way to write that is to simply wrap the logic above in a EXCEPT function like this:
Wait, wuh? That only shows internet tax amounts for line items 6, 7, and 8. What about line items 1 and 2?
Let’s see what happens when we rewrite the logic:
Ok, that works. But now I’m starting to second-guess my understanding of how EXCEPT works. So I tried the following query which moves the original filter into a query-scoped set statement and then applies the EXCEPT function to the named set:
Oy! That works. But why? I have a theory on this that I’ll share once we’re further along ***.
At this point I’m a totally stumped and starting to get the feeling that this is a bug, but I’m nowhere near confident enough in my analysis to suggest it with any confidence. So like a curious little monkey, I fired up my beloved SQL Profiler and started to take a look under the covers.
Using Profiler, I captured the query-related events for the following querys, which were executed 1 at a time with a clear-cache XMLA statement between each:
And here’s what the Profiler window showing the traced data for the queries above transposed from top-to-bottom -> left-to-right:
After combing through the trace events, the only thing that stood out was that the first 2 queries (returning correct results) were evaluating a portion of the query in naive-mode:
On the other hand, the query on the far right (the one returning incorrect results) was not:
At this point, I’m thinking the SSAS engine is attempting to “shortcut” the logic (incorrectly) thus leading to the incorrect result.
*** Yes, so this is my theory about why moving the nested filter statements into a query-scoped named set works. By doing that the SSAS engine is forced to evaluate nested-filter chunk first …before applying the EXCEPT function down in the body of the SELECT…and therefore the engine never has the opportunity to (incorrectly) “optimize/shortcut” the logic.
But that’s a complete hunch at this point and I’ve also exhausted the extent of my troubleshooting capabilities, so here is where I fired off the tweet to the #ssashelp hashtag and received a response from Chris Webb.
This eventually led to opening my first connect item…a minor milestone in this geeks life. That said, it isn’t like this is going to be a high priority issue seeing as there are plenty of ways to rewrite this logic in ways that evaluate correctly. But it is another reason to always validate your MDX queries and statements.
During the intro, I mentioned a workaround that Chris provided. And that is to modify the msmdsrv.ini file:
Then, after a restart, we can test the original query that was previously evaluating incorrectly:
Now we see the correct results. And in a Profiler trace the part of the query that was previously not running in NaiveEvaluation mode…is now running in NaiveEvaluation mode:
Pretty cool workaround, but I can’t help but wonder how this global setting affects the performance of the rest of the MDX calculations and queries that were previously taking advantage of storage engine filtering…definitely something to consider.