MDX Language Reference
This section provides a comprehensive reference for the Multidimensional Expressions (MDX) language used with SQL Server Analysis Services.
Tip: MDX is a powerful query language for multidimensional data, enabling complex analysis and data retrieval.
Overview
Multidimensional Expressions (MDX) is a query language syntax that supports the creation and manipulation of data stored in OLAP (Online Analytical Processing) cubes. MDX combines aspects of SQL and spreadsheet functions, providing a robust syntax for defining calculations, aggregations, and data extraction from multidimensional data models.
Key Concepts
- Members: Individual data points within a dimension.
- Tuples: A set of members, one from each of a specified set of dimensions.
- Sets: An ordered collection of tuples.
- Measures: Numerical values representing business metrics, often aggregated.
- Dimensions: Hierarchical structures that categorize data (e.g., Time, Geography, Product).
- Hierarchies: Levels within a dimension, allowing drill-down and roll-up operations.
Basic Syntax Examples
Selecting Data
A basic MDX query selects data from a cube:
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
{([Date].[Calendar Year].&[2022], [Product].[Category].&[Bikes]),
([Date].[Calendar Year].&[2023], [Product].[Category].&[Clothing])} ON ROWS
FROM [Adventure Works]Using Functions
MDX provides a rich set of built-in functions for calculations and data manipulation. For example, using the Aggregate function:
WITH
MEMBER [Measures].[Total Sales] AS 'Aggregate({[Date].[Calendar Year].&[2022], [Date].[Calendar Year].&[2023]})'
SELECT
[Measures].[Total Sales] ON COLUMNS
FROM [Adventure Works]MDX Function Categories
| Category | Description | Example Function |
|---|---|---|
| Set Functions | Manipulate sets of members or tuples. | NON EMPTY, HEAD, TAIL |
| String Functions | Perform operations on string data. | UPPER, LEFT, LEN |
| Numeric Functions | Perform mathematical operations. | SUM, AVG, ROUND |
| Time-Based Functions | Work with time-based data and calculations. | ParallelPeriod, SamePeriodLastYear |
| Aggregate Functions | Calculate aggregations over sets. | Aggregate, Count |
MDX Statements
SELECT: The primary statement for retrieving data.WITH: Used to define calculated members, sets, or named sets for use within a query.FROM: Specifies the cube or data source to query.WHERE: Filters the context of the query.