The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “oracle database development”

28 Mar 2014
15 Comments

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

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:

  1. The Oracle optimizer uses the constraints to make better decisions on join paths.
  2. 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).
  3. 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:

  1. 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).
  2. 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.
  3. (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 , , , , , , , , , , , , , , , , , ,
28 Apr 2013
1 Comment

Another First at ODTUG’s #Kscope13

Those crazy kids at ODTUG have done it again!

At this year’s KScope in New Orleans this user group is introducing an evening of crossover sessions. Over the years the event has grown and actually blended the traditional Oracle database development world with the world of EPM, Hyperion, and Essbase.

In fact many folks who have come down the typical Oracle development path, myself included, know very little about the other side. So for these past few years at the conference we found there were not a lot of people crossing the various tracks (after all there are so many options to choose from).

In order to promote better cross pollination and as a way to encourage folks to expand their horizons and start to understand the “other” side, ODTUG has added two evening sessions on Tuesday so that we can gain that knowledge and not have to skip any of our regualr sessions.

The sessions will be presented by seasoned ODTUGers and Oracle ACEs. Check out these sessions:

Oracle Database Tools 101: How Does All This Stuff Get Built Anyway?

John King, King Training Resources

If you’ve been an Essbase/Hyperion, Applications, or BI user you may wonder what all the “hubbub” on the other side of Kscope is all about. Or maybe you’re curious — “I know there’s a database under the covers and lots of developers; what do they do?” If you want to know about the underpinnings of your favorite Oracle software, this session is for you. We’ll talk about how it all fits together: database, SQL, PL/SQL, ADF, Forms, APEX, and more (without too many boring details)! Attending this session will improve your understanding of and ability to communicate with the “bit-twiddlers” in your organization.

Hyperion 101: An Introduction to the Oracle Enterprise Performance Management Suite

Andrew Jorgensen, MindStream Analytics

Have you been hearing all this talk about Oracle Hyperion? Are you wondering what exactly an Essbase is?

This is the session for those who are looking for an introduction into Business Intelligence and Enterprise Performance Management. We’ll start with the core business problems that are addressed with these products. The session will then include an overview of all the products that cover the Oracle Hyperion Suite and what each is best used for. We’ll finish this session covering a variety of reporting options including OBIEE.

Get more details here: ODTUG Kscope13: Expertise + Crossover.

If you have not yet signed up to attend KScope13, it’s not too late. Just get on over to the registration page and get in on the learning!

See you in New Orleans!

Kent

P.S. I will of course be there so be sure to look me up and attend one of my sessions.

 

Posted in ODTUG, Oracle, User Groups and tagged , , , , , , , ,

Post Navigation

%d bloggers like this: