You may obtain an incorrect result when you run an MDX query against an instance of SQL Server 2005 Analysis Services if the WHERE clause contains a set

Article translations Article translations
Article ID: 947509 - 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 may obtain an incorrect result. This behavior occurs if the following conditions are true:
  • The MDX query contains a WHERE clause.
  • The WHERE clause contains a set.
  • One of the query axes contains a user-defined hierarchy.
  • At least one member in the set is from an attribute that is in the same dimension as the user-defined hierarchy.
  • The attribute is also used in the user-defined hierarchy.
  • The member of the user-defined hierarchy in the query axis is an ancestor of at least one member in the WHERE clause.

CAUSE

This behavior occurs because Analysis Services overwrites the coordinates of the attribute in the set.

WORKAROUND

To work around this behavior, use one of the following methods.

Method 1

To slice the cube, use a subquery instead of a WHERE clause. For example, you can use the following MDX query instead of the MDX query that is described in the "Steps to reproduce the behavior" section.
SELECT 
	[Measures].[Internet Sales Amount] ON 0,
	[Date].[Calendar].[Calendar Year].&[2004] ON 1
FROM (
	SELECT {[Date].[Date].&[915], [Date].[Date].&[916]} ON 0
	FROM [Adventure Works]
)

Method 2

In the query axis, use an attribute hierarchy instead of a user-defined hierarchy. For example, you can use the following MDX query instead of the MDX query that is described in the "Steps to reproduce the behavior" section.
SELECT 
	[Measures].[Internet Sales Amount] ON 0,
	[Date].[Calendar Year].&[2004] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Date].&[915], [Date].[Date].&[916]})

STATUS

This behavior is by design.

MORE INFORMATION

In versions earlier than SQL Server 2005 Service Pack 2 (SP2), this behavior also applies to MDX queries that contain a single tuple in the WHERE clause. In SQL Server 2005 SP2, this behavior does not occur for queries that contain a single tuple in the WHERE clause. However, this behavior does occur for queries that contain a set in the WHERE clause.

Steps to reproduce the behavior

Run the following MDX query against the Adventure Works cube of the AdventureWorks Analysis Services Project Enterprise Edition sample project.
SELECT 
	[Measures].[Internet Sales Amount] ON 0,
	[Date].[Calendar].[Calendar Year].&[2004] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Date].&[915], [Date].[Date].&[916]})
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: 947509 - Last Review: February 13, 2008 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2005 Analysis Services
Keywords: 
kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB947509

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