Basic MDX Queries and Syntax

MDX SELECT Statement

Basic MDX queries use the SELECT statement to identify a data set that contains a subset of multidimensional data. The basic MDX SELECT statement consists of the following clauses:

WITH clause (optional)

This allows calculated members or named sets to be computed during the processing of the SELECT and WHERE clauses.

Note: You might encounter a syntax error when a member name containing a single quotation mark is used for a calculated member in an MDX query. To prevent this, include an additional single quotation mark in the member name that contains the quotation mark.

SELECT clause

The SELECT clause defines the axes for the MDX query structure by identifying the dimension members to include on each axis. The number of axis dimensions of an MDX SELECT statement is also determined by the SELECT clause. The members from each dimension (to include on each axis of the MDX query) must be identified.

FROM clause

The cube that is being queried is named in the FROM clause. It determines which multidimensional data source will be used when extracting data to populate the result set of the MDX SELECT statement. The FROM clause (in an MDX query) can list only a single cube. Queries are restricted to a single data source or cube.

WHERE clause (optional)

The WHERE clause further restricts the result data. The axis that is formed by the WHERE clause is often referred to as the slicer. The WHERE clause determines which dimension or member is used as a slicer dimension. This restricts the extracting of data to a specific dimension or member. Any dimension that does not appear on an axis in the SELECT clause can be named on the slicer.

Note: MDX queries, and specifically the SELECT statement, can have up to 128 axis dimensions. The first five axes have aliases. Furthermore, an axis can be referred to by its ordinal position within an MDX query or by its alias. In total you can have a maximum of 64 different axes.

The SELECT clause of the statement supports using MDX functions to construct different members in a set on axes. The WITH clause of the statement supports using MDX functions to construct calculated members to be used in an axis or slicer. The following example shows the syntax for the SELECT statement:

[WITH 
  [MEMBER <member-name> AS '<value-expression>' | 
   SET <set-name> AS '<set-expression>'] . . .]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

MDX Syntax

When you create and edit MDX queries, be aware of the following syntax guidelines:

MDX keywords are case insensitive. However, to easily locate keywords in your code, consider using uppercase text when documenting keywords in an MDX query.
Do not use reserved words as names or identifiers. You can, however, quote reserved words.

Note: For more information about reserved words see SAS MDX Reserved Keywords .
Brackets used in MDX queries should balance.—for example: [ ], ( ), and { }. If brackets do not balance, you should use the SAS option VALIDVARNAME.
Single and double quotation marks should balance.