The Data Warrior

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

Archive for the tag “data model”

16 Mar 2015

Better Data Modeling: The Book

Trying to be as productive as possible during my infrequent down time, I just published another Kindle book with some of my best tips for Oracle Data Modeler. it is called Better Data Modeling: Tips for Enhancing Your Use of Oracle SQL Developer Data Modeler.

If you are one of the 3.5 million users (or so) who have downloaded this tool, and you want to know my little secrets for getting the most out of SQL Developer Data Modeler (#SQLDevModeler), this book if for you.

If you were an Oracle Designer user and are looking for a replacement data modeling tool, or you are using one of the other mainstream, expensive modeling tools and want a more cost effective alternative, then you owe it to yourself to look at Oracle SQL Developer Data Modeler (SDDM). Oracle Data Modeler has been around for over five years now and is up to version 4.1. It really is an industrial strength data modeling tool that can be used for any data modeling task you need to tackle.

SQL Developer Data Modeler (SDDM) is a fully functional tool provided for FREE by Oracle. It has many features built in that can be leveraged to capture the design of an existing (probably undocumented) database or you can use it to design a new database, even a data warehouse from scratch. There are a load of great features. This book will show you my favorite features along with detailed step by step instructions (with screen shots) on how to use them.

Tips include:

  • How to easily color code your diagrams
  • How to make hundreds of views really fast
  • How to find missing foreign keys
  • How to find missing unique keys
  • How to connect to a SQL Server database (if you must…)

As a bonus, there are two appendices with my run down on common data modeling mistakes and my famous rant on why you need foreign keys in your data warehouse.

So if you don’t use Oracle Data Modeler yet, read my book to see why you should.

If you do use it, I hope this little book will make you even more productive than you already are!

Model on!

Kent

The Data Warrior

P.S. After you read the book, please leave a review on Amazon to help other folks decide if the book is for them.

Posted in Data Modeling, SQL Developer Data Modeler and tagged , , , , , , , , , , , , , , , , | Leave a comment
31 Oct 2014

Reverse engineer and discover foreign keys with SQL Developer Data Modeler

This is a nice piece of functionality that Oracle added to SQL Developer Data Modeler some time ago. Heli gives a nice step by step guide on how to use it.

HeliFromFinland

You can easily document your database with Data Modeler: just reverse engineer the database with File-> Import -> Data Dictionary. But what if the database has no foreign keys? What’s the point of documenting then? Just tables that has nothing to do with each other….

Well, Data Modeler can help with that too: you can use “Discover Foreign Keys” functionality to find the foreign keys that should be in the database and even generate the DDLs for them if needed…

There are two ways Data Modeler can guess the foreign keys for you: the column has the same name as a primary key column in parent table or using the naming templates. And combinations of those.

Select the relational model from the Browser. Right-click and select “Discover Foreign Keys”. I have two tables: CUSTOMER and ORDER.

Tables

I will ask Data Modeler to find me the foreign keys.

If I select…

View original post 390 more words

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , | Leave a comment
23 Mar 2014
2 Comments

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

Well it was the last day of the 1st annual WWDVC. What an event is has been. (See recap of Day 1 and Day 2, here and here)

The sign outside the meeting room

The sign outside the meeting room

Don’t forget you can see all the action by searching on #WWDVC on twitter.

Agility and Data Vault

Long time data vault advocate Tom Breur opened our closing day with a talk about how we should strive to build the right product and build it right without creating more technical debt in the process.

He said agile is about taking small steps not about being faster. If we do this right, the solution we deliver should generate more legitimate business requirements.

He encouraged us all to read the Theory of Constraints and The Goal (I have), and to learn about lean delivery. Our goal should be to deliver continuously and consistently. The shorter the sprint, the better.

Next he went on to tell us his conclusion that while building a Kimball based solution may appear to deliver more value to the business, it takes too long. And in the end it is a fragile and rigid solution subject to major re-work when requirements change after deployment.

We can deliver value, quicker, using the data vault method. And what we deliver can be done incrementally and more easily added to without re-work.

Tom drew this to show how much quicker we can deliver some value sooner

Tom drew this to show how much quicker we can deliver some value sooner

Data Vault Case Studies

John Sells and Josh Bartells from Data Blueprint shared with us their experiences implementing data vault solutions for their clients.

John and Josh several successful DV projects

John and Josh share several successful DV projects

Why Data Blueprint decided to use DV as their consulting

Why Data Blueprint decided to use DV as their consulting

The gujys from DataBlueprint discuss hurdles and objections they encountered selling DV to clients

The guys from DataBlueprint discuss hurdles and objections they encountered selling DV to clients

It was great to hear about their success stories from the field and see how they addressed the challenges many of us have faced.

Data Vault Modeling Tool

MID GmbH from Germany has a pretty nice modeling tool with built in capabilities to support modeling a data vault solution from stage tables through reporting (including using the new DV icons).

Nice ability to visually show relationships across diagram types

Nice ability to visually show relationships across diagram types

MID's modeler has nice diagram to show all sorts of metadata relationships

MID’s modeler has nice diagram to show all sorts of metadata relationships

This is a tool worth checking out if you are doing a lot of data vault modeling.

Concluding Remarks

Dan closed out this inaugural event with a few remarks, some memories, thanks, and talk about plans for doing this again next year.

Dan closes out the 1st Annual WWDVC and asks about next year - where and when?

Dan closes out the 1st Annual WWDVC and asks about next year – where and when?

Guess I need to be careful what I say! :-)

Guess I need to be careful what I say! 🙂

One last time I have to say I am glad I came and can’t wait to do it again next year!

Snowy St Albans

Snowy St Albans

So long for now from Vermont.

Don’t forget to check out LearnDataVault.com and get ready to join us next year!

Kent

Posted in Data Modeling, Data Vault, Data Warehouse 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 , , , , , ,
04 Dec 2013
4 Comments

Better Data Modeling: The best FREE data modeling tool just got better!

Yes, it true Virginia, the is a Santa Claus!

And this year Santa brought you a new, improved version of the best FREE data modeling tool in the known universe: Oracle SQL Developer Data Modeler 4.0.

The team at Oracle went all out this year and produced three (yes three) pre-release versions to make sure all the fixes and new features were rock solid before they called it production.

That is a lot of testing and work.

But worth the effort – they fixed piles of bugs and added dozens of new features.

Oracle product manager Jeff Smith (@thatjeffsmith) has already published several articles highlighting his favorite new features. Check out what he has to say here then go download the new version and give it try.

Let me know what you your favorite new feature is.

Merry Christmas!

The Oracle Christmas Elf

(Kent)

P.S. Without proven methods and standards, even the best tool will not insure you build the best model, so why not increase your chances by giving yourself the gift of knowledge by picking up a copy of my data model checklist book (on sale for a few more hours).

 

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , ,

Post Navigation

← Older Entries
Newer Entries →
%d bloggers like this: