Analysis Services: For Distinct Count measure NULL = 0

If you are to look at the table of values where you want to do a distinct count on the ID column

ID

ColA

NULL

blah

NULL

blah

0

blah

3

blah

1

blah

1

blah

2

blah

2

blah

Within SQL, you will get a value of 4 in which there are four distinct values, 0, 1, 2, 3 and NULL is not counted.

But within Analysis Services, if you were to put a distinct count measure on top of the ID column, you would also get a value of 4 for four distinct values, but those distinct values are 1, 2, 3, and 0.  In this situation, NULL and 0 are the same value.  This is a little bit more apparent if your table had the values of

ID

ColA

1

blah

2

blah

2

blah

3

blah

NULL

blah

Within SQL, you will get a value of 3 (distinct values of 1, 2, and 3) but within AS, you will get a value of 4 (distinct values of 1, 2, 3, and NULL or 0).  There are various philosophical reasons why this is happening ranging from the fact that there are actually quite a few definitions for what NULL is (I really don’t want to get into that) and within AS we’re looking at things from a multi-dimensional point of view (i.e. from the dimension members) hence the exclusion of a NULL value implies exclusion of the rows.  Regardless of the philosophical point of view on this, one should just be aware of this when working with their distinct count measures.

Oh note, thanks to John Lam for reminding me that if you turn on the NullProcessing property it will preserve the NULL value – i.e. the first table will have 5 distinct values of 0, 1, 2, 3, and NULL.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s