The Data Warrior

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

24 Oct 2013

Our greatest glory…

Our greatest glory is not in never failing, but in rising every time we fall.

— Confucius

Posted in Quotes and tagged , | Leave a comment
16 Oct 2013

Better Data Modeling: New and Improved Oracle SQL Developer Data Modeler (#SQLDevModeler)

Yup, my friends at Oracle have been hard at working enhancing what was already the best FREE data modeling tool out there.

They just released SDDM R4 EA3! You can go get it right now: http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/datamodeler-4ea-downloads-1988443.html

As always there are both new features and bug fixes.

One of the coolest new features is the ability to show entity (or table) comments right on the diagram in the object. This will be very useful for enabling data model reviews with the business users.

Product manager Ashley tweeted and example the other day:

 

For even more details and ideas how to use this feature check out Jeff Smith’s post on the feature here.

So what are you waiting for? Go get it today!

Data Modeling is Fun!

Later

Kent
The Oracle Data Warrior

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , | Leave a comment
06 Oct 2013
2 Comments

Better Data Modeling: Finding Missing Unique Keys in Oracle #SQLDevModeler

One of the best practices I recommend is to always define unique business keys for every entity (or table) in a model.

It is the only way to really understand what the data in that object represents.

So what do you do when you inherit someone else’s model with hundreds of tables and few (if any) unique keys to be found?

After you reverse engineer it into SDDM (SQL Developer Data Modeler), you could go through the model table by table and look at the properties.

Or, you could look at all the diagrams to look for the the little U’s indicating a column is part of a unique key constraint (assuming there are any diagrams to look at).

Or you could create a Custom Design Rule that checks for you.

So how do you write a design rule that will list all tables with no UKs on them?

Open your design, the go to Tools -> Design Rules -> Custom Rules.

  1. Hit the green Plus sign to add a new rule.
  2. Give it a name (like Missing UKs),
  3. Select Table for the object type,
  4. Mozilla Rhino for the Engine,
  5. Warning for the type, and
  6. Select table as the variable
  7. Past in this code: 
function checkUKs(table){
ruleMessage=””;
if(table.getUKeys().size() == 0){
  ruleMessage=”no UKs”;
  errType=”Problem:”;
  return false;
} else {
  return true;
}
}
checkUKs(table);

Hit Save, then Apply.

The result will be a list of all the tables in your design that do not have any Unique Key Constraints defined.

Now the real work begins – fixing those tables! As you work your way through the model adding the new business keys, you can keep using this report to see which ones you have left, and make sure you don’t miss any.

Get to it my friends!

Kent

The Oracle Data Warrior

P.S. Special thanks to DimitarSlavov  of Oracle for posting the code to answer my question. If you want to see the whole thread go here.

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , ,
02 Oct 2013

Data Modeling for Fun and Profit: Are you ready to take the Database Design Challenge?

Are you up to publicly testing your database design chops?

Want to improve your street cred?

If so, then read on…

Relational databases form the backbone of thousands, if not millions, of applications and systems around the globe. A key part of building these applications is designing and implementing the data structures they use.

(Well a few of us “old school” guys still think so no matter what the anyone else thinks!)

Proper table design can mean the difference between a scalable, high performing database that is a joy to query and an unfathomable, unsupportable mess that makes your brain melt.

Given the importance of these databases, understanding good data modelling techniques and physical implementation methods are essential skills for data and system architects, database administrators and developers creating database applications.

Building on the hugely successful SQL and PL/SQL quizzes already available at the PL/SQL Challenge, the new weekly Database Design Quiz kicks off on Saturday, October 5, 2013 to help you build these skills. The quiz will cover many areas of database modeling and design, from logical design all the way to physical database design, including topics such as:

  • Normalization – ensuring you have high quality data
  • Referential integrity – saving you the time and effort of writing your own application-based constraints
  • Indexing – enabling you to write fast and efficient queries

Whether you’re an experienced data modeler or completely new to relational databases, the weekly Database Design Quiz offers you the opportunity to both learn new approaches and show off your expertise. It will teach techniques that you can use to improve the quality for your work and impress future employers with your achievements.

This weekly quiz is managed by Chris Saxon, who has been playing the PL/SQL Challenge since August 2010 and placed second in the most recent PL/SQL Championship. More to the point of this quiz, however, he is also a database technologist with 10 years experience designing and building Oracle database applications. He currently works as the Data Architect for the airline Flybe, a role which sees him creating the data structures for the flybe.com database and the company’s enterprise data warehouse. He also runs the blog www.sqlfail.com, a project to explain database concepts and other topics of interest using just SQL and PL/SQL.

Registering is quick, easy and free. If you’re not already a member of the PL/SQL Challenge, then head to www.plsqlchallenge.com and sign up for a free account.

Let the Database Design competition commence!

Show me what ya got!

Kent

P.S. I have been helping Chris a little by reviewing the questions and I can tell you this quiz will be a real challenge even for those of you with years of experience. So get on over and sign up today (there will be prizes): www.plsqlchallenge.com

Posted in Data Modeling and tagged , , , , , , | Leave a comment
29 Sep 2013

Let’s Review #OOW13 and #OTW13 in Pictures

Yes I have been derelict in my duty and not posted about the sessions I attended at Oracle OpenWorld (#OOW13) and OakTable World (#OTW).

Well here are the high points with pictures!

Monday

Monday started off with the now annual Swim the Bay (so I missed the keynote). If you have Facebook, you can see pictures from the event here.

Most of the day I then spent at the alternate conference, OakTable World (#OTW13) seeing a few talk and giving one myself.

My good friend from Denver, Tim Gorman gave a nice talk about all the data compression options available in Oracle.

Tim Gorman: Oracle Compression Options

Tim Gorman: Oracle Compression Options

Next was a great session from the well known blogger and author Fabian Pascal. I have been reading his work for years but this was the first time I got to hear him speak in person. As with his writing, the talk was both intellectually stimulating and challenging!

Fabian Pascal: The Last Null

Fabian Pascal: The Last Null

It really is quite a debate in the database world about the meaning and use of NULL in an RDBMS. Fabian has a proposal on how we can (and should) represent data in a way where there will never be NULL attributes.

After a some scheduling issues. later in the day, I did my presentation on using Data Vault Modeling for Agile Data Warehouse Modeling. The room I got had a huge wall for me to project my session on. Definitely the biggest screen ever for one of my talks.

Biggest screen ever for me and my data vault presentation.

Biggest screen ever for me and my data vault presentation.

Tuesday

Started the morning with a few friends doing morning Chi Gung in Union Square, then followed by getting a quick survey of the exhibit hall in Moscone South and a trip to the Demo grounds.

The throng descends into the depths of Moscone West to hunt the exhibit hall for goodies.

The throng descends into the depths of Moscone West to hunt the exhibit hall for goodies.

The hall was of course HUGE as usual so some of the vendors who were tucked in back got creative on getting the foot traffic to come their way.

A clever gimmick one vendor did to get traffic to their booth in the gigantic hall

A clever gimmick one vendor did to get traffic to their booth in the gigantic hall

For sessions, I attend a road map session on Oracle’s Big Data strategy given by my friend JP Dijcks.

JP talks all things Big Data

JP talks all things Big Data

Mostly he painted a picture of the issues with figuring out how to collect and put all that data to real work. Of course Oracle has a ton of products to offer to help solve the problem.

How to shrink the gap between getting big data and actually using it!

How to shrink the gap between getting big data and actually using it!

Next up I attended Jeff Smith’s session on SQL Developer 4.0 and got to learn that there was a data mining extension available for the tool that makes doing some advanced analytics a lot easier.

Definition for Data Mining. An extension for Data Mining is available for SQL Developer.

Definition for Data Mining. An extension for Data Mining is available for SQL Developer.

Next on my agenda was the Cloud keynote with Microsoft. I wrote about that here.

Finally for the day, a late presentation by Maria Colgan and Jonathan Lewis giving us their top tuning tips in what they called the SQL Tuning Bootcamp.

Optimizer tips from a pro Jonathan Lewis. I am sure it means something to someone out there!

Optimizer tips from a pro Jonathan Lewis. I am sure it means something to someone out there!

As always with these type session, there was a ton of useful information that makes my brain hurt. I have to keep reviewing  my notes to make sure I can use at least 10% of what they taught.

Wednesday

This was mostly a work day for me at a client site. And a late lunch to see the final race of the America’s Cup.

In case you have been under a rock since last week, Team USA won! It was great to actually be there on Pier 27 during the final race. Not a great vantage point overall but with the big screen to watch and then seeing the boats right after they finished, it was worth the walk.

After the race and a little more data model work at my client’s office, I walked back to the conference to see a final session (for me) given by Gwen Shapira about using solid state disks with Exadata.

I really did not know much about SSDs before this session but feel really educated now. I actually had no idea that SSD and FLASH drives or FLASH memory were the same thing. Guess I was behind on the hardware buzzwords.

Gwen and Mark on Solid State Disk AKA Flash

Gwen and Mark on Solid State Disk AKA Flash

Then it was off to the annual blogger meetup then dinner on the town with friends at The Stinking Rose (thanks Tim!).

I decided to skip the appreciation event this year and take it easy, have a nice dinner, then pack up to head home. Thursday it was breakfast at Lori’s Diner then off to the airport and back home.

As a reminder if you want to see what the buzz was at the events, just check out the hashtags #OOW13 and #OTW13 on twitter (if you had a big data machine you might even be able to generate some insight from those feeds).

Well that’s a wrap for this years big show.

Next up, I will be speaking at the upcoming ECO conference in North Carolina. Should be fun.

Later.

Kent

P.S. If you want to see my OTW presentation, you can find them on Slideshare.

P.P.S. For another great review of OOW13 check out this post by my friend from Turkey, Gurcan. See if you can find my unlabeled cameo in the post.

Posted in Data Modeling, Data Vault, Data Warehouse, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , , , , , | Leave a comment

Post Navigation

← Older Entries
Newer Entries →