MDX not recognizing my expressions as tuple sets?

When I have two members references with the &[Value] syntax being crossjoined, then I get this error:

Query (3, 3) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

Notice that there are two spaces in The function as if it were trying to name one of the functions that has no name. I can recreate the issue with the "Adventure Works DW 2008R2" sample database.

Does not work:

SELECT
  NON EMPTY
  {
     [Date].[Day of Month].&[1]
    * [Sales Territory].[Sales Territory Country].&[Canada]
    * [Measures].[Sales Amount]
  }
  ON COLUMNS,  
  NON EMPTY
  {
    [Product].[Product Name]   
  }
  ON ROWS
  FROM [Adventure Works]

Works:

SELECT
  NON EMPTY
  {
     [Date].[Day of Month].&[1]
    * {([Sales Territory].[Sales Territory Country].&[Canada])}
    * [Measures].[Sales Amount]
  }
  ON COLUMNS,  
  NON EMPTY
  {
    [Product].[Product Name]   
  }
  ON ROWS
  FROM [Adventure Works]

Also Works:

SELECT
  NON EMPTY
  {
     {([Date].[Day of Month].&[1])}
    * [Sales Territory].[Sales Territory Country].&[Canada]
    * [Measures].[Sales Amount]
  }
  ON COLUMNS,  
  NON EMPTY
  {
    [Product].[Product Name]   
  }
  ON ROWS
  FROM [Adventure Works]

Also Works:

SELECT
  NON EMPTY
  {
     [Date].[Day of Month].&[1]        
    * [Measures].[Sales Amount]
  }
  ON COLUMNS,  
  NON EMPTY
  {
    [Product].[Product Name]   
  }
  ON ROWS
  FROM [Adventure Works]

Also Works:

SELECT
  NON EMPTY
  {
     [Sales Territory].[Sales Territory Country].&[Canada]    
    * [Measures].[Sales Amount]
  }
  ON COLUMNS,  
  NON EMPTY
  {
    [Product].[Product Name]   
  }
  ON ROWS
  FROM [Adventure Works]

What was it interpreting my expression as, if not as a tuple set? I stumble over these kinds of things all the time and it's really confusing as to what it is trying to do with my tuple sets that it thinks it's a string or numeric expression.

Solution

This is indeed confusing because it appears there is not much difference between the two queries. The problem is described here in a blog post: http://www.softwaremasons.com/MartinsBlog/tabid/74/EntryId/56/Is-SSAS-Too-Lenient-on-MDX-Syntax.aspx

If you replace the first * operator with the CrossJoin function you'll find that the query executes fine.

The * is simply a CrossJoin written a different way - so if it works one way, and fails the other - we can assume that the problem is in how the Analysis Services engine is parsing the * operator. When you add in the CrossJoin function, you are explicitly returning a set which it then uses with * to crossjoin with the measure, and all is well. In your working examples, you have explicitly identified a set by using braces, thus giving the engine what it needs to proceed with the query - but when you try to use * with two members, it fails. Technically, each member is a set of one and it should work, but the engine is not parsing the query in this way and fails.

The blog post above is being kind when it says the issue is the leniency of MDX. It lets us get away without strongly typing our results (i.e. excluding the {} to type a member as a set). I would call it a bug myself. :) If the language lets us be loose in one place, it should be consistent everywhere else.