SQL Server MDX - Working With More Than Two Axes

Working With Axes in MDX SELECT Statements

The SQL Server MDX SELECT statement supports the specification of up to 128 axes, but we generally only use two in our queries.

• The MDX query editor in Management Studio can only represent two axes, columns and rows.
• SSRS only supports two axes in MDX SELECT statements.

Specialist client tools might require data to be delivered via more than two axes, but in the main two axes is plenty for our requirements.

There is, however, a scenario where using more than two axes can be helpful in an MDX SELECT statement.

Making MDX Filtering Easier

An MDX SELECT statement restricts a hierarchy from appearing in more than one part of the statement.

• A hierarchy can appear in a single axis or in a WHERE clause (on a slicer).

In the main this is not an issue, but occasionally it results in us having to use attribiute hierarchies in addition to user defined hierarchies to achieve the level of filtering we require.

Using a multiple axis query as a subquery to an MDX SELECT statement can overcome this.

Take the following example:

SELECT 
  NON EMPTY 
    [Sales Territory].[Sales Territory].MEMBERS ON 0
 ,([Product].[Product Categories].[Category],[Date].[Calendar].[Month].MEMBERS) ON 1
FROM 
(
  SELECT 
    [Date].[Calendar].[Calendar Year].&[2007] ON 0
   ,[Sales Territory].[Sales Territory].[Country].[Canada] ON 1
   ,[Product].[Product Categories].[Category].&[1] ON 2
  FROM [Adventure Works]
)
WHERE [Measures].[Internet Sales Amount];

The sub query cannot be run individually in Management Studio as it references 3 axes (0, 1 and 2), but we can see that it slices the cube by Calendar Year 2007, Country Canada and Product Category Bikes, so that is the only part of the cube that will be passed to the outer SELECT statement.

The following query uses two axes to demonstrate the slicing that those tuples result in:

--Using two axes to demonstrate slicing
SELECT [Sales Territory].[Sales Territory].[Country].[Canada] ON 0,
	   ([Date].[Calendar].[Calendar Year].&[2007],
	    [Product].[Product Categories].[Category].&[1]
	   )  ON 1
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount];

The result is:

The outer MDX query then specifies all sales territories, product categories and months:

SELECT 
  NON EMPTY 
    [Sales Territory].[Sales Territory].MEMBERS ON 0
 ,([Product].[Product Categories].[Category],[Date].[Calendar].[Month].MEMBERS) ON 1

and the WHERE clause in the outer query limits the results to the Internet Sales Amount measure:

WHERE [Measures].[Internet Sales Amount];

The result of the whole MDX SELECT statement is shown below:

We can see that only Bikes, Canada and months from 2007 are included in the final results.

If you would like to learn more about MDX SELECT statements and querying cubes why not think about attending one of our MDX Programming training courses?