Axis in MDX

Friends,

Most important function of MDX is AXIS. The Axis function returns a set of tuples on the specified axis. PFB syntax of AXIS function.

“Axis(Number)”

Number – is a valid numeric expression which specifies the axis number.

The position starts from 0 and it can be continued as the cube database data is MULTI dimensional. MDX query is capable of returning data in more than two dimensions but our systems doesn’t support it. As our systems support only up to two dimensions, our MDX queries will stick to two AXIS only. For example, Axis(0) returns the COLUMNS axis, Axis(1) returns the ROWS axis, and so on. The Axis function cannot be used on the filter axis. The Axis numbers should be in order. You cannot use AXIS(1) without defining AXIS(0). PFB the sequence –

AXIS(0) or simply 0 – Columns

AXIS(1) or simply 1 – Rows

AXIS(2) or simply 2 – Pages

AXIS(3) or simply 3 – Sections

……….

……….

You can use any format to define AXIS like AXIS(Number) or Simply Number or with name like columns,rows,pages .. PFB sample queries of all three kinds –

Using AXIS –

select [Measures].[Sales Amount] on Axis(0),
[Dim Product].[Products].[Products] on Axis(1)
From [Adventure Works]

Using Number –

select [Measures].[Sales Amount] on 0,
[Dim Product].[Products].[Products] on 1
From [Adventure Works]

Using Name –

select [Measures].[Sales Amount] on Columns,
[Dim Product].[Products].[Products] on Rows
From [Adventure Works]

For all the above three queries the result set is same and the screenshot of the same is shown below –

That’s the complete story about AXIS function and hope you understood the concept. Happy Coding !!

Regards,

Roopesh Babu V