SSAS 度量值中的distinct count局聚合方式会数为null的值

我们来看一个例子

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.

 

By the way,  if you turn on the NullProcessing property in Sqlserver it will preserve the NULL value – i.e. the first table will have 5 distinct values of 0, 1, 2, 3, and NULL.

 

如何解决在SSAS中distinct count去数为null的值？

答案是在Sqlserver中从新做一个视图，将事实表中要distinct count的那一列为null的行全部用sql语句的where条件过滤掉。。。请看老外的回答，其中ID这一列就是要在CUBE中distinct count的列：

I had the same problem. I solved it by creating a view with a WHERE condition ISNULL(ID,0) <> 0 and then creating a Distinct Count out of this view