Please guide - especially about Time Dimension and approach in general

Hi,

I have a table which contains all the transaction details for which I am trying to create a CUBE... The explanation below in brackets is only for clarity about each field. Kindly note that I am using the following table as my fact table. Let's call it tblFact

This table contains fields like transaction date, Product (which is sold), Product Family (to which the Product Belongs), Brand (of the product), Qty sold, Unit Price (at which each unit was sold), and some other fields.

I have created a Product dimension based on tblFact. I don't know if this is a good idea or not :confused: Is it okay and am I on the right track or should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide.

Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I realize that this is a lenghty post but reply and more importantly guidance from the experienced users will be greatly appreciated becuase I have recently started learning/playing around on the OLAP side of things and I know that this is the time that I get my foundations correct otherwise I'll end up getting used to some bad practice and will find it difficult to change my approach to cube designing later down the road.

So many thanks in advance and I eagerly look forward to reply from someone.No worries mate,

This is what the forum is for...

Ok - Down to what you need to do

When doing the design for a cube I always do a bit of anyalsis first. Looks like you have crack this bit. You know what your dimensions are - Time , product , brand etc This is what you should group on to build your fact table.

Your facts are going to be Qty Sold , Price. This is what you will be suming on with the SQL to build your fact table.

You said "should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide."

This is exactly what a good cube design is based on mate.

I presume the basis of your fact table is a transactions type table.
First thing you need to do is build all your dimension tables.

A table for product dimension table should look something like :

create table tblProduct
(prod_id tinyint ,
prod_txt varchar (255)
)

Don't forget to put in a id for unknown product - just in case you get these in your base transaction table

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.

Now once you have this you want to build your fact table.

What you do is take your fact table and join to each of you dimension tables (should be a left outer join) and sum on the qty and unit price and group up accross all your dimensions.

This now should be you fact table that you can reference in Anyalsis Manager. You will have to define all this with in here as well.

Whoo, that was an effort.

Any problems , questions give me a shout

Cheers|||Hi aldo_2003,

Many thanks for the reply. It answers a good number of my questions. Can you kindly advise regarding the remaining question i.e. the quoted portions below:

Build the rest of your dimension tables like this and assign a tinyint composite key to each dimension. What you what is your fact table to be as small as possible in terms of datatypes.
Question: By composite key do you mean define a Primary key in each table? I will do so but was planning to define the data type for my ProductID field (for example) in my tblProducts as int. However I'll follow your advise and instead use the datatype tinyint. Thanks for the tip :)

And my last question hopefully:
Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I think Part 2 (above) is easy and all I have to do is make a copy of tblFact but this copy (say tblTime) will only contain the transaction date column (from tblFact). Kindly confirm my understanding.

However it's the answer to part 1 (above) and especially the part 3 above that is requested.

Looking forward to your reply.|||Your welcome,

forgot about the time question

what you want to do is create a tblTime dimension table

should basically have the grandularity that you want to use

you can find scripts on the net that will help you create and manage a time dimension table.

table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

populate this table with all the dates in your date range i.e

Jan 1999 12:00am to Jan 2009 12:00am

This is now your time dimension table.
Using anyalsis manager join back on to the fact table.
Anyalsis manager should guide you through the process of creating a time dimension.

Hope this helps

p.s the only reason I used a tinyint is that I assumed you would have no more than 255 products, if you have more then up the datatype to what you need

Cheers|||Hi again,

Don't mean to "over-flatter" but your replies REALLY have been of great help... Here I was trying to build everything (the fact as well as the dimensions) using only a single table and now I am quite clear regarding what's the right approach :)

3 last questions please :rolleyes:

you can find scripts on the net that will help you create and manage a time dimension table.
table should look like

create table tblTime
(date_time smalldatetime,
quarter tinyint,
month tinyint,
week tinyint ,
day int
)

That's another new tip :) Can u kindly guide where I can get these scripts from? I am assuming that these scripts will not only create the table (in a similar structure as you have suggested above) but will also populate the table with the all the desired date ranges e.g. Jan 1999 12:00am to Jan 2009 12:00am.

2nd last question: So my approach which I was assuming for creating tblTime (for the Time dimension was incorrect) i.e. I thought that this will simply contain the ALL the transaction dates from the transaction table (as I described in my previous post). But from your reply my understanding is that this approach is wrong.

and the last question: So the tblTime does not have to store the ProductID?

Sorry for all the botheration.|||No worries buddy

Glad to be of help

The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers|||http://www.databasejournal.com/features/mssql/article.php/10894_1466091_6

http://www.winnetmag.com/Article/ArticleID/23142/Windows_23142.html|||Originally posted by aldo_2003
The time dimension table is stand alone and does not have to contain any other info other than time info.

I'll see if my collegue know and get back to you

Cheers

Thanks aldo : for the reply, the link to articles, and also for the clarification about the time dimension's underlying table containing only time related information.

Kindly do let me know if you get a script which not only creates the table for time but also populates it ...

Bless you!|||Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.|||Originally posted by Satya
Check this link http://www.winnetmag.com/Articles/ArticleID/41531/pg/4/4.html for any help.

Hi Satya,

Many sincere thanks for the article. I went through it...

However at this stage I am learning the basics (as evident from this post and my questions to aldo) therefore I found he article to be real "HEVY STUFF" and very difficult to digest at the moment. I talks about the MDX world and that's something that I have yet to explore... I know I will have to get into MDX soon but have enough of the basic to get right first :)

I however would like to request if you can help me with another post from me in this forum (Subject" Need help to create my Time Dimension")... it's somewhat related to portion of the discussion in this post but I did not want to unnecessarily prolong this particular thread/post...

Looking forward to your help in my other post.

Thanks again and regards.|||Joozh
Since this has been answered so well i just wanted to chime in and suggest some reading for you

The Data Warehouse Toolkit by Ralph Kimball (http://www.bestwebbuys.com/books/compare/isbn/0471200247/isrc/b-home-search)

this is the second edition of this book revised in 2002 and it is a must have for every OLAP develper.sql

Planning for SQL Server 2005: 64-bit, OS, processor, memory

I was browsing Microsoft's SQL Server site, looking for
some details about SQL Server 2005. Didn't find what
I was looking for...

I'm thinking about moving an existing SQL Server 2000
workload to a new box, using SQL Server 2005, and
maybe the 64-bit version.

My questions are:

1. What is the current target date for release of SQL Server 2005?
Will 64-bit ship when 32-bit ships?

2. Will 64-bit SQL Server 2005 require a special version
of Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?
Will it work with both Intel and AMD processors?

3. How many CPUs, and how much memory, will be supported by
SQL Server 2005, 32-bit and 64-bit, on each OS that can run
SQL Server 2005.

I'm looking for a chart here, something like the chart on
page 117 of Kalen Delaney's "Inside SQL Server 2000" book.

SQL Server 2005 SQL Server 2005
Feature Enterprise 32-bit Enterprise 64-bit
------ ------ ------
CPUs supported
Win Srvr 2003:
Win Srvr 2003 Adv:
Win Srvr 2003 Ent x64:

Physical memory
supported
Win Srvr 2003:
Win Srvr 2003 Adv:
Win Srvr 2003 Ent x64:

Has Microsoft published this info, and I just can find it?Larry Bertolini (bertolini.1@.osu.edu) writes:
> I was browsing Microsoft's SQL Server site, looking for
> some details about SQL Server 2005. Didn't find what
> I was looking for...
> I'm thinking about moving an existing SQL Server 2000
> workload to a new box, using SQL Server 2005, and
> maybe the 64-bit version.
> My questions are:
> 1. What is the current target date for release of SQL Server 2005?

The only answer I have heard is "when we're ready". Maybe someone
has said things like "second half of 2005". But I think that is about
as precise information you can get.

> Will 64-bit ship when 32-bit ships?

Since every drop I've seen of SQL2005 has included 64-bit, I see no
reason to assume that 64-bit would not ship when 32-bit ships.

> 2. Will 64-bit SQL Server 2005 require a special version
> of Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?

I don't think the whole thing about editions have been sorted out
yet.

> Will it work with both Intel and AMD processors?

Provided that Win2003 SP1 has shipped by then, I would assume so.

> 3. How many CPUs, and how much memory, will be supported by
> SQL Server 2005, 32-bit and 64-bit, on each OS that can run
> SQL Server 2005.

Again, I don't think anything that has been finalized yet. These
limits may not necessarily be hard limits, but rather what sort
of configuration that actually has been tested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

PK Index in SQL Server

I'm a little confused on the details of how a PK index is implemented in SQL
Server.
It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
_filegroup_" you are specifying the index the PK will use in its entirety.
What I'm accustomed to (under Informix) is to create the table, create the
index the constraint will use and then create the constraint, which will
detect the appropriate index and use it (Informix has much more robust
storage options, which are available in create index, but not add
constraint).
However, under SQL Server when I do this, I seem to get two unique indexes
on the same column(s). Also, SQL Server seems to limit indexes to a single
filegroup negating anything fancy in the first place (this is still true
with DPV's).
So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
index, period. Also, except for the entry in sysconstraints, a constraint
and an index are implemented completely identically in SQL Server.
Am I correct?
Thanks,
JayHi Jay,
In SQL Server you create a primary key, adding a constraint. The way SS
implement such constraint, is creating a unique index, behind the scene, that
could be clustered or nonclustered. If you do not specify what kind of index
to use, SS will use clustered by default. It will be the same with UNIQUE
constraints.
You can not disable a primary key or unique constraint using "alter table
... nocheck constraint_name", but you can disable the index associated to the
costraint. Notice that if the index is a clustered one, then disabling it
will stop you from inserting into the table.
CREATE TABLE dbo.t(
c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
)
GO
ALTER TABLE dbo.t
NOCHECK constraint pk_t
GO
ALTER TABLE dbo.t
NOCHECK constraint uq_c2
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
ALTER INDEX pk_t ON dbo.t DISABLE
go
ALTER INDEX uq_c2 ON dbo.t DISABLE
go
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
INSERT INTO dbo.t VALUES(1, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
SELECT * FROM dbo.[t]
GO
DROP TABLE dbo.[t]
GO
AMB
"Jay" wrote:
> I'm a little confused on the details of how a PK index is implemented in SQL
> Server.
> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> _filegroup_" you are specifying the index the PK will use in its entirety.
> What I'm accustomed to (under Informix) is to create the table, create the
> index the constraint will use and then create the constraint, which will
> detect the appropriate index and use it (Informix has much more robust
> storage options, which are available in create index, but not add
> constraint).
> However, under SQL Server when I do this, I seem to get two unique indexes
> on the same column(s). Also, SQL Server seems to limit indexes to a single
> filegroup negating anything fancy in the first place (this is still true
> with DPV's).
> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
> index, period. Also, except for the entry in sysconstraints, a constraint
> and an index are implemented completely identically in SQL Server.
> Am I correct?
> Thanks,
> Jay
>
>|||Thank Alejandro,
So, if I understand you correctly, with the exceptions of focusing on
clustering and disabling indexes, adding a primary key constraing is the
same as adding an index, except that you get the extra stuff that comes with
a primary key.
Thanks,
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> Hi Jay,
> In SQL Server you create a primary key, adding a constraint. The way SS
> implement such constraint, is creating a unique index, behind the scene,
> that
> could be clustered or nonclustered. If you do not specify what kind of
> index
> to use, SS will use clustered by default. It will be the same with UNIQUE
> constraints.
> You can not disable a primary key or unique constraint using "alter table
> ... nocheck constraint_name", but you can disable the index associated to
> the
> costraint. Notice that if the index is a clustered one, then disabling it
> will stop you from inserting into the table.
>
> CREATE TABLE dbo.t(
> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> )
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint pk_t
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint uq_c2
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> ALTER INDEX pk_t ON dbo.t DISABLE
> go
> ALTER INDEX uq_c2 ON dbo.t DISABLE
> go
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> INSERT INTO dbo.t VALUES(1, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> SELECT * FROM dbo.[t]
> GO
> DROP TABLE dbo.[t]
> GO
>
> AMB
>
> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in
>> SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its
>> entirety.
>> What I'm accustomed to (under Informix) is to create the table, create
>> the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique
>> indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a
>> single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
>> own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>|||> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
Yes. A PK and UQ constraint is "implemented" though a unique index, which is created for you when
you defined (add) your constraint. The index has the same name as the constraint. This is also why
the constraint definition is "littered" with physical attributes like clustered/nonclustered and
filegroup information. It is just a matter of getting used to that this is the way they've done it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:ejzlYVICIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> Hi Jay,
>> In SQL Server you create a primary key, adding a constraint. The way SS
>> implement such constraint, is creating a unique index, behind the scene, that
>> could be clustered or nonclustered. If you do not specify what kind of index
>> to use, SS will use clustered by default. It will be the same with UNIQUE
>> constraints.
>> You can not disable a primary key or unique constraint using "alter table
>> ... nocheck constraint_name", but you can disable the index associated to the
>> costraint. Notice that if the index is a clustered one, then disabling it
>> will stop you from inserting into the table.
>>
>> CREATE TABLE dbo.t(
>> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> )
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint pk_t
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint uq_c2
>> GO
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> ALTER INDEX pk_t ON dbo.t DISABLE
>> go
>> ALTER INDEX uq_c2 ON dbo.t DISABLE
>> go
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> INSERT INTO dbo.t VALUES(1, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> SELECT * FROM dbo.[t]
>> GO
>> DROP TABLE dbo.[t]
>> GO
>>
>> AMB
>>
>> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its entirety.
>> What I'm accustomed to (under Informix) is to create the table, create the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>
>|||Technically, yes.
However, the point of having constraints and indexes is because
logically, they have different purposes.
If you are adding a constraint (such as a Primary Key), you are saying
something about your data, you are determining "business rules". With a
Primary Key you are saying: this is the key that identifies the row, and
it should be unique. Then, it is up to the RDBMS how it enforces this
constraint. If you have a compound primary key, then from this point of
view, the order of the columns does not matter.
The current and all past versions of SQL Server enforce a Primary Key
and Unique Constraint with a unique index. This could be changed in the
future. Maybe one day SQL Server might enforce it using hashing or some
other technique.
If you add an index, you are optimizing your data access. When you add a
compound index, the column order matters a lot, because of the nature of
B-tree indexes. It also matters because a join between two tables with a
compound key in different order is very inefficient.
I consider it a good practice to always use constraints to determine
keys and uniqueness, and to use indexes for additional tuning. This way,
the person optimizing the data access knows which indexes can be removed
without destroying integrity, and the person (re)modelling the schema
does not have to look at indexes.
--
Gert-Jan
Jay wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>|||Jay,
You got very good answers from Tibor and Gert-Jan. It is important, at least
for me, to point what Gert-Jan already mentioned, that primary key is part of
the models earlier than the physical. We do not talk about indexes, during
logical and conceptual models, to enforce a constraint.
AMB
"Jay" wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
>
>|||Thanks Alejandro,
But I have no issues with the logical constructs as I've been working with
relational databases for just under 20 years, it was just the physical
implementation I needed to grasp.
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2700F07B-B429-4829-BD6A-83DDDB464703@.microsoft.com...
> Jay,
> You got very good answers from Tibor and Gert-Jan. It is important, at
> least
> for me, to point what Gert-Jan already mentioned, that primary key is part
> of
> the models earlier than the physical. We do not talk about indexes, during
> logical and conceptual models, to enforce a constraint.
>
> AMB
> "Jay" wrote:
>> Thank Alejandro,
>> So, if I understand you correctly, with the exceptions of focusing on
>> clustering and disabling indexes, adding a primary key constraing is the
>> same as adding an index, except that you get the extra stuff that comes
>> with
>> a primary key.
>> Thanks,
>> Jay
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> > Hi Jay,
>> >
>> > In SQL Server you create a primary key, adding a constraint. The way SS
>> > implement such constraint, is creating a unique index, behind the
>> > scene,
>> > that
>> > could be clustered or nonclustered. If you do not specify what kind of
>> > index
>> > to use, SS will use clustered by default. It will be the same with
>> > UNIQUE
>> > constraints.
>> >
>> > You can not disable a primary key or unique constraint using "alter
>> > table
>> > ... nocheck constraint_name", but you can disable the index associated
>> > to
>> > the
>> > costraint. Notice that if the index is a clustered one, then disabling
>> > it
>> > will stop you from inserting into the table.
>> >
>> >
>> > CREATE TABLE dbo.t(
>> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> > )
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint pk_t
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint uq_c2
>> > GO
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > ALTER INDEX pk_t ON dbo.t DISABLE
>> > go
>> >
>> > ALTER INDEX uq_c2 ON dbo.t DISABLE
>> > go
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(1, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > SELECT * FROM dbo.[t]
>> > GO
>> >
>> > DROP TABLE dbo.[t]
>> > GO
>> >
>> >
>> > AMB
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> I'm a little confused on the details of how a PK index is implemented
>> >> in
>> >> SQL
>> >> Server.
>> >>
>> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_)
>> >> ON
>> >> _filegroup_" you are specifying the index the PK will use in its
>> >> entirety.
>> >>
>> >> What I'm accustomed to (under Informix) is to create the table, create
>> >> the
>> >> index the constraint will use and then create the constraint, which
>> >> will
>> >> detect the appropriate index and use it (Informix has much more robust
>> >> storage options, which are available in create index, but not add
>> >> constraint).
>> >>
>> >> However, under SQL Server when I do this, I seem to get two unique
>> >> indexes
>> >> on the same column(s). Also, SQL Server seems to limit indexes to a
>> >> single
>> >> filegroup negating anything fancy in the first place (this is still
>> >> true
>> >> with DPV's).
>> >>
>> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create
>> >> its
>> >> own
>> >> index, period. Also, except for the entry in sysconstraints, a
>> >> constraint
>> >> and an index are implemented completely identically in SQL Server.
>> >>
>> >> Am I correct?
>> >>
>> >> Thanks,
>> >> Jay
>> >>
>> >>
>> >>
>>

Pivot Query Question - Can I have 2 columns in a Pivot?

I want two columns in Pivot format. Is it possible.

I have the following table:
CREATE TABLE CONDITION DETAILS
(
CONDITIONID INT,
NAME VARCHAR(100),
DESCRIPTION VARCHAR(50),
USERNAME VARCHAR(50),
NOTIFY_FREQUENCY VARCHAR(10))

INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','First Person Notified','JC','Daily')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Second Person Notified','BG','Weekly')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Third Person Notified','BG','Monthly')

Now I have the following statement
SELECT * FROM
(SELECT CONDITIONID, NAME, DESCRIPTION, USERNAME
--, NOTIFY_FREQUENCY
FROM CONDITION) SOURCEQUERY
PIVOT (MIN(USERNAME) FOR [DESCRIPTION] IN ([First Person Notified], [Second Person Notified], [Third Person Notified])
) AS PIVOTTABLE

Output:
CONDITIONID NAME First Person Notified Second Person Notified Third Person Notified
1 Receipt of statements JC BG BG

I also want the Frequency to be displayed, I want output to be like
1 Receipt of Statements Daily JC Weekly BG Monthly BG

Can anyone suggest. Thanks.

The first thing that I want to do is to give kudos for such an outstanding job of perparing your question. Having the table definition and the insert statements for the test data made this much easier to set up for testing. Nice Work!

I have to confess that my work on this might not be as good of an effort as the question. I have never done a double-pivot so I really am not sure what is the best way to go about it. This seems to work. I would really like commentary from Umachandar or someone similar who is familar with a "best approach."

SELECT conditionId,
name,
rtrim(substring([First Person Notified], 11, 50)) as [1st Person],
rtrim(left([First Person Notified], 10)) as [1st Frequency],
rtrim(substring([Second Person Notified],11, 50)) as [2nd Person],
rtrim(left([Second Person Notified],10)) as [2nd Frequency],
rtrim(substring([Third Person Notified], 11, 50)) as [3rd Frequency],
rtrim(left([Third Person Notified],10)) as [3rd Person]
FROM ( SELECT CONDITIONID,
NAME,
DESCRIPTION,
cast (isnull(NOTIFY_FREQUENCY, '') as char(10))
+ username
as pivotStuff
FROM CONDITION
) SOURCEQUERY
PIVOT ( MIN(pivotStuff) FOR [DESCRIPTION]
IN ( [First Person Notified],
[Second Person Notified],
[Third Person Notified]
) ) AS PIVOTTABLE

-- conditionId name 1st Person 1st Frequency 2nd Person 2nd Frequency 3rd Frequency 3rd Person
-- -- - -- - -- - -- -
-- 1 Receipt of statements JC Daily BG Weekly BG Monthly

|||

Thanks. This sure works. Had thought about this solution but was not sure if it was right way to do, so had posted my query on to the forums.

Please let me know if there is a better way to achive the above results.