Microsoft KB Archive/944527

= You obtain no result set when you run an MDX query against an instance of SQL Server 2005 Analysis Services =

Article ID: 944527

Article Last Modified on 11/19/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-



SYMPTOMS
You have a Multidimensional Expressions (MDX) query that returns the product of members from the same dimension. When you run the query against an instance of Microsoft SQL Server 2005 Analysis Services, you obtain no result set. Additionally, you may receive a message that resembles the following in the Message window:

Executing the query ...

Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet

Formatting.

Cell set consists of 1 rows and 0 columns.

Done formatting.

Execution complete

If you request the explicit cell by using the same members that the original MDX query uses, you obtain rows and columns. However, the cell contains a NULL value.

You are more likely to encounter this behavior when the default member of the attribute hierarchy is not the All member.



CAUSE
This behavior occurs because the Autoexists feature is applied to members from the same dimension if they do not coexist in the cube space.



STATUS
This behavior is by design.



MORE INFORMATION
In SQL Server 2005 Analysis Services, the actual space of a cube is much smaller than the potential space that is defined by the products of all attribute hierarchies of the cube. This is because not all members of an attribute hierarchy intersect with all other members of another attribute hierarchy in the same dimension. Therefore, the cells that are defined by those intersections do not exist in the cube space. However, you can construct an MDX query that requests a cell that does not exist in the cube space. For example, the cell (Dallas, France) does not typically exist in a cube space.

Additionally, the cell cannot contain calculations. Also, you cannot write back to the cell.

An example MDX query that returns no result
In this example, you run the following MDX query against the Adventure Works cube of the AdventureWorks Analysis Services Project Enterprise Edition sample project: SELECT CROSSJOIN([Account].[Account Number].&[1110], [Account].[Accounts].DEFAULTMEMBER) ON 0 FROM [Adventure Works] GO In this case, the MDX query returns no result set. Additionally, you may see the message that is mentioned in the &quot;Symptoms&quot; section.

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

An example MDX query that requests a cell that does not exist
SELECT [Account].[Account Number].&[1110] ON 0, [Account].[Accounts].DEFAULTMEMBER ON 1 FROM [Adventure Works] GO In this case, you always obtain the NULL value.

An example MDX query that shows members that do not intersect in the cube space
WITH MEMBER Measures.TestMeasure AS COUNT(EXISTS(CROSSJOIN({[Account].[Account Number].&[1110]},{[Account].[Accounts].DEFAULTMEMBER}))) SELECT MEASURES.TestMeasure ON 0 FROM [Adventure Works] GO In this case, you obtain the result of 0.

An example MDX query that tries to write back to a cell that does not exist
UPDATE CUBE [Adventure Works] SET([Account].[Account Number].&[1110], Measures.DEFAULTMEMBER) = 15 GO In this case, you receive the following error message:

Errors in cell writeback: Unable to write back to a cell because the specified cell does not exist in the cube space.

