Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Primary Keys, are they a good practice for Fact Tables?

Primary Keys, are they a good practice for Fact Tables?

Primary Keys, are they a good practice for Fact Tables?


In a data warehouse, is it  a good practice for fact tables to have a actually primary key added as a composite of its FK's.

As I understand using a PK can slow down the ETL load process and I can use my ETL tool (informatica) to define a logical key when there's a need to update or insert the fact table - this supports my ETL process, doesn't allow duplicates, all without having to have a physical primary on the fact table.

Since the clustered index doesn't need to be on a primary key I don't believe there are any performance issues of the OLAP side, but are they any benefits I'm missing out on?

Any insight would be much appreciated.

RE: Primary Keys, are they a good practice for Fact Tables?

I don't use them.  Even if I did, it would not always be possible to create a primary key as a composite of foreign keys.  

Taking a simple example, on a sales fact table, if a customer made two identical purchases with the same date key, with the same time key (possibly within the lowest granularity of 15 minutes), with the same product key, with the same location key, and so on and so on, then the only thing which would be unique would be the transaction number (the business key).  So you could have a primary key on the transaction number, but I don't see any benefit there as your ETL process will enforce the uniqueness of that key.  Yes, you could put an index on it, but it does not need to be a primary key.  


RE: Primary Keys, are they a good practice for Fact Tables?

According to Kimball the answer is depends.

Fact Table Surrogate Keys

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close