Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

NON EMPTY limitations

with 11 comments

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,
NON EMPTY
[Customer].[Customer].MEMBERS
*
[Date].[Date].
MEMBERS
*
[Product].[Product].
MEMBERS
*
[Geography].[City].
MEMBERS
ON
1
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.

Execution complete

 
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].&[1])  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…
 
 

Written by Chris Webb

January 6, 2006 at 12:05 pm

Posted in MDX

11 Responses

Subscribe to comments with RSS.

  1. Chris,we came around this error quite a number of times (working with Reporting Services)… We were told that this is a beta problem… And from CTP to CTP we got the problem less frequently… But as you see, the problem is still there… I\’ll check if we have some more information about that…Thomas

    Thomas

    January 6, 2006 at 10:06 pm

  2. Could you tell me who (what group) it was that told you this was a bug in the beta — was there an article on this that my team and I can look into further?Have you found any resolutions at all around this problem?Thanks,Angela

    Unknown

    January 23, 2006 at 9:32 pm

  3. Angela,I would guess that Thomas was probably told it was a bug by the Analysis Services dev team; I\’ve certainly not seen any public information on this issue and I wouldn\’t expect there to be any until it was fixed.The workaround would be to use the NonEmpty function to filter each set before you crossjoin, something like this:SELECT [Measures].[Internet Order Quantity] ON 0,GENERATE(NONEMPTY([Customer].[Customer].MEMBERS,[Measures].[Internet Order Quantity]),NONEMPTY([Customer].[Customer].CURRENTMEMBER*[Date].[Date].MEMBERS*[Product].[Product].MEMBERS*[Geography].[City].MEMBERS,[Measures].[Internet Order Quantity]))ON 1FROM [Adventure Works]

    Chris

    January 24, 2006 at 2:20 pm

  4. Chris,
     
    do you have any update on that? I now get into the same problem again… And: I have one box with SQL 2005 RTM, my colleague is using SP1. I\’m getting the error, he doesn\’t get it but the query runs "forever"…
     
    Thanks,
     
     
    Thoams

    Thomas

    May 10, 2006 at 12:07 pm

  5. No ,I haven\’t I\’m afraid. I wonder if it\’s logged at the MS feedback centre? I\’ll look for it.

    Chris

    May 12, 2006 at 2:31 pm

  6. Hi Chris,
     
    Hoping you have found a way around this.  We are hitting it using scope at the leaves level with only 7 dimensions.  Very strange.

    Unknown

    November 21, 2007 at 9:13 pm

  7. As far as I understand it, the problem happens when the engine has to evaluate a set of this size – but this only tends to happen in practice when calculations which don\’t use bulk evaluation/block computation are involved, because when your calculations do use this or you have real measures then the engine can work around it itself. So I think the key is to rewrite your calculation to be more efficient and the problem should disappear.

    Chris

    November 21, 2007 at 9:35 pm

  8. Is SSAS 2008 still affected by this limitation? Thank you. GS.

    Genci

    November 11, 2008 at 6:05 pm

  9. Well, the above query now returns an out-of-memory exception from SQL MS when I try to run in on AS2008, so it seems AS is happy to return the resultset – it\’s just that SQLMS can\’t display it! However I\’m not sure if the limitation has gone or the threshold where the this error was raised has just got higher. To be honest I hadn\’t hit this error in AS2005 since SP2 or thereabouts, so it might have been fixed some time ago.

    Chris

    November 14, 2008 at 12:22 pm

  10. Im getting this beacuse of my currency conversion scope statement.

    How can i make sure the it uses bulk evaluation/block computation

    James Taylor

    October 26, 2011 at 2:29 pm

    • Can you be a bit more specific with what you’re doing please? You shouldn’t be using NonEmpty inside a scope statement…

      Chris Webb

      October 27, 2011 at 8:04 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,202 other followers