Search code examples
data-warehousebusiness-intelligencedimensional-modelingstar-schemafact-table

multi links between Fact_Table & Date_Dimension

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,

Solution

  • 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.