MDX Syntax Reference

This document provides a comprehensive reference for the Multidimensional Expressions (MDX) syntax used with SQL Server Analysis Services. MDX is a query language that enables you to retrieve data from and manipulate data in a cube.

Basic Structure of an MDX Statement

An MDX statement typically consists of the following components:

SELECT statement: Specifies the data to be retrieved.
FROM clause: Identifies the cube or perspective to query.
WHERE clause: Filters the results.
DIMENSION PROPERTIES: Specifies properties for dimensions.
CELL PROPERTIES: Specifies properties for cells.

The SELECT Statement

The SELECT statement is the core of any MDX query. It defines the axes of the query and the measures or calculations to be returned.

SELECT
    { [Axis_1_Specification], [Axis_2_Specification], ... }
ON COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS,
    { [Axis_n_Specification], ... }
ON COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS
FROM
    [Cube_Name]
WHERE
    ( [Slicer_Specification] )

Axis Specifications

Each axis in an MDX query is defined by a set of tuples. This set can include member expressions, function calls that return sets, or calculations.

Common Axis Functions:

Members(): Returns all members of a specified level or hierarchy.
Hierarchize(): Orders members within a hierarchy.
DrilldownMember(): Expands members to show their children.
Crossjoin(): Combines members from different sets to create all possible combinations.
Union(): Combines sets of members.

The FROM Clause

The FROM clause specifies the data source, typically a cube or a perspective.

FROM [YourCubeName]

You can also query from a perspective, which is a predefined subset of a cube.

FROM [YourCubeName].[YourPerspectiveName]

The WHERE Clause (Slicer)

The WHERE clause acts as a slicer, filtering the entire dataset being queried without consuming an axis.

WHERE ([Dimension].[Hierarchy].[Member])

You can specify multiple members or tuples in the slicer.

WHERE ([Date].[Calendar Year].&[2023], [Measures].[Sales Amount])

MDX Keywords and Operators

Keywords

Keyword	Description
`SELECT`	Initiates a query to retrieve data.
`FROM`	Specifies the data source (cube or perspective).
`WHERE`	Filters the query results (slicer).
`ON`	Assigns a set to an axis.
`WITH`	Defines calculated members or sets.
`MEMBER`	Declares a calculated member.
`SET`	Declares a calculated set.
`CASE`	Conditional logic.

Operators

Operator	Description	Example
`+`, `-`, `*`, `/`	Arithmetic operators.	`[Measures].[Sales] + [Measures].[Returns]`
`=`, `<>`, `<`, `>`, `<=`, `>=`	Comparison operators.	`[Product].[Category].&[Bikes]`
`AND`, `OR`, `NOT`	Logical operators.	`[Measures].[Sales] > 1000 AND [Date].[Calendar Year].&[2023]`
`.`	Member access operator.	`[Product].[Category].&[Bikes]`
`:`	Range operator.	`[Date].[Calendar Day].&[20230101] : [Date].[Calendar Day].&[20230131]`
`{}`	Set constructor.	`{ [Product].[Category].&[Bikes], [Product].[Category].&[Components] }`

Common MDX Functions

MDX provides a rich set of functions for data manipulation, aggregation, and navigation. Here are a few essential ones:

Numeric Functions: SUM, AVG, COUNT, MAX, MIN, STDEV, VAR.
String Functions: LEFT, RIGHT, LEN, SUBSTRING, UPPER, LOWER.
Date and Time Functions: NOW, DATE, TIME, YEAR, MONTH, DAY.
Set Functions: NONEMPTY, HEAD, TAIL, ITEM, FILTER, ORDER.
Navigation Functions: Parent, Children, Ancestor, FirstChild, LastChild.

Note: For a complete list and detailed descriptions of all MDX functions, please refer to the MDX Functions Reference.

Named Sets and Calculated Members

WITH clause allows you to define named sets and calculated members within your query, enhancing reusability and readability.

Calculated Member Example:

WITH MEMBER [Measures].[Sales Variance] AS
    [Measures].[Sales Amount] - [Measures].[Budget Amount]
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales Variance]} ON COLUMNS,
    [Date].[Calendar Year].MEMBERS ON ROWS
FROM [YourCubeName]
WHERE ([Date].[Fiscal Year].&[2023])

Named Set Example:

WITH SET [Top Customers] AS
    ORDER(
        NONEMPTY(
            [Customer].[Customer Name].MEMBERS
        ),
        [Measures].[Sales Amount],
        BDESC
    )
SELECT
    [Top Customers] ON ROWS,
    {[Measures].[Sales Amount], [Measures].[Quantity Sold]} ON COLUMNS
FROM [YourCubeName]

Best Practices

Use explicit member names where possible for clarity and performance.
Leverage the NON_EMPTY keyword to reduce query results.
Define calculated members and named sets in the WITH clause for better organization.
Understand the cube structure (dimensions, hierarchies, levels, members) to write efficient MDX.

MDX Documentation