|
Home > Archive > Commerce Server Analysis > December 2004 > Distinct Users when Specific Dates are selected
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Distinct Users when Specific Dates are selected
|
|
| Robert Bogue [MVP] 2004-12-09, 5:49 pm |
| 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] 2004-12-09, 5:49 pm |
| 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.
"Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com> wrote in
message news:uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl...
> 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.
>
| |
| Brian Blum [MSFT] 2004-12-10, 5:50 pm |
| 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.analysis
NNTP-Posting-Host: 192-pool1.ras15.ilchi-e.alerondial.net 206.148.92.192
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.analysis:2359
X-Tomcat-NG: microsoft.public.commerceserver.analysis
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.
"Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com> wrote in
message news:uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl...
> 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.
>
This posting is provided "AS IS" with no warranties, and confers no rights.
EBusiness Server Team
| |
| Brian Blum [MSFT] 2004-12-10, 8:46 pm |
| 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.analysis
NNTP-Posting-Host: 192-pool1.ras15.ilchi-e.alerondial.net 206.148.92.192
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.analysis:2359
X-Tomcat-NG: microsoft.public.commerceserver.analysis
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.
"Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com> wrote in
message news:uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl...
> 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.
>
This posting is provided "AS IS" with no warranties, and confers no rights.
EBusiness Server Team
| |
| Robert Bogue [MVP] 2004-12-13, 8:28 am |
| 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/d...l/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
"Brian Blum [MSFT]" <brianbl@online.microsoft.com> wrote in message
news:iy%23BCox3EHA.3696@cpmsftngxa10.phx.gbl...
>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.analysis
> NNTP-Posting-Host: 192-pool1.ras15.ilchi-e.alerondial.net 206.148.92.192
> Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.analysis:2359
> X-Tomcat-NG: microsoft.public.commerceserver.analysis
>
> 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.
>
> "Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek[nospam]com> wrote in
> message news:uja3Mng3EHA.3092@TK2MSFTNGP10.phx.gbl...
>
>
>
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> EBusiness Server Team
|
|
|
|
|