MDX Syntax Elements - Analysis Services

Multidimensional Expressions (MDX) is a query language for OLAP (Online Analytical Processing) databases. It provides a rich set of functions and syntax for retrieving and manipulating data from multidimensional cubes. This document outlines the fundamental syntax elements of MDX.

Identifiers

Identifiers are names used to refer to objects such as cubes, dimensions, hierarchies, levels, members, and measures. They can be simple names or names qualified by their parent object.

Example:

[Sales].[Product].[Product Name].&[123]

Keywords

Keywords are reserved words in MDX that have special meaning and cannot be used as identifiers. Examples include SELECT, FROM, WHERE, ON, WITH, BREAK, CALCULATE.

Example:

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

Operators

Operators are used to perform operations on values, such as arithmetic, comparison, and logical operations.

  • Arithmetic: +, -, *, /
  • Comparison: =, <, >, <=, >=, <>
  • Logical: AND, OR, NOT

Example:

[Measures].[Sales Amount] * 1.1

Functions

MDX offers a vast library of built-in functions for data manipulation, aggregation, set analysis, and more. These functions are categorized by their purpose (e.g., set functions, string functions, numeric functions).

Example:

SUM({[Date].[Calendar Year].&[2023]}, [Measures].[Internet Sales Amount])

Sets

A set is an ordered collection of tuples. Sets are fundamental to MDX and are used extensively in queries to define axes, filter data, and perform calculations.

  • Set Constructor: {tuple1, tuple2, ...}
  • Set Functions: { [Member1], [Member2] }, { [Dimension].[Hierarchy].Members }

Example:

{[Sales].[Region].[North America], [Sales].[Region].[Europe]}

Tuples

A tuple is an ordered collection of members, typically one from each relevant dimension. Tuples represent a single point in a multidimensional cube.

Example:

([Date].[Calendar Year].&[2023], [Product].[Category].&[Bikes], [Measures].[Sales Amount])

Properties

Members and other MDX objects can have properties associated with them, such as unique names, captions, or member levels. These can be accessed using the . operator.

Example:

[Date].[Calendar Year].&[2023].Properties( 'CAPTION' )

Scope Assignment

Scope assignment is used to assign a value to a specific cell or range of cells within the context of a calculation.

Example:

SCOPE([Measures].[Budget Amount]);
THIS = [Measures].[Sales Amount] * 0.9;
END SCOPE;