can I combine several partitions into one in a cube?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Word 2003 memory Leakage - 17 Oct 2005 19:53 GMT

Hi,

I got 4 partitions corresponding to four fact tables.

Does it affect cube query performance? most of time, i need to access all
partitions.

If so, how can I combine them into one partition?

Thanks,

Guangming

Reply to this Message

Dave Wickert [MSFT] - 18 Oct 2005 03:13 GMT

If you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.

Signature

Dave Wickert [MSFT]
dwickert@online.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.

> Hi,
>
[quoted text clipped - 8 lines]
>
> Guangming

Reply to this Message

Word 2003 memory Leakage - 20 Oct 2005 14:03 GMT

In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.

it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.

Most time, merging is not necessary, I guess.

Guangming

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
[quoted text clipped - 15 lines]
> >
> > Guangming

Reply to this Message

Darren Gosbell - 21 Oct 2005 04:46 GMT

> In management studio, go to cube - partition, right click you'll get 'merge
> partition ...'.
>
> it seems that if there are multiple partitions in one measure group, you can
> merge them. If partitions are for different measure groups, you can not.

Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them

If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.

In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.

Signature

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

Reply to this Message