The Data Warrior

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

Archive for the category “SQL Developer Data Modeler”

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 , , , , , , , , , , , , , , , , , ,
22 Mar 2014
4 Comments

Live from the 1st Annual World Wide Data Vault Consortium: Day 2

Wow has is been an amazing event with many amazing people from around the globe. I am VERY glad that I took the time and came to St Albans for this inaugural event.

As with Day 1, there is just too much great information for me to adequately cover it in a blog post (or 10!), so I will give you some highlights and lots of pictures.

The good news is that sometime in the next few months you will be able to purchase access to a video of the entire event on LearnDataVault (then you will be really bummed about not having come in person). Dan and Sanjay figure with their schedules it will take a few months to produce a top quality video. The good news is we had professional videographers for all three days and they filmed every keynote and every talk.

I will let you know when the video is ready.

Dan Does a Deep Dive on Data Vault 2.0

Due to popular demand, Dan actually changed the agenda and made the first session of Day 2 a detailed look at some of the more important aspects of Data Vault 2.0. This is material that he has not really written much about and is only available otherwise in his DV 2.0 bootcamp class.

Here are some of the highlights (again to see even more details check #WWDVC on twitter).

Opposing goals between DW storage and Mart presentation have led to many failed DW/BI projects.

Opposing goals between DW storage and Mart presentation have led to many failed DW/BI projects.

BTW – Dan would like to see us stop calling the reporting side “Data Marts” and start calling them “Information Marts”. To be agile we have to stop mixing the raw data storage (EDW) with the reporting that has applied business rules.

Dan went on a bit of a rant about the need to measure things.

DV 2.0 Methodology helps us be more precise to be more successful

DV 2.0 Methodology helps us be more precise to be more successful

Then we had a discussion about DV 2.0 architecture and methodology and how it supports and fits into the knowledge pyramid.

Dan introduces us to DV 2.0 and the Knowledge Pyramid

Dan introduces us to DV 2.0 and the Knowledge Pyramid

The DV 2.0 Architecture

The DV 2.0 Architecture

The some discussion about DV 2.0 agility backed up by a real user case study.

A DV success at Qsuper

A DV success at Qsuper

It sure was great to see real numbers on a DV 2.0 success story!

So how did they do it? Dan then showed us his recommended approach to requirements gathering that helps the process become more agile.

DV 2.0 approach to getting better requirements faster

DV 2.0 approach to getting better requirements faster

Then finally another rant about how to get better performance from our systems.

Dan's Rules of Performance

Dan’s Rules of Performance

Roelant Vos (Analytics8) talks about DV Automation

Characteristics of ETL for Data Vault

Characteristics of ETL for Data Vault

Roelant Vos discusses generating Data Vault ETL from meta data

Roelant Vos discusses generating Data Vault ETL from meta data

Roelant uses Lego as a great analogy for the patterns in Data Vault and why it is possible to auto-create ETL code.

Model driven ETL generation is possible

Model driven ETL generation is possible

What is needed to support generation of ETL

What is needed to support generation of ETL

With all this in mind, Roelant has built a nice little kit to actually generate DV ETL code for his clients. Nice job! You can follow Roelant on twitter here.

Doug Needham (ClearMeasure) blows our minds with Data Vault Math.

There were a ton of tweets about this session. Doug has done some incredibly innovative work on using mathematics to determine the quality/completeness of a data vault model and some slick ways to validate that it has been loaded correctly. Lots of talk about graph theory and calculating edges ensued.

Doug blows our minds with math

Doug blows our minds with math

Research on how to determine the driving key for a Link

Research on how to determine the driving key for a Link

More calculations from Doug - vector math!

More calculations from Doug – vector math!

This is the first time I have met Doug, a former marine corp DBA. We have found we have a lot of experiences and thoughts in common, including a common client! He even grew up in Texas in the town where I now live!

In this session Doug famously said “If you do not know what a hypothesis, control group or A/B test is, you are NOT a data scientist.”

One of the great things about these event is the people you meet. I am glad to have met Doug.

Using Oracle SQL Developer Data Modeler for DV

After a much needed brain break from Doug’s talk and some networking time, I got to take the floor again to show my favorite FREE data modeling tool. I did my usual top 10 type talk but with a slant towards how I leverage all those features to support a data vault modeling project.

Data Vault Diversity: The former Marine mathematician and the long haired enviromentalist

Data Vault Diversity: The former Marine mathematician and the long haired environmentalist

There were lots of good questions and interaction and lots of interest in how I have built virtual data marts on top of data vault warehouses.

Starting my SDDM intro talk

Starting my SDDM intro talk

And that was it for Day 2. We all took a break then had a happy hour and dinner (with demo) sponsored by AnalytixDS. Good food and good fun!

Data Vault geeks from around the world in snowy St Albans, Vermont

Data Vault geeks from around the world in snowy St Albans, Vermont

More to come on Day 3!

Kent

Posted in Data Modeling, Data Vault, Data Warehouse, SQL Developer Data Modeler and tagged , , , , , ,
26 Jan 2014
1 Comment

Better Data Modeling: The Oracle Data Warrior Speaks!

Looks like I will be doing a bit of speaking this year at a number of  events around the country, and later on, the globe (more on that later).

As usually all my talks will center around using SQL Developer Data Modeler, data modeling standards, Data Vault, agile, or a combination of all of the above.

If you have budget and time, please come out to at least one of these events this year, I would love to meet you in person and talk about the world of Oracle and Data Modeling.

If you aren’t planning to attend one of these – WHY NOT?

These are all great events with tons of learning opportunities. The networking alone is worth the price of admission.

Here is a list of the first three events confirmed on my calendar (and SURPRISE – they are NOT all Oracle related events):

RMOUG Training Days

In less than two weeks: The Rocky Mountain Oracle Users Group Training Days 2014 in Denver, Colorado. This runs from Feb 5-7 , will have at least 1,000 people and you cannot beat the price.I will be presenting Friday at 1:30PM on how I save my clients big $$ by applying repeatable processes and standards to my data models.

Follow it on twitter with #RMTD14.

Data Vault Consortium

Next up March 20 – 22nd , I will be participating in the 1st ever World Wide Data Vault Consortium and User Group meetup in beautiful northern Vermont near the home of my good friend, the inventor of the The Data Vault Model and Methodology, Dan Linstedt. I will be speaking about agile and data warehousing, using SDDM to do Data Vault modeling, and no doubt engaging in some lively debates with Data Vault experts from around the globe. Check out the agenda on the event page for more details on who will be speaking (hint: Bill Inmon, father of  data warehousing is participating!).

Enterprise Data World 2014

The #EDW14 event is really the annual conference put on by DAMA International and the speaker list is a veritable who’s-who of the data architecture and modeling world. This year the event is in Austin, Texas on April 27 – May 1. Since that is quite close to where I live, I figured I would submit an abstract and I was honored to be accepted. I have attended this event only once before when it was in Denver (a long time ago!) and have been a member of DAMA on and off for years, but this is the first time I have been asked to speak. I am looking forward to it for sure (not sure how I will fit my talk into a 45 minute slot!). Sign up for it here.

If you are planning to attend any of these, drop me a line over Twitter or LinkedIn so we can plan to meet up.

Later.

Kent

The Oracle Data Warrior

Posted in Data Modeling, Data Vault, Data Warehouse, Oracle, SQL Developer Data Modeler, User Groups and tagged , , , , , , , , , , , , ,
19 Jan 2014
3 Comments

Better Data Modeling: Color Code Your Data Model Diagrams using #SQLDevModeler

One of the standards I recommend in my book  Check List for Doing Data Model Design Reviews is to use color in your diagrams to visually differentiate types of entities or tables.

As luck would have it, Oracle SQL Developer Data Modeler has a feature that makes this very easy. It is Classification Types.

In the latest version. 4.0, you set these up by going to the context menu on the Design level. From that menu pick Properties. Once on the property dialog go to Settings -> Diagram -> Classification Types. (In 3.x look under Tools -> Preferences)

The default install comes with a bunch already – fact, dimensions, logging, summary, and temporary. Each has a pre-set color assigned. You can change that color by clicking on the color and selecting another option from the pallet. You can also set a prefix for each type. (Note – if you are already using a classification and change the color, when you hit apply the new color will be applied in all existing diagrams within the design.)

You add new types by clicking the green plus (+) sign and then just add in whatever you want and save.

For Data Vault modeling, I add three new types: Hub, Link, and Satellite with the colors you see in the screen shot here.

Using Classification Types to Color Code Your Diagrams

Using Classification Types to Color Code Your Diagrams

To apply a classification type to an existing table, open the table property dialog and look for the classification types node in the tree (in 4.0). In 3.x, there is a simple classification type drop down on the main property page.

Once applied, the first letter of the classification type will appear in the upper left corner of the table (see screen shot).

Another way I have used this recently was in my current data warehouse project where I have source, stage, and dimensional tables all in one design. I found I often want to show all three tiers in on diagram (sub view) for a sprint (we are using a SCRUM approach) to help the ETL programmers and QA folks have one place to go where they can see how these layers are related. So for this project, I also added a source and stage classification type.

So if you have been color coding you diagrams by hand, this tip should save you a bunch of time since you won’t have to pick the colors by hand on each table. Plus the color selection will be more consistent.

If you aren’t color coding, now would be a great time to start!

Bonus Tip: If, like me, you want to be consistent across all your designs with the types and colors, I just figured out I can hack the dl_settings.xml file to copy my classification type customizations from one design to another. Just be sure to exit and then restart SDDM after you update the file for it to take effect.

Have fun coloring your diagram! (Maybe more people will read them)

Kent

The Oracle Data Warrior

 

Posted in Data Modeling, Data Vault, Data Warehouse, Oracle, SQL Developer Data Modeler and tagged , , , , , ,
06 Jan 2014
1 Comment

Oracle Data Warrior: 2013 in Review

Happy New Year!

I have been busy relaxing with my family on vacation so I decided not to write a full review this year. It was a busy year in many ways (not just blogging!). If you take a look in the right column, you will see a list of recent posts, top posts, and a complete archive by month. Go crazy…

If you want some highlights and details to see what happened on Oracle Data Warrior, check out this cool report provided by WordPress. Below the report I am also posting the link for Jeff Smith’s SQL Developer year-in-review.

Year in Review Report

The WordPress.com stats helper monkeys prepared a 2013 annual report for my blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 46,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 17 sold-out performances for that many people to see it.

Click here to see the complete report.

Other Reviews

Well my main man Jeff Smith did a nice write up for SQL Developer. Check it out here: www.thatjeffsmith.com/archive/2013/12/the-best-of-2013-sql-developer-posts/

I was pretty busy on Twitter this year too. Here is a list of my top tweets: https://twitter.com/KentGraziano/timelines/419701819502104577.

For something fun check out the Vizify video link in my twitter profile.

For me, 2014 will be more adventures in Oracle data land, attending conferences, listening in at the BBBT, working with Dan and Sanjay on DV 2.0 stuff, and working to build and improve data models and data warehouses for my clients.

What will you do in 2014?

Cheers!

Kent G

The Oracle Data Warrior

Posted in BBBT, Data Modeling, Data Vault, Data Warehouse, ODTUG, Oracle, SQL Developer Data Modeler, User Groups and tagged , , , , , ,

Post Navigation

← Older Entries
Newer Entries →