The Data Warrior

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

Archive for the tag “#SQLDevModeler”

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 , , , , , ,
22 Sep 2013

#OOW13 – Oracle OpenWorld 2013: Pictures, Tweets and more

#OOW13 is well underway now with lots of tweets and blog posts already out on the web. Check the stream on twitter under #oow13 for all the news.

I tried to see some of the Americas Cup race on Saturday but unfortunately the weather and wind did  not cooperate so the race was called for the day. 😦

But got a few pictures to share:

Americas Cup Racing: Team USA

Americas Cup Racing: Team USA

And a  nice view:

Golden Gate from the East Bleachers at the Americas Cup

Golden Gate from the East Bleachers at the Americas Cup

The New Oracle Plaza

Imagine our shock when we learned that there would be no Howard Street Tent this year! The nerve.

But in its place is the open-air Oracle Plaza, full of comfortable seats for hanging out, networking, eating lunch, and watching the keynotes on a big screen. Oracle is really counting on the weather to be typical dry fall weather. Hopefully that holds up.

OOW13: The New Oracle Plaza

OOW13: The New Oracle Plaza

User Group Sunday

I did attend a few sessions on Sunday.

The only session this year (from a user) on my favorite tool, Oracle SQL Developer Data Modeler was given by Heli Helskyaho, the CEO of Miracle Finland (@HeliFromFinland).

Heli Helskyaho: Why do I need #SQLDevModler?

Heli Helskyaho: Why do I need #SQLDevModler?

She did an excellent job of introducing folks to SDDM and laid out 9 use cases for using the tool.

9 use cases for #SQLDevModeler

9 use cases for #SQLDevModeler

(Hard to read I know, but zoom in…)

I also attended a session by my buddy Stewart Bryson, who is now the new Chief Innovation Officer for Rittman Mead globaly, Congrats Stewart!

He did an amazing (IMO) talk about how to go beyond agile and achieve Extreme BI. He gave us the beginnings of a complete Oracle-based framework which looks very promising.

He said some heretical things like skip the staging area for your data warehouse! I do think he is on to something and can make it work so we have a real chance at real time BI and still have a solid architectural foundation.

Stewart Bryson's talk about Extreme BI

Stewart Bryson’s talk about Extreme BI

Opening Keynote

Of course the was the opening Keynote by Oracle CEO Larry Ellison. Key words: #bigdata #inmemory and #AmericasCup (of course). Lots of big news.

Check out this info graphic about the event.

Mark Rittman already did a nice review of the talk (among other things). So rather than repeat check out his post.

And for those interested, right now the Americas Cup standings are USA 5 to New Zealand 8. But that will change later today for sure when they race again.

More to come about #oow13 and this years Oaktable World (#OTW13).

Later.

Kent

The Oracle Data Warrior

P.S. Had a great evening last night at the annual Oracle #ACE dinner which was hosted at the Walt Disney Museum in The Presidio.

Posted in Data Modeling, Data Warehouse, ODTUG, Oracle, SQL Developer Data Modeler and tagged , , , , , , , | Leave a comment
25 Aug 2013
17 Comments

Better Data Modeling: Are you making these 3 beginner mistakes in your data models?

There are lots of people in the database industry that end up building data models.

Some of them are very educated and well trained and are professional data modelers and data architects. (If that is you, you can probably skip this article)

Others have learned on-the-job with little or no training or education on modeling concepts or techniques. They may be database administrators or even programmers that got asked to produce a model diagram by their boss or project manager, after the project was delivered (but there was no data modeler on the project ever).

This article is for this last group of folks who may want to improve their knowledge or skill in data modeling.

So here are three of the most common mistakes I have seen over the years:

1. Only defining surrogate keys

Do all your tables have a primary key defined and that primary key is a single column integer generated by the system? That is a surrogate key.

Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.

CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key.

2. Using Visio or PowerPoint to draw your data model diagrams

This is all too common when no data modeler has been hired for a project or there is a really tight (as in NO) budget. The result is a pretty picture that gets out of date very quickly and can’t help you generate DDL code to build (or rebuild) the database.

Instead of that, you should either invest in a real data modeling tool (like ERWin), or better still, get Oracle’s totally free SQL Developer Data Modeler (my favorite!). The point being with a real data modeling  tool, you can forward and reverse engineer your database tables, make changes, review them, generate DDL, etc.

3. Not reviewing the model with real business people

Build it and they will come does not work!  How do you even know you are using the right terminology or have defined the relationships correctly if you have not talked to a business person about their data and data needs?

Instead of that, you need to involve business users from the very beginning of the project. You need to either set up 1:1 interviews with key stake holders or better yet have group JAD sessions to discuss the project and have them help you define the data model. That is the best way to get buy in to the end result.

Just avoiding these three rookie mistakes can greatly improve your chance of success.

Bonus Tip:  The best way to avoid these and other common rookie mistakes is to use a pre-defined data model review checklist.

You can get a jump-start on your own check list by downloading  my Kindle book A Checklist for Doing Data Model Design Reviews on Amazon.com.

Get it here: http://www.amazon.com/Check-Doing-Design-Reviews-ebook/dp/B008RG9L5E/, or just search Amazon for author Kent Graziano.

Here’s to building better data models! Are you with me?

Kent Graziano

The Data Warrior

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , , ,
31 Jul 2013

Another Quick Tip for SQL Developer Data Modeler

Not sure how I missed this little utility, but I did.

Ever need to quickly build a set of views on a set of tables to create a read-only data layer you could expose to some users or processes?

Well as I am developing a new data warehouse for my current client, we decided to control access by creating a read-only user that would hold views that pointed back to our main data warehouse schema. The BI tool points to the read only schema (for now anyway).

Anyway, under the Tools menu I found a Table to View generator.

Under the Tools menu look for the Wizard to create a view definition based on a table

Under the Tools menu look for the Wizard to create a view definition based on a table

Once you select it from the menu you get a dialog box with all the tables in your model selected. So with one push of a button you have views on all the tables.

Get a list of table to convert and Select or Deselect all

Get a list of table to convert and Select or Deselect all

Then you can edit the views, if needed, using the view query builder.

Or you can select (or de-select) specific tables to build views on.

Even better – the tool applies a naming standard on the output view names (v_<table name>).

On top of this, if you happen to have some views (maybe for testing?) that you want to turn into tables and then populate with an ETL process, right below the first wizard is an option to create a table definition from the view definition.

Now granted most of you can easily do either of these tasks using plain old SQL, but imagine you need to do it for several hundred tables.

This little wizard would save you a ton of time (and testing).

And you will have a documented data model when you are done.

So go give it a try!

Later.

Kent

NB: I am taking a little downtime in early August so don’t look for any new posts until near the end of the month.

Posted in Data Modeling, Data Warehouse, Oracle, SQL Developer Data Modeler and tagged , , , , , , | Leave a comment
19 Jun 2013
1 Comment

See you at KScope13!

Are you ready?

It is almost time for the annual ODTUG KScope conference in New Orleans. It starts with the Community Service Day on Saturday June 22nd and runs through Thursday June 27th at the Sheraton Hotel right on the edge of the French Quarter.

For my readers that are attending, I will be giving three talks this year, leading morning Chi Gung classes,  as well as sitting on the BI Lunch and Learn Panel.

My talks will be:

Five Ways to Make Data Modeling Fun – Monday at 9:45 AM

Using OBIEE and Data Vault to Virtualize Your BI Environment: An Agile Approach – Monday at 3 PM (with Stewart Bryson)

Top Ten Cool Features in Oracle SQL Developer Data Modeler – Tuesday at 12:15 PM

And on Wednesday at 10:45 AM you will find me in Social Media Lounge getting interviewed about Data Modeling, ODTUG, and KScope.

If you are joining me for Morning Chi Gung, I believe we will meet in the hotel lobby at 6:45 AM so we can walk to the river front park where we will hold our class. It is only a 30 minute class right before breakfast so please give it a try and get energized for a long day of learning and networking! Follow me on twitter @KentGraziano for any updates to the location and meeting time.

Don’t forget to download the new KScope Mobile App so you can keep track of your schedule and not miss any of these sessions.

See you in New Orleans!

Kent

The Oracle Data Warrior

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

Post Navigation

← Older Entries
Newer Entries →