Working around the 4.2 billion tuple calculation limit in Analysis Services 2005 is possible in some cases...

An Analysis Services 2005 query against a calculation on a cell may report the error: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." This may be because there are simply more underlying cells in the query than the supported maximum, but in some cases, the query can be rewritten to avoid the error by breaking out parts of a clause into other clauses. Specifically, an IIF() statement apparently considers the total number of cells for calculation from both action clauses when counting the number of underlying cells. But two IIF statements run within the same query are not counted together when the server code checks for this limit. Each appear to be evaluated seperately, and so long as neither exceeds the 4.2B tuple limit, the query will pass the check and not fail in this way.

Here is an example of a calculation that produced this error when run for a high level cell in a relatively large database:

IIF(

MDX Condition=true,

MDX Expression 1,

MDX Expression 2

)

Replacing the query with the following achieved the same results, but avoided the error:

IIF (

MDX Condition = true,

NULL,

MDX Expression 2

)

+

IIF(

MDX Condition = true,

MDX Expression 1,

NULL

)

This approach allows Analysis Services to take advantage of internal optimizations since it can eliminate all cells for the first condition with NULL results, regardless of the context of the calculation. The server is able to use "block mode" evaluation in the formula engine that does not require each result to be evaluated on a cell by cell basis. This optimization may also improve performance in some cases for the same reason.

Posted By: Jon Burchel