Hello,

We want to count the total unique websitevisitors over a certain
period. We have a cube we call VisitorCube, with a visitormeasure with
the aggregation distinct count. The period is filtered with the
following member:

MEMBER [Calendar].[Period] AS 'SUM([Calendar].[All
Calendar].[2003].[June].[1]:[Calendar].[All
Calendar].[2003].[July].[31]'

This member describes a period of 2 months: june and july. The result
of the query in which this member is used (in the where-clause) is for
example 100. In other words: 100 unique visitors have visited the
website during the given period. We use the following MDX-query:

WITH MEMBER [Calendar].[Period] AS 'SUM([Calendar].[All
Calendar].[2003].[June].[1]:[Calendar].[All
Calendar].[2003].[July].[31])'
SELECT {[Measures].[VisitorId]} ON COLUMNS
FROM VisitorCube
WHERE ([CalendarDim].[Period])

The problem is as follows: this value of 100 is wrong. The actual
number of visitors is for example 90. In our query the total number of
visitors is the sum of number of visitors per day. We use 'distinct'
to ensure uniqueness of visitorcounts, but somehow this doesn't work
quite well. Below a statistical example of a situation in which the
problem occurs. It gives two days followed by unique visitornumbers
(the number given to a single visitor) that visited the website that
day.

1 June 2003: 1 3 5 9
2 June 2003: 4 10 1 3

The total number of unique visitors should be: 6

When executing the MDX-query, OLAP returns a number of 8 unique
visitors, because the distinct-operation seems to be used per day and
not over the complete period.

Our question: how can we make sure OLAP returns a distinct count that
has been calculated over the complete period?

Thanks in advance!

Kind regards,

Peter Swart & Jeroen Oosterlaar

Sounds like your problem is similar to this recent thread -
if so, you will be able to do a distinct visitor count using this
technique IF you have a [Visitor] dimension whose leaf level is down to
the individual visitor. In that case, you can use NonEmptyCrossJoin to
find out how many non-empty visitor instances exist over the 3 months.
But the performance may not be great till you tune the query.

From: Sean Boon [MS]
Date Posted: 7/15/2003 11:36:00 AM

Audrey,

Distinct count cannot be rolled up on the fly using AGGREGATE() or SUM()
functions. What you can do is employ the method described in a
whitepaper
on MSDN.

http://msdn.microsoft.com/library/de.../en-us/dnolap/
html/distinct2.asp


Sean


--
Sean Boon
SQL Server BI Product Unit

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

"Audrey Ng" <odd26uk (AT) yahoo (DOT) co.uk> wrote

Audrey,

I think that you can avoid invoking NECJ twice by using:

[Cases].[Individual].Members instead of: [Cases].Members

This assumes that [Cases].[Individual] is your leaf level.
You could then simplify by avoiding the iif condition:

{[Locations].CurrentMember},
{[MR Measures].CurrentMember},
{[Programs].CurrentMember},
{[Staging].CurrentMember},
{[Time Calendar].CurrentMember},
LastPeriods(12,[Time Fiscal].CurrentMember),1).Count
See if this improves your response time significantly. If you can make
the intermediate level of [Cases] visible, then there is a possibility
to try another MDX approach.
- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!