MDX Syntax Conventions

This document outlines the standard syntax conventions used in Multidimensional Expressions (MDX). Adhering to these conventions ensures clarity and consistency in your MDX queries.

General Conventions

MDX is a case-insensitive language, meaning that keywords, function names, and object names can be written in any case. However, it is a common practice to use uppercase for keywords and mixed or lowercase for object names to improve readability.

Keywords

Keywords such as SELECT, FROM, WHERE, ON, WITH, CREATE, ALTER, DROP, DIMENSION, HIERARCHY, LEVEL, MEMBER, SET, and CALCULATE should generally be written in uppercase.

Identifiers

Identifiers include cube names, dimension names, hierarchy names, level names, member names, set names, and variable names. These can be:

• Unquoted: If an identifier does not contain spaces or special characters (e.g., [Date].[Year]).
• Quoted: If an identifier contains spaces or special characters, it must be enclosed in square brackets ([...]). For example, [Product Category].[Product Name].
• Doubled Brackets for Single Quotes: If an identifier itself contains a square bracket, you must double the bracket within the quoted identifier. For example, [Sales[USD]] would be written as [[Sales[USD]]].

Operators

Common operators include arithmetic operators (+, -, *, /), comparison operators (=, <, >, <>, <=, >=), and logical operators (AND, OR, NOT).

Comments

Comments can be added to MDX code for explanation.

• Single-line comments start with --.
• Multi-line comments are enclosed in /* ... */.

Structuring Queries

A typical MDX query follows a structure that defines the data to be retrieved and how it should be organized.

The SELECT Statement

The core of an MDX query is the SELECT statement. It specifies the data to be retrieved from the cube.

SELECT
    [Axis1_Expression] ON COLUMNS,
    [Axis2_Expression] ON ROWS
FROM
    [Cube_Name]
WHERE
    ([Slicer_Expression]);

FROM Clause

The FROM clause specifies the cube or dimension from which to retrieve data. Usually, this is a cube name.

WHERE Clause (Slicer)

The optional WHERE clause acts as a slicer, filtering the query results by specifying a specific member or set of members for a dimension. This filter applies to all axes of the query.

WITH Clause

The WITH clause is used to define calculated members or named sets that can be used within the query. This enhances query flexibility.

WITH
    MEMBER [Measures].[Sales Percentage] AS
        ([Measures].[Sales Amount] / [Measures].[Previous Sales Amount]) * 100,
    SET [Top 10 Products] AS
        TOPCOUNT([Product].[Product Name].Members, 10, [Measures].[Sales Amount])
SELECT
    [Top 10 Products] ON ROWS,
    [Date].[Calendar Year].Members ON COLUMNS
FROM
    [Sales Cube]
WHERE
    ([Measures].[Sales Amount]);

Member and Set Expressions

MDX heavily relies on expressions to navigate and select data. These expressions refer to members, hierarchies, levels, and create sets of members.

Member Access

Members are typically accessed using dot notation or square brackets, often including the dimension, hierarchy, and level.

• [Dimension].[Hierarchy].[Level].[Member]
• [Dimension].[Hierarchy].<Level Name>.<Member Name> (using reserved keyword prefix)
• [Dimension].[Hierarchy].Members (returns all members of the hierarchy)
• [Dimension].[Hierarchy].[Level].Members (returns all members of a specific level)

Set Functions

MDX provides numerous functions to create and manipulate sets of members. Some common ones include:

• {...}: Creates a set of explicitly listed members.
• ALLMEMBERS: Returns all members of a specified hierarchy or level, including descendants and ancestors.
• MEMBERS: Returns all members of a specified hierarchy or level.
• CHILDREN: Returns the direct children of a specified member.
• ANCESTORS: Returns a set of ancestors for a given member.
• DESCENDANTS: Returns a set of descendants of a given member.
• TOPCOUNT, BOTTOMCOUNT, TOPPERCENT, BOTTOMPERCENT: Functions for ranking and selecting members based on a measure.

Example of Conventions

Consider this example demonstrating various conventions:

-- This is a query to show sales by product category for the year 2023
WITH
    SET [Product Categories] AS
        {[Product].[Category].[Category].Members} -- Using a named set for clarity
    MEMBER [Measures].[Total Sales] AS
        SUM(
            [Date].[Calendar Year].[Year].&[2023], -- Referencing a specific year member
            [Measures].[Sales Amount]
        )
SELECT
    [Product Categories] ON ROWS, -- Products on rows
    [Date].[Calendar Year].&[2023] ON COLUMNS -- Only 2023 on columns
FROM
    [Sales Cube]
WHERE
    ([Measures].[Sales Amount]); -- Default measure for context if not specified elsewhere