Omer van Kloeten's .NET Zen

Untangling MDX

I've been messing with SQL Server's Analysis Services and have spent my first couple of weeks at Nuconomy working on an MDX DOM. MDX, or Multidimensionl Expressions, is the query language for OLAP databases and looks like SQL. I say looks like, because for someone who's used to standard, relational SQL (that is, me), it's extremely confusing, given the fact that a lot of it means things that are quite different from SQL.
This post will not be about what MDX is or even what OLAP is. There are plenty of great tutorials all around the web. This post will talk about a problem I had with MDX's structure.

Let's take a simple MDX query:

SELECT [Dim1].[Hierarchy].[Level] ON 0
       [Measures].[Something]     ON 1
FROM   MyCube
WHERE  [Dim2].[Hierarchy].[Level].[Member Name]

This query selects [Dim1].[Hierarchy].[Level] on axis 0 and [Measures].[Something] on axis 1, but it first slices the cube by [Dim2].[Hierarchy].[Level].[Member Name]. This is all well and good, but what if we want to slice the cube by [Dim1].[Hierarchy].[Level].&[Some Unique Name]?

Our query will have to look like this:

SELECT [Dim1].[Hierarchy].[Level].&[Some Unique Name] ON 0
       [Measures].[Something]                         ON 1
FROM   MyCube
WHERE  [Dim2].[Hierarchy].[Level].[Member Name]

While the intuitive choice is not even a valid MDX statement, simply because a dimension can not appear both as a queried axis and as a sliced axis:

SELECT [Dim1].[Hierarchy].[Level] ON 0
       [Measures].[Something]     ON 1
FROM   MyCube
WHERE  ([Dim2].[Hierarchy].[Level].[Member Name],
        [Dim1].[Hierarchy].[Level].&[Some Unique Name])

This is really annoying if you're working on automating queries, because now you have to find out whether or not the dimension you want to slice by is already in the queried axes list.

After a bit of digging along with Lior, we found that there's a very simple solution to this, and it even outperforms the classic solution - subqueries!

Simply take your previous query and reformat it:

SELECT [Dim1].[Hierarchy].[Level] ON 0
       [Measures].[Something]     ON 1
FROM   (SELECT [Dim1].[Hierarchy].[Level].&[Some Unique Name] ON 0,
               [Dim2].[Hierarchy].[Level].[Member Name]       ON 1
        FROM   MyCube)

Added bonuses:

1. Now the query looks more readable to me, as a developer coming from the relational world.
2. The fact that we can slice in levels (multiple subqueries) allows us to create more complex slicing scenarios, such as a slice/filter/slice, where with the original syntax we could only slice first and ask questions later.
3. Want to break context? You still can. Just refer to something outside the context set by the subcube (like some sliced dimension's All Member) and you're out of the context.
4. Aggregating? Not a problem. All aggregations, etc. will occur only inside the context we've set with the subcube (also known as Visual Totals).