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

Article translations Article translations
Article ID: 942839 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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 1
From [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 1
    From [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:
http://msdn2.microsoft.com/en-us/library/ms143804.aspx

Properties

Article ID: 942839 - Last Review: November 2, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 2005 Analysis Services, when used with:
    • Microsoft SQL Server 2005 Service Pack 2
Keywords: 
kbnosurvey kbarchive kbsql2005bi kbsql2005as kbprb kbexpertiseadvanced kbtshoot KB942839

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