Primary Keys, are they a good practice for Fact Tables? 1

[AMAL77]

In a data warehouse, is it a good practice for fact tables to have a actually primary key added as a composite of its FK's.

As I understand using a PK can slow down the ETL load process and I can use my ETL tool (informatica) to define a logical key when there’s a need to update or insert the fact table - this supports my ETL process, doesn't allow duplicates, all without having to have a physical primary on the fact table.

Since the clustered index doesn't need to be on a primary key I don't believe there are any performance issues of the OLAP side, but are they any benefits I'm missing out on?

Any insight would be much appreciated.

 

[RiverGuy]

I don't use them. Even if I did, it would not always be possible to create a primary key as a composite of foreign keys.

Taking a simple example, on a sales fact table, if a customer made two identical purchases with the same date key, with the same time key (possibly within the lowest granularity of 15 minutes), with the same product key, with the same location key, and so on and so on, then the only thing which would be unique would be the transaction number (the business key). So you could have a primary key on the transaction number, but I don't see any benefit there as your ETL process will enforce the uniqueness of that key. Yes, you could put an index on it, but it does not need to be a primary key.

 

[MDXer]

According to Kimball the answer is depends.

Fact Table Surrogate Keys