 |
|
 |
|
|
 |
Distinct Users when Specific Dates are selected |
 |
 |
|
|
12-09-04 10: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.
[ Post a follow-up to this message ]
|
|
|
 |
|
 |
|
 |
|
|
|
Re: Distinct Users when Specific Dates are selected |
|
|
|
|
12-09-04 10:49 PM
A little more specifically. The following MDX query works on the
mscsWebUsage cube...
SELECT {[Measures].[DistinctUsers], [Measures].[First V
isit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]} D
IMENSION
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].[Septem
ber].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004
] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First V
isit],
[Measures].[Repeat Users], [Measures].[Repeat Users Pct.]} D
IMENSION
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.
>
[ Post a follow-up to this message ]
|
|
|
|
|
|
|
|
|
|
|
Re: Distinct Users when Specific Dates are selected |
|
|
|
|
12-10-04 10: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 V
isit],
[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].[Septem
ber].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004
] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First V
isit],
[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
[ Post a follow-up to this message ]
|
|
|
|
|
|
|
|
|
|
|
Re: Distinct Users when Specific Dates are selected |
|
|
|
|
12-11-04 01:46 AM
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 V
isit],
[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].[Septem
ber].[09/30/2004] ,
[Date].[All Date].[2004].[3].[September].[09/16/2004
] } )'
SELECT {[Measures].[DistinctUsers], [Measures].[First V
isit],
[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
[ Post a follow-up to this message ]
|
|
|
|
|
|
|
|
|
|
|
Re: Distinct Users when Specific Dates are selected |
|
|
|
|
12-13-04 01:28 PM
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...r />
inct2.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].[0
9/30/2004]:[Date].[All
> Date].[2004].[3].[September].[09/16/2004] }
>
> --------------------
> From: "Robert Bogue [MVP]" <robert[dot]bogue[at]crowechizek
1;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!TK2MSFTNGP
10
> 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].[Sept
ember].[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[nosp
am]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
[ Post a follow-up to this message ]
|
|
|
|
|
|
|
|
|
|
|
Sponsored Links |
|
|
|
|
 |
All times are GMT. The time now is 08:37 AM. |
 |
|
|
 |
|
 |
|
|
 |
|
Forum Rules:
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
|
HTML code is OFF
vB code is ON
Smilies are ON
[IMG] code is OFF
|
|
|
|
Medical and Health forum | Computer Games Reviews | Graphics design forum
|
 |
|
 |
|
