Creating Unique Indexes on Partition table..

In partition tables, SQL Server forces to include the Partition column as part of unique index/unique constraint or primary key. Is there a way to over-ride this option.

We have a table, which is partitioned on a Calculated Column (generated using custom logic). Also we need to enforce uniqueness based on few other columns. But SQL Server does not allow indexes/contraints on these columns without including the partition column.

Thanks for your help.

Well, you can create a non-aligned unique index on a partitioned table, but doing that will in essence not allow meta-data-only partition switching. Depending on your needs, that may be ok or not. You could also use a DML trigger to enforce uniqueness instead of a unique index. Or, if your environment allows for the use of them, you could always create an indexed view on the table and create a unique index on that view that will in essence enforce the uniqueness on the corresponding table as well.

For more information, see the following topic in SQL 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5af648b1-8454-4c17-a47e-f9656572440b.htm

HTH,

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Horizontal Partition

From a previous post I got an example of creating a Horizontal Partition. I have altered the code in an attempt to understand why the partition I want fails on insert. The altered script is:

create table Table_A_Jan (Col1 int not null, Col2 char(2) not null CHECK (col2 = 'RL')--CHECK (Col2 >= cast('20060101' as datetime) and Col2 < cast('20060201' as datetime))--,--Col3 char(5) not null, CONSTRAINT [PKJan] PRIMARY KEY CLUSTERED ([Col1], [Col2]/*, [Col3] */) WITH FILLFACTOR = 95 ON [PRIMARY] )

create table Table_A_Feb (Col1 int not null,Col2 datetime not null CHECK (col2 = 'RP') --(Col2 >= cast('20060201' as datetime) and Col2 < cast('20060301' as datetime))--,--Col3 char(5) not null, CONSTRAINT [PKFeb] PRIMARY KEY CLUSTERED ([Col1], [Col2]/*, [Col3]*/) WITH FILLFACTOR = 95 ON [PRIMARY] )

CREATE VIEW A AS SELECT * FROM Table_A_Jan

UNION ALL

SELECT * FROM Table_A_Feb

Then the insert

INSERT INTO A (Col1, Col2/*, Col3*/) VALUES (45, 'RL'--'2006-01-21 23:55:34.000'--, '00073' )

And the resulting error message

because the primary key of table '[AAAA].[dbo].[Table_A_Jan]' is not included in the union result.

As long as I keep Col2 as date time everything works. Why does it fail when I want the CHECK contraint to be equal to a particular string?

(Sorry about the font issue don't get what is going on here)

Andrew.

This forum where you will seek help for Analysis Services related questions. I know your quest started from exploring solutions for Analysis Services application, but looks like you missing some basic knowlege on relational databases (SQL Server and likes). I suggest you get yourself a good book or tutorial on how to work with SQL.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.