MDX Querying Basics

Introduction to MDX

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) data stored in SQL Server Analysis Services (SSAS). MDX is designed to query multidimensional data structures, commonly known as cubes. It allows you to retrieve specific data points, slice and dice data across various dimensions, and perform complex calculations.

What is a Cube?

A cube is a multidimensional data structure that organizes data into measures (numerical values like sales, profit) and dimensions (categories for analysis like Time, Geography, Product). Dimensions are hierarchical, allowing you to drill down or roll up data.

Basic MDX Syntax

An MDX query typically consists of the following clauses:

• SELECT: Specifies the data you want to retrieve.
• FROM: Specifies the cube or perspective you are querying.
• WHERE: Filters the data to a specific subset.

The SELECT Clause

The SELECT clause defines the axes of your query. You can have up to 128 axes, but typically you'll use the columns and rows axes.

SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works]

In this example:

• [Measures].[Internet Sales Amount]: Selects the 'Internet Sales Amount' measure.
• ON COLUMNS: Places the selected measure on the columns axis.
• [Date].[Calendar Year].Members: Selects all members of the 'Calendar Year' level within the 'Date' dimension.
• ON ROWS: Places the selected date members on the rows axis.
• [Adventure Works]: Specifies that we are querying the 'Adventure Works' cube.

The WHERE Clause (Slicer)

The WHERE clause, also known as the slicer, filters the entire query result set without consuming an axis. This is useful for selecting a specific context for your data.

SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works]
WHERE
    ([Geography].[Country].&[United States])

Here, the query is filtered to show sales amounts only for the United States.

Navigating Dimensions and Hierarchies

MDX provides powerful functions to navigate the hierarchical structure of dimensions.

Members

A member represents an element within a dimension's hierarchy. You can reference members directly or use functions to retrieve them.

-- Selecting a specific member
[Geography].[Country].&[United States]

-- Selecting all members of a level
[Date].[Calendar Year].Members

-- Selecting members of a hierarchy
[Product].[Category].Members

Hierarchies

Hierarchies represent the levels of aggregation within a dimension. For example, a 'Date' dimension might have a hierarchy with levels like 'Year', 'Quarter', 'Month', and 'Day'.

-- Accessing a specific hierarchy within a dimension
[Date].[Calendar]

Performing Calculations

MDX excels at performing complex calculations. You can define calculated members on the fly.

WITH MEMBER MEASURES.[Sales Per Customer] AS
    [Measures].[Internet Sales Amount] / [Measures].[Internet Customer Count]
SELECT
    {[Measures].[Internet Sales Amount], [Measures].[Sales Per Customer]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM
    [Adventure Works]

This query defines a new measure, [Sales Per Customer], which calculates the average sales amount per customer.

Conclusion

This article has covered the fundamental concepts of MDX querying. By understanding the basic syntax, how to navigate dimensions and hierarchies, and how to perform simple calculations, you are well on your way to effectively querying your multidimensional data in SQL Server Analysis Services.