Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Should all dimensions keys in the fact be PKs?

3 posters

Go down

Should all dimensions keys in the fact be PKs? Empty Should all dimensions keys in the fact be PKs?

Post  erazon Mon Jun 25, 2012 2:42 pm

Hi all,
I think I still come from the 3NF mentality that the PK should be the minimal set of attributes that can uniquely identify a row in a table. But as for the fact table in dimensional modeling, this "rule" does not follow right?

My question is, should the primary key of the fact table always be the collection of all[u] FKs of the dim tables? Let us say for example, I have 10 dates in the fact (one is a transaction date which is logically be a PK and the others are 9 are "attribute" dates), should all of these 10 date keys be part of the PK?

Thanks,
Ed

erazon

Posts : 2
Join date : 2012-06-25

Back to top Go down

Should all dimensions keys in the fact be PKs? Empty Re: Should all dimensions keys in the fact be PKs?

Post  BoxesAndLines Mon Jun 25, 2012 8:26 pm

Nope. Use the minimum set of columns to define the PK. In fact I now just put a surrogate key on my fact tables as well. A single numeric PK for all tables in the warehouse is a wonderfully useful thing.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Should all dimensions keys in the fact be PKs? Empty Re: Should all dimensions keys in the fact be PKs?

Post  ngalemmo Mon Jun 25, 2012 10:42 pm

To add to that, there really isn't any particular reason to declare a PK unless you intend to update rows in the fact. If you have a transactional type of fact table where you only insert rows, there isn't a need for a PK.

Also, as far as fact tables go, it is not a good idea to actually enforce PKs even if you need one. It only introduces unnecessary overhead.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Should all dimensions keys in the fact be PKs? Empty Re: Should all dimensions keys in the fact be PKs?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum