Microsoft Learn

MDX Syntax - SQL Server Analysis Services

Multidimensional Expressions (MDX) is a query language for OLAP (Online Analytical Processing) databases such as SQL Server Analysis Services. MDX enables users to ask ad hoc questions of multidimensional data. This page provides a comprehensive overview of MDX syntax, covering fundamental concepts, common functions, and practical examples.

Core Concepts

MDX operates on cube structures. Key elements include:

• Dimensions: Represent categories of data (e.g., Time, Geography, Product).
• Hierarchies: Organize dimensions into levels (e.g., Year > Quarter > Month in the Time dimension).
• Members: Specific points within a hierarchy (e.g., "2023" in the Year level, "USA" in the Country level).
• Sets: Collections of members or tuples.
• Tuples: Ordered sets of members, one from each of a specified set of hierarchies.
• Measures: Numeric values derived from the cube's fact table (e.g., Sales Amount, Quantity Sold).

Basic MDX Query Structure

A typical MDX query consists of the following clauses:

• SELECT: Specifies the axes (rows and columns) of the result set.
• FROM: Identifies the cube or tabular model to query.
• WHERE: Filters the query context (slicer).

Example: Simple Query

Retrieve the 'Sales Amount' for the 'United States' across all 'Months'.

SELECT
  {[Measures].[Sales Amount]} ON COLUMNS,
  {[Date].[Calendar Year].Members} ON ROWS
FROM [YourCubeName]
WHERE ([Geography].[Country].&[USA])
            

Common MDX Functions

MDX provides a rich set of functions for data manipulation and analysis. Here are a few essential ones:

Set Functions

• {member1, member2, ...}: Creates a set.
• [Hierarchy].[Level].Members: Returns all members of a specified level.
• [Hierarchy].[Member].Children: Returns direct children of a member.
• [Hierarchy].[Member].Parent: Returns the parent of a member.
• NonEmpty(Set): Returns a subset of a set containing only non-empty tuples.

Member Functions

• [Hierarchy].[MemberName]: Refers to a specific member.
• [Hierarchy].[MemberUniqueName]: Refers to a member by its unique name, often used for clarity and precision.
• [Hierarchy].CurrentMember: Refers to the current member in a set iteration.
• [Hierarchy].[Member].Properties("PropertyName"): Retrieves a property of a member.

Aggregate Functions

• SUM(Set, NumericExpression): Aggregates values for a set.
• AVG(Set, NumericExpression): Calculates the average.
• COUNT(Set, NumericExpression): Counts members or tuples.

Working with Dates

Date handling is crucial. MDX offers specific functions for time-based analysis.

Example: Time Intelligence

Calculate Year-to-Date (YTD) sales.

WITH MEMBER [Measures].[Sales YTD] AS
  SUM(YTD([Date].[Calendar Year].[Date].CurrentMember), [Measures].[Sales Amount])
SELECT
  {[Measures].[Sales Amount], [Measures].[Sales YTD]} ON COLUMNS
FROM [YourCubeName]
            

Advanced Topics

MDX supports complex operations like calculated members, subcubes, and scripting for more sophisticated analysis.

• Calculated Members: Define new measures or members dynamically.
• Subcubes: Query subsets of a cube for performance.
• MDX Scripting: Use procedural logic within the cube definition.

For detailed syntax, function references, and advanced patterns, please refer to the official SQL Server Analysis Services documentation.