Does Primary key in Dimension table have to be part of the Primary key in the Fact table?

Enrique Benito Casado

We have a Star schema with a very big Fact table, and around 20 Dimension tables, of course the PK from Dimension Tables have to be FK in the Fact table.

But do all that FK in the Fact Table need to be part of the PK too?

I work in severals DWH where that was not like that, but recently in a University exam, I got bad grade for not putting it.

Is that true? Is it a good practice?

If my Fact Table has 20 dimensions, does the Fact Table PK have to be composite for those 30 attributes?

jmng

It is a good practice because it helps ensure that all your facts reference existing dimension members, and may improve performance in some DBMS, but as with any other design/approach it is not mandatory.

Composite keys for fact tables are actually a recommended practice by Kimball's dimensional modelling methodology, namely because:

In most environments this composite key will suffice as the primary key to the fact table. There is typically no advantage of assigning a surrogate key to the fact rows at a logical level because we have already defined what makes a fact table row unique.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related