This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database.
The usual reason is that it “slows down” load performance.
No surprise there. Been hearing that for years.
And I say one of two things:
1. So what! I need my data to be correct and to come out fast too!
or
2. Show me! How slow is it really?
Keep in mind that while getting the data in quickly is important, so is getting the data out.
Who would you rather have complain – the ETL programmer or the business user trying to run a report?
Yes, it has to be a balance, but you should not immediately dismiss including FKs in your warehouse without considering the options and benefits of those options.
So here are my three main reasons why you should include FK constraints in your Oracle data warehouse database:
- The Oracle optimizer uses the constraints to make better decisions on join paths.
- Your Data Modeling and BI tools can read the FKs from the data dictionary to create correct joins in the meta data of the tool (SDDM, Erwin, OBIEE, Cognos, Bus Objects can all do this).
- It is a good QA check on your ETL. (Yeah, I know… the ETL code is perfect and checks all that stuff, bla, bla, bla)
Now of course there are compromise options. The three main ones are I know:
- Drop the constraints at the start of the load then add them back in after the load completes. If any fail to build, that tells you immediately where you may have some data quality problems or your model is wrong (or something else changed).
- Build all the constraints as DISABLE NOVALIDATE. This puts them in the database for the BI tools and data modeling tools to see and capture but, since they are not enforced, they put minimal overhead on the load process. And, so I am told by those that know, even a disabled constraint helps the optimizer make a smarter choice on the join path.
- (really 2a) Best of both – disable the constraints, load your data, then re-enable the constraints. You get optimization and quality checks.
So NOW what is your reason for not using FKs in your data warehouse?
Happy Modeling!
Kent
Posted in
Data Modeling,
Data Warehouse,
Oracle,
SQL Developer Data Modeler and tagged
#techtip,
data dictionary,
Data Modeling,
Data Warehouse,
data warehouse design,
data warehousing,
ETL,
FK,
FKs,
foreign keys,
free tip,
Oracle,
Oracle data warehouse database,
Oracle Database,
oracle database development,
oracle optimizer,
referential integrity,
RI,
tuning
Over the years, I have had numerous conversations about the value of having referential integrity (RI) constraints, such as primary and foreign keys, in a relational data warehouse or data mart.
The Data Warrior 