I was putting together a ridiculously large query for my previous post to try to test performance of the HAVING clause, and tried to run the following on Adventure Works:
SELECT [Measures].[Internet Order Quantity] ON 0,
FROM [Adventure Works]
To my surprise, I got the following error message:
Executing the query …
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
Obviously some kind of internal limit here to do with NON EMPTY: the query runs if you remove it. Now 4,294,967,296 tuples is a lot of tuples but imagine that you had a million customers and ten thousand products, and you wanted to find out which customers had bought which products – you’d want to find the non empty combinations in a set of tuples which is still larger than that limit. OK in any normal cube this would probably bring back far too many rows to be a useful query, but what if you had a very specific slice in the WHERE clause (eg adding WHERE([Ship Date].[Date].&) onto the end of the above query still gives the same message) so that you’d only expect to return a very small number of rows in your results? There are certainly going to be ways to work around this if you are writing your own MDX, but I wonder if any of the commercially available client tools which use NON EMPTY know to do this yet? Hmm…