4294967296 Tuple limit and 32 bit Architecture

We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:

Example

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}

Error

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.

Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?

Hi prakal,

the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.

Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.

You would have your query done on the 32bit box as well.

|||

I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

Or, if you had all of these attributes as part of a hierarchy you could just do

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

|||

A recent thread on this forum linked to a Microsoft Support article that might be relevant:

Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"

SYMPTOMS

When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

This problem may occur if the following conditions are true:

? The user account is a member of a role that has dimension security set on a measure. ? The complex MDX query contains the NON EMPTY clause.

Back to the top

CAUSE

This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.

|||

Darren,

Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".

Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.

|||

Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.

Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {

NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])

* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])

} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}