Partitioning dimensions?

I have several large dimensions (one with 1 million members, another with 70+ million members).

The first one is growing rather quickly - and instead of fully re-processing a dimension every time I refresh the cube - is there a good way to partition the dimension so I only incrementally add to it?

The other one is fairly static (updates every few weeks) -> but I'm still a bit afraid of tackling a 70+ million dimension and integrating it into our cube. I've avoided the problem by aggregating one level above (which has only several thousand members). Any tips on this?

Thanks!

Arjun

Hello Arjun. You cannot partition dimensions only measure groups.

You can only do an incremental update if you add new members to the dimensions. I you remove members or change the structure you will have to do all full process on all partions.

Chris Webb have some information of how you could handle large dimensions:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!777.entry

HTH

Thomas Ivarsson

|||I've done some performance tests showing the performance of ProcessAdd on large dimensions. You might take a look:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

Partitioning dimensions?

I have several large dimensions (one with 1 million members, another with 70+ million members).

The first one is growing rather quickly - and instead of fully re-processing a dimension every time I refresh the cube - is there a good way to partition the dimension so I only incrementally add to it?

The other one is fairly static (updates every few weeks) -> but I'm still a bit afraid of tackling a 70+ million dimension and integrating it into our cube. I've avoided the problem by aggregating one level above (which has only several thousand members). Any tips on this?

Thanks!

Arjun

Hello Arjun. You cannot partition dimensions only measure groups.

You can only do an incremental update if you add new members to the dimensions. I you remove members or change the structure you will have to do all full process on all partions.

Chris Webb have some information of how you could handle large dimensions:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!777.entry

HTH

Thomas Ivarsson

|||I've done some performance tests showing the performance of ProcessAdd on large dimensions. You might take a look:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/3/Default.aspx

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1
>

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!
Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en...es_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!
|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/e...des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!

Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
--
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!