Which one is correct to retrieve the data using the MDX query?

Question

For example, I am interested in [Measures].[Internet Sales Amount] in the intersection with [Geography].[All Geographies].[Canada].[Alberta] and [Product].[Product Categories].[All Products].[Accessories], I use the MDX query below to get the result:

select 
  {[Measures].[Internet Sales Amount]} on axis(0), 
  {[Geography].[Geography].[All Geographies].[Canada].[Alberta]} on axis(1),
  {[Product].[Product Categories].[All Products].[Accessories]} on axis(2)
from [Adventure Works]

And the result is: 700760

On the other hand, I also use the MDX query below:

select 
  {[Measures].[Internet Sales Amount]} on axis(0), 
  DESCENDANTS({[Geography].Geography].[All Geographies].[Canada].[Alberta]},2,LEAVES) on axis(1),
  DESCENDANTS({[Product].[Product Categories].[All Products].[Accessories]},2,LEAVES) on axis(2) 
  from [Adventure Works]

to retrieve the leaf level node values and try to sum them up. The data is

celMeasure":[[7425,69],[7425,68],[27106,67],[27106,66],[9480,65],[9480,64],[7307,63],[7307,62],[15444,61],[15444,60],[23141,59],[23141,58],[34818,57],[34818,56],[22436,55],[22436,54],[21541,53],[21541,52],[27971,51],[27971,50],[48860,49],[48860,48],[40308,33],[40308,32],[78028,31],[78028,30],[74354,25],[74354,24],[72954,19],[72954,18],[46620,13],[46620,12],[7219,11],[7219,10],[21178,9],[21178,8],[15391,7],[15391,6],[20230,5],[20230,4],[39591,3],[39591,2],[39360,1],[39360,0]]

The sum up result is: 1401524

Why the result using these two ways are different? And which one is correct?

FrankPl FrankPl · Accepted Answer · 2014-07-16T13:03:05

The reason you get different results is that the "Geography" dimension is not related to the "Internet Sales" measure group in Adventure Works! Thus, the same sales values are shown for each member of Axis(1). In your first query, this axis has one member: "Alberta", and in the second, it has two: "T2P 2G8" and "T5". Thus, each value for each product is repeated twice in the second report, and hence you get twice the amount of the first query if you sum them up.

For "And which one is correct?": This depends on what you want to see. But probably, neither of them is what you want to use. Use the [Customer].[Customer Geography] hierarchy instead of the [Geography].[Geography] hierarchy in your query, as that is related to the Internet Sales measure group.

Which one is correct to retrieve the data using the MDX query?

1 Answers