First of all I want to thank Pragmatic Works for once again allowing me to have the opportunity to present and for so graciously hosting me. Secondly thank you to everyone who attended the MDX presentation.
Great News!, If you were not among the 500 attendees who made it to the live training you can view the recorded version here: MDX 101.
Want to skip the reading and watch my Q&A Video? MitchellSQL
MDX 101 Question and Answer
This ended up being a slightly longer post than I expected. Below is a summary list of the questions answered in this post.
- What tool did you use for deploying MDX code?
- How can you handle divide by zero errors in MDX?
- How can we use MDX in SSRS?
- Why does the children function not return the [All] member?
- How long does it take to become an MDX Expert?
- What books would you recommend for MDX?
We had a lot of great questions, many of which I will quickly answer here and some of which will lead to a series of follow up blogs. So let’s get started!
Q: What tool did you use for deploying the MDX code and can you provide the link?
A: Absolutely. The tool I used is a free tool called BidsHelper, you can download BidsHelper from CodePlex. For your convenience here is the link: BidsHelper
Q: How can you handle divide by zero errors when doing division, like in your Profit Margin example.
A: Ahh, great question. We can use the IIF function along with the IsEmpty function in MDX to check the denominator first to make sure the value is not empty or equal to 0. Here is an example:
First, here is the original query.
[Measures].[Profit] / [Measures].[Internet Sales Amount]
Ok, so next we need to check if the denominator [Internet Sales Amount] to see if it is empty or equal to 0. If it is then we will simply return the value NULL, if it is not then we will perform the division.
IIF(
ISEMPTY([Internet Sales Amount]) OR [Internet Sales AMount] = 0,
NULL,
[Measures].[Profit] /
[Measures].[Internet Sales Amount]), FORMAT_STRING = "PERCENT"
Q: How can we use MDX in SSRS?
A: Another great question and I apologize for not being able to cover this in the webinar. I will do a follow up blog and provide some more details in the coming weeks. For now let’s discuss the basics.
- Create a data source in SSRS to your Analysis Services Cube.
- Create a data set that uses your Analysis Services Cube Connection.
- Finally use the Query Designer to build out your MDX. Alternative you can write your own MDX by selecting the expression button located to the right of the Query window, don’t forget to remove the = sign:
Q: Why does the children function not bring back the all member?
A: This is a very common question in MDX, the quick answer is that the children function is a navigation function. When we use the children function on any member in the cube only the children of the that member is returned in the result set. Let’s walk through an example.
The following MDX Query returns the [All Products] member from the attribute hierarchy “Category”.
Now we can take the children of the member [All Products]. In the attribute hierarchy Category we should expect the categories to be returned.
Q: How long does it take to become an MDX Expert?
A: Well, I’m not an MDX Expert yet so I would recommend asking my good friend SQLDusty who is a wizard with MDX and has a great blog with 30 – 40 MDX related posts! Click here to check out his website: SQLDusty
Q: Can you recommend a book for MDX.
A: There are many books out there and I have read through quite a few of them. Sometimes I need to have things explained from many different perspectives before I finally understand them 
. Here are a couple books I recommend and why.
Microsoft SQL Server 2008 Analysis Services UNLEASHED. Although primarily SSAS focused this book has an awesome section on MDX, and in my opinion the author does a great job of explaining MDX. There are probably a hundred and fifty or so pages of MDX out of around 800 pages.
Expert Cube Development with SSAS multidimensional Models. Once again this is another SSAS focused book but has a good section on very commonly used design patterns. Chapter 6 is fully designated to calculations in the cube.
Practical MDX Queries is also a great book for someone who is brand new to MDX. This book walks through all the basic MDX Functions.
Once again thank you everyone for attending the webinar and for all the feedback received!
Mitchellsql 