Measure with "Distinct Count" Aggregate Function and Null value

I Have a cube with many measures. One of them use as "Aggregate
Function" the function "Distinct Count". But some records of fact table
has null value in the field of this measure. In SQL Server, when I
execute a "SELECT COUNT (DISTINCT field)" command, it eliminates the
Null value, as showed in the next exemple:
Table X
field A
1122
2345
4567
1122
1122
2345
null
null
When a execute the command:
SELECT COUNT (DISTINCT A) FROM X
I recieve "3" as result.
But when I create a measure over the field A in a cube and I choose the
aggregate funtion "Distinct Count", I recieve "4" as result. I think
that Analysis Services is considering "null" as one of distinct values
of the field A, but I dont want that, because it makes no sense for me.
Is there a way to eliminate the null value in Analysis Services, to show
"3" as the result in the same way I receive in SQL Server?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***I am new to SQL server, but experieced in Oracle.
My top of mind test would be:
select count(distinct *) from x => 4
select count(distinct a) from x => 3
It's standard SQL behaviour, I think.
pls verify, tks|||When a execute the command "select count(distinct *) from x" , I recieve
"4" as result and when a execute the command "select count(distinct a)
from x", I recieve "3" as result.
But my problem is not with SQL Server. My problem is in Analysis
Services. I created a measure over the field "a", with the aggregate
function "Distinct Count". But the result in Analysis Services is "4"
and not "3", as I expected.
Do you have a clue?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***|||try to create a new cube (copy/paste the current one)
then create a view on the database like:
select * from mytable where mycolumntodcount IS NOT NULL
use this view in the new cube.
this cube will contain only the dcount measure and the null values are
ignored.
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:ehCLkEZAGHA.2272@.TK2MSFTNGP11.phx.gbl...
> When a execute the command "select count(distinct *) from x" , I recieve
> "4" as result and when a execute the command "select count(distinct a)
> from x", I recieve "3" as result.
> But my problem is not with SQL Server. My problem is in Analysis
> Services. I created a measure over the field "a", with the aggregate
> function "Distinct Count". But the result in Analysis Services is "4"
> and not "3", as I expected.
> Do you have a clue?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***|||I tried this solution and It works. But I dont want to create a new
cube. That will be my last option. Is not possible to ignore the null
vules with "distinct count" as the aggregate function? Is not there a
propriety that I can configure?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***|||the only other option I can propose is:
add a dimension in the cube, add a column in the fact table called "ToCount"
which contains a Y/N or 0/1 value linked to this new dimension.
case when MyColumn is null then 'N' else 'Y' end as ToCount
hide the dimension.
Rename the dcount measure to HiddenDcount, hide this measure
create a calculated measure which is:
(measures.HiddenDCount, MyDummyDimension.&[Y])
this ignore the null values.
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:u9ciH0kAGHA.2040@.TK2MSFTNGP14.phx.gbl...
> I tried this solution and It works. But I dont want to create a new
> cube. That will be my last option. Is not possible to ignore the null
> vules with "distinct count" as the aggregate function? Is not there a
> propriety that I can configure?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***