Search code examples
ssasmdx

Result is the same for all columns

The cube used in this question is available as 'MDX Step-by-Step', matching the default Adventureworks cube.

I'm learning to work with MDX, so my apologies in case the answer to this question is pretty obvious. The following query results in the following result, how is it that the amount of sales isn't split out across the different countries?

SELECT  { [Customer].[Customer Geography].[All Customers]
          , [Customer].[Customer Geography].[Country].MEMBERS } ON COLUMNS,
        Product.Category.Members ON ROWS
FROM    [Step-by-Step]

enter image description here

Solution

  • this MDX query don't specify a Measure, so it will take the default measure for the cube. The cube seems to me to be AdventureWorks: I've executed the same query on my test machine and it's giving me the same results. The default measure for the AdventureWorks Cube is [Measures].[Reseller Sales Amount]. If you open the cube definition in Visual Studio BIDS you will notice under the Dimension Usage tab that the [Measures].[Reseller Sales Amount] will not use the [Customer] dimension, so it the cube can't split the amount through that dimension.

    Reseller Sales Amount

    That said, you can try to specify a measure in your query that run against the Customer dimension, such as [Internet Sales Amount], like:

    SELECT 
[Measures].[Reseller Sales Amount] * {[Customer].[Customer Geography].[All Customers],
[Customer].[Customer Geography].[Country].MEMBERS } ON COLUMNS,
Product.Category.Members ON ROWS
FROM    [Step-by-Step]

    or you can edit the cube definition to get the default measure to interact with the Customer dimension, elaborate the cube, deploy it and re run your original query. If it's for learning reasons, I think that adding the measure will be enough.