MDX Functions: Tuple Functions

Tuple functions in MDX are used to manipulate and generate tuples, which are ordered sets of members, one from each dimension. They are fundamental for constructing complex queries and performing sophisticated analysis.

Key Tuple Functions

{ } (Tuple Constructor)

The most basic way to create a tuple is by enclosing a comma-separated list of members within curly braces. Each member in the tuple should belong to a different dimension. If a dimension is omitted, it's assumed to be the default member of that dimension.

-- Creates a tuple with the 'USA' member from the 'Geography' dimension and the '2023' member from the 'Date' dimension.
{ [Geography].[USA], [Date].[2023] }

NonEmpty(Set_Expression, [Measure_Expression])

Returns a set of tuples that are not empty. An empty tuple is one for which the specified measure expression evaluates to a null or zero value.

-- Returns a set of non-empty tuples from the 'Sales' cube for all products.
NONEMPTY( [Product].Members, [Measures].[Sales Amount] )

CrossProduct(Set_Expression1, Set_Expression2, ..., [Non_Empty_Flag])

Returns a set of tuples that is the Cartesian product of two or more sets. Each tuple in the resulting set contains one member from each of the input sets. If Non_Empty_Flag is set to TRUE, only non-empty tuples are included.

-- Creates a cross product of 'USA' members and '2023' members.
CROSSPRODUCT( {[Geography].[USA]}, {[Date].[2023]} )

-- Creates a non-empty cross product of products and their subcategories.
CROSSPRODUCT( [Product].Children, [Product].CurrentMember.Children, TRUE )

{ ... } ON COLUMNS, { ... } ON ROWS

While not strictly a function, this syntax is crucial for defining the tuples that will populate the axes of your result set. The members or tuples defined before ON COLUMNS will form the columns, and those before ON ROWS will form the rows.

SELECT
  { [Measures].[Sales Amount], [Measures].[Profit] } ON COLUMNS,
  { [Product].[Bikes], [Product].[Clothing] } ON ROWS
FROM [Adventure Works]

StrToTuple(String_Expression)

Converts a string representation of a tuple into an actual MDX tuple. The string must be in a valid MDX tuple format.

-- Assumes a string variable named @TupleString containing "[Geography].[USA],[Date].[2023]"
STRTUPLE(@TupleString)

TupleToStr(Tuple_Expression)

Converts an MDX tuple into its string representation.

-- Converts the specified tuple into a string.
TUPLETOSTR( {[Geography].[USA], [Date].[2023]} )
-- Result might be "[Geography].[USA],[Date].[2023]"

Working with Tuples in Queries

Tuples are central to how MDX defines the context for calculations and data retrieval. They determine which specific data points are aggregated or displayed. For example, when you reference a measure within a specific context, you are implicitly working with a tuple.

Implicit Tuple Context

In many MDX statements, the current context is represented by a tuple. For instance, within a calculated member definition, the current member of each dimension defines the current tuple context.

-- This calculated member gets the Sales Amount for the current tuple context.
CREATE MEMBER CURRENTCUBE.[Measures].[Current Sales] AS
  [Measures].[Sales Amount]

Tuple Manipulation with Set Functions

Tuple functions often work in conjunction with set functions. For example, you might use NonEmpty to filter a set of tuples based on a measure's value.

Tip: Understanding the concept of the "current tuple context" is crucial for debugging and writing efficient MDX queries.

The ability to construct, manipulate, and interpret tuples is fundamental to mastering MDX for SQL Server Analysis Services.