Help with data warehouse fact table design

Hi EE,

I am trying to design a data warehouse and been reading Kimball's Data Warehouse Toolkit. It says that the no of foreign keys should not exceed then 20.
When i looked into my relational database and started designing as per Kimball's design i found out that there are going to be over 60 foreign keys. Hence i thought to ask you guys help.
For example one of my table has got the addresskey and index of 0-9, for index 0 it means pick up address, for address 1 it means drop off address, for index 2 its firs stop over. Now in DW am  i right in assuming that i have to treat them as a separate column and each address and stop over will have its own key?  meaning that 10 foreign keys for addresses only. There's another table called reference with similar columns.

I would really appreciate your help here on how to handle this scenario?
If I put this as a project how much is it going to cost approx?

Kindest regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
Kent OlsenData Warehouse Architect / DBACommented:
All situations differ, but I have trouble visualizing a data warehouse with that many foreign keys.  It probably means that the fact table isn't denormalized enough.  But it doesn't mean that it's wrong, either.  It probably does reduce performance.

Instead of the fact table having a foreign key relationship to address, user, account and similar tables these items probably need to be denormalized and contained in each row of the fact table.
Kent OlsenData Warehouse Architect / DBACommented:
Hi Shah,

Put all of the addresses into the same dimension table.  Then when you join the dimension table to the fact table you filter on the address type that is appropriate.

CREATE TABLE dim_addresses
  MailingAddressID int;
  BillingAddressID int;
  DropOffAddress int;
  PickupAddress int;

Open in new window

Good Luck!
Ali ShahSQL DeveloperAuthor Commented:
Hi Kent,

Thanks a lot for your reply. How would i design the fact table? is it going to be
MailingAddressKey int,
BillingAddressKey int,
DropOffAddressKey int,

Open in new window

Or is there a way that i do only one addresskey in fact table and then join it with dimension table

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Kent OlsenData Warehouse Architect / DBACommented:
A data warehouse design usually denormalizes some of the data, sacrificing a bit of space for simplicity and efficiency.  Having a single address field in the fact table that will/could link to any of several columns in a dimension table adds complexity at several levels -- the ETL (load) process, reorganizations, queries, etc.

Include a column in the fact table for each address type.  Then populate the one(s) that are appropriate for the row.

Ali ShahSQL DeveloperAuthor Commented:
Thanks Kent, Will it be okay if my fact table has around 50 foreign keys? I was reading and Kimball suggest that no of foreign keys should be no more than 20?
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot Kent for explaining and helping and clearing my mind on this. I think i will have to live with these address and reference foreign keys within fact table and i would try to denormalize other dimensions.

Once again thank you very much


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.