How to retrieve the internal node's dataset using MDX query?

Question

I tried to retrieve the dataset at the higher level (NOT the leaf level) using the MDX query. But the result is not correct. For example, based on the Microsoft Adventure Works DW 2008R2 database. I want to get the data from these two dimensions:

[Customer].[Education].[All Customers]
[Geography].[Country].[All Geographies]

with the Internet Sales Amount measure. The MDX query I use is:

select {[Measures].[Internet Sales Amount]} on axis(0), ({[Customer].[Education].[All Customers]}) on axis(1) ,({[Geography].[Country].[All Geographies]}) on axis(2) from [Adventure Works]

The result is 29358677.

But when I sum up the children nodes of both the two dimensions, the result is as follows:

The result is 176152062. What's the problem with my MDX query?

Answer 1

I get a different number than you: Your query returns a three dimensional result, and most client tools only can cope with up to two dimensions. As you only have one member (the Internet Sales Amount measure) on columns, I shifted that to the slider axis and your rows axis to the columns, and your page axis to the rows, thus being able to run the modified query

select ({[Customer].[Education].[All Customers]}) on axis(0) ,
       ({[Geography].[Country].[All Geographies]}) on axis(1)
 from [Adventure Works]
where [Measures].[Internet Sales Amount]

in SSMS, and the result is $29,358,677.22. This is exactly the sum of each of the columns in your second table. Each of these columns has an identical result. And that is to be expected, as the Geography dimension is not related to the Internet Sales measure group. Hence the same Internet Sales Amount value is repeated against each member of this dimension. If you want to use geography breakdown for Internet Sales in AdventureWorks, you have to use the hierarchies found in the Location folder of the Customer dimension.