-1

In my Fact_Table I have several Date fields such as:

  • order_date
  • payment_date
  • purchasing_date
  • estimated_delivery_date
  • actual_delivery_date
  • ...

How to choose which ones that need to be linked to the Date_Dimension and the others?

Thank you for your advice,

seeusoon
  • 45
  • 6
  • 1
    All of them should point to the date dimension that becomes a role-playing dimension http://www.jamesserra.com/archive/2011/11/role-playing-dimensions/. What problem are you facing exactly? – Marek Grzenkowicz Nov 16 '15 at 17:26
  • Hi @MarekGrzenkowicz, I don't really have a problem, I was just wondering if it is correct to have like 7 foreign keys from the Date_Dimension into the Fact_Table. According to what you said and the website you provide it seems so. Thank you then! Have a great day. – seeusoon Nov 18 '15 at 06:54
  • 1
    technically, what I would do, is use the same time dimension table and logically use it as orderdatedim, purchdatedim, actualdeliverydatetim etc. its possible in SSAS. – KrazzyNefarious Nov 18 '15 at 08:22
  • All right, both of you convince me to do so. Thank you again. – seeusoon Nov 19 '15 at 02:07

1 Answers1

2

You don't need to use foreign keys in a data warehouse, as your ETL should take care of integrity. Also, you might want hot-swappable dimensions in the future, and they don't use foreign keys.

Usually, a "smart" key is a bad idea, though I make an exception for dates, as it makes it easy to partition fact tables by date. Use an int type, and values like 20160201 (for Feb 1 2016).

You can, of course, join tables in SQL without foreign keys.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152