SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, July 17, 2006

Referential integrity in DW necessary??

SQL Apprentice Question
In a data warehousing application, what is the impact of imposing
referential integrity on the database side? Does it help or degrade the
performance considering the complex transformations that take place during
the ETL process. I have read a few articles suggesting not to impose
referential integrity on the Data Warehouse but would like to hear more
views on this. Request you to please share your previous experiences on
this....
Thanks in advance.
Celko Answers
>> In a data warehousing application, what is the impact of imposing referential integrity on the database side? <<


Unlike OLTP, a warehouse is scrubbed BEFORE it is persisted in the
schema and then it is STATIC. There is not (well, should not be) any
need to have RI or a lot of constraints. What you want ina data
warehouse is access methods for aggregation of bulk data. I have a
book on OLAP & ANALYTICS IN SQL due out in a few months which gives an
over view of the issues

No comments: