Hi All ,
I am reviewing a cube which has thousands of partitions (at least
trying to) as opening in analysis manager is like watching paint dry ..
in the end we wrote a program to dump the structure to an XML file as
watching paint dry is no fun.

Many of these partitions are very small and I can't believe that AS is
really benefitting from this strategy..

Fact Tables and partitions are created programmatically based on
various data values in the source data systems by a "clever" program
but it all feels like it is ending up with way too many files. Build
times are pretty poor as dimensions change regularly requiring cube
rebuilds. Data size of largest composite cube is only a few GB so I
just don't see the point of them all and I'm a little concered that as
the number of partitions get bigger and bigger that AS may choke at
some point ..

I guess the question is .. am I wrong and this strategy really is not a
problem for AS just a problem for the poor support guys who have to
manage 2000+ fact tables and associated stored procs.

I'm a great believer in keeping things simple and this doesn't feel
simple but I'd welcome any other opinions.

Thanks

ps Its AS2000

Did you mean to say 2000+ partitions instead of 2000+ facts?

I think (if I remember correctly) AS2000 had some pretty good
recommendations around the maximum number of rows that should be in a
partition. Sounds like your automated program is over-partitioning the cube
unless you have that many rows. I want to say I read 500,000 rows per
partition...

How many rows in your underlying fact table?

<mpsatwork (AT) gmail (DOT) com> wrote

The normal pattern is approximately 10 million rows per partition. Less than
1 million rows is more harm for manageability than gain for performance. Of
course, this varies depending a lot on the usage pattern (if each query
always filtered on the partition slice then the improvement in query
performance may be worth the management cost for some customers).

There are definitely customers who have lots and lots of data and they do
create lots of partitions (in the 100s to 1000s) -- but they tend to use the
partition utility to manage partitions rather than Analysis Manager

Some resources:

http://www.microsoft.com/technet/pro.../ansvcspg.mspx

http://msdn.microsoft.com/library/de...itionsindw.asp

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Tim Dot NoSpam" <Tim.NoSpam (AT) hughes (DOT) net> wrote