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

Question

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?

Answer 1

It is a good practice because it helps ensure that all your facts reference existing dimension members, and may improve performance in some DBMS because of indexing, 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.