Axes in MDX Query

An Axis is a group, or collection of members from one or more dimension, which is constructed as Tuples. The main purpose of axis is used extract or filters the specific part of the cube values.
Characteristics of Axis:

• Axis number is zero based.
• zero (0) for the x-axis, 1 for the y-axis, 2 for the z-axis
• MDX supports up to 128 axes.
• First 5 axes have names like COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS, remaining are accessed thru axis number only like 5, 6, 7, etc.,
• We access specify the access in MDX Query in following different ways:
• on Axis(0), on Axis(1) , etc.,
• on 0, on 1, etc.,
• on columns, on rows, on pages, on sections, on chapters, on 5, on 6 etc.,
• we can’t skip any axis in MDX Query, we no need maintain the sequential axis, but sequence should exist in MDX Query

For Ex:
This Query contains one columns and Rows

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
{[Date].[Calendar].MEMBERS} ON ROWS
FROM [Adventure Works]

The same query will work, even if the different sequence,

SELECT
{[Date].[Calendar].MEMBERS} ON ROWS,
{[Measures].[Internet Sales Amount]} ON COLUMNS
FROM [Adventure Works]

But it won’t work, if we miss or skip any sequence like, here axis 1 is not used, It is Invalid

SELECT
{[Date].[Calendar].MEMBERS} ON 0,
{[Measures].[Internet Sales Amount]} ON 2
FROM [Adventure Works]

But You can write SELECT clause without using any Axes, in this case, all the dimensions available in the cube will act as slicer, MDX fetches the first cell value

MDX: Tuple Vs Set

Tuple:

• It is a collection of members from different Dimension
• It Allows only one member from each dimension
• It is a Basic Unit to forming an axis
• As a rule, tuples are enclosed in parenthesis

Set:

• One or More tuples from same dimension,
• All tuples enclosed in a set with same order of dimensions

Set Can contains tuples and tuple can consist of dimensions.

Pupose of ampersand (&) character in MDX

In MDX Query, A member can be referenced by either its member name or by its member key


The ampersand (&) character is used in MDX to differentiate a member key from a member name, as shown in the following example:


Reference the member by its member Key:
[Time].[2nd half].&[Q4]


Reference the member by its member name:
[Time].[2nd half].[4th quarter]