Foreign key referential integrity in the fact tables

View previous topic View next topic Go down

Foreign key referential integrity in the fact tables

Post  Ashish Mishra on Thu Jul 07, 2011 5:01 am

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

Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Thu Jul 07, 2011 3:13 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  hang on Thu Jul 07, 2011 8:02 pm

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?

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  John Simon on Thu Jul 07, 2011 8:06 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  Ashish Mishra on Fri Jul 08, 2011 2:10 am

thanks for the valuable inputs

Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Sun Jul 10, 2011 12:04 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  John Simon on Tue Jul 12, 2011 4:16 am

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  hang on Tue Jul 12, 2011 8:06 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  BoxesAndLines on Tue Jul 12, 2011 9:03 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Tue Jul 12, 2011 2:01 pm

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  hang on Tue Jul 12, 2011 6:23 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  John Simon on Tue Jul 12, 2011 6:42 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  BoxesAndLines on Tue Jul 12, 2011 7:15 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Tue Jul 12, 2011 8:02 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  hang on Wed Jul 13, 2011 12:18 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  Dave Jermy on Wed Jul 13, 2011 5:57 am

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.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Wed Jul 13, 2011 12:12 pm

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  bidw_kk on Mon Jul 18, 2011 1:48 am

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?

bidw_kk

Posts : 5
Join date : 2011-07-16

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  ngalemmo on Mon Jul 18, 2011 8:34 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  bidw_kk on Tue Jul 26, 2011 12:16 pm

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.

bidw_kk

Posts : 5
Join date : 2011-07-16

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  John Simon on Tue Jul 26, 2011 10:14 pm

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!

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  BoxesAndLines on Tue Jul 26, 2011 10:20 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Foreign key referential integrity in the fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum