You are currently offline, waiting for your internet to reconnect

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

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 1FROM (	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 1FROM [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 1FROM [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:
Properties

Article ID: 947509 - Last Review: 02/13/2008 17:48:18 - Revision: 1.1

Microsoft SQL Server 2005 Analysis Services

  • kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB947509
Feedback
tml>