Error message when you run an MDX query against an instance of SQL Server 2005 Analysis Services: "The set in the WHERE clause cannot contain multiple measures"

Article translations Article translations
Article ID: 947511 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

When you run a Multidimensional Expressions (MDX) query against an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message:
The set in the WHERE clause cannot contain multiple measures.
This problem occurs when the following conditions are true in the WHERE clause of the MDX query:
  • The WHERE clause contains a member from the Measures dimension.
  • The WHERE clause contains two or more attributes from the same dimension.
  • The members that you specify from these attributes do not co-exist.

CAUSE

Because the members from these attributes do not co-exist, the WHERE clause generates a NULL tuple. When SQL Server 2005 Analysis Services tries to calculate the current cell, the calculation fails because the current measure is an empty set.

WORKAROUND

To work around this problem, move the combination of the members that do not co-exist into a separate axis. For example, you can update the MDX query in the "Steps to reproduce the problem" section as follows:
SELECT [Date].[Calendar Year].Children on columns , 
[Promotion].[Promotion].Children on rows
FROM [Adventure Works] 
WHERE (
([Customer].[Occupation].&[Manual],
[Customer].[Yearly Income].&[1]),
[Measures].[Internet Order Quantity]
)
Applications may generate MDX queries that meet the conditions that are described in the "Symptoms" section. In this situation, you may encounter the same problem. For example, you may encounter the problem in ProClarity. However, you cannot use this method to work around this problem because you may be unable to access the MDX queries.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the problem

Execute the following MDX query against the Adventure Works cube of the AdventureWorks Analysis Services Project Enterprise Edition sample project.
SELECT [Date].[Calendar Year].Children on columns , 
[Promotion].[Promotion].Children on rows
FROM [Adventure Works] 
WHERE (
[Customer].[Occupation].&[Manual],
[Customer].[Yearly Income].&[1],
[Measures].[Internet Order Quantity]
)
Note The AdventureWorks Analysis Services Project Enterprise Edition sample project is included in the Analysis Services database project. To download the Analysis Services database project, visit the following Microsoft Web site:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

Properties

Article ID: 947511 - Last Review: February 4, 2008 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2005 Analysis Services
Keywords: 
kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB947511

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com