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 ***

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 ***

MDX-why the null result is missing?

Hi,friends, this is a newbie quetion about MDX.

I just write the MDX below:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007] } on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the result very well:

2007

ADTD 128

Then I change the MDX to this:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007],[Time].[TimeLevel].[Year].&[2008]} on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the same result:

2007

ADTD 128

It's very strange to me. Because there is no 2008's record in the Cube, I think the result ought to be :

2007 2008

ADTD 128 0

But why the result just makes the 2008's column Missing?

Thanks!

What is the MDX formula for KPIValue("ADTD"); and can you reproduce this problem with a KPI query in Adventure Works?|||

the MDX formula for KPIvalue("ADTD") is just refer to an Measure which Count the records of the facttable. I think this is not the answer. You could reproduce this problem with a KPI query in Adventrue Works.

And not only the KPI query, but every MDX query is like this. It looks like it's a common law of the MDX. You could have a simple test like this:

select ([Measures].) on rows, {[Time].[TimeLevel].[Year].&[2008]} on columns from [Cube] where [Agent].[AgentLevel].&[2]

Because there is no 2008's records in the facttable, and you will get the result like this:

A

not like this:

2008

A 0

but I really want to get the second result, because if there is no 2008's records, the right answer of the query ought to be 0.

Please have a test based on the Adventrue Works., thanks!

|||

"because if there is no 2008's records, the right answer of the query ought to be 0" - well, the "right" answer depends on the scenario, but maybe applying CoalesceEmpty() will help you, like:

With

Member [Measures].[OrdersNoNull] as

CoalesceEmpty([Measures].[Order Count],0)

select

{[Date].[Calendar].[Month].&[2004]&[7],

[Date].[Calendar].[Month].&[2004]&} on 0,

{[Measures].[Order Count],

[Measures].[OrdersNoNull]} on 1

from [Adventure Works]

-

July 2004 August 2004
Order Count 976 (null)
OrdersNoNull 976 0

|||

Puri , you don't catch my mean. Use your example, you get the answer:

July 2004 August 2004
Order Count 976 (null)

And this is also what I want. But I got the answer:

July 2004

Order Count 976

The Agust 2004 column is missing. That's my problem. I run the MDX , and return a result without August 2004 column. If the result is (null), that's OK, that's what exactly I want. My problem is not to change (null) to 0. My problem is just the column is missing.

Thanks.

|||What tool are using to run your MDX query? My results came from Management Studio - so are you saying that you don't get the same results, when you run the above query in Management Studio?|||

Yes, me in Management Studio too. And I also tried the ADOMD.net, the same result.

You could try to only select one Measure like me have done.

Thanks.

|||To be clear, could you post an actual Adventure Works query, with the results you get in Management Studio, so that others can try to reproduce your problem?|||

eh,you could test it at a very simple way.

Just in the MDX you select a Time not existed. For example, if there is from 2000 to 2008's data in the cube, and no 2050 's data in the cube. Then you JUST select the 2050's in the MDX query. Then you will get the result I said. the 2050's column is missing, not the (NULL).

|||

I think it sounds very reasonable though? You dont have any record of the year you specified in your MDX query, then of course the result retured with nothing on that year?

With best regards,

Yours sincerely,

MDX-why the null result is missing?

Hi,friends, this is a newbie quetion about MDX.

I just write the MDX below:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007] } on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the result very well:

2007

ADTD 128

Then I change the MDX to this:

select KPIValue("ADTD")on rows, {[Time].[TimeLevel].[Year].&[2007],[Time].[TimeLevel].[Year].&[2008]} on columns from [TargetCube] where [Agent].[AgentMember].&[2]

And this return the same result:

2007

ADTD 128

It's very strange to me. Because there is no 2008's record in the Cube, I think the result ought to be :

2007 2008

ADTD 128 0

But why the result just makes the 2008's column Missing?

Thanks!

What is the MDX formula for KPIValue("ADTD"); and can you reproduce this problem with a KPI query in Adventure Works?|||

the MDX formula for KPIvalue("ADTD") is just refer to an Measure which Count the records of the facttable. I think this is not the answer. You could reproduce this problem with a KPI query in Adventrue Works.

And not only the KPI query, but every MDX query is like this. It looks like it's a common law of the MDX. You could have a simple test like this:

select ([Measures].) on rows, {[Time].[TimeLevel].[Year].&[2008]} on columns from [Cube] where [Agent].[AgentLevel].&[2]

Because there is no 2008's records in the facttable, and you will get the result like this:

A

not like this:

2008

A 0

but I really want to get the second result, because if there is no 2008's records, the right answer of the query ought to be 0.

Please have a test based on the Adventrue Works., thanks!

|||

"because if there is no 2008's records, the right answer of the query ought to be 0" - well, the "right" answer depends on the scenario, but maybe applying CoalesceEmpty() will help you, like:

With

Member [Measures].[OrdersNoNull] as

CoalesceEmpty([Measures].[Order Count],0)

select

{[Date].[Calendar].[Month].&[2004]&[7],

[Date].[Calendar].[Month].&[2004]&} on 0,

{[Measures].[Order Count],

[Measures].[OrdersNoNull]} on 1

from [Adventure Works]

-

July 2004 August 2004
Order Count 976 (null)
OrdersNoNull 976 0

|||

Puri , you don't catch my mean. Use your example, you get the answer:

July 2004 August 2004
Order Count 976 (null)

And this is also what I want. But I got the answer:

July 2004

Order Count 976

The Agust 2004 column is missing. That's my problem. I run the MDX , and return a result without August 2004 column. If the result is (null), that's OK, that's what exactly I want. My problem is not to change (null) to 0. My problem is just the column is missing.

Thanks.

|||What tool are using to run your MDX query? My results came from Management Studio - so are you saying that you don't get the same results, when you run the above query in Management Studio?|||

Yes, me in Management Studio too. And I also tried the ADOMD.net, the same result.

You could try to only select one Measure like me have done.

Thanks.

|||To be clear, could you post an actual Adventure Works query, with the results you get in Management Studio, so that others can try to reproduce your problem?|||

eh,you could test it at a very simple way.

Just in the MDX you select a Time not existed. For example, if there is from 2000 to 2008's data in the cube, and no 2050 's data in the cube. Then you JUST select the 2050's in the MDX query. Then you will get the result I said. the 2050's column is missing, not the (NULL).

|||

I think it sounds very reasonable though? You dont have any record of the year you specified in your MDX query, then of course the result retured with nothing on that year?

With best regards,

Yours sincerely,

MDX: Null value to replace column in Query

I have an MDX query that is not doing what I want it to do.

Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:

i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL

Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL

I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!

My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,

NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])

) ON 1
FROM Mimir04

But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned

What exactly is the solution to returning an empty column?

Could you please clarify whether you'd like empty rows or empty columns?

Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?

Thank you

MDX NON_EMPTY_BEHAVIOR value 0 or null

Hello,

I want the value for NON_EMPTY_BEHAVIOR to be 0 or null.

This can't be done with:

FORMAT_STRING = "€ #,#;-€ #,#;€ 0",

NON_EMPTY_BEHAVIOR = 0,

VISIBLE = 1 ;

or

FORMAT_STRING = "€ #,#;-€ #,#;€ 0",

NON_EMPTY_BEHAVIOR = null,

VISIBLE = 1 ;

I can create a named calculation, use this in the measure group and in the NON_EMPTY_BEHAVIOR but I don't like this construction.

FORMAT_STRING = "€ #,#;-€ #,#;€ 0",

NON_EMPTY_BEHAVIOR = { [Zero] },

VISIBLE = 1 ;

Does someone has a better solution?

Regards

Hessel Appers

Hello! Perhaps you got this wrong. NON_EMPTY_BEHAVIOR is not about how you display zero or null in cells.

It is about optimizing calculated measures and you always refer to a measure for this property.

HTH

Thomas Ivarsson