O'Reilly logo

MDX with Microsoft SQL Server 2008 R2 Analysis Services: Cookbook by Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Optimizing MDX queries using the NonEmpty() function

NonEmpty() is a very powerful MDX function. It is primarily used to reduce sets in a very fast manner.

In the previous recipe we used it to get the count of children of the current member that are not empty for the measure Order Count. In this recipe, we'll show how it can be used again, this time using the Customer and Date dimensions.

Getting ready

Start a new query in SSMS and check that you're working on the right database. Then write the following query and execute it:

SELECT { [Measures].[Internet Sales Amount] } ON 0, NON EMPTY Filter( { [Customer].[Customer].[Customer].MEMBERS } * { [Date].[Date].[Date].MEMBERS }, [Measures].[Internet Sales Amount] > 1000 ) ON 1 FROM [Adventure Works] ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required