Wednesday, November 24, 2010

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

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

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

{[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

{[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

No comments:

Post a Comment