The Data Warrior

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

Archive for the tag “SQL Developer Data Modeler”

Early Christmas: The New #SQLDev Data Modeler is Here!

Thanks to the gang at Oracle for an early Christmas present – the newest version of Oracle SQL Developer Data Modeler (SDDM) is ready for download and use.

The best FREE data modeling tool on the planet just got better!

To be clear this is Early Adopter (EA) version 2 of SDDM 4.2. You can get it here right now!

#SQLDev Data Modeler New Features

Of course there are some bug fixes from EA1, but also some new features for you to enjoy:

Import from Oracle Database

  •   performance and filtering enhancements
  •   ability to define Oracle Client for thick connections
  •   view and materialized view driving query and columns now parsed and validated

Versioning

  •   improvements in performance
  •   new models are shown as a single node in pending changes window

Reporting

  • PDF reports allow diagrams to be embedded with links from diagram to details part into report
  • HTML report for tables now include diagrams

 

SQL Developer Data Modeler EA2 adds diagrams to HTML reports

#SQLDev Data Modeler HTML report with diagrams embedded

So go download and unwrap that present!

Cheers!

Kent

The Data Warrior

P.S. If you need training on Oracle Data Modeler, be sure to check out my online video training course along with my tips and tricks ebook. (HINT: Buy them now, and you may be able to deduct the cost from your 2016 taxes as an educational expense.)

Better Data Modeling: Discovering Foreign Keys (FK) in #SQLDevModeler (SDDM)

A while back I had an interesting situation when I was attempting to reverse engineer and document a database for a client.

I had a 3rd party database that had PKs defined on every table but no FKs in the database. The question I posed (on the Data Modeler Forum) was:

How do I get the FK Discover utility to find FK columns with this type of pattern:

Parent PK column = TABCUSTNUM

Child FK column = ABCCUSTNUM

So the root column name (CUSTNUM) is standard but in every table the column name has a different 3 character “prefix” that is effectively the table short name. Is there way to get the utility to ignore the first three characters of the column names?

This was in SDDM 4.1.873.

No easy answer.

Well, the ever helpful Philip was very kind and wrote me a slick custom Transformation Script that did the trick! (Check the post if you want to see the code.)

But wait there’s more!

In his response he mentioned a feature coming in 4.1.888 – the ability to include a table prefix as part of a FK column naming template (just like this app had done).

Cool, I thought, but how does that help?

Well with the template in place it turns out that you can have the FK Discovery utility search based on the Naming Template model rather than just look for exact matching column names.

Using the Custom Naming Template

So recently (today in fact) I was trying to add FKs to the Snowflake DB model I reverse engineered a few weeks back (Jeff pointed out they were missing). I noticed the model had that pattern of a prefix on both the FK and PK column names.

In the CUSTOMER table the PK is C_CUSTKEY. In the ORDER table it is O_CUSTKEY. Nice simple pattern (see the diagram below for more). That reminded me of the previous issue and Philip’s script.

Snowflake Schema

Off to OTN to find that discussion and refresh my memory.

In the post, Philip has posted an example of a template that fit my previous problem:

{table abbr}SUBSTR(4,30,FRONT,{ref column})

With the note that {table abbr} would be the equivalent of what I called the table prefix. So first I went to the table properties and put in the prefixes using the Abbreviation property:

Add Table Abbrev

Then all I had to do was modify his example to account for the underscore and the fact that the main column text would start at character #3 instead of #4:

{table abbr}_SUBSTR(3,30,FRONT,{ref column})

I input that by going to Properties -> Settings -> Naming Standards -> Templates and then editing the default template:

Set up FK template

Discover FKs!

Now it was just a matter of running the utility. You find that with a right mouse click on the Relational Design node:

Discover FK tool

Next you get the list of candidate FKs:

Create FKs

Note that the utility also suggested some FKs based on the unique constraints (UKs) as well. I did not want those, so I unchecked them before I hit “OK”.

The result was getting all the FKs I wanted added into my model! Viola!

Snowflake with RI

So I can happily report that Philip’s little enhancement works just fine in 4.1.3. WooHoo! I can see this being very useful for a lots of cases in the future.

In a future post (early next year), I will continue with showing how we implemented Referential Integrity constraints in Snowflake DB and if I can generate the DDL from #SQLDevModeler.

Happy New Year Y’all

Kent

The Data Warrior & Snowflake Technical Evangelist

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.

End Your Data Modeling Pain

Tired of hand drawn data models, or models drawn in Visio or even PowerPoint?

Still limping along on an old Oracle Designer repository?

Can’t afford the license for your current modeling tool anymore?

Or maybe your shop has no documented data model diagrams at all (horrors!).

Well, 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 (and your organization) to look at Oracle SQL Developer Data Modeler (SDDM). 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.

As I have mentioned before (many times) in this blog, SQL Developer Data Modeler (SDDM) is a fully functional modeling 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 and re-engineer it or you can use it to design a new database, even a data warehouse from scratch. There are features to apply standards, and then generate DDL to implement your newly standardized design. You can even use one logical model to develop multiple physical models that can be deployed to different RDBMS (like Oracle, SQL Server, and DB2). You even have options on the type of notation used in the diagrams (i.e., Barker, IE, etc.).

Unfortunately there are no comprehensive, end-to-end classes that can teach you how to really get started with SDDM and can point you to some of the awesome features and show you how they work.

Not until NOW!

Announcing my new online class: Introduction to Oracle SQL Developer Data Modeler.

This course will start from a totally blank page and walk you through all the major features and options of the tool to show you how to design build and deploy a database. I provide lots of examples and tips on how to do all this so that you can quickly be productive (while you watch!).

In this class, I will introduce you to the tool and show you how to use some of the cool features I use almost daily. I will provide step-by-step instructions on how to use these features. The class includes both lectures with screen shots and hands on demos of the tool (recorded live by me). Hint: some of my best tips are in the demos.

In this course you will see:

  • How to create new objects (entities, attributes, relationships) from a blank page
  • How to reverse engineer and document existing databases
  • How to reverse engineer logical models from physical to get a clean ERD
  • How to reconcile an existing data model to the database or the database to a model
  • How to generate a script to change an existing database

Tips, tricks and features I demonstrate include:

  • Modifying the delivered reporting templates
  • How (and when) to use the abbreviations utility
  • How to use and apply domains
  • How to create and applying object naming templates
  • How to add audit columns to every table in a model
  • How to add custom design rules for model quality checks
  • How to use the built in quality checks
  • How to use the newest find and extract feature to enable updating table and column comments by end users
  • How to generate a simple data dictionary

As an added bonus, in addition to step by step slides, and the demos, I have included a zip file with example output, templates, and DDL scripts that I created during the demos. You will be able to download these to your machine to try the exercises yourself.

If you have questions along the way, our modern learning management system (LMS) lets you enter your questions in the course comments whenever you want. I will get an immediate email notification so I can promptly answer your questions.

Unlike typical user group presentations, where I can only scratch the surface or show a few tricks, this course contains over four and a half hours of content broken up into bite-sized modules so you can easily digest the detailed information. If it does not make sense, you can simply play the same segment over again and even follow along on using your own install of SDDM.

By the end of the course you will be able to effectively use Oracle SQL Developer Data Modeler for all your data modeling tasks.

So head over to Learn Data Vault and check out the preview video of the class, then sign up and learn. Use my special offer code of GRAZIANO10S to get 10% off.

See you online!

Kent

The Data Warrior

P.S. The current price of the course is the initial sale price. I am not sure when the price will go up but Dan and Sanjay tell me that I set the price too low and they will have to raise it in the near future. So, I would sign up now, before that price goes up.

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

Post Navigation

%d bloggers like this: