MDX Basic Concepts

A basic MDX query has the following syntax structure:

SELECT {[     <Attribute Set and Axis Specification>

           [, <Attribute Set and Axis Specification>]

             ...

       ]}

FROM   [<Cube Name>]

[WHERE [<Slicer Specification>]]

In an MDX query, the main SELECT clause defines a dataset comprised of a multidimensional data subset, which includes:

• Number of axes (up to 128)
• Members from each dimension on each axis
• Name of cube that is the data source
• Members from slicer dimension on which data is sliced for members from the axis dimensions

The basic MDX SELECT statement contains:

• Mandatory SELECT clause
• Mandatory FROM clause
• Optional WHERE clause.

The SELECT clause defines the axis dimensions.

The FROM clause defines the multidimensional data source from which data is extracted to populate the result set.

The WHERE clause optionally defines the slicer dimension or member that restricts that data extraction to a specific dimension or member. 

The MDX SELECT statement supports other optional syntax for more refined filtering, e.g.:

• WITH keyword
• CROSS JOIN keyword
• MDX functions that create calculated members to include in either an axis or slicer dimension

The MDX syntax is similar to SQL syntax, but has clear differences:

• An axis can be referenced by its ordinal position (zero-based number: 0, 1, 2, etc.) or by its alias (if assigned) up to the first five axes. After axis 4, axes can only be defined and referred to by its ordinal position number (e.g., axis 0 and axis 2 cannot be used if axis 1 is not defined).
• Although MDX queries can have up to 128 axis dimensions in the SELECT statement, an error that states results cannot be displayed for sets with more than two axes will occur if more than two axes are used in a query unless they are separated with commas. 
• Tuples are points/coordinates where dimensions intersect in the cube, which use the form [Dimension].[Level].[Member] when writing a MDX query (Member can be written as the name, or key with an ampersand &—name is more user friendly). Hovering over any item listed in the Metadata tab of the MDX query window will display the MDX syntax for that particular item:

• Tuples can include one or more members from a cube's dimension as long as two members are not from the same dimension. 
• If using more than one dimension in a tuple, enclose them in parentheses ( ) separated by a comma , . 

Examples:

1. Display results for the year 2012 would be [Date].[2012] 
2. Display results for the year 2012 from Washington would be ([Date].[2012], [Location].[State])

(Note: It is still standard practice to use enclosing parentheses even when there is only one tuple with one member from one dimension)

• Sets are separated by enclosing tuples or members in curly braces {  }, which can be written as a list separated by commas or as a range separated by a colon : if they are all from one dimension.

Example: 

1. Display results for the years 2010 through 2012 would be either: {[Date].[2010], [Date].[2011], [Date].[2012]} or {[Date].[2010]:[Date].[2012]}

• The FROM clause names the data source for the MDX query, which is restricted to a single cube (unless retrieved value-by-value with the LookupCube function).
• The WHERE clause defines the slicer dimensions; any unassigned dimension is a slicer dimension filtered on its default members.
• The WHERE clause can alter filtering of specified dimensions to improve control of data therein.
• Comment MDX code just as any other code so that the query's purpose and context can be easily remembered by the original author or understood by others who use, reuse, and/or copy it. There are three ways to comment in an MDX query:

1. /* and */ (forward slash asterisk, asterisk forward slash to block  comment multiple lines of text—everything between the /* and the */ symbols will be ignored when the MDX query is parsed and/or executed.)
2. // (pair of forward slashes to begin a comment on a single line)
3. -- (pair of dashes to begin a comment on a single line)