Foreign Key Constraints in Data Mart Design

View previous topic View next topic Go down

Foreign Key Constraints in Data Mart Design

Post  ljsong on Wed Jun 15, 2011 3:57 pm

What's the best practice on foreign key constraints in your company's Data Mart design. For example do you build any foreign key from dimension table to fact table? do you usually build them but not enforce (enable) them in the database? Do you have different practices in development environment, test environment, and production environment?

ljsong

Posts : 1
Join date : 2011-06-15

View user profile

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  BoxesAndLines on Wed Jun 15, 2011 9:55 pm

I build them and enforce them in development environments. For test and production environments, I do not enforce. If the database supports the definition only foreign (e.g. SQL Server) and the modeling supports DDL generation (e.g. not ER Studio) then I generate the appropriate DDL. I have spent countless hours for clients building data models that are reverse engineered from the database. This is made more tedious since the FK constraints are not generated (I'm not recommending you do enable). When you reverse engineer these databases you end up with 100 boxes and no lines. And if you're going to build a data model, you need boxes and lines.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  ngalemmo on Wed Jun 15, 2011 10:19 pm

If you are using surrogate primary keys, the process used to assign those keys ensures relational integrity. Enforcing FK constraints in the database is redundant and not necessary.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Enforce and capture exceptions

Post  AndreLinssen on Fri Jun 17, 2011 8:48 pm

A data mart is a subset of a data store, therefore, a data mart is a subset of a fact table (or whatever design your data warehouse has) for a particular purpose. However, in your question you
define a data mart as a fact table with dimensions, which means it's not a subset but an entire fact table with dimensions. Ok, enough about what we define as a data mart.
Ask yourself the following question: do you want to be 100% sure your facts refer to a record in a dimension? If the answer is "yes", then you should enforce foreign keys. But your work doesn't stop there. On the contrary, it starts when a fact is referring to a non existent record in a dimension. You should think about a procedure to capture these "exceptions" and deal with them.
The advantage of enforcing foreign keys is that you can be absolutely sure you fact and dimensions have proper references. For reporting purposes this is important. Think about what would happen to facts that are not linked, do you see them in your report? Do you want to include them using outer joins in your queries? If so, realize it will be a burden for the performance of these reports.
Take into consideration that sometimes scripts are updating the facts and dimension for whatever purposes. Are you still sure that every fact is referring to a dimension?
I would recommend you enforce the foreign keys. But, you should develop a mechanism that keeps track of the facts that violate these constraints.
avatar
AndreLinssen

Posts : 6
Join date : 2011-02-15
Age : 51
Location : Netherlands

View user profile http://www.linssen-it.nl

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  BoxesAndLines on Fri Jun 17, 2011 11:24 pm

AndreLinssen wrote:A data mart is a subset of a data store, therefore, a data mart is a subset of a fact table (or whatever design your data warehouse has) for a particular purpose...

I'm with you on the first part, but you lost me on the therefore part. A data mart would never be a subset of a fact table. It could be more than one fact and related dimensions based on a business process.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  ngalemmo on Sat Jun 18, 2011 8:09 am

It's a novel definition... personally I've given up on using 'data mart', it seems everyone has their own definition. Kimball originally used it to refer to a single star schema, Inmon used it to refer the result of a publication from the DW. It has gone downhill from there...

As far as enforcing RI with FK contraints (when performing surrogate key assignment), its a lot like wearing a belt and suspenders. If you are really that worried, maybe you shouldn't go outside.
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 Constraints in Data Mart Design

Post  hang on Sat Jun 18, 2011 7:02 pm

ngalemmo wrote:its a lot like wearing a belt and suspenders. If you are really that worried, maybe you shouldn't go outside.
I like the analogy! Donít overkill your data warehouse, as it will defeat its two major purposes, performance and ease of use.

hang

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

View user profile

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  John Simon on Sun Jun 19, 2011 8:03 pm

Having foreign key constraints can help with Star Query optimizers (at least in SQL Server). It can also help with documentation and with reporting tools that use the constraints to understand relationships.

Do not enforce them though - your surrogate key generation ensures that enforcing the constraint is redundant. It will also slow down your ETL load for no benefit.

Andre has a very weird definition of a star schema. That said, I attended a Bill Inmon seminar last year and he said that the definitions for most of the DW world have been taken over by vendors to suit their own purposes, hence his decision to copyright the term DW 2.0

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 Constraints in Data Mart Design

Post  BoxesAndLines on Sun Jun 19, 2011 9:18 pm

John Simon wrote:...That said, I attended a Bill Inmon seminar last year and he said that the definitions for most of the DW world have been taken over by vendors to suit their own purposes, hence his decision to copyright the term DW 2.0

Right. Isn't Bill a vendor too?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Foreign Key Constraints in Data Mart Design

Post  John Simon on Mon Jun 20, 2011 1:48 am

He is, and regardless, he does have a point.

I mean, what the hell does ODS stand for these days? I get asked as a consultant to build an ODS when what they really mean is a 3NF database (i.e. an Inmon-style DataWarehouse). I asked Inmon about an ODS and he said it was for intra-day reporting and stored maybe 30 days worth of data and sits on a separate database for performance reasons, which makes sense to me. It's crazy how when a client gives me a specification I need to question them on any industry-specific terms, because those terms could mean anything these days.

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 Constraints in Data Mart Design

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