Have you ever wondered how can I return all my non selling products in MDX? Well, if you found your way to this post hopefully you have! Fortunately this is actually quite easy to do.
ISEMPTY FUNCTION
The ISEMPTY function returns a BOOLEAN value. If the cell evaluated is empty the value of TRUE is returned and if it is not empty then the value of FALSE is returned. This is a great function to nest inside other functions. Let me show you how we can use this function along with the filter function in MDX.
MDX FILTER FUNCTION
Here is a simple MDX query that returns [Internet Sales] on columns and [Products] on rows.
In this query we can quickly see we definitely have products that have never had internet sales. Now how can we return only the products that have never had sales? Well there are many ways to do this of course but I want to show how to use the Filter Function along with the ISEMPTY function for this example.
Here I use the set of members returned from [Product].[Product].Children as the first parameter in the filter function. For the second parameter in the filter function I use the ISEMPTY function on [Measure].[Internet Sales Amount]. This means that only products from parameter 1 that have no sales will be returned in the final result set. In the result set above we can see that only products without sales are being returned in the final result set. Easy right?
I’m trying something a little different with this blog. I have created a new channel over on YouTube, please feel free to search MitchellSQL and subscribe to my channel! 🙂 Instead of writing everything out in this blog post I created a quick 8 minute video. Please watch the video and let me know if you prefer videos or blog post! Thanks for looking!
Hey everyone, in this blog I want to address a very common MDX Question. What is the difference between the NON EMPTY keyword and NONEMPTY function? To take it a step further which one should you use?
Non Empty keyword VS NONEMPTY Function.
The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.
Should I use NON EMPTY keyword or NONEMPTY function?
Ok Mitchell, so you told me when each of these are evaluated but really you haven’t told me anything up until this point. Can you tell me which one I should use already? Well, unfortunately, it depends. Let’s walk through an example of each using the BOTTOMCOUNT function.
BOTTOMCOUNT FUNCTION with NON EMPTY Keyword
In this example I’m returning the bottom ten selling products for internet sales. Notice that I have returned all products that have no internet sales, this is not necessarily a bad thing, maybe you want to return products that don’t have sales.
However if you don’t want to return these products then we can try using the NON EMPTY keyword. In the below example you can see the results when I add NON EMPTY to the ROWS axis.
WHOOOAAA, what happened?? A lot of people would have expected the results here to show the bottom ten products that DID have sales. However, that is not the case, remember that I said the NON EMPTY keyword is evaluated LAST after all axes have been evaluated. This means that first the bottom ten selling products which have $0 in sales are first returned and then the NON EMPTY keyword removes all that empty space from the final result.
BOTTOMCOUNT function with NONEMPTY function.
So let’s try this again, if you want to return the bottom ten products that had sales then we must first remove the empty space before using the BottomCount function. Take a look at the code below:
In this code we first remove the empty space before using the BOTTOMCOUNT function. The result is we return the bottom ten products that had internet sales. Once again neither one is right or wrong here it just depends on what you want in your final result.
NON EMPTY Keyword vs. NONEMPTY Function – Performance
There is a very common misconception that the NONEMPTY function always performs better than the NON EMPTY keyword, however this is not true. My good friend Dustin Ryan has a great blog discussing this point. Head on over to his blog here: SQLDusty
Thanks for taking a look at this blog and I hope you enjoyed it!
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:
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.
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!
. Here are a couple books I recommend and why.
Mitchellsql 