I am just learning about data warehousing and business intelligence, and reading Ralph Kimball’s excellent book “The Data Warehouse Toolkit, Second Edition”.
In dimensional modeling, many of the fact table fields are foreign keys that link to the primary keys of dimension tables. My question is, when the data warehouse database is implemented in SQL Server 2005, is there any point to adding SQL Server foreign key constraints between the fact and the dimension tables? The data should have already been cleansed in the loading process, and adding foreign key constraints doesn’t automatically index the foreign key fields – the fields have to be indexed separately. It seems that foreign key constraints might add complexity, slow processing, and provide no benefit.