RonKyle (3/10/2008)
I suppose that for some downloads there would be no change to the dimension tables, but I haven't had that experience. There are always new dimension facts to download too, such as new technicians or new zip codes where the work is being done. There is the chance that something will go wrong with those downloads, in which case, you wouldn't want the fact table to also succeed. FKs prevent that second download from succeeded.
There could be a case where the download time performance is absolutely crucial, but most downloads occur in the middle of the night, and 10 or 20 extra seconds isn't going to matter.
In general, I think that it is better to implement ETL features in the ETL itself and keep them out of the OLAP. And that is a large part of the whole philosophy of having an OLAP database that is separate from your OLTP database.
Also, I have been in many environments where my customer was pushing the limits of their 4 to 6 hour ETL window and there have not been many cases where one FK would have added only 10 to 20 seconds, let alone one or more for every dimension.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]