SQL BI

My Experience - Troubleshooting SQL BI Stack

MDX: Nonempty v/s NonEmpty

MDX: Nonempty v/s NonEmpty

Rate This
  • Comments 3

Nonempty

Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.

NONEMPTY(set_expression1 [,set_expression2])

set_expression1

A valid Multidimensional Expressions (MDX) expression that returns a set.

set_expression2

A valid Multidimensional Expressions (MDX) expression that returns a set.

In this example, getting all Countries Reseller Sales Amount for year 2005, in output you can observer I am also getting countries with Reseller Sales Amount value as Null

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,{[Geography].[Country].Children} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

image

What changes I need to incorporate to a query so that I will get result something like this:

image

Here is the query with nonempty, query is returning set of tuples with non null values.

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,{NonEmpty([Geography].[Country].Children)} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

! Non-empty is a characteristic of the cells references by the tuples, not the tuples themselves.

Non Empty

If we want to use Non Empty to get the same output we need to make below mentioned modifications:

SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
,Non Empty{([Geography].[Country].Children)} ON ROWS
FROM [Adventure Works]
WHERE
  [Date].[Calendar Year].&[2005];

image

Non Empty operator works on the top level of the query, which means sets defined at axis are generated first and then the tuple having NULL values are removed.

Can be easily proved with below mentioned query:

SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
,{
    Filter
    (
   ([Geography].[City].[City].members
      * 
[Date].[Calendar].[Date].members)
     ,
      [Measures].[Internet Sales Amount] >4000
    )
  } ON ROWS
FROM
[Adventure Works]

image

Which  means without NonEmpty or Non Empty, 697356 cells are calculated, now lets using Non Empty

Lets try with Non Empty

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,Non Empty {
Filter
(
([Geography].[City].[City].members
*
[Date].[Calendar].[Date].members)
,
[Measures].[Internet Sales Amount] >4000
)
} ON ROWS
FROM
[Adventure Works]

image

No change in Cell Calculated because internally as explained earlier Non Empty work on Top Level of Query and after getting complete output it shows cells with Non Empty Value.

But If we execute the query with nonempty() we will get the difference, no of cell calculated are only 3211 which mean unlike Non Empty its returning set of tuples that are not empty from the specified set.

 

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
, {
Filter
(
nonempty([Geography].[City].[City].members
*
[Date].[Calendar].[Date].members)
,
[Measures].[Internet Sales Amount] >4000
)
} ON ROWS
FROM
[Adventure Works]

image

Comments
  • Nice information... Feels good to know how Performance works in the background, even if we can achieve the same result with both the commands.. :)

  • Nice.....

  • Thanks for the tip, and I especially appreciate the usage of MDX Studio to clearly prove the differences in performance!

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post