Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Kicking the Tires of MDX Rest Stop #2 MDX Journey Location Atlanta Georgia

  • 30 November 2009
  • Author: MikeMollenhour
  • Number of views: 6510
  • 0 Comments

Kicking the MDX Tires

So here we are in a second rest stop just outside of Atlanta. Talking to a couple of good ole Georgia boys I find that they have a bunch of bike trails up here. I said that’s great but I don’t have a bike with me. He quickly tells me about this bike shop “Adventure Works” where I can purchase about any bike my heart desires.  I step into “Adventure Works” and the manager needs some assistance with a Data Warehouse he has created. It’s Adventure Works data from the Road Trip with MDX Series (Trip Plan Intro) if you haven’t already downloaded please do it now, I need all the help I can get on this journey.

 

 

Since this is the first time I have seen Adventure Works cube I decide to open the doors and climb inside. The first thing I need to look at is something like an instrument panel for MDX. We use SQL Management Studio much like running sql queries but instead connect to Analysis Server engine:

   

Once we connect we can click the “New Query” button and we are ready to start writing MDX Code, your screen should now look something like this:

 

 

 

 

 

 

 

 

Our First MDX Query

So now what is the simplest query I can write that will return results? Let’s try this:

 

 

select from 

[Adventure Works]

Results returned should be much like this

Note if you get the message : “Executing the query ...

The Adventure Works cube either does not exist or has not been processed.” Chances are that the correct cube is not selected in the cube drop down.

So the first question us familiar with TSQL syntax ask is where is the Column List and how do we know what it is going to return. This is really a non dimensional query and returns the default measure for all scenarios. This would correspond to our whole x,y chart in previous lessons. In this case our default is “Reseller Sales Amount” so this query has returned us the total amount of reseller sales in our cube. We can verify by actually specifying the measure in the MDX as follows

Select [Measures].[Reseller Sales Amount] on Columns

from

[Adventure Works]

 

Notice the above query returned the exact same amount that the first query did, that is because it is defined as the default measure. The only difference is now we have to specify that we want it on the Columns ( we can also use “0” as an ordinal for columns) for display and also see the column label in the results. Lets not confuse the matter and assume that since we have a column axis that we are slicing by dimensions yet, we are still seeing the measure for all dimensions. So now I can show the manager this info, of course the first thing he says is this is for all product and all years “what good is it”. Alright lets slice this by product now. Lets bring in the x-axis or the product dimension. To do this we add it to our ordinal axis of 1 or Rows axis.

 

 

Select [Measures].[Reseller Sales Amount] on Columns

,[Product].[Product] on Rows

from

[Adventure Works]

 

 

 

Notice the above query returned the same value as before but it does now show that it is for all products. This is because the “All Products” is the default member of the product dimension. Now if we actually want a more common approach to dimension reporting we would want to “group by” individual products the Allmembers property allows us to do this

 

 

Select [Measures].[Reseller Sales Amount] on Columns

,[Product].[Product].[AllMembers] on Rows

from

[Adventure Works]

 

 

 

Now we see the Reseller Sales Amount divided out by products. You may note that there are a lot of nulls indicating that there are no Reseller Sales Amount entries for certain products. Later in this series we will show how to filter these empty values out. For now lets concentrate on how we get the y axis in our results or months(part of Date dimension) which would be much like a group by product,month in SQL.

 

 

 

 

Notice that dimension to slice by are specified in the query like “([Product].[Product].AllMembers,[Date].[Month of Year].Allmembers)”This is a lot like we do in mathematics coordinates (x,y) seperated by a comma and enclosed within parenthesis. This type of representation is called a tuple.You know this manager was pretty easy to please and this is all he needed. But wait a minute he also mentioned these great reporting capabilities to the home office in Birmingham Alabama and they would like me to stop by for a quick run at showing them some reports. I guess we know where the next stop will be.

 

Conclusion Rest Stop 2

This concludes our trip to the Atlanta rest stop where we covered SQL Management Studio, default members and how to slice a measure by multiple dimensions. Now it is on to Birmingham to get some more detailed mdx reports, for now travel safe.

 

 

 

Select [Measures].[Reseller Sales Amount] on Columns

,([Product].[Product].[AllMembers],[Date].[Month of Year].AllMembers) on Rows

from

[Adventure Works]

Print
Categories: Query Languages
Tags:
Rate this article:
5.0

MikeMollenhourMikeMollenhour

Other posts by MikeMollenhour

Please login or register to post comments.