MDX Basics for Beginners

By John Smith, Senior Program Manager, Analysis Services Team

Welcome to this introductory guide to Multidimensional Expressions (MDX). MDX is a query language for OLAP (Online Analytical Processing) data, similar to SQL for relational databases. It allows you to retrieve, manipulate, and analyze data from cubes and multidimensional datasets within SQL Server Analysis Services (SSAS).

What is MDX?

MDX is designed to work with the multidimensional data model. Instead of tables and rows, MDX operates on dimensions, hierarchies, levels, and members within a cube. This allows for powerful slicing, dicing, drilling down, and rolling up of data, providing insights into complex business scenarios.

Key Concepts

Before diving into MDX syntax, let's understand some fundamental concepts:

Cube: A multidimensional data structure that aggregates data from fact tables and organizes it by dimensions.
Dimension: Represents a category of data (e.g., Time, Geography, Product).
Hierarchy: An ordered set of levels within a dimension (e.g., Year > Quarter > Month).
Level: A step in a hierarchy, representing a specific granularity (e.g., Year level, Country level).
Member: An individual item within a level (e.g., '2023' at the Year level, 'USA' at the Country level).
Measure: A numeric value that can be aggregated (e.g., Sales Amount, Quantity).

Your First MDX Query

Let's start with a simple query to retrieve all sales amounts for the current year.


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

Explanation:

SELECT: Specifies the data you want to retrieve.
{[Measures].[Internet Sales Amount]} ON COLUMNS: Places the 'Internet Sales Amount' measure on the columns axis. Measures are typically represented in the [Measures] dimension.
{[Date].[Calendar Year].&[2023]} ON ROWS: Places the member '2023' from the 'Calendar Year' level of the 'Date' hierarchy on the rows axis. The .& notation is used to reference a specific member by its unique name.
FROM [Adventure Works]: Specifies the cube named 'Adventure Works' as the data source.

Working with Slicers

Slicers filter the context of the query without appearing on the axes. Here, we want to see sales for a specific product category.


SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS
FROM
    [Adventure Works]
WHERE
    ([Product].[Category].&[1]) -- Assuming Category ID 1 is Bikes

Explanation:

WHERE ([Product].[Category].&[1]): This clause acts as a slicer, filtering the query to only include data where the Product Category is 'Bikes' (assuming it has a unique name/key of &[1]).

Navigating Hierarchies

MDX excels at navigating hierarchical data. You can drill down or roll up to different levels.


SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    [Date].[Calendar].Children ON ROWS -- All months in the current context
FROM
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].&[2023])

Explanation:

[Date].[Calendar].Children: This expression returns all the direct children of the current member in the 'Date' hierarchy, effectively showing all months within the selected year.

Tip: You can use the .Parent and .Children functions to traverse up and down your hierarchies. For more granular control, use functions like .Ancestor() and .Descendants().

Common MDX Functions

MDX offers a rich set of functions for data manipulation and analysis:

SUM(): Aggregates values.
COUNT(): Counts members or values.
AVG(): Calculates the average.
PROFIT(): A common business measure.
HEAD() and TAIL(): Returns the first or last members of a set.
DRILLDOWNMEMBER() and DRILLUPMEMBER(): For dynamic drill operations.

Example: Sales by Product Category and Subcategory


SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    NON EMPTY {[Product].[Category].Members * [Product].[Subcategory].Members} ON ROWS
FROM
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].&[2023])

Explanation:

NON EMPTY: Ensures that only rows with actual data are returned.
{[Product].[Category].Members * [Product].[Subcategory].Members}: This is a crossjoin of all members from the 'Category' and 'Subcategory' levels, creating a flattened structure on the rows axis.

Conclusion

This guide has introduced the fundamental concepts of MDX and provided basic examples of how to query your SSAS cubes. MDX is a powerful and flexible language. As you gain more experience, explore functions like TopCount(), BottomCount(), and calculated members to unlock the full potential of your multidimensional data.

Continue learning by exploring the MDX Function Reference and the tutorials.