[SOLVED] Physical Pkey in Fact table

Physical Pkey in Fact table

I was in an interview. I did some code for them and they were keen on why there is no PKEY in Fact table, why there is duplicate data. In my opinion, FACT holds foreign keys from dim and there is no need of Physical PKEY. And on obvious point foreign key column will have duplicates. That whats its purpose is. To show me different fact across different stages. Now, Logically some composite key can make up as primary in fact table but is it good scenario to have that as physical in database.

Summarizing my question. 1. Does FACT should have primary key physically? 2. Can we have physical PKEY on set of fkey column( i dont think ms sql will allow this) ? 3. Does FACT should have surrogate key just for a sake of a pkey? We can have ordering on other important column like date?

Response is awaited want to understand the different opinion on this.

Solution

I am going to assume that when the interviewer asked about a primary key for a fact table, they were asking whether it needed a surrogate primary key (i.e. a unique number, usually generated by a sequence or auto-increment).

Within the Kimball methodology surrogate primary keys are used in dimension tables. With few exceptions, a fact table does not need a surrogate primary key. A fact table has a primary key but it is a composite key made up of a subset of the foreign key columns pointing back to the dimensions, and this makes a unique identifier suitable as a primary key. This key is physical in that you define it when creating the table and databases typically build an index for the defined primary key.

Exceptions to this generalization are:

Sometimes the business rules allow for identical fact rows. In this case, you need a surrogate key to uniquely identify a fact record.
Some ETL tools perform better if you have a surrogate primary key, most especially when you face a need for ETL to update/insert a row and then delete a previous fact record.

In these cases, a surrogate primary key is beneficial. However, it's not something you expose to the end user, it's merely a convenience to meet technical needs.