MDX Syntax Reference

This section provides a comprehensive reference for the Multidimensional Expressions (MDX) syntax used with SQL Server Analysis Services. MDX is a query language used to retrieve data from multidimensional data sources, such as cubes.

Introduction to MDX

MDX is designed to query and manipulate multidimensional data. It allows users to retrieve data from OLAP cubes, perform calculations, and define custom members and sets. Understanding the fundamental components of MDX is crucial for effective data analysis.

Core Concepts

  • Members: Individual data points within a dimension.
  • Tuples: A set of members, one from each unique dimension, that define a specific point in a cube.
  • Sets: An ordered collection of tuples.
  • Hierarchies: A structure that organizes members within a dimension into levels.
  • Dimensions: Categories of data, such as Time, Geography, or Products.
  • Measures: Numerical values that can be aggregated, such as Sales Amount or Quantity.

MDX Statement Structure

An MDX query typically consists of the following clauses:

  • SELECT: Specifies the data to be retrieved.
  • FROM: Specifies the cube or perspective to query.
  • WHERE: Filters the query to a specific context.

The SELECT Clause

The SELECT clause defines the axes of the query (rows, columns, pages, etc.) and the members or sets to be displayed on those axes.

SELECT
    { [Measures].[Sales Amount] } ON COLUMNS,
    { [Date].[Calendar Year].Members } ON ROWS
FROM
    [Adventure Works]

The FROM Clause

The FROM clause specifies the cube that the query will operate on.

FROM
    [YourCubeName]

The WHERE Clause

The WHERE clause defines the slicer, which sets a context for the entire query. It's often used to filter data by a specific member or tuple.

WHERE
    ( [Geography].[Country].&[United States] )

Common MDX Functions

MDX provides a rich set of functions for data manipulation and calculation. Here are some frequently used ones:

Set Functions

  • Members: Returns all members of a specified level or hierarchy.
  • Children: Returns the children of a specified member.
  • Descendants: Returns all descendants of a specified member, optionally including members at specific levels.
  • NonEmpty: Returns a set of tuples that are not empty.

Numeric Functions

  • Sum: Calculates the sum of a numeric expression evaluated over a set.
  • Avg: Calculates the average of a numeric expression evaluated over a set.
  • Count: Counts the number of tuples in a set.
  • YTD: Calculates the Year-to-Date value of a numeric expression.

String Functions

  • StrLen: Returns the length of a string.
  • SubString: Extracts a substring from a string.

MDX Syntax Elements

Identifiers

Identifiers are used to refer to objects within the cube, such as dimensions, hierarchies, levels, members, and measures. They can be enclosed in square brackets[] or double quotes"".

-- Dimension
[Customer]

-- Hierarchy
[Date].[Calendar]

-- Level
[Product].[Category]

-- Member (unique name)
[Product].[Category].&[1]

-- Measure
[Measures].[Internet Sales Amount]

Operators

MDX supports various operators for arithmetic, comparison, and logical operations.

  • Arithmetic: +, -, *, /
  • Comparison: =, <>, <, >, <=, >=
  • Logical: AND, OR, NOT

Keywords

MDX uses reserved keywords to define the structure and logic of queries.

SELECT
    ON COLUMNS
    ON ROWS
FROM
    WHERE
    WITH
    MEMBER
    SET

Examples

Example 1: Total Sales by Year

Retrieve the total sales amount for each calendar year.

SELECT
    [Measures].[Internet Sales Amount] ON COLUMNS,
    [Date].[Calendar Year].Members ON ROWS
FROM
    [Adventure Works]

Example 2: Sales for a Specific Country

Show sales amount for each product category for the United States.

SELECT
    [Measures].[Internet Sales Amount] ON COLUMNS,
    [Product].[Category].Members ON ROWS
FROM
    [Adventure Works]
WHERE
    ( [Geography].[Country].&[United States] )

Example 3: Top 5 Customers by Sales

Identify the top 5 customers based on their sales amount.

SELECT
    TOP 5
    [Customer].[Customer Name].Members ON ROWS,
    [Measures].[Internet Sales Amount] ON COLUMNS
FROM
    [Adventure Works]

Further Reading