Foreign key referential integrity in the fact tables

Hi experts,

While implementing the physical model for a star schema
is it advisable to keep the foreign key of the dimensions on fact table at the database level or should it be maintained only through the ETL instead of enforcing by DBMS

what are pro and cons

I am looking from the perspective of the load and extraction performance


Databases that I have to implement this model is
DB2
oracle
Teradata
SAS

should my choice also be based on these factor.

Regards and thanks

In general, the process of assigning surrogate keys in the ETL process is more than sufficient to ensure FK RI. Enforcing constraints in the database is redundant and not necessary. Doing so slows down the load process.

However, some BI tools make use of FK declarations to ease defining relationships between tables. Therefore it is common practice to delcare FKs, but not implement contraints to enforce them.

Since ETL is a tightly controlled process, any database level RI requirements in data warehouse should be superseded by ETL. In OLTP system, RI constraints are targeted at data entry on record level and hence can protect data integrity. In data warehouse, data are loaded in batches by ETL and RI validation is just a bear minimum part of the whole complex process. RI constraints has performance implication to ETL, and some ETL also has its own peg order about loading the data which could require undoing the RI constraints.

In my experience, the only value having database RI is to automatically draw the connection lines between tables by some modelling tools. So part from superficial usefulness in data warehouse, database RI could be only useful, If you want your data bypass the ETL. Do you really want to do that?

I don't know about other databases, but in SQL Server the foreign key constraints allow for Star Query Optimizations. You should check if this is the same for your databases.

thanks for the valuable inputs

John Simon wrote:I don't know about other databases, but in SQL Server the foreign key constraints allow for Star Query Optimizations. You should check if this is the same for your databases.

Oracle doesn't require it (but there are other things you need to do, not related to constraints or declarations), last I saw of DB2 (2 yrs ago) they did not support star joins, and for Teradata... forgedda bout it.

Does SQL Server require a constraint or a simple declaration?

Just the declaration.
Btw, I'm now working as a solution lead on a large Teradata implementation. I was reading the Teradata documentation today and Teradata does have Star Query optimizations.

I am not sure what the 'declaration' means. In my understanding, it is declarative foreign key constraint by DDL. However I have done some googling, and dug up this site, http://msdn.microsoft.com/en-us/library/bb522541.aspx. To summarrise, in SQL Server 2008 R2, the optimization can be achieved automatically through Bitmap Filtering that has the following requirements:

- Fact tables are expected to have at least 100 pages. The optimizer considers smaller tables to be dimension tables.

- Only inner joins between a fact table and a dimension table are considered.

- The join predicate between the fact table and dimension table must be a single column join, but does not need to be a primary-key-to-foreign-key relationship. An integer-based column is preferred.

- Joins with dimensions are only considered when the dimension input cardinalities are smaller than the input cardinality from the fact table.

You can include 'NOCHECK' on the foreign key statement which will create the constraint but not enforce it. The real value for me has always been that I get boxes and lines when I reverse engineer a dimensional model. Normally, I just get boxes.

John Simon wrote:Just the declaration.
Btw, I'm now working as a solution lead on a large Teradata implementation. I was reading the Teradata documentation today and Teradata does have Star Query optimizations.

Is that by creating a join index or have they come up with something different?

BoxesAndLines wrote:You can include 'NOCHECK' on the foreign key statement which will create the constraint but not enforce it.

It's nice to know 'NOCHECK'. So if that is all, I would be happy to add constraint declaration without enforcing it, just to please Boxes and Lines.

I have been waiting for some new form of database system that is squarly targeted at data warehousing. In the new system, I would like to see at least a couple of RDBMS features should disappear. For instance, Transaction Log is another feature that is an overkill for ETL process.

I always had foreign keys with NOCHECK. It also helps if you are using Report Builder or creating a DSV in SSAS.

Neil, I'm not sure. I'll have to check and get back to you.

hang wrote:

BoxesAndLines wrote:You can include 'NOCHECK' on the foreign key statement which will create the constraint but not enforce it.

It's nice to know 'NOCHECK'. So if that is all, I would be happy to add constraint declaration without enforcing it, just to please Boxes and Lines.

I have been waiting for some new form of database system that is squarly targeted at data warehousing. In the new system, I would like to see at least a couple of RDBMS features should disappear. For instance, Transaction Log is another feature that is an overkill for ETL process.

Thanks Hang, after all, it's all about me.

hang wrote:
I have been waiting for some new form of database system that is squarly targeted at data warehousing. In the new system, I would like to see at least a couple of RDBMS features should disappear. For instance, Transaction Log is another feature that is an overkill for ETL process.

Netezza is such a system, so you don't need to wait. It got rid of a lot of normal OLTP oriented features to suit data warehousing.

It does not enforce constraints.
It does not maintain transaction logs. It uses serialization to enforce transaction states (simple and effective).
It does not provide row level locking (not possible using serialization).
It does not have indexing (in the traditional sense).
It is really fast.

Thanks ngalemmo, it's good to know. Is Netezza the only such a system on the market at the moment? I guess it would set the direction for next generation of data system.

John Simon wrote:I always had foreign keys with NOCHECK. It also helps if you are using Report Builder or creating a DSV in SSAS.

And also for automatic join statement suggestions for software like Redgate's SQL Prompt - making code writing much, much faster.

hang wrote:Thanks ngalemmo, it's good to know. Is Netezza the only such a system on the market at the moment? I guess it would set the direction for next generation of data system.

Yes, it is unique in its implementation. Other MPP type appliances have not significantly altered the underlying DBMS (usually Postgres).

ngalemmo wrote:
However, some BI tools make use of FK declarations to ease defining relationships between tables. Therefore it is common practice to delcare FKs, but /not implement contraints to enforce them.

How do we declare FKs without implementing constraints? Does Oracle allow that?
I tried looking in oralce books for the syntax but could not find any.
Moreover, in the book - Oracle data warehouse tuning for 10g: By Gavin Powell, he mentions on page 51 that

.....Star transaformation queries require foreign key constraints and foreign key indexes.....Star queries require fact table foreign key constraint indexes to be created as bitmap indexes.

Is he refering to FK declaration or FK constraints?

My experience with Oracle (up through 10G) was not to define any constraints at all. Just have bitmap indexes on the fact table FK columns.

There is a way to define a constraint but not enforce it. But, I'm out of town and don't have an Oracle SQL reference handy...

I'm also not sure what Mr. Powell refers to when he talks about a 'FK constraint index'. The only index needed to check a FK constraint is an index on the PK of the referenced table, which would never be a bitmap index. I would assume he means a bitmap index on the FK column... which has nothing to do with constraints.

A quick experiment has shown that Oracle indeed needs only the bitmap indexes on the joining columns (FKs in this case) and not the foreign key constraints.

ngalemmo, can you please tell how to define a constraint but not enforce it whenver your you get time.

I am now on a very large EDW project and it has become a nightmare because of lack of hard Referential Integrity. I always though use Soft RI (i.e. not constrained), but I've changed my position.

Here is my new recommendation: Use Primary and Foreign Key contraints, then take them off after 6 months. This will show up any issues with the source system or ETL jobs. Especially if you are outsourcing your ETL offshore!

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/general005.htm