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 don´t 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.developersdex.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.developersdex.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.developersdex.com ***

I tried this solution and It works. But I don´t 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.developersdex.com ***

Even if you didn't have the NULL challenge with Distinct Count, creating a
separate cube for the Distinct Count is a best practice. There are
aggregation and query performance benefits.

The dimensional granularity to the distinct count cube (with the NOT NULL
filter) would be identical to the other cube, so any queries against the
virtual cube would not require any special calculations.

Erik
----
Erik Veerman
erik (at) solidqualitylearning.com

"Paulo Andre Ortega Ribeiro" wrote:

>
> I tried this solution and It works. But I don4t 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.developersdex.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 don´t 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.developersdex.com ***

Here another idea:
Add a hidden measure "NULL_Exists" that's a 0 or 1 based on whether the
distinct count column is NULL or not (Null=1). Use the MAX aggregate type
(the result telling you whether there was a null or not across the
dimensionality you are looking at)
Use a calculated measure that = [DistinctCountMeasure] - [NULL_Exists]

So, if there wasn't a NULL, the NULL_Exists returns a 0 and the distinct
count isn't changed. If there was a NULL, then the result is the distinct
count - 1.

----
Erik Veerman
erik (at) solidqualitylearning.com


"Jéjé" wrote:

> 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 don´t 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.developersdex.com ***
>
>
>