MDX Statements in Multidimensional Modeling

Last updated: March 15, 2024

Multidimensional Expressions (MDX) is a query language for OLAP (Online Analytical Processing) databases. In SQL Server Analysis Services (SSAS), MDX is used to retrieve data from multidimensional cubes. This document provides an overview of common MDX statements and their usage within the context of multidimensional modeling.

Core MDX Concepts

Before diving into specific statements, it's essential to understand key MDX concepts:

• Axes: Queries in MDX are defined by specifying data on different axes. The most common axes are the COLUMNS axis and ROWS axis, but others like SLICES, PAGES, and SECTIONS are also supported.
• Sets: A set is an ordered collection of tuples. Tuples represent a point in the multidimensional cube space.
• Members: A member is an instance of an attribute within a dimension.
• Tuples: A tuple is a combination of members, one from each of a specified set of dimensions.

Common MDX Statements

SELECT Statement

The SELECT statement is the foundation of any MDX query. It specifies the data you want to retrieve from the cube.

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

Syntax Breakdown:

• SELECT: Keyword to begin the query.
• {[Measures].[Internet Sales Amount]} ON COLUMNS: Defines the content of the COLUMNS axis. Here, it's a set containing a single measure.
• {[Date].[Calendar Year].Members} ON ROWS: Defines the content of the ROWS axis. It selects all members of the 'Calendar Year' level from the 'Date' dimension.
• FROM [Adventure Works DW]: Specifies the cube to query.

WITH Clause

The WITH clause allows you to define calculated members or named sets that can be used within the query. This is powerful for creating custom metrics.

WITH MEMBER [Measures].[Profit Margin] AS
    ([Measures].[Profit] / [Measures].[Internet Sales Amount])
SELECT
    {[Measures].[Internet Sales Amount], [Measures].[Profit Margin]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works DW]

Syntax Breakdown:

• MEMBER [Measures].[Profit Margin] AS ...: Declares a new member `[Profit Margin]` within the `[Measures]` dimension.
• The calculation `([Measures].[Profit] / [Measures].[Internet Sales Amount])` defines the value of the new member.

CURRENCY CONVERT Function

This function converts a numeric value from one currency to another, using specified exchange rates and dates.

SELECT
    CURRENCYCONVERT(
        [Measures].[Sales Amount],
        [Currency].[Currency Code].[USD],
        [Currency].[Currency Code].[EUR],
        [Date].[Fiscal Year].&[2023]
    ) ON COLUMNS
FROM
    [Sales Cube]

Syntax Breakdown:

• [Measures].[Sales Amount]: The value to convert.
• [Currency].[Currency Code].[USD]: The source currency.
• [Currency].[Currency Code].[EUR]: The target currency.
• [Date].[Fiscal Year].&[2023]: The date/time context for the conversion rate.

Subsets of Sets Functions

MDX provides numerous functions to manipulate sets, such as HEAD, TAIL, NONEMPTY, and ORDER.

SELECT
    NONEMPTY{[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works DW]
WHERE
    ([Product].[Category].[Bikes])

The NONEMPTY keyword ensures that only rows or columns that contain data are returned, improving query performance and readability.

Working with Hierarchies

Dimensions often have hierarchies. MDX allows you to navigate these hierarchies.

• .Children: Returns the direct children of a specified member.
• .Parent: Returns the parent of a specified member.
• .Levels: Returns all levels within a hierarchy.
• .Members: Returns all members of a hierarchy or level.

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    {[Product].[Category].[Category].Children} ON ROWS
FROM
    [Sales Cube]

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Sales Cube]
WHERE
    (YTD([Date].[Calendar Year].CurrentMember), [Measures].[Sales Amount])

MDX Query Performance Considerations

Efficient MDX queries are crucial for a responsive analytical experience. Consider the following:

• NONEMPTY: Use it judiciously to filter out empty cells.
• Limiting Set Sizes: Avoid returning excessively large sets of members.
• Using Subcubes: Define the scope of your query using the WHERE clause.
• Predefined Calculations: Leverage calculated members and named sets defined in the cube.