I always declare fact to dimension foreign key constrains in SQL Server 2005.

However I do not enforce the foreign key constraint, nor define any cascading event actions.

Example :

ALTER TABLE

[dm].[Fact_Episode_Summary] WITH NOCHECK ADD CONSTRAINT

[FK_Fact_Episode_Summary_Dim_Hospital] FOREIGN KEY([Hospital_DID])

REFERENCES [dm].[Dim_Hospital] ([Hospital_DID]) NOT FOR REPLICATION

GO

ALTER TABLE [dm].[Fact_Episode_Summary] NOCHECK CONSTRAINT

[FK_Fact_Episode_Summary_Dim_Hospital]

GO

I, like other noted here , do not enforce the constraint as the ETL process swill always gurantee that the relationship is valid. To do so would be an unnecessary overhead.

However the relationship is very important to the query optimiser. It tells the optimiser that it can rely on the 1:n relationship. For very large databases this is critical as it allows the qury optimser to design a suitable parrallel query plan.

I have been building data warehouses for about 10 years now.