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:
我尝试使用MDX查询在更高级别(而不是叶子级别)检索数据集。但结果并不正确。例如，基于微软的冒险工程DW 2008R2数据库。我想从这两个维度获得数据:
[Customer].[Education].[All Customers]
[Geography].[Country].[All Geographies]
with the Internet Sales Amount measure. The MDX query I use is:
用网络销售金额衡量。我使用的MDX查询是:
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.
结果是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?
结果是176152062。我的MDX查询有什么问题?
1 个解决方案
#1
2
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
我得到的数字与您不同:您的查询返回一个三维结果，大多数客户端工具只能处理两个维度。由于在列上只有一个成员(Internet销售额度量)，所以我将其移动到slider轴，将行轴移动到列，将页轴移动到行，从而能够运行修改后的查询
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.
在SSMS中，结果是$29,358,677.22。这就是第二个表中每一列的和。每一列都有相同的结果。这是意料之中的，因为地理维度与互联网销售测量组无关。因此，对这个维度的每个成员重复相同的Internet销售额值。如果您想在AdventureWorks中使用地理分类，您必须使用客户维度的位置文件夹中找到的层次结构。
#1
2
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
我得到的数字与您不同:您的查询返回一个三维结果，大多数客户端工具只能处理两个维度。由于在列上只有一个成员(Internet销售额度量)，所以我将其移动到slider轴，将行轴移动到列，将页轴移动到行，从而能够运行修改后的查询
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.
在SSMS中，结果是$29,358,677.22。这就是第二个表中每一列的和。每一列都有相同的结果。这是意料之中的，因为地理维度与互联网销售测量组无关。因此，对这个维度的每个成员重复相同的Internet销售额值。如果您想在AdventureWorks中使用地理分类，您必须使用客户维度的位置文件夹中找到的层次结构。