robert bogue [mvp]

I'm having some problems when using the DistinctUsers measure from the
mscsWebUsage cube. All is well until I select dates at the day level
instead of the year, quarter, or month level.

I'm just now digging into it. On the surface it looks to be something about
the way that measure is being calculated/derived. Has anyone seen this
issue before?

Rob Bogue
MS MVP Windows Server-Networking, MCSE, MCSA:Security, etc.

robert bogue [mvp]

A little more specifically. The following MDX query works on the
mscsWebUsage cube...

SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]} DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[All Date].[2004].[3].[September])

And this MDX query doesn't...

WITH MEMBER [Date].[XL_QZX] AS
'Aggregate ( { [Date].[All Date].[2004].[3].[September].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]} DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[XL_QZX])

It's not clear to me why that is. The second query is, obviously, from
Excel. I'm not sure why the DistinctUser measure would suddenly break.

Any ideas?

Rob Bogue
MS MVP Windows Server-Networking, MCSE, MCSA:Security, etc.

brianbl

This might be because the [Week of Year] dimension doesn't have days in it.
I've forwarded you query off to an internal expert.
--------------------
From: "Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com>
References: <uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl>
Subject: Re: Distinct Users when Specific Dates are selected
Date: Thu, 9 Dec 2004 15:30:29 -0500
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
Message-ID: <OYknz3i3EHA.4072@TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.commerceserver.****ysis
NNTP-Posting-Host: 192-pool1.ras15.ilchi-e.alerondial.net 206.148.92.192
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.****ysis:2359
X-Tomcat-NG: microsoft.public.commerceserver.****ysis

A little more specifically. The following MDX query works on the
mscsWebUsage cube...

SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]}
DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[All Date].[2004].[3].[September])

And this MDX query doesn't...

WITH MEMBER [Date].[XL_QZX] AS
'Aggregate ( { [Date].[All Date].[2004].[3].[September].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]}
DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[XL_QZX])

It's not clear to me why that is. The second query is, obviously, from
Excel. I'm not sure why the DistinctUser measure would suddenly break.

Any ideas?

Rob Bogue
MS MVP Windows Server-Networking, MCSE, MCSA:Security, etc.


This posting is provided "AS IS" with no warranties, and confers no rights.

EBusiness Server Team

brianbl

I got this response back from the person most familiar with the CS200x
cubes...


The second query is attempting to sum a distinct count measure for two days
(09/30/2004 and 09/16/2004). Since distinct count measures are non-additive
I believe this is the reason for the failure.

A second issue is that if they are interested in a range of dates for
non-additive measures then the query syntax for the date should show the
range using the colon (they used a comma).

{ [Date].[All Date].[2004].[3].[September].[09/30/2004]:[Date].[All
Date].[2004].[3].[September].[09/16/2004] }

--------------------
From: "Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com>
References: <uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl>
Subject: Re: Distinct Users when Specific Dates are selected
Date: Thu, 9 Dec 2004 15:30:29 -0500
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
Message-ID: <OYknz3i3EHA.4072@TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.commerceserver.****ysis
NNTP-Posting-Host: 192-pool1.ras15.ilchi-e.alerondial.net 206.148.92.192
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.****ysis:2359
X-Tomcat-NG: microsoft.public.commerceserver.****ysis

A little more specifically. The following MDX query works on the
mscsWebUsage cube...

SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]}
DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[All Date].[2004].[3].[September])

And this MDX query doesn't...

WITH MEMBER [Date].[XL_QZX] AS
'Aggregate ( { [Date].[All Date].[2004].[3].[September].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First Visit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]}
DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY HIERARCHIZE({DrillDownLevel({[Week of Year].[All Week of
Year]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM [mscsWebUsage] WHERE ([Date].[XL_QZX])

It's not clear to me why that is. The second query is, obviously, from
Excel. I'm not sure why the DistinctUser measure would suddenly break.

Any ideas?

Rob Bogue
MS MVP Windows Server-Networking, MCSE, MCSA:Security, etc.


This posting is provided "AS IS" with no warranties, and confers no rights.

EBusiness Server Team

robert bogue [mvp]

Brian:

Yea, It's definitely a distinct count issue, however, its unclear how to
resolve it. As for changing the syntax of the MDX, I've not tried seeing
what Excel will do if I modify it's MDX underneath it and I'm unclear what
results I would get if i used the range syntax as you suggest -- It would
seem to me that this wouldn't allow non-contigious date ranges.

I asked some folks to track down Sean Boon [MS] who had posted on some
DISTINCT COUNTs and their various limitations in the current product. The
URL I got back was
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/distinct2.asp

This, however, seems to suggest cube redesign not MDX changes.

I sent this off towards Caesar and John and they're connecting with Sean.

Rob