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.

Cheers,
San.

>> 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 havea
book on OLAP & ANALYTICS IN SQL due out in a few months which gives an
over view of the issues.

--CELKO-- wrote:
>>> 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 havea
> book on OLAP & ANALYTICS IN SQL due out in a few months which gives an
> over view of the issues.
>
While what Joe says is correct wrt constraint enforcement the DB2
optimizer can make very good use of constraints.
To solve these conflicting directions Db2 supports "informational
constraints" that is you can define constarints (check and RI) as "NOT
ENFORCED" but "ENABLE QUERY OPTIMIZATION"
This enables rules such as a theorem prover for check constraints and
"RI-Join-Elimination" to kick in.
Also it is recommended to specify UNIQUE indexes for these RI.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Thanks Celko for sharing your views....
So, in a nutshell, what are the pros and cons of having RI on a DW?

Cheers,
San.