Search code examples
ssasmdx

Adventure Works: Calculate the yearly sales based on marital status

I'm teaching myself MDX and want to know how much married people spend per year vs single people. It sounded simple in my head:

  • Create a tuple on the columns based on [Sales Amount] and [Marital Status]
  • Put [Fiscal Year] on the rows
SELECT
([Measures].[Sales Amount], [Customer].[Marital Status].Children) ON COLUMNS,
NON EMPTY ([Date].[Fiscal Year].Children ) ON ROWS
FROM [Adventure Works]

I was thinking that when the query ran, it would lay out the columns and rows and then create the necessary tuples to retrieve the intersecting values. While the query did run, the results weren't what I expected. It shows that the married people spent the exact same amount as the single people.

enter image description here

The most likely cause is bad query, but I also considered that maybe some dimensions never intersect in a cube. Am I off track with this assumption? I hope I am because with just the little I've learned so far, I can envision some great potential with MDX given a cube with lots of dimensions.

On the other hand, if it is just a bad query, a point in the right direction would be appreciated.

Thank You!

Solution

  • The issue is you are using the wrong measure. Try the below query. 

    SELECT
([Measures].[Internet Sales Amount], [Customer].[Marital Status].Children) ON COLUMNS,
NON EMPTY ([Date].[Fiscal Year].Children ) ON ROWS
FROM [Adventure Works]

    The reason that this query works and yours doesnt is that the underlying Fact for "[Measures].[Sales Amount]" is not associated to Customer Dimension. Where as the "[Measures].[Internet Sales Amount]" underlying fact is.

    If you have the SSAS project for adventure works, go to solution explorer, then in cubes double click Adventure works, now select the dimension usage tab. You will see the mapping.