FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Publisher reserves the right to serve qualified requesters only.

Do you have any specific suggestions about the use of a primary key in fact tables?

Q: We are trying to find some theory about the benefits to have a Primary Key to identify records in a Fact Tables. We do not use it because we presume all the integrity issues are treated in ETL and the volume of Index will greatly increase the size of my DW. Do you have any specific suggestions about the use of a PK in Fact Tables? (I'm not talking about FK with dimensions.)

Chuck Kelley's Answer: You will find many different answers to this question. Personally, I would prefer to have the primary key defined on my Fact tables to make sure that the ETL is doing the job correctly. It is my opinion that the database should always be correct and I don't always trust the developers to get it right. I have seen the developers put in Distinct clauses to make them distinct before writing them out, thus dropping some data.

Having said that, I have created Fact tables without a primary key and only the FK to the dimensions defined with Indexes.


Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

For more information on related topics, visit the following channels:



Industry Vendors