Referential integrity in DW necessary??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shsandeep - 16 Jul 2006 04:26 GMT

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.

Reply to this Message

--CELKO-- - 16 Jul 2006 05:20 GMT

>> 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.

Reply to this Message

Serge Rielau - 16 Jul 2006 05:27 GMT

>>> In a data warehousing application, what is the impact of imposing referential integrity on the database side? <<
>
[quoted text clipped - 4 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Reply to this Message

shsandeep - 16 Jul 2006 05:40 GMT

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

Cheers,
San.

Reply to this Message