Disadvantages in using cube partitions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anonymous red - 22 Oct 2004 14:13 GMT

Hello,

Could anyone tell me if there are any disadvantages in using partitions
in cubes ?

Thanks

Reply to this Message

Dave Wickert [MSFT] - 23 Oct 2004 07:48 GMT

1) they are an Enterprise Edition feature, thus they involve additional
upfront costs.
2) they are more difficult to maintain -- see the SSAS Operations Guide at:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
You will see that there are lots of interesting "gotchas" if you don't
maintain the data slice, etc. So they involve more administrative overhead.

However, personally I feel that their benefits more than out-weigh the
costs. See the SSAS Performance Guide for additional information on the
benefits:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx
There is also an excellent chapter on the importance of partitioning in the
SQL Server 2000 Resource Kit.

Signature

Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

> Hello,
>
> Could anyone tell me if there are any disadvantages in using partitions
> in cubes ?
>
> Thanks

Reply to this Message

anonymous red - 25 Oct 2004 14:31 GMT

In fact I have a cube of size 100 MB with about 8 dimensions that
contains data for 24 months. There are only isolated reports that do not
use the whole 24 months data altogether. In fact, the time taken for the
processing of the cube or the complexity of development does not matter
much for my case: the main focus is access time on reports. What I would
like to know is that how I can have an idea of how much the majority of
my reports (which are based on the 24 months altogether) will suffer if
I start managing partitions in my cube based on the time dimension.
Basically, how significant is the overhead for a query consuming data
across all the members of a dimension when the cube bears N partitions
based on that dimension itself as compared to a single partition or to
how many number of partitions per MB of a cube does this overhead become
an issue.

I would be most grateful if you could provide me with some information
pertaining to the above issues.

Reply to this Message

Dave Wickert [MSFT] - 25 Oct 2004 19:11 GMT

100MB is so small that I doubt if you will see any real advantage or
disadvantage of partitioning. If it was 100GB then we'd have a worthwhile
discussion, but 100MB is just too small to see anything.

Signature

Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

> In fact I have a cube of size 100 MB with about 8 dimensions that
> contains data for 24 months. There are only isolated reports that do not
[quoted text clipped - 12 lines]
> I would be most grateful if you could provide me with some information
> pertaining to the above issues.

Reply to this Message