TopCount and very large sets

Today I came accross an unexpected problem. I am working with a partner on a local government project. He reported an MDX error "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." The function concerned was TopCount although I suspect that TopSum and TopPercent would display the same behavior. The query was asking for a set:
 
NON EMPTY
TopCount(
{ <A very large Set> }
,
10
,
( <Tuple> )
) ON ROWS
 
The set concerned would not be very large afetr NON EMPTY is applied but clearly, and probably quite correctly, the TopCount function is being evaluated before NON EMPTY.
 
The Solution to the problem is logically to use:
 
TopCount(
NonEmpty(
{ <A very large Set> }
,
( <Tuple> )
)
,
10
,
( <Set representing the report columns> )
)
 
This applies the NON EMPTY criteria to the set before TopCount is evaluated and therefore presents a much smaller set. This set me thinking:
 
  • For a generic query builder tool should we always implement the NonEmpty function in preference to NON EMPTY?
  • Is there any functional difference between the 2 implementations?
  • Is there any performance difference between the 2 implementations

My initial research indicates that there is no real difference other than that NON EMPTY is a little clearer when you are reading the MDX.

About these ads
This entry was posted in MDX. Bookmark the permalink.

One Response to TopCount and very large sets

  1. Chris says:

    Ahh, but in other cases putting the NonEmpty function inside the TopCount function will actually hurt performance – counter-intuitive though it might seem. Though you\’re doing a TopCount over a smaller set, the fact that it may end up being an arbitrary shaped set as a result of the NonEmpty will make certain operations within the engine less efficient. So long as you put the set you\’ve got on columns in the second parameter of the NonEmpty function, though, you should get the same results.

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