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”

#SQLDevModeler Tip: From Domain to Database… A Comment Conundrum

Great tip on creating a custom transformation script in SQL Developer Data Modeler (SDDM) from the awesome David Schleis:

Recently on the Data Modeler Forum, I came across this question:

Is it possible to mirror domain comments, from Domain Administration into attribute “Comments in RDBMS”?  Would like to mirror these to the ddl so they can be then available in column comments in the database.

I knew that one of the example transformation scripts provided with Data Modeler copies the column “Comments” property to “Comments in RDBMS”, so I thought I would point this out to the questioner, and that would be that.  But….

See the rest of the story… From Domain to Database… A Comment Conundrum

Model on!

Kent

The Data Warrior

Advertisements

New Version of SQL Developer Data Modeler is available!

It is now version 17.2!

Don’t worry, you have not missed 13+ releases!

A new versioning number system has been put in place and applies to both SQL Developer and SQL Developer Data Modeler (SDDM). A new version will come out about four times a year and the version number is created using the last two digits of the year and the number of the quarter.

So version 17.2 = Q2 2017

Data Modeler

You can get the newest version here:

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

SQL Developer

There is also a new version (17.2) for this tool as well. Get it here:

http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

Download and have fun!

Kent

The Data Warrior

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.

Post Navigation

%d bloggers like this: