MDX - 3rd + dimension example needed

Question

I am trying to learn MDX. I am an experienced SQL Developer.

I am trying to find an example of an MDX query that has more than two dimensions. Every single webpage that talks about MDX provides simple two dimensional examples link this:

select 
{[Measures].[Sales Amount]} on columns,
Customer.fullname.members on rows
from [Adventure Works DW2012]

I am looking for examples that use the following aliases: PAGES (third dimension?), section (forth dimension?) and Chapter (fifth dimension?). I have tried this but I do not think it is correct:

select 
{[Measures].[Sales Amount]} on columns,
Customer.fullname.members on rows,
customer.Location.[Customer Geography] as pages
from [Adventure Works DW2012]

I am trying to get this output using an MDX query (this is from AdventureWorks DW2012):

Answer 1

That's not a 3-dimensional resultset in your screenshot, unless there's something cropped from it.

Something like

SELECT [Geography].[Country].Members ON 0,
[Customer].[CustomerName].Members ON 1
FROM [whatever the cube is called]
WHERE [Measures].[Sales Amount]

(dimension/hierarchy/level names may not be exactly right) would give a resultset like the one in your message.

The beyond 2nd-dimension dimensions and dimension names are not used in any client tool that I know. (Others may know different). They seem to be there in MDX so that MDX can hand >2-dimensional resultsets to clients that can handle them (e.g. an MDX subquery handing its results to the main query).

An often-used trick in MDX is to get the members of two dimensions onto one axis by cross-joining:

SELECT {[Date].[Calendar Date].[Calendar Year].Members * [Geography].[Country].Members} ON 0, [something else] ON 1 FROM [Cube]

Answer 2

How about the following - it does not send more than two dimensions back to a flat screen but it uses quite a few dimensions explicitly:

SELECT 
  [Measures].[Sales Amount] ON O,
  [Customer].[fullname].MEMBERS ON 1
FROM
( 
  SELECT
    [Date].[Calendar Month].[Calendar Month].&[February-2012] ON 0,
    [Geography].[Country].[Country].&[Canada] ON 1,
    [Product].[Product].&[Red Bike] ON 2,
    [Customer].[Customer].&[foo bar] ON 3
  FROM [Adventure Works DW2012]
)

I've made up the dimension | hierarchy | member combinations as I do not have access to the cube.

Also if we consider implicit dimensions then take the following:

SELECT 
  [Customer].[Location].[Customer Geography]  ON 0,
  [Customer].[fullname].[fullname].&[Aaron Flores] ON 1
FROM [Adventure Works DW2012]
WHERE 
  (
   [Measures].[Sales Amount]
  );

On the slicer I've used braces (..) which indicate a tuple, but this is actually shorthand for the following:

SELECT 
  [Customer].[Location].[Customer Geography]  ON 0,
  [Customer].[fullname].[fullname].&[Aaron Flores] ON 1
FROM [Adventure Works DW2012]
WHERE 
  (
   [Measures].[Sales Amount]
  ,[Date].[Calendar Month].[Calendar Month].[All],
  ,[Geography].[Country].[Country].[All],
  ,[Product].[Product].[All]
  ,...
  ,...
  ....
  );

The All member from every dimension in the cube could be included in this slicer without affecting the result.

So the whole nature of mdx is multi-dimensional - yes you do not get more than a 2 dimensional table returned to your screen but the way you get to that cellset could well involve many dimensions.