Partitioning Strategery

Hi all,

I've got several large fact tables that I want to partition by time. What's the best way to strategy to manage this? I have a C# app that uses AMO to manage the re-processing currently, but right now I'm fairly naive and just re-process every measure group.

What I'd like is a way to automatically partition by month / day [depending on measure group], and then enhance my re-process app to only process the latest partition.

My current idea is to establish a naming structure for the existing MGs/partitions, [i.e. "Foo 12-2006"], parse the date, adjust the named query, create a new partition if needed, and re-process if appropriate. Though it's quite doable, it seems a bit complex for something that should be a more of a mainstream scenario.

How have you guys implemented this functionality? I saw something about specifying the "slice" dimension on partitions, but I'm not sure if that can be used to help this.

Cheers,

Arjun

The follwoing is my experience for different cases when refreshing data in a partitioned cube.

C1. New data for a new time period

Incrementally processing the time dimension, creating the new partition for the new time period and fully process it.

C2. New data for existing time period

Create meta data in RDBMS such as batch etc, merge the new data into existing partition

C3. Updated or deleted data for existing period

Fully process the partition

In SSAS2005, slice is automatically set for msolap partition, and it is only used for querying, not for processing, so you maybe need not to consider it at all.

|||

CJ - thanks for your insight.

Typically my cases fall under (C1) - new data for a new time period. What's the best way to manage new partition creation? Does SSAS provide any functionality to make this easier, or do I need to build my own infrastructure?

|||

If a partition strategery works for you, it is a good one.

I always build the partitions logic by myself.

Partitioning Questions

I'm looking at using table partitioning in SQL 2005 and have a few
questions.
a.. Is partitioning really only available in Enterprise Edition? I have
Std Edition and when I tried to create a partitioning function, it told me
"Partition function can only be created in Enterprise edition of SQL Server.
Only Enterprise edition of SQL Server supports partitioning." Yet I don't
see this same statement anywhere in the documentation.
b.. All of the articles I'm reading on partitioning talk about how to
create a new table and use partitioning. How do I partition an existing
table?
c.. Does partitioning work equally well with datetime and int columns?
d.. Will partitioning give me better performance than say moving my
indexes to another filegroup?
Thanks, AndréHi André
Take a look in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm
"Features supported by the Editions of SQL Server 2005
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23be3$PZhGHA.3496@.TK2MSFTNGP04.phx.gbl...
> I'm looking at using table partitioning in SQL 2005 and have a few
> questions.
> a.. Is partitioning really only available in Enterprise Edition? I have
> Std Edition and when I tried to create a partitioning function, it told me
> "Partition function can only be created in Enterprise edition of SQL
> Server. Only Enterprise edition of SQL Server supports partitioning." Yet
> I don't see this same statement anywhere in the documentation.
> b.. All of the articles I'm reading on partitioning talk about how to
> create a new table and use partitioning. How do I partition an existing
> table?
> c.. Does partitioning work equally well with datetime and int columns?
> d.. Will partitioning give me better performance than say moving my
> indexes to another filegroup?
>
> Thanks, André
>
>|||Please check the topic "Modifying Partitioned Tables and Indexes"
in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1b85f670-74aa-4a4b-9a88-3352f1d274d1.htm
It gives details for each of the following:
You can modify partitioned tables and indexes in the following ways:
a.. Modify a partition function to re-partition any tables or indexes that
participate in it.
b.. Modify a partition scheme to designate a filegroup to hold a
newly-added partition.
c.. Convert a nonpartitioned table to a partitioned table.
<===============
d.. Convert a partitioned table to a nonpartitioned table.
e.. Transfer data by adding, moving, or removing partitions
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:etHWW3ahGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Kalen. Do you by chance have any good links that talk about how to
> partition an existing table?
> Andre
>|||Thanks Kalen. Do you by chance have any good links that talk about how to
partition an existing table?
Andre|||Thanks Kalen - I'll check this out.
Andre

Partitioning Questions

I'm looking at using table partitioning in SQL 2005 and have a few
questions.
a.. Is partitioning really only available in Enterprise Edition? I have
Std Edition and when I tried to create a partitioning function, it told me
"Partition function can only be created in Enterprise edition of SQL Server.
Only Enterprise edition of SQL Server supports partitioning." Yet I don't
see this same statement anywhere in the documentation.
b.. All of the articles I'm reading on partitioning talk about how to
create a new table and use partitioning. How do I partition an existing
table?
c.. Does partitioning work equally well with datetime and int columns?
d.. Will partitioning give me better performance than say moving my
indexes to another filegroup?
Thanks, AndrHi Andr
Take a look in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-
0a5fea89f355.htm
"Features supported by the Editions of SQL Server 2005
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:%23be3$PZhGHA.3496@.TK2MSFTNGP04.phx.gbl...
> I'm looking at using table partitioning in SQL 2005 and have a few
> questions.
> a.. Is partitioning really only available in Enterprise Edition? I have
> Std Edition and when I tried to create a partitioning function, it told me
> "Partition function can only be created in Enterprise edition of SQL
> Server. Only Enterprise edition of SQL Server supports partitioning." Yet
> I don't see this same statement anywhere in the documentation.
> b.. All of the articles I'm reading on partitioning talk about how to
> create a new table and use partitioning. How do I partition an existing
> table?
> c.. Does partitioning work equally well with datetime and int columns?
> d.. Will partitioning give me better performance than say moving my
> indexes to another filegroup?
>
> Thanks, Andr
>
>|||Thanks Kalen. Do you by chance have any good links that talk about how to
partition an existing table?
Andre|||Please check the topic "Modifying Partitioned Tables and Indexes"
in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1b85f670-74aa-4a4b-9a88-3352
f1d274d1.htm
It gives details for each of the following:
You can modify partitioned tables and indexes in the following ways:
a.. Modify a partition function to re-partition any tables or indexes that
participate in it.
b.. Modify a partition scheme to designate a filegroup to hold a
newly-added partition.
c.. Convert a nonpartitioned table to a partitioned table.
<===============
d.. Convert a partitioned table to a nonpartitioned table.
e.. Transfer data by adding, moving, or removing partitions
--
HTH
Kalen Delaney, SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:etHWW3ahGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Kalen. Do you by chance have any good links that talk about how to
> partition an existing table?
> Andre
>|||Thanks Kalen - I'll check this out.
Andre

Partitioning Question

Hi All,

When we are partitioning a table in SQL Server 2005 , we get the following error.

Any help appreciated

Partition columns for a unique index must be a subset of the index key.

Thanks

Manish

Got the answer. We have to have the partitioning key also as a part of the primary key for the table, if it is not already the primary key

Partitioning Question

I'm faced with a project that requires the caching of vacations.
Each vacation has a departure date & a price.
The amount of different vacations that will need to be cached is probably near 1 million per day.

I will then need to select the price(s) of vacations for either a single day or a date range (based on the vacation criteria).

I was considering creating a new partition (table with a date on it) every day.
This would allow me to jump into the needed table(s) based on the vacation search criteria. This would also allow me to drop tables with past dates.

I was considering running this all on 1 sql server. I was hoping I could create multiple threads for a datespan search and hit all the tables in the daterange at the same time.

Can you guys enlighten the noob on where I really need some help on this?There's quite a lot of info on this in "Books online" (i.e. the Help that comes with SQL Server). If you follow the set of rules, having a table for each day with a 'check constraint' (e.g. on the date) and a top level view doing a UNION of all the tables it should work quite nicely.

Partitioning performance on one disk?

Hi,

I know partitioning improves the performance on large tables.

We have a table of app. 100.000.000 records.
We want to partition the table, but we were wondering if there is still an increase in performance if all the partitions are on the same disk. We want to partition the data per month, but we don't have any disks left were we can spread the partitions.

So my main question is:
Is there still a performance increase when you use partitioning on one disk in stead of multiple disks.

Thx!

Regards Tony

sure. though the gain is maximized when you partition across multiple disks and the system is running on multi cpu.

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:

>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is no
t
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:
>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?
Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>
|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

Partitioning in SQL 2000

Is partitioning fact tables in SQL 2000 really inadequate compared to other
RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
of datawarehouse have partitioning of data and I dont believe the
partitioned views are robust enough. Is that a true statement ?Robust enough for what? Your requirements are not necessarily the same as
others. In 2000 your only choice really is a Partitioned View which does
have limitations just like any other feature has limitations. Only you can
decide if it meets your needs or not. Yes 2005 has new partitioning
features and will fill in the voids for instances that the Partitioned Views
don't fit. But that does not mean that feature will be right for everyone
either. It all boils down to what you need to do and how you do it.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>|||partitionning works fine in SQL 2000.
you have to use the right partition and create good queries to insure that
you'll ask for a limited number of partition only.
if your partition is by year, but you never filter on the year, then the
advantage of the partition is not used.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QS$1jrfFHA.2152@.TK2MSFTNGP14.phx.gbl...
> Is partitioning fact tables in SQL 2000 really inadequate compared to
> other
> RDBMS out there ? I heard SQL 2005 has some cool stuff but in 2000, a lot
> of datawarehouse have partitioning of data and I dont believe the
> partitioned views are robust enough. Is that a true statement ?
>

partitioning fact table

We have a very big fact table, almost 20million rows for each year, If I partition with each year in the warehouse how can I access all the fact partions as a single fact table when designing it in the data source view in BIDS?

Although I can partition the cube in analysis services by each year, this doesn't seem to solve the problem because the table is enormous and I need to partition it in the warehouse level. Can anyone shed some light?

I will greatly appreciate your feedbacks.

Rok

In SQL 2005 you can create partitioned tables and Indexes and the Query Engine is smart enough to direct requests to a specific partition. You can also store different partitions in different file groups if you want. You should be able to find plenty of documentation in BOL.

But you don't need to have all your data in one table for it to work with BIDS, You can pick one table to work with in the cube designer and then link other tables to specific partitions.

|||

This paper covers both relational and OLAP partitioning for Project REAL:

Project REAL: Data Lifecycle Partitioning

Overview

This paper provides a detailed discussion on how partitioning was implemented, both on the relational data warehouse and in the Analysis Services cubes. In addition to providing the general "how we did it" overview, we include specific code segments and lessons learned in the hopes that the reader will benefit from both our successes and failures. It is our hope that anyone planning or implementing a BI system based on SQL Server 2005 will find the reference implementation useful.

...

Partitioning error

Hi,

I am trying to implement partitioning on a table
depending upon the fiscal_month value...

The current values are from 1-6...

Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)

-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])

CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
...

[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)

truncate table Load_Data_old -- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old

-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)

Alter partition scheme LoadDataPartitionScheme next used [primary]

-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)

Select * from sys.partition_range_values

function_id boundary_id parameter_id value
---- ---- ---- --
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7

Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6

ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.

Values in Load_Data_new for fiscal_month is 7

But when i try

Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7

it works fine...

reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htmI got the answer..

Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)

even though the Load_Data_new table has only month = 7 data...
a constraint is mandatory....

PARTITIONING DISKS

Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
Fabio
First off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com

PARTITIONING DISKS

Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com

PARTITIONING DISKS

Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
--
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com

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 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 droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
Hi 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 droptable.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 droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
>