In SQL, NULL means undefined value

Any SQL programmer can tell you that zero is not NULL, NULL is not zero, and NULL is not even equal to NULL either, because NULL means undefined. One undefined value cannot be equal to another undefined value.

In MDX, NULL means an empty cell

What about in MDX, how do we detect zero and NULL? What does even NULL mean? Since we are leaving the one dimensional SQL world, and are stepping into the multi-dimensional cube, NULL has a slightly different meaning. To me, it really means an empty cell. When the Internet Sales is showing as (null) for 11/11/2014, it means that the cell of {[Internet Sales], [11/11/2014]} is an empty cell. If you query the fact table, SELECT Internet Sales FROM factSales WHERE Date = 11/11/2014, it should return no rows back.

What if the Internet Sales is showing as 0 (zero) for 11/11/2014? The cell is no longer empty. the fact table should have one row WHERE Date = 11/11/2014, only that the Internet Sales is zero.

Detecting zero and NULL in MDX

How do we detect when Internet Sales is zero or when the cell is totally empty in MDX?

I wrote the following demo MDX code to see the various ways and the results.

I first created two values, a zero value and a NULL value.

Then I created three new values to detect the zero value, using three different ways

• 0 = 0?: is 0 = 0?. It should be true.
• 0 Is NULL?: It should be false.
• 0 IsEmpty?: It should be false.

I also created three new values to detect the NULL value, using three different ways

• NULL = 0?: a big question mark here.
• NULL Is NULL?: It should be false. Remember NULL is not equal to NULL in SQL.
• NULL IsEmpty?: It should be true. Remember NULL means empty cell in MDX.

Then I wrote the following MDX query.

Here is the result.

Zero is zero, AND NULL is also zero

Do the four results in red surprise you?

• Zero is zero, AND NULL is also zero.
• Zero is certainly not empty.
• NULL IS empty.

It did surprise me. I don’t have much explanation why MDX thinks NULL is zero. At this point, I am just taking in this fact.

Here is the code in text.

with

member [measures].[A Zero Value] as 0

member [measures].[A NULL Value] as null

member [measures].[0 = 0?] as

iif ( [measures].[A Zero Value] = 0,

“true”,

“false”

)

member [measures].[0 Is NULL?] as

iif ( [measures].[A Zero Value] is null,

“true”,

“false”

)

member [measures].[0 IsEmpty?] as

iif ( IsEmpty([Measures].[A Zero Value]) = True,

“true”,

“false”

)

member [measures].[NULL = 0?] as

iif ( [measures].[A NULL Value] = 0,

“true”,

“false”

)

member [measures].[NULL Is NULL?] as

iif ( [measures].[A NULL Value] is null,

“true”,

“false”

)

member [measures].[NULL IsEmpty?] as

iif ( IsEmpty([Measures].[A NULL Value]) = True,

“true”,

“false”

)

select { [Measures].[A Zero Value],

[measures].[0 = 0?],

[measures].[0 Is NULL?],

[measures].[0 IsEmpty?],

[Measures].[A NULL Value],

[measures].[NULL = 0?],

[measures].[NULL Is NULL?],

[measures].[NULL IsEmpty?]

} on 0

from    [RADM_REPORTING]

Empty cells, natural or user-defined can be a powerful way to optimize our MDX queries

Then you might think that since MDX thinks NULL is zero, then why many people set certain side of a MDX calculation to be NULL. Why cannot we all use zero, not mess with the NULL? Well, empty cells, natural or user-defined can be a powerful way to optimize our MDX queries, with the help of either the NONEMPTY() function or the Non EMPTY keyword that works only axes. Check out our book MDX with SSAS 2012 Cookbook on this topic.