MDX Syntax Elements

This document provides a comprehensive overview of the syntax elements used in Multidimensional Expressions (MDX). Understanding these elements is crucial for writing effective MDX queries to retrieve and analyze data from SQL Server Analysis Services multidimensional models.

Core Syntax Elements

MDX uses a variety of keywords, operators, and identifiers to define queries and expressions. Here are some of the fundamental syntax elements:

Keywords

Keywords are reserved words that have special meaning in MDX. Examples include:

  • SELECT: Begins a query.
  • FROM: Specifies the cube or dataset.
  • WHERE: Defines a slicer context.
  • WITH: Defines calculated members or sets.
  • ON COLUMNS, ON ROWS: Specifies axis placement.

Identifiers

Identifiers are names given to objects within the Analysis Services model, such as cubes, dimensions, hierarchies, levels, and members. They can be regular identifiers or delimited identifiers (enclosed in square brackets).

Examples:

[Customer].[Customer Name]
[Measures].[Internet Sales Amount]
[Date].[Calendar Year].&[2023]

Operators

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

Arithmetic Operators:

  • +, -, *, / (addition, subtraction, multiplication, division)
  • % (modulo)

Comparison Operators:

  • =, <>, <, >, <=, >= (equal to, not equal to, less than, greater than, less than or equal to, greater than or equal to)

Logical Operators:

  • AND, OR, NOT

Set Operators:

  • * (set intersection)
  • + (set union)
  • - (set difference)

Functions

MDX provides a rich library of built-in functions for performing calculations, manipulating sets, and retrieving data. These are covered in more detail in the "MDX Functions" section.

Example:

Sum( [Measures].[Internet Sales Amount] )

Query Structure

A typical MDX query involves selecting data from a cube and specifying dimensions on axes.

SELECT Statement

The SELECT statement is the foundation of any MDX query. It specifies the members to be returned on the axes of the result set.

SELECT
    {[Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works DW2019]
WHERE
    ([Date].[Calendar Quarter].&[2023 Q3], [Customer].[Country].&[United States])

FROM Clause

The FROM clause specifies the cube or dataset from which data is retrieved.

FROM [Adventure Works DW2019]

WHERE Clause (Slicer)

The WHERE clause, also known as the slicer, filters the data returned by the query. It applies a context to the entire query and does not appear on any axis.

WHERE ([Date].[Calendar Year].&[2023])

WITH Clause

The WITH clause allows you to define calculated members, sets, or named cubes that can be used within the query. This enhances the flexibility of your analysis.

WITH MEMBER [Measures].[Sales Margin Percentage] AS
    ([Measures].[Internet Sales Amount] - [Measures].[Internet Gross Profit]) / [Measures].[Internet Sales Amount]

Data Types

MDX supports various data types for values, including numbers, strings, dates, and sets.

  • Numeric: Integers and floating-point numbers.
  • String: Text values.
  • Date: Date and time values.
  • Set: A collection of members.
  • Tuple: An ordered collection of members, one from each of one or more unique hierarchies.

Comments

Comments can be included in MDX scripts to explain the logic or to temporarily disable code. Single-line comments start with --, and multi-line comments are enclosed in /* ... */.

-- This is a single-line comment

/* This is a
   multi-line comment */

Related Topics