Distributed Partitions (Across multiple servers)

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.

I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.

Any help would be great.

Thanks!

Eric Elliston

SWFLParent.com

http://www.swflparent.com

Hi Eric,

I think you're thinking of distributed partitioned views. Have a look at http://msdn2.microsoft.com/en-us/library/aa479364.aspx which actually presents a few options available to you.

Cheers,

Rob

distinct count with multiple partitions?

We analyze much of our data using distinct measures. Some of the underlying sources - i.e. daily page views are heavy and have millions of rows a day. Because of this I've created daily partitions to only process the incremental data that's arrived. However, I'm curious - how does distinct count perform when it needs to rollup over multiple partitions?

IE Say it's Dec 25th, and I have 25 unique partitions for each day of December thus far. Internally, how do the distinct measures correctly accumulate the unique instances of my measure? Are there any significant performance concerns to be aware of?Distinct Count measure will work fine with multiple partitions. AS keeps the distinct count values inside the partitions, and therefore it can correctly aggregate across them. Partition per day should be OK too.|||Arjun: Make sure you are on at least SQL 2005 SP1 QFE rollup, preferably SP2 CTP2. There were a couple of bugs fixed for distinct count measures over multiple partitions.|||Good to know, thanks! Jeff, do you have any details on the nature of the issues with distinct counts over multiple partitions?

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