MDX Syntax Reference

This section provides a comprehensive reference for Multidimensional Expressions (MDX) syntax used in SQL Server Analysis Services. MDX is a query language for OLAP cubes, allowing you to retrieve and manipulate data from multidimensional data models.

Basic Structure of an MDX Query

An MDX query typically consists of the following clauses:

• SELECT: Specifies the data to be returned from the cube.
• FROM: Specifies the cube or cubes from which to retrieve data.
• WHERE: Filters the results based on specified criteria.
• WITH: Defines user-defined members, tuples, and sets.

The SELECT Clause

The SELECT clause defines the axes of the query. You can specify up to 128 axes, but typically queries use a maximum of two (rows and columns).

SELECT
    {[Measures].[Sales Amount], [Measures].[Profit]} ON COLUMNS,
    {[Product].[Category].[Bikes].Children} ON ROWS
FROM
    [Adventure Works DW]
WHERE
    ([Date].[Calendar Year].&[2003], [Geography].[Country].&[United States])

The FROM Clause

The FROM clause specifies the source of the data. This is usually a cube, but can also be a view or other valid multidimensional object.

FROM
    [YourCubeName]

The WHERE Clause

The WHERE clause (also known as the SLICER) filters the results. It defines a slicer dimension that limits the context of the query.

WHERE
    ([Time].[Fiscal Year].&[FY2023])

The WITH Clause

The WITH clause allows you to define temporary calculations, named sets, and named members that can be used within the query.

WITH
    MEMBER [Measures].[Sales Per Unit] AS '[Measures].[Sales Amount] / [Measures].[Quantity]'
    SET [High Value Products] AS 'Filter([Product].[Product Name].Members, [Measures].[Sales Amount] > 10000)'
SELECT
    [High Value Products] ON ROWS
FROM
    [Sales Cube]
WHERE
    ([Date].[Calendar Year].&[2023])

Key MDX Concepts and Keywords

Members

Members are individual data points within a dimension. They can be accessed using their unique name or a qualified name.

• [DimensionName].[HierarchyName].[MemberName]
• [DimensionName].Members (returns all members of a hierarchy)
• [DimensionName].[HierarchyName].CurrentMember

Tuples

A tuple is an ordered collection of members, one from each of a set of dimensions. Tuples represent a specific point in the cube space.

• ([Dimension1].[Member1], [Dimension2].[Member2], ...)

Sets

A set is an unordered collection of tuples. Sets are fundamental to MDX operations and are used extensively in the SELECT and WHERE clauses.

Common MDX Functions

MDX provides a rich set of functions for data manipulation, aggregation, and calculations.

Set Functions:

• Members(): Returns the members of a hierarchy or attribute.
• Children(): Returns the direct children of a member.
• Ancestor(): Returns an ancestor member of a specified member at a given level.
• Descendants(): Returns a set of descendants of a member.
• TopCount(), BottomCount(): Returns the top or bottom N members based on an expression.
• Filter(): Filters a set based on a condition.
• Union(): Returns the union of two sets.
• Crossjoin(): Returns the Cartesian product of two sets.

Numeric Functions:

• Sum(): Sums the values of an expression over a set.
• Avg(): Calculates the average of an expression over a set.
• Count(): Counts the members of a set or non-empty values.
• CoalesceEmpty(): Returns a non-empty value if the expression is empty, otherwise returns the expression's value.

String Functions:

• StrToMember(): Converts a string to a member.
• StrটুSet(): Converts a string to a set.

Common MDX Keywords

Keyword	Description
SELECT	Initiates the data retrieval part of the query, defining axes.
FROM	Specifies the cube or data source.
WHERE	Filters the query context (slicer).
ON	Assigns a set to an axis (e.g., ON COLUMNS).
MEMBER	Defines a calculated member.
SET	Defines a named set.
WITH	Introduces clauses for defining temporary objects.
CASE	Evaluates conditions and returns a value.
FORWARDS	Iterates through members in their defined order.
BACKWARDS	Iterates through members in reverse order.

Example MDX Queries

1. Simple Measure Retrieval

Retrieves the total sales amount for all products.

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS
FROM
    [Adventure Works DW]

2. Slicing by Year

Retrieves sales amount for a specific year.

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS
FROM
    [Adventure Works DW]
WHERE
    ([Date].[Calendar Year].&[2023])

3. Crossjoin of Dimensions

Shows sales by product category and country.

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    NON EMPTY {[Product].[Category].Members * [Geography].[Country].Members} ON ROWS
FROM
    [Adventure Works DW]

4. Using a Calculated Member

Calculates and displays profit margin.

WITH
    MEMBER [Measures].[Profit Margin] AS '([Measures].[Profit] / [Measures].[Sales Amount]) * 100'
SELECT
    {[Measures].[Sales Amount], [Measures].[Profit], [Measures].[Profit Margin]} ON COLUMNS
FROM
    [Adventure Works DW]
WHERE
    ([Date].[Calendar Year].&[2023])