As a data warehouse architect/designer, we always have to deal with the question: “should I use foreign keys ? Should I enforce them ?” Well, yes! for the first question, probably not for the second. Here is why.

Relationships between fact tables and dimension table are described by foreign keys. For example fact_order.dwh_customer_id relates to dim_customer.dwh_customer_id (Yes, when I am in charge of modeling, I choose to prefix all technical and surrogate dwh keys with dwh_, etl meta data is prefixed with … etl_ such as for etl_modification_time or etl_crc_value). It’s always a good idea to use the same column name in both tables when applicable: this would make understanding the data model easier for IT and the Business for one, and also reporting tools can also rely on this convention.

Now, should this relationship also be described by a foreign key DDL ? Yes! In a BI platform, “meta data” matters ! And a foreign key is additional meta information. It can be used by the query optimizer of the database to speed up queries, by data modeling tools, and least but not last, by the reporting solution! Microstrategy, for instance would read this meta information to automatically associate fact tables and their dimensions on that key information. It also helps these tools for (inner) join culling (although this can usually be forced anyway).