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, Matrix and Aggregate()

All,
I keep reading that the Matrix is the perfect tool to use with OLAP data, but I am very confused about the efficacy of such a notion. As we know, OLAP is about precalculated aggregations, and the noticeable performance improvements that such an analytical database yields.
But RS answer to all of this is to calculate the aggregations at run-time,they recommend to bring in the leaf level data from the cubes, and then let RS aggregate the results at runtime.
This may an acceptable approach for simple MDX queries, but for advanced analysis -- it just does not make good sense. 
We desperately want to use RS for our Enterprise Reporting solution, but we are hard pressed to justify the performance issues (Cellset flattening + run-time aggregations) and the complexity of a MDX/ Matrix solution (if one exists)
Any examples of a multiple group (rows and columns) Matrix using an MDX 
datasource would be appreciated!!!
Thanks,
Jim

Are you asking about RS 2000 or RS 2005?

MDX queries designed in graphical query designer of RS 2005 can retrieve AS server aggregates directly within the query. You would then use the =Aggregate(...) function within the matrix cells instead of using regular aggregations. Based on the matrix cells' scopes, the Aggregate(...) function will determine the correct aggregate row from the flattened rowset. However, this means that the aggregate rows must be present and retrieved by the MDX query in the first place. Adjusting the MDX query based on the usage of the Aggregate function within the report is taken care of automatically by report designer if you designed the MDX query with the graphical MDX query designer of RS 2005.

-- Robert

|||

Robert,

Thanks for getting back to me -- I'm using RS 2000, so the good news is that it sounds like RS 2005 has addressed the issue of the MDX / Matrix complexity.

Any ideas on RS 2000? We won't be going to 2005 until Q1 2007.

Thanks,

Jim

|||

Technically, you could implement a full custom data extension for RS 2000. That data extension would need to implement the IDataReaderExtension interface (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_8je0.asp) among other interfaces and identify aggregate rows within the flattened rowset returned from a AS 2000 server.

However, this is a non-trivial task and requires a lot of effort to get everything working. Btw, RS 2000 already supports the Aggregate function - so you would then use it as described in my previous posting. Anyway, the Aggregate function is only useful if with a data extension that implements the IDataReaderExtension interface. None of the RS 2000 data extensions implement that functionality however.

Specific support for AS server aggregates is a feature that was added in RS 2005 in combination with a new RS 2005 Analysis Services data extension that works on top of the new AS 2005 AdoMd data provider.

-- Robert

|||

When I use the Aggregate() function instead of the default Sum() function in subtotals RS2005 returns nothing, i.e. it doesn't work.

The problem in all its simplicity: I have two measures in my MDX query:

Sales in dollars (can use Sum for subtotals)

MDX to aggregate measure over specific dimensions

Hi,

I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).

I was thinking that the solution would be to have an MDX expression of the form

([Measures].[Measure],
[D1].CurrentMember,
[D2].CurrentMember,
[D3].CurrentMember,
[D4].[(All)],
[D5].[(All)])

but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.

My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!

Thanks in advance.
James

Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
*
[Customer].[Country].[Country].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo)

You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

HTH,

Chris

|||
Hi Chris, many thanks for your reply.

This indeed worked. Going back to my previous example, I created a calculated member as:

(Root([D4]),
Root([D5]),
[Measures].[Measure])

and the measure is only sliced by dimensions D1, D2 and D3.

Thanks for your help!

James
|||

Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.

One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use

([D4].[All], [D5].[All], [Measures].[Measure])

|||
Hi, yes I thought those were my two options.

I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!

Many thanks for your help.
James

MDX Question

Can I do an MDX query with a start/stop filter specified as dates, but aggregate results by month? For example, could I query data between Jan 15, 2005 and April 14, 2005 and get results aggregated by month? I would expect four resulting rows or cells:

January (only contains data from the fifteenth to the thirty first)
February (data for entire month)
March (data for entire month)
April (only contains data from the first to the fourteenth)

I know how to filter by month and aggregate by month:

SELECT [Date].[Month].[2005-01]:[Date].[Month].[2005-04] ON Columns FROM [My Cube];

I also know how to filter by day and aggregate by day:

SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube];

But I don't know how to filter by day and aggregate by month. Is this at all possible using MDX?

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

|||

Mosha Pasumansky wrote:

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

Brilliant! That worked. Thanks!

MDX query to aggregate distinct count

Hi there

I'm after some help with an mdx query, I'm using SQL 2005 analysis services and I want to create a query which compares the most recent three months with three months 1 month prior. The measure i want to compare is a distinct count so can't use sum to combine. i attempted to use (for the current 3 months);

aggregate([Dim Date].[Calendar Year].CurrentMember.Lag(3) :

[Dim Date].[Calendar Year].CurrentMember,

[Measures].[Active Player Count])

but got an error that I can't use aggregate on a calculated member, the measure Active Player Count is a standard measure that has distinct count measure as it's aggregation.

Hi,

It's not clear from the MDX fragment here what exactly you're trying to do, but here's an example using Adventure Works which does something similar:

WITH MEMBER MEASURES.LASTTHREEMONTHS AS

AGGREGATE([Date].[Calendar].CURRENTMEMBER : [Date].[Calendar].CURRENTMEMBER.LAG(2),[Measures].[Customer Count])

MEMBER MEASURES.LASTTHREEMONTHSONEPRIOR AS

AGGREGATE([Date].[Calendar].CURRENTMEMBER.PREVMEMBER : [Date].[Calendar].CURRENTMEMBER.LAG(3),[Measures].[Customer Count])

SELECT {[Measures].[Customer Count], MEASURES.LASTTHREEMONTHS,MEASURES.LASTTHREEMONTHSONEPRIOR} ON 0,

[Date].[Calendar].[Calendar Quarter].&[2003]&[4].CHILDREN

ON 1

FROM

[Adventure Works]

HTH,

Chris

|||

thanks Chris

Looks like I had the same query but had the lag around the wrong way , I had the lag (3) in the first part of the equation rather than the second.

Derek

mdx query

I write the following MDX query that functions correctly:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { [PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] : [PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{[PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] :[PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

from [PMO_MDB]

If I try to insert the query inside Reporting Services substituting some data with query parameter in the following way I get an error:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{ STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@.PROJECTSTREAM, CONSTRAINED) ) ON COLUMNS FROM [PMO_MDB])

WHERE ( IIF( STRTOSET(@.PROJECTSTREAM, CONSTRAINED).Count = 1, STRTOSET(@.PROJECTSTREAM, CONSTRAINED), [PROJECT].[STREAM].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query seems sintaticcally correct !

So what happened !

Here is the error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Parser: The FY_MONTH_START parameter could not be resolved because it was referenced in an inner subexpression. (msmgdsrv)

BUTTONS:

OK

Having the same problem in reporting services. Have you found anything?|||

Sounds like it's having a problem with the STRTOMEMBER function inside of the custom member definition. Since you know that your member will only be a single value, you could always write the query in dynamic mdx and pass the parameter values in. The top would look something like this:

Code Snippet

=" WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS , " +

It's a little tricky to get the designer to accept dynamic mdx. You have to write the original mdx query first. Then you have to click on the elipses for the dataset to bring up the dataset dialog. Then in the dialog, click the "f(x)" button to fill in the text. The dialog box that comes up will allow you to enter in the "=" sign and do the rest of your dynamic mdx.

|||

Thanks for the reply

I solved simply removing the character ' in the member definition !

Don't know why .... but now it functions -)

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )

.....

Cosimo

MDX issue with SUM function and + operator

Hi,

Facing a problem with the sum and aggregate functions when using the + operator
The simplified representation of the problem is as below

The following query returns null.

[Measures].[xyz] is a base measure, no mdx calculation

with member TestValue as
'[Measures].[xyz] + [Measures].[xyz]'

member SummedValue as
'sum (
{
DimensionA.Level2.Member1,
DimensionA.Level2.Member2,
DimensionA.Level2.Member3,
DimensionA.Level2.Member4
}
,TestValue
)

select
SummedValue on columns
from <cube>

The above query returns a valid integer value

when TestValue is just '[Measures].[xyz]' and not the sum(+) or subtraction(-),
though it works when it is multiplication or division (* or /).

Is there anything wrong in the usage or is there a workaround.

Regards

I ran the following query against the AdventureWorks cube, and it delivered the desired results.

Code Snippet

with member TestValue as

'[Measures].[Internet Order Count]+[Measures].[Internet Order Count]'

member SummedValue as

'sum (

{

[Customer].[Customer Geography].[State-Province].&[FL]&[US],

[Customer].[Customer Geography].[State-Province].&[AL]&[US],

[Customer].[Customer Geography].[State-Province].&[GA]&[US],

[Customer].[Customer Geography].[State-Province].&[MI]&[US]

}

,TestValue

)'

select

SummedValue on columns

from [Adventure Works]

Can you give some details on how your cube differs from this?

|||

we have this problem only in a particular environment.

Is this dependent on Service packs of SQL Server or 32/64 bit versions of SQL Server.

Regards

|||I tested in an XP SP2 32-bit / SQL Server 2005 SP2 environment. I can try a few others tomorrow. It would be helpful if you let the forum know what type of environment you are running in.|||

This might be a bit of a long shot, but try changing your expression to make it a bit more explicit. The '+' operator is overloaded to also act as a union operator, because you are passing in two members, maybe SSAS is choosing to make a set of two members rather than doing a numeric addition.

Try changing your calc expression from

with member TestValue as
'[Measures].[xyz] + [Measures].[xyz]'

To:

with member TestValue as ([Measures].[xyz]) + ([Measures].[xyz])

Note that the single quotes around inline calculated members are no longer required in SSAS 2005 and in fact it is better not to include them as SSAS can then report the location of any errors with more accuracy. (plus you get better syntax coloring support in SSMS)

|||

thanks for the quick replies...

this does not work in a 64-bit SQL Server SP1 ......SSAS version 9.00.2176.00 environment

but works in 32-bit SQL Server SP2......SSAS version 9.00.3042.00

this lists the fixes in SQL Server 2005 SP2

http://support.microsoft.com/default.aspx/kb/921896

is it linked to this fix

919957 (http://support.microsoft.com/kb/919957/) FIX: Some cells return the NULL value instead of returning the actual value when you query a dimension that contains a parent/child hierarchy in a SQL Server 2005 Analysis Services cube

Regards