Microsoft KB Archive/940019

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

Article ID: 940019

Article Last Modified on 10/31/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



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.



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 &quot;Symptoms&quot; section mentions.



WORKAROUND
To work around this issue, use one of the following methods to implement security on measures:

Method 1
 * 1) Create two cubes. One cube contains the measure on which you want to implement security. The other cube does not contain the measure.
 * 2) On the cube that contains the measure, grant access permission to user accounts that should have the access permission to the measure.
 * 3) On the cube the does not contain the measure, grant access permission to user accounts that should not see the measure.

Method 2
 * 1) Add a new dimension that is constant to the measure group. For example, add a dimension that contains the following two members:
 * 2) * [Allowed]
 * 3) * [Disallowed]
 * 4) Make sure that all the fact rows include a named column that has the [Disallowed] value.
 * 5) Define dimension security for the role. The role disallows the [Disallowed] member. The role enables visual totals.



STATUS
This behavior is by design.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005as KB940019

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.