MSDN Community

MSDN > Community > Articles > Analysis Services > MDX Scripting Basics

MDX Scripting Basics

Multidimensional Expressions (MDX) is a query language used with SQL Server Analysis Services (SSAS) and other OLAP (Online Analytical Processing) systems. It allows you to query multidimensional data structures, often referred to as cubes. This article provides a foundational understanding of MDX scripting, covering essential concepts and syntax.

Understanding MDX Fundamentals

MDX queries are structured to retrieve data from a cube. A typical MDX query consists of several key components:

• SELECT Clause: Specifies the data you want to retrieve, including measures and dimension members.
• FROM Clause: Identifies the cube you are querying.
• WHERE Clause: Filters the data to a specific slice or context.

Basic MDX Query Structure

Let's start with a simple MDX query to retrieve sales amounts from a cube:

SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    {[Product].[Category].[Category].MEMBERS} ON ROWS
FROM
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].&[2003])

Explanation:

• SELECT {[Measures].[Sales Amount]} ON COLUMNS: This selects the 'Sales Amount' measure and places it on the columns axis.
• {[Product].[Category].[Category].MEMBERS} ON ROWS: This selects all members from the 'Category' level of the 'Product' dimension and places them on the rows axis.
• FROM [Adventure Works]: Specifies that the query is targeting the 'Adventure Works' cube.
• WHERE ([Date].[Calendar Year].&[2003]): This acts as a slicer, filtering the results to only include data from the calendar year 2003.

Key MDX Concepts

1. Members

A member is an item within a dimension hierarchy. For example, 'Bikes' could be a member of the 'Product Category' dimension.

2. Hierarchies

Dimensions are often organized into hierarchies. For instance, a 'Date' dimension might have hierarchies for 'Calendar' and 'Fiscal' periods, each with levels like 'Year', 'Quarter', and 'Month'.

3. Measures

Measures represent the quantifiable data in your cube, such as 'Sales Amount', 'Quantity Sold', or 'Profit'.

4. Sets

A set is an ordered collection of members. Sets are fundamental to MDX operations. The syntax {[Measure1], [Measure2]} creates a set of measures.

5. Tuples

A tuple is an ordered set of members, one from each dimension, that defines a specific point in the multidimensional data structure. For example, ([Product].[Category].[Bikes], [Date].[Calendar Year].&[2003]) is a tuple.

Working with Sets

MDX provides powerful functions for manipulating sets. Some common ones include:

• .MEMBERS: Returns all members of a dimension level or hierarchy.
• .CHILDREN: Returns the direct children of a specified member.
• .ANCESTORS: Returns the ancestors of a specified member.
• .DESCENDANTS: Returns the descendants of a specified member.
• {...}: Creates a set.

Calculated Members

MDX allows you to create dynamic members that are calculated on the fly. This is incredibly useful for creating custom calculations without altering the underlying cube schema.

WITH MEMBER [Measures].[Sales YTD] AS
    'SUM(YTD([Date].[Calendar].[Date].CurrentMember.Hierarchy.CurrentMember), [Measures].[Sales Amount])'
SELECT
    {[Measures].[Sales Amount], [Measures].[Sales YTD]} ON COLUMNS,
    {[Product].[Category].[Category].MEMBERS} ON ROWS
FROM
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].&[2003])

In this example, [Measures].[Sales YTD] is a calculated member that computes the Year-to-Date sales amount.

Conclusion