MDX Puzzle #1 Solution

OK, are you ready to start the MDX journey.  In MDX Puzzle #1 posting I presented you with the challenge of writing an MDX query based on the requirements that I outlined in that posting.  In this solution and all subsequent solutions I will walk you through the process of writing the MDX restating each requirement along the way.  Also, as I write the query I will define and explain any new concepts and functions that are introduced.  Enough with the formalities let’s go.

The first thing that you will need to do is get connected to a SQL Server Analysis Server (SSAS).  If you are like me, a long-time DBA, you are probably thinking, how in the heck do I connect to SSAS.  I have been connecting to SQL Servers for more than ten years, now they want me to connect to an Analysis Server.  Well don’t worry it’s just like connecting to a SQL Server.  First, open SQL Server Management Studio (SSMS), yes I said SSMS.  Select Analysis Services from the Server Type drop down list, type your server name and click Connect.

Now that you are connected, you will need to ensure that you are querying the correct database.  In the menu bar there is a drop down list that contains a list of all the available databases.  Choose the Adventure Works DW 2008R2 database from the drop down list then click the New Query button.  I was getting a little excited, because these steps are very similar to connecting to and writing a query against a SQL Server Database.

When you click the New Query button a new tab becomes available.  On this tab you will select the cube (Adventure Works) and the Measure Group (Internet Sales), which you will by querying.  Finally, I am ready to start writing this MDX query.  Here is the first query that I wrote against the cube:

SELECT *

FROM [Adventure Works]

I wanted to see all the columns (hahahahahaha) in the cube.  Was I wrong.  This is a cube (Multi-Dimensions) not a single table.  When selecting from the cube you select Measures and Attributes.  What do I do?  Well looking at the new tab in SSMS I expanded Measures then expanded the Internet Sales folder.  The first requirement from the puzzle was to return Internet Sales Amount and Internet Order Quantity.  I performed a drag and drop on the Internet Sales Amount measure, replacing the asterisk.  I repeated the steps for the Internet Order Quantity Measure.  Separating the two with a comma and arrived at the next query that I ran:

This one did not run either.  I was already beginning to not like this MDX stuff.  Not only was it very specific, but I noticed that everything has to be fully qualified.  To get it to run I had to add two things to my query.  First, curly braces ({ }) were place around both measures.  Then the ON COLUMNS keywords were added immediately after the second brace.  The braces creates the set of measures and the ON COLUMNS keywords specifies which axis to place the result set.  There are several variations to this, as time goes on we will discuss them all.

Finally, my first executable MDX query.  The next requirement was to show Product Categories and SubCategories as ROWS in the result set.  In my earlier research, I found the solution for this.  You will need to add a Tuple to your query.  A tuple is a combination of dimension members or attributes.  In this puzzle we are required to return two members from the same dimension, note that a tuple can contain members from different dimensions.  My second working MDX query:

In addition to the tuple, the ON ROWS keywords were included in the query.  As with the ON COLUMNS, ON ROWS specify which axis to place the data in the result set.  Maybe this MDX stuff isn’t too bad after all.  Let’s move on.  I did get a little stuck on the next requirement, which was to add Ship Years to the Columns axis.  After a little searching I came up with the solution.  I had to create two tuples within the set of measures.  The tuples contained the Ship Year coupled with each measure.

I also suffixed each Ship Year and both the Category and Subcategory with the Children function.  This function returns a set of children for a specified member.  You may notice several null values in your result set.  How do you get rid of them?  You can’t just add WHERE <some column> IS NOT NULL to your query.  This brings me to our last requirement, which was to filter the result set to Exclude NULL values and only show Men from the Customer Dimension.  This to me was kind of simple.  A quick search and I found the NON EMPTY keyword, which returns the set of tuples that are not empty (does not contain NULL values).

Wow, a lot of work, but this is finally starting to make a little sense to me.  Finally, we need to filter the result set so that it only returns men from the Customer Dimension.  In MDX you will use a WHERE clause to filter the data, but instead of filtering it is considered Slicing.  To Slice our result set to meet our requirements add one final line of syntax to our query, which is our SOLUTION!

Download Script

Done.  What a task, but I learned so many things from this.  I can’t wait for the next challenge.  Please post your comments and solutions here.  As always, if you have any questions please feel free to email me at pleblanc@pragmaticworks.com.