MDX query axis [dimension].[dimension].[dimension] behaving differently for different dimensions

Question

In the SSAS AdventureWorks cube -- I don't understand why some dimensions can go directly on a query axis and others can't.

For example, this works perfectly well:

Select [Measures].[Internet Sales-Sales Amount] on Columns,
[Sales Reason].[Sales Reason].[Sales Reason] on Rows
From [Analysis Services Tutorial];

This doesn't give an error, but only provides a single value for all customers, not grouping by customer as I'd expect:

Select [Measures].[Internet Sales-Sales Amount] on Columns,
[Customer].[Customer].[Customer] on Rows
From [Analysis Services Tutorial];

I'm not understanding what's different about the Customer dimension that makes it behave differently than the Sales Reason dimension. Where should I be looking?

Check the dimension usage tab in the AW cube solution. Since I'm not near my system, can't verify, but I strongly suspect that there is no relation between 'Customer' and 'FactInternetSales'. — SouravA
There is in fact a relationship - DimCustomer and FactInternetSales are linked by the Full Name attribute, which is the key on the Customer dimension — wrschneider

whytheq whytheq · Accepted Answer · 2015-09-13T19:51:18

In plain AdvWrks I don't see a measure [Measures].[Internet Sales-Sales Amount] so I can't replicate the behaviour you mention.

In AdvWrks I can do these similar queries:

SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,[Customer].[Customer].[Customer] ON 1
FROM [Adventure Works];

Gives this:

SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,[Sales Reason].[Sales Reason].[Sales Reason] ON 1
FROM [Adventure Works];

Maybe the cube you are using [Analysis Services Tutorial] has limited functionality and is only set up to address certain tutorials. Do you have the full AdvWrks cube to play with?

MDX query axis [dimension].[dimension].[dimension] behaving differently for different dimensions

2 Answers