An MDX query returns additional cell data when you use the IsEmpty function on the query axis in Analysis Services with SQL Server 2005 Service Pack 2

This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
Consider the following scenario. In a Multidimensional Expressions (MDX) query, you specify a hierarchy of a dimension on the slicer axis. You also specify a different hierarchy of the dimension on a query axis. Additionally, you use the IsEmpty function on the query axis. Then, you run the MDX query in Microsoft SQL Server 2005 Analysis Services with Microsoft SQL Server 2005 Service Pack 2 (SP2). In this scenario, the MDX query returns additional cell data.

This problem does not occur in versions that are earlier than SQL Server 2005 SP2.
Cause
This problem occurs because the slicer axis uses the members from the query axis instead of the members that you specify on the slicer axis.
Workaround
To work around this problem, use the NonEmpty function instead of the IsEmpty function.

For example, update the MDX query in step 5 from the "Steps to reproduce the problem" section as follows.
Select  [Measures].[Internet Sales Amount] On 0,  NonEmpty([Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount]) On 1From [Adventure Works]Where [Product].[Product Model Lines].[Product Line].&[R]
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

  1. In Business Intelligence Development Studio, open the AdventureWorks DW Standard Edition sample project.
  2. Deploy the sample project to an instance of Analysis Services with SQL Server 2005 SP2.
  3. Open SQL Server Management Studio, and then connect to the instance of Analysis Services with SQL Server 2005 SP2.
  4. Open a new query window.
  5. Run the following MDX query.
    Select  [Measures].[Internet Sales Amount] On 0,  Filter([Product].[Product Categories].[Product].Members, Not IsEmpty([Measures].[Internet Sales Amount])) On 1From [Adventure Works]Where [Product].[Product Model Lines].[Product Line].&[R]
    The MDX query returns 159 cells. The following member is not used to restrict the cells on the query axis:
    [Product].[Product Model Lines].[Product Line].&[R]
Note When you deploy the sample project to an instance of Analysis Services or of Analysis Services with SQL Server 2005 SP1 and then you run the MDX query, the MDX query returns 68 cells. The following member is used to restrict the cells on the query axis:
[Product].[Product Model Lines].[Product Line].&[R]
References
For more information about how to install the sample project, visit the following Microsoft Developer Network (MSDN) Web site:
Properties

Article ID: 942839 - Last Review: 11/02/2013 06:27:00 - Revision: 2.0

  • Microsoft SQL Server 2005 Analysis Services
  • kbnosurvey kbarchive kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942839
Feedback