Part 1: MDX Code Development for Beginners

As business intelligence continues to make a big splash into businesses, SSAS and cubes are becoming a requirement. One of the limitations that DBAs face every day is converting from reading and writing T-SQL statements to being able to read and write MDX with ease. In order help others who may have been thrown into the role, I am going to start a series on MDX code writing.  This blog post will be geared towards translating a simple T-SQL SELECT statement into an MDX SELECT statement.

Let’s start with your basic T-SQL SELECT statement:

SELECT COLUMNS
FROM TABLE

Let’s look at the basic SELECT Statement in MDX form:

SELECT
ON COLUMNS,
ON ROWS
FROM

With T-SQL, you have a SELECT which defines your column list and then a FROM statement which defines the table you’re selecting from. MDX has a little bit of a twist. In the above example, you have a SELECT which defines your measures that you are selecting. ON COLUMNS defines the Y axis and is similar to your SELECT list in T-SQL, so it is the values you want to have stored in columns.  ON ROWS specifies the X axis. This is identifying the data that is defined in each row as depicted in the below diagram:

 

 

Defining your X or ON ROWS is important to understand. As you get into more complex MDX code, you will find that you will identify ON COLUMNS and ROWS more frequently to create additional slicers, hierarchies, etc.

So let’s look at a simple SELECT in MDX while defining your X and Y axis data.

SELECT        [Measures].[Internet Sales Amount]
ON COLUMNS,

              ([Customer].[Customer].[Customer].ALLMEMBERS )
ON ROWS
FROM   [Adventure Works]

The above Select depicts that I want to SELECT my Internet Sales Amount Measure, and I want to know the Internet Sales Amount for all customers in my current cube which is named “Adventure Works.” Here is what a subset of the data looks like:

 

You get to see all customer names and the amount that they have individually spent making purchases over the internet. A quick tip of mine is to add filters like the WHERE clause when you want to isolate records to a specific individual. The above T-SQL gave you everyone in my cube based on the ALLMEMBERS multi-part identifier. Now I am going to isolate my query to Aaron A. Allen who has a Customer ID of 20075. Here is what the code would look like to isolate it to the specific member.

Now if we take a look at the results, we get:

 

The above code acts like a T-SQL statement with a WHERE clause. The example filter is used to isolate the customer above and depicts what rows will be on my X axis.

Note that there are other ways filters can be used when writing MDX. There is an actual WHERE clause, and we will talk about it in upcoming blog posts in this series. Another example of filtering ROWS that are on your X axis is to add more rows directly into your SELECT statement. Examine the below code:

SELECT  [Measures].[Internet Sales Amount]
 ON COLUMNS,
   {[Customer].[Customer].[Customer].&[20075],
    [Customer].[Customer].[Customer].&[11524]}
 ON ROWS
  FROM [Adventure Works]

Notice that in the code I have defined two customers instead of one like the previous example.  This action adds another customer to my ROWS definition.

The above examples have shown how ON ROWS can be used and how filters on ROWS can be introduced.  We used the ALLMEMBERS function which returns all members of the customer dimension. Then we isolated it down to return a single member on our X axis. Lastly, we added in one additional member, and the depicted results have two members on the X axis.

Understanding and being able to relate MDX to T-SQL will help a lot of novice developers who are tasked with writing or fixing MDX code. This is the beginning of a series of blog posts in an attempt to help others understand the HOW and WHY when dealing with MDX. T-SQL may have been your starting point, but MDX and cubes may actually be your organization’s future.

In Part 2 of the series, we will be looking at the different types of filters that can be applied to the basic SELECT statement.  We will talk about HAVING, WHERE, and NON-EMPTY functions.

I hope you have enjoyed PART 1 of this series. Stay tuned for PART 2.

 

Leave a Reply

Your email address will not be published. Required fields are marked *