The Basic MDX Query (MDX)
The basic Multidimensional Expressions (MDX) query is the SELECT statement—the most frequently used query in MDX. By understanding how an MDX SELECT statement must specify a result set, what the syntax of the SELECT statement is, and how to create a simple query using the SELECT statement, you will have a solid understanding of how to use MDX to query multidimensional data.
Specifying a Result Set
In MDX, the SELECT statement specifies a result set that contains a subset of multidimensional data that has been returned from a cube. To specify a result set, an MDX query must contain the following information:
- The number of axes or sets of hierarchies. You can specify up to 128 axes in an MDX query.
- The members from each dimension to include on each axis of the MDX query.
- The name of the cube that sets the context of the MDX query.
- The members from a slicer axis on which data is sliced for members from the query axes. For more information about slicer and query axes, see Restricting the Query with Query and Slicer Axes (MDX).
To identify the query axes, the cube that sets the context of the query, and the slicer axis, the MDX SELECT statement uses the following clauses:
- A SELECT clause that determines the query axes of an MDX SELECT statement. For more information about the construction of query axes in a SELECT clause, see Specifying the Contents of a Query Axis (MDX).
- A FROM clause that determines which multidimensional data source to use when extracting data to populate the result set of the MDX SELECT statement. For more information about the FROM clause, see SELECT Statement (MDX).
- A WHERE clause that optionally determines which dimension or member to use as the slicer axis that restricts the extracting of data to a specific dimension or member. For more information about the construction of a slicer axis in a WHERE clause, see Specifying the Contents of a Slicer Axis (MDX).
Note
For more detailed information about the various clauses of the SELECT statement, see SELECT Statement (MDX).
SELECT Statement Syntax
The following syntax shows a basic SELECT statement that includes the use of the SELECT, FROM, and WHERE clauses:
[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ]
SELECT [ * | ( <SELECT query axis clause>
[ , <SELECT query axis clause> ... ] ) ]
FROM <SELECT subcube clause>
[ <SELECT slicer axis clause> ]
[ <SELECT cell property list clause> ]
The MDX SELECT statement supports optional syntax, such as the WITH keyword, the use of MDX functions to construct members by calculation for inclusion in an axis or slicer axis, and the ability to return the values of specific cell properties as part of the query. For more information about the MDX SELECT statement, see SELECT Statement (MDX).
Comparing the Syntax of the MDX SELECT Statement to SQL
The syntax format for the MDX SELECT statement is similar to that of SQL syntax. However, there are several clear differences:
- MDX syntax distinguishes sets by surrounding tuples or members with braces (the { and } characters.) For more information about member, tuple, and set syntax, see Working with Members, Tuples, and Sets (MDX).
- MDX queries can have up to 128 query axes in the SELECT statement, but only the first 5 axes have aliases. You can refer to an axis by the axis' ordinal position within an MDX query or by the axis' alias, if the axis has an alias. As with an SQL query, the FROM clause names the source of the data for the MDX query. However, the MDX FROM clause is restricted to a single cube. Information from other cubes can be retrieved on a value-by-value basis by using the LookupCube function.
- The WHERE clause describes the slicer axis. If a hierarchy is not mentioned as part of the WHERE clause, Microsoft SQL Server 2005 Analysis Services (SSAS) assumes that any hierarchy not explicitly included in a query axis is implicitly included in the slicer axis, and the hierarchy is filtered on its default members. The WHERE clause can change the filtering process for specified hierarchy and provide you with fine control of included data.
SELECT Statement Example
The following example shows a basic MDX query that uses the SELECT statement. This query returns a result set that contains the 2002 and 2003 sales and tax amounts for the Southwest sales territories.
SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON COLUMNS,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
In this example, the query defines the following result set information:
- The SELECT clause sets the query axes as the Sales Amount and Tax Amount members of the Measures dimension, and the 2002 and 2003 members of the Date dimension.
- The FROM clause indicates that the data source is the Adventure Works cube.
- The WHERE clause defines the slicer axis as the Southwest member of the Sales Territory dimension.
Notice that the query example also uses the COLUMNS and ROWS axis aliases. The ordinal positions for these axes could also have been used. The following example shows how the MDX query could have been written to use the ordinal position of each axis:
SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
Important
Normally, sets are evaluated in the larger cube context. However, the EXISTING Keyword (MDX) forces sets to be evaluated in the current context.