Is there a maximum amount or set limit of partitions that a cube can handle?

I am sure there is, but I can't track down the exact figure. It is most
likely 255, but could be 65335, it just depends on whether they used 1
or 2 bytes to hold the internal identifiers.

It is my understanding that in AS'05 all internal identifiers are 4 byte
integers, meaning that you can have a bit over 2 billion of any object
type.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

I worked with a cube based upon more than 2.5 billion fact rows, and it had
about 1800 partitions the way we eventually sliced it. We experimented with
slicing by yet another dimension, which pushed it past 6000 partitions, but
abandonned it when it didn't provide any querying or processing benefit. The
problem with this, is that changes to objects like named sets, calc members,
etc...anything that require cube saves, becomes very slow, if not impossible.
We made changes to this cube only when necessary.

This is obviously an extreme case. I don't believe there's a documented
maximum number of partitions, but chances are, if you're thinking of
approaching a huge number, you might be "over-partitioning" and are going to
be up against some administrative nightmares.

- Phil

"Darren Gosbell" wrote:

I've got a cube with over 8 billion rows sliced by just time (daily
partitions). Each partition is about 50M rows. Needless to say it
has a lot of paritions. The bottom line is that it works. Devling
into partition in DSO or AM can be painful because it takes so long to
load the partitions collection so I have SQL tables witha all of the
cube metadata including partition names, etc. So all my DSO scripts
access the partition by name directly. Still pretty slow though.

The real downside to over-partitioning is the additional threads needed for
querying if you are issuing queries which don't match all of the criterion.
For example, in Project REAL, we have partitioned in the Store Inventory
data by week (52 weeks) and by subject (25 subject "groups") -- thus we have
1300 partitions. So long as the queries slice by both week and subject we
are OK, i.e. only one partition is hit. But if some queries slice by just
week, it means that for each week's data, we need to spin up 25
per-partition queries and merge the results. Thus it was important that we
bump up the various thread count pools.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"SQL McOLAP" <SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote

We have 7 cubes, tied together via a virtual cube. Each of the 7 cubes
contains 2 partitions per customer. We have hundreds of customers, so
thousands of partitions. It works, but we have some pretty fancy DSO
code managing it. We are constantly processing different partitions
all day long. We had to do this for query & processing performance.

"Dave Wickert [MSFT]" wrote:

Can you share thread count pool settings? I have some cubes with over 100
partitions. Unfortunately, some queries hit all the partitions.
InitWorkerThreads = ?
PoolProcessThreads = ?
PoolWorkerThreads = ?
WorkerThreads = ?
ProcessThreads = ?