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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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

Kent OlsenDBACommented:
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?
Kent OlsenDBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.