The Data Warrior

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

Archive for the category “Oracle”

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 , , , , , ,
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
25 Sep 2013
1 Comment

#OOW13 Oracle OpenWorld 2013: #Microsoft, #Cloud, Oh My!

Let me lead with the big stuff first, then I will report on some other sessions I attended later.

Live at the #Cloud Keynote

This should be interesting – joint keynote with Brad Anderson Corporate VP Cloud Enterprise from Microsoft.

Brad Anderson Corporate VP from Microsoft on stage at Oracle OpenWorld 2013

Brad Anderson Corporate VP from Microsoft on stage at Oracle OpenWorld 2013

This is definitely a first!

Brad says Oracle workloads running on Windows servers is one of the most common configurations in the data center.

Talking about The Cloud OS. Claims Windows Azure – public cloud is battle tested. For example:

  • 1.5 billion unique games of Halo on XBox
  • 8.5 trillion objects stored in Azure blob store

Announcing that available today on Azure:

  • Oracle DB
  • Oracle Weblogic

Check it out here: http://www.windowsazure.com/oracle

Only public cloud in the industry where you can provision an Oracle db!

Next Jeff Woosley demoed Azure for OOW. Neat Azure auto scale – triggered by pre-set but changeable threshold.

Also Infrastructure as a Service with Azure VMs.  Looks very easy to set up and configure an Oracle database in the cloud.

Very agile – can develop a configuration once and deploy anywhere.

I saw the first evidence of the partnership earlier today in the exhibit hall. Surprising.

Front and Center

Front and Center

No telling what will be next!

The Larry No Show

In a shocking (to some) turn of events, Oracle CEO Larry Ellison was a “no show” for his much anticipated cloud keynote. He was supposed to be on the stage after Microsoft’s presentation but alas Larry was otherwise engaged.

So he sent Thomas Kurian to present in his stead. Thomas is a great speaker and had some interesting things to say, but you had to feel for the guy when what was easily several thousand people got up and walked out of the keynote hall.

Now Larry had a pretty good reason – Oracle Team USA had won the first race of the day in the Americas Cup (during the Microsoft keynote actually) and the second match race of the day was to be run pretty much exactly when he was supposed to be on stage. So we can only imagine how torn he was (or not).

Hack off 60,000 Oracle customers, or miss seeing his racing boat win another race?

In all my years of attending OOW (all the way back to the first one in the mid ninety’s), I only remember Larry doing this one other time.

And imagine this, it was when he was in Australia during the America’s Cup that year. Now in that case he did at least phone it in and did a remote video talk from his yacht.

Folks were pretty hacked off then. And we did not win.

The upside this time (if there is one) is that Team USA did in fact win that race so now the cup is tied 8 – 8.

So tomorrow (Wednesday) will be the final race. One way or the other it will be over (for this year anyway).

Go Team USA! Let’s keep the Cup in America.

Kent

P.S. It is late so I will post about the excellent sessions I attended later this week. Stay tuned.

P.P.S. Still managing to work in some morning chi gung:

Posted in Oracle 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
20 Sep 2013

At the mother ship: Oracle ACE Director Briefing

Well, I can’t really talk about the meeting (yet) as everything is under NDA until next week (at least), but I can say it is great to get together with the top Oracle practitioners in the world and chat and discuss the issues and solutions in our field. It is like returning to the mother ship.

Oracle HQ - The Mother Ship

Oracle HQ – The Mother Ship

With all the tech talk and product futures, there is also just general catching up, networking, and bonding. Much is learned over pizza and beer.

And we are all following the America’s Cup race with Oracle Team USA trying to keep the cup away from Team New Zealand

I try to do my part in making sure we stay healthy and energized as well by running Chi Gung (Qigong) sessions in the morning before breakfast. Here is a shot of some of us outside this morning:

 

Not the best photo of Chi Gung practice, but what are you gonna do.

Lots more to come this week at Oracle Open World, so stay tuned for some very exciting announcements.

Go Team Oracle!

Kent

Posted in Chi Gung, Oracle and tagged , , , , , , , , | Leave a comment

Post Navigation

← Older Entries
Newer Entries →