Simple MDX again

WITH
SET [a] AS [Product].[Category].Levels(1).Members
SET [b] AS [Date].[Calendar].Levels(1).Members
SELECT
     Order([a], [b].Item(0), BDESC ) ON ROWS
     ,[b]  ON COLUMNS
FROM [Adventure Works];

The “Category” above is not an MDX keyword, but a part of the hierarchy under the “product” dimension.


What happens if you change the dimension to one in which the measure does not spread over?

WITH
SET [a] AS [Product].[Category].Levels(1).Members
SET [b] AS [Date].[Calendar].Levels(1).Members
SELECT
     Order([a], [b].Item(0), BDESC ) ON ROWS
     ,[b]  ON COLUMNS
FROM [Adventure Works];

Which gives:

CY 2001 CY 2002 CY 2003 CY 2004 CY 2006
Bikes $7,395,348.63 $19,956,014.67 $25,551,775.07 $13,399,243.18 (null)
Components $615,474.98 $3,610,092.47 $5,482,497.29 $2,091,011.92 (null)
Clothing $34,376.34 $485,587.15 $871,864.19 $386,013.16 (null)
Accessories $20,235.36 $92,735.35 $296,532.88 $161,794.33 (null)

Now what do we get if we only look at the top level of the row dimenion?

WITH
SET [a] AS [Product].[Category].Levels(0).Members
SET [b] AS [Date].[Calendar].Levels(1).Members
SELECT
     Order([a], [b].Item(0), BDESC ) ON ROWS
     ,[b]  ON COLUMNS
FROM [Adventure Works];
CY 2001 CY 2002 CY 2003 CY 2004 CY 2006
All Products $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)

Which is just the total “whatever measure this is” for the column dimension.
Now let’s throw in a dimension which doesn’t split the measure.

WITH
SET [a] AS [Department].Levels(1).Members
SET [b] AS [Date].[Calendar].Levels(1).Members
SELECT
     Order([a], [b].Item(0), BDESC ) ON ROWS
     ,[b]  ON COLUMNS
FROM [Adventure Works];

you get this:

CY 2001 CY 2002 CY 2003 CY 2004 CY 2006
Executive General and Administration $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)
Inventory Management $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)
Manufacturing $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)
Quality Assurance $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)
Research and Development $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)
Sales and Marketing $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60 (null)

Which looks like a cartesian join enacted on a crosstab. So each example of the row dimention gets an instance of the column dimensions (which the measure is applicable to)
How do I find out how my measures and dimensions are related?
…for that you need BIDS.