Distinct Count Measures Spanning Partitions

We have a large cube partitioned now by week, whereas before it was one big
dumb partition. The problem is I don't know how to get DISTINCT COUNT
measures to behave properly. If I create a named set and do a SUM or an
AGGREGATE function of all the partitions in that set, it literally adds the
distinct counts from the partitions instead of doing a distinct count for al
l
the partitions in the set.
Is this even possible?Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
measures, whether the cube is partitioned or not. So it's not clear why
partitioning the cube would worsen problems with distinct count measures
- can you give a specific example of what happened?
http://groups-beta.google.com/group...erver.olap/msg/
5b7f7bedfe8d147e[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "Sean Boon [MS]"
Date: Thu, 9 Oct 2003 13:02:57 -0700
Subject: Re: Distinct Count
Hi Chet,
Distinct count will work with MDX's WHERE clause. What doesn't work is
attempting to aggregate across sets that consist of multiple members.
In
other words you can't use the AGGREGATE() function or a calculated
member
based on the AGGREGATE() function in the where clause. It's a
limitation of
the distinct count aggregate type. There is a way around this though and
it's outlined in the following whitepaper.
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnol.
.
Sean
Sean Boon
SQL Server BI Product Unit[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Sorry for not being more specific.
Calculated members based upon named sets that span what the cube is
partitioned by is what causes the problems.
Let's say there's a cube that is partitioned by month with this sample Fact
Data:
CustID TimeID
1 1
1 32
1 33
1 61
1 64
2 1
3 1
4 1
5 32
6 32
7 62
8 62
The TimeID's from 1-31 are Jan, 32-59 Feb, and 60+ are March, all of 1998.
I'm looking to get Distinct Count of Customers (CustID).
When querying the cube at the natural Year, Quarter, and Month levels, the
correct results are returned:
Year Distinct Count = 8
Quarter 1 Distinct Count = 8
Jan Distinct Count = 4
Feb Distinct Count = 3
Mar Distinct Count = 3
As you can see, drilling up and down with native members of dims in the cube
is just fine, regardless of partitioning.
However, we do a lot of dynamic rolling analysis. Various front ends will
generate a named set or calculated member, or both, for "the last N months."
Let's say I created a simple calculated member called "LastTwoMonths":
[Time].[1998 01] + [Time].[1998 02]
This returns 7, which is incorrect. It does the distinct count of each
partition and adds them together, which is not "correct" in the sense of tru
e
distinct counting. With the above data, the distinct count for those two
months should be 6.
I can't figure out how to get the distinct count to work properly when the
cube is partitioned by the same dimension that many calc members are based
upon.
Thanks for giving this matter the attention. I really appreciate it!
"Deepak Puri" wrote:

> Pl. note that, in AS 2000, Aggregate() will not work on Distinct Count
> measures, whether the cube is partitioned or not. So it's not clear why
> partitioning the cube would worsen problems with distinct count measures
> - can you give a specific example of what happened?
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b7f7bedfe8d147e
> Newsgroups: microsoft.public.sqlserver.olap
> From: "Sean Boon [MS]"
> Date: Thu, 9 Oct 2003 13:02:57 -0700
> Subject: Re: Distinct Count
> Hi Chet,
> Distinct count will work with MDX's WHERE clause. What doesn't work is
> attempting to aggregate across sets that consist of multiple members.
> In
> other words you can't use the AGGREGATE() function or a calculated
> member
> based on the AGGREGATE() function in the where clause. It's a
> limitation of
> the distinct count aggregate type. There is a way around this though and
> it's outlined in the following whitepaper.
> http://msdn.microsoft.com/libr-ary/...ry/e-n-us/dnol.
> ..
> Sean
> --
> Sean Boon
> SQL Server BI Product Unit
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Based on your detailed description, I would re-iterate that, with AS
2000, you won't be able to roll up Distinct Count measures dynamically
in a calculated member. However, this should work in AS 2005 (Yukon), if
that's an option.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql