A question about PK&FK with a fact table?

[bluegod]

Hi all,
I'm confused in following writing.(under the underline)
It means The fact table doesn't need PK at all?
what situation Should I need PK to control integrity check?
or I just use the primary index to do that?
thank very much.

---------------
Actually, the fact table doesn't need a primary
key. The collection of foreign keys on a fact
table can be thought of as the primary key, if
you really need to say you have on, but it isn't
necessary.

The only reason for a primary key in a
dimensional warehouse is if you have something
else with a foreign key pointing to it. This is
not the case with a fact table.

If you are trying to use the key to associate
rows between fact tables, you are going to get
yourself into trouble. Fact tables should be
associated across common conforming dimensions
otherwise, not only do you no longer have a
dimensional model, but you would be required to
rebuild such associations should changes in the
granularity of one of the tables occurs.
Besides, fact table relationships are generically
many-to-many, which cannot be represented by a
simple primary key relationship.

 

[mossback]

The dimensions contain PKs which should be unique (they also should be surrogate keys, but that's another story). Since users would (usually/hopefully) access the fact table by constraining on one or more dimensions, the fact table only needs to have a FK for each of the dimensions. Hope this helps.

 

[kishleo]

yeah, the statement (under the line) is very clear you might just consfused with this, well really fact table doesnt need a key, as you are aware of fact table contains records depends on the granularity of the requirement, and we will group all the records together based on the dimensions in a fact table, so there is no possibility for a duplicate records, so there is no need for a primary key
but the second thing is we have many FK's to dimension tables. so the combination of all the fk's is treated as a primary key to track the record. the main reason behind this is to avoid values into fact table which doesnt exist in the dimensions. in rare cases it is also possible to have a surrogate(system generated) key for fact table, this need mainly arises when the database where you are going to host the datawarehouse doesnt support some things, for example in sql server 2000 the primary key cannot be more than 16 columns, so if you have more than 16 dimensions in a fact table(not a normal case) you can generate another system generated key as a fact table.

the only thing you have to remember is there is no need for a primary key on the fact table because all the records were summed up based on dimensional attributes. so there is no possibilites for a duplicate records.
hope it is clear, anything else just ask ô¿ô Kishore

 

[dwtech]

The fact table has a multipart primary key. The parts are the dimensions primary keys. For each record in the fact table there is a unique combination of dimension keys which is multipart primary key.

So the Fact as such does not need another column as a primary key. Be Diligent!
DWTECH

 

[bluegod]

I'm deeply appreciative of your help.
It's very clear , help me a lot~

bluegod

 

[khobar]

Hence can i conclude the FACT table has only a composite FOREIGN KEY made of all the dimesnionsal keys and as such it does not need a primary key. The avoidance of primary key will fasten the loading process in the Fact table

kh

 

[kishleo]

no khobar,
you cannot conclude that, because it is possible to have a system generated key as a primary key in some cases, i.e. some times we dont take primery key as a FK's of dimensions, instead of that we generate a new key.

ready my post earlier in the same thread ô¿ô Kishore