O'Reilly logo

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition by Sherry Li, 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

The NonEmpty() function is a very powerful MDX function. It is primarily used to improve query performance by reducing sets before the result is returned.

Both Customer and Date dimensions are relatively large in the Adventure Works DW 2016 database. Putting the cross product of these two dimensions on the query axis can take a long time. In this recipe, we will show how the NonEmpty() function can be used on the Customer and Date dimensions to improve the query performance.

Getting ready

Start a new query in SSMS and make sure that you are working on the Adventure Works DW 2016 database. Then write the following query and execute it:

SELECT { [Measures].[Internet Sales Amount] } ON 0, NON EMPTY ...

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