MDX Walkthrough

Multidimensional Expressions which are usually abbreviated to MDX are also sometimes abbreviated by frustrated developers as @#%*^$!.   For those of us who have been using T-SQL for a long time MDX can be an unpleasant new language to pick up.  For those of us who do not work with MDX all the time, it’s a constant cycle of readjusting, relearning some old tricks and then setting it aside again.  I have found that many web postings are either too simplistic to help the new MDX user, too complicated or go too quickly from start to end to be easily absorbed.  When I do have my breakthroughs, I have learned to take extensive notes to remind myself of what was going on so that when I pick MDX back up, I have my own set of notes.

In this posting, I am providing a step by step walkthrough of creating an MDX query that uses several MDX functions, a named set and a calculated member.  The goal of this posting is to start simple and build up until we have a fairly complex query.  For this reason, the blog posting is a bit long.  It is based on the AdventureWorks2008R2 Analysis Services database so you should be able to use all of the code samples below.

Here goes! 

The goal is to create some MDX that will cover the following.

• List of Reseller Sales Amount by City
• Order By Sales Amount highest to lowest.
• Display Rank in Sales
• Display the "All" Level for the cities but do not include this in the ranking.

Here is the screen shot of the targeted results.

Let’s start with a simple MDX query.  This has no frills but gets us in the neighborhood of our expected goals.  The results are not sorted, there is no ranking and there are Null records where no data exists.

SELECT 
	[Measures].[Reseller Sales Amount]
	ON COLUMNS
	,[Geography].[City].AllMembers ON ROWS
FROM [Adventure Works];
 
 

It is possible to get closer to desired results by expanding the basic statement to include an ORDER function.

SELECT 
	[Measures].[Reseller Sales Amount]
	ON COLUMNS
	,ORDER
		(
		[Geography].[City].AllMembers 
		,[Measures].[Reseller Sales Amount]
		,DESC
		)
		ON ROWS
FROM [Adventure Works];
 
 

However, without the use of the WITH statement, MDX becomes unwieldy and hard to read.  Additionally, combinations of functions eventually require the use of WITH to create named measures and sets.  In our case, we can move the logic for the ORDER statement into a named set.

WITH
	SET [OrderedCity]
		AS 
		ORDER
			(
			[Geography].[City].AllMembers
			,[Measures].[Reseller Sales Amount]
			,DESC
			)
	
SELECT 
	{
	[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,[OrderedCity] ON ROWS
FROM [Adventure Works];
 
 
 

Please note that we have some pesky (null) entries for cities without sales.  This can be eliminated using the NON EMPTY clause in the ROWS section of the SELECT statement.

WITH
	SET [OrderedCity]
		AS 
		ORDER
			(
			[Geography].[City].AllMembers
			,[Measures].[Reseller Sales Amount]
			,DESC
			)
	
SELECT 
	{
	[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,NON EMPTY [OrderedCity] ON ROWS
FROM [Adventure Works];

Note that our pesky (null) rows have returned.  This is because the NON EMPTY clause sees a value for the [City Rank] measure since all the (null) values have tied for last place.

WITH
	MEMBER [Measures].[City Rank] 
	AS Rank
	(
	[Geography].[City].CurrentMember
	,[Geography].[City].AllMembers
	,[Measures].[Reseller Sales Amount]
	)

SET [OrderedCity]
	AS 
	ORDER
		(
		[Geography].[City].AllMembers
		,[Measures].[Reseller Sales Amount]
		,DESC
		)
	
SELECT 
	{
	[Measures].[City Rank]
	,[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,NON EMPTY [OrderedCity] ON ROWS
FROM [Adventure Works];

In order to permit the NONEMPTY clause to function the way we would like, we need to prevent the ranking of the cities without Reseller Sales. This can be accomplishing by replacing the ranking value for these cities with a (null) using a CASE WHEN statement.  For the T-SQL people out there, this syntax will feel very familiar.  The new syntax checks to see if there is a value for the tuple of ([Geography].[City].CurrentMember, [Measures].[Reseller Sales Amount]) using the NOT ISEMPTY function.  If so, a the rank is returned.  Otherwise, a Null is returned.

WITH
	MEMBER [Measures].[City Rank] 
AS
	CASE
		WHEN 
			NOT ISEMPTY
			(
				(
				[Geography].[City].CurrentMember
				,[Measures].[Reseller Sales Amount]
				)
			)
		THEN
			RANK
				(
				[Geography].[City].CurrentMember
				,[Geography].[City].AllMembers
				,[Measures].[Reseller Sales Amount]
				) 
		ELSE
			NULL
	END		

				
SET [OrderedCity]
	AS 
			ORDER
			(
			[Geography].[City].AllMembers
			,[Measures].[Reseller Sales Amount]
			,DESC
			)
SELECT 
	{
	[Measures].[City Rank] 
	,[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,NON EMPTY [OrderedCity] ON ROWS
FROM [Adventure Works];

I want to display the [All Geographies] with the total for [Reseller Sales Amount] but I do not want to that value to be included in the ranking by city.  To accomplish this, I added an additional CASE WHEN in the [Measures].[City Rank] named member to see if the current member IS the [All Geographies] member.

WITH
	MEMBER [Measures].[City Rank] 
AS
	CASE
		WHEN 
			NOT ISEMPTY
			(
				(
				[Geography].[City].CurrentMember
				,[Measures].[Reseller Sales Amount]
				)
			)
		THEN
			CASE 
				WHEN [Geography].[City].CurrentMember IS [Geography].[City].[All Geographies]
					THEN NULL
					ELSE
						RANK
							(
							[Geography].[City].CurrentMember
							,[Geography].[City].AllMembers
							,[Measures].[Reseller Sales Amount]
							) 
					END
		ELSE
			NULL
	END		

				
SET [OrderedCity]
	AS 
			ORDER
			(
			[Geography].[City].Members
			,[Measures].[Reseller Sales Amount]
			,DESC
			)
SELECT 
	{
	[Measures].[City Rank] 
	,[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,NON EMPTY [OrderedCity] ON ROWS
FROM [Adventure Works];

Notice that this (kind of) corrects the ranking but actually exposes another problem with [Measures].[City Rank].  Although the ranking value for [All Geographies] is (null), the formula still includes [All Geographies] when it performs the ranking.  Therefore, Toronto is shown as #2 when it should be #1. 

This is corrected by changing the set of Geography members that is considered.  Instead of AllMembers, use the Siblings function.  This means that when ranking a city, the RANK function only looks at all other cities (siblings).

WITH
	MEMBER [Measures].[City Rank] 
AS
	CASE
		WHEN 
			NOT ISEMPTY
			(
				(
				[Geography].[City].CurrentMember
				,[Measures].[Reseller Sales Amount]
				)
			)
		THEN
			CASE 
				WHEN [Geography].[City].CurrentMember IS [Geography].[City].[All Geographies]
					THEN NULL
					ELSE
						RANK
							(
							[Geography].[City].CurrentMember
							,[Geography].[City].Siblings
							,[Measures].[Reseller Sales Amount]
							) 
					END
		ELSE
			NULL
	END		

				
SET [OrderedCity]
	AS 
			ORDER
			(
			[Geography].[City].Members
			,[Measures].[Reseller Sales Amount]
			,DESC
			)
SELECT 
	{
	[Measures].[City Rank] 
	,[Measures].[Reseller Sales Amount]
	} ON COLUMNS
	,NON EMPTY [OrderedCity] ON ROWS
FROM [Adventure Works];

Note that the CASE state used to suppress the [All Geographies] ranking value is still needed.  Without it, the RANK function would rank [All Geographies] against its siblings (there are none at this level of the hierarchy) and come back with a 1.  I have not included the MDX for this but have included an image of the results of this situation below.

I hope this have been helpful for those still getting their feet wet. In the future I  will attempt to keep providing additional examples of using MDX in SSRS reports and dashboards.

Stay Thirsty.

Craig