The Data Warrior

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

Archive for the tag “#SQLDev”

#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

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.)

SALE: Oracle SQL Developer Data Modeler Jump Start

So in honor of the upcoming #OOW15 and User Group Sunday Symposiums, I decided to put my video workshop for #SQLDevModeler on Sale!

Why use #SQLDevModeler?

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). Data Modeler has been around for 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. (And it works with MS SQL Server and DB2, not just Oracle.)

#SQLDevModeler 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 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. You also have options on the type of notation used in the diagrams (i.e., Barker, IE, etc.).

Workshop? What Workshop?

I’m so glad you asked! 😉

Since there was no one around teaching classes on SDDM, I figured I should build one that would be easily accessible and show you exactly how to use the tool for the most common data modeling tasks.

This workshop 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.

In this workshop, I introduce you to the tool and show you how to use some of the cool features I use almost daily. I provide step-by-step instructions on how to use these features.

In this 4+ hour long workshop you will see:

  • How to create new objects from a blank page
  • How to build a logical ERD (with sub-types!)
  • How to reverse engineer and document existing databases
  • How to reverse engineer logical models from physical
  • How to use the visual view builder
  • How to use the interactive view testing tool
  • How to reconcile a model to the database or the database to a model
  • How to generate DDL for multiple RDBMSs (Not Just Oracle!)
  • How to generate a script to change an existing database

Tips, tricks and features I will demonstrate:

  • 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
  • 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, I also give you a live demonstration of important aspects of the tool.

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

So how about a sample?

The platform that we used to deliver the video is pretty darn good, but I figured you might want to see what the content looks like so here are two modules taken right out of the class. The first one talks about using sub views in SDDM, and the 2nd one talks about creating Entities.

Enjoy!

 Subviews

Entities

Sale Coupon!

So ready to sign up?

Great. Go here to get right in!

Normally the price is $1299  (which is MUCH less than you would have to pay me to come to your office and teach you myself), but for a limited time, I am giving a big discount so that even more folks can afford it.

Use coupon code KentSDDM to get $300 off the regular price.

I am sure you will find this a very useful workshop with examples you can use (yes there are a few downloads when you finish the class). The examples alone will save you a bunch of time and money.

But don’t take my word for it:

So sign up today, start watching, then you can ask me questions at #OOW15!

Cheers.

Kent

The Data Warrior

P.S. The sale will not last long, so be sure to jump in now to save $300 while you can. Go on, sign up here (you know you should).

Tech Tip: Connect to SQL Server Using Oracle SQL Developer (updated)

I spend a lot of time reverse engineering client databases to see what kind of design they are working with or to simply create a data model diagram for them (so they know what they have).

Along the way I often need to actually look at the data as well to do some analysis and profiling.

Often this means looking at data and models in SQL Server as well as Oracle.

What’s an Oracle Data Warrior to do?

Hook up my FREE handy dandy Oracle SQL Developer to the SQL Server database.

How do you do that?

First you need to get the right driver. You can find it here: http://sourceforge.net/projects/jtds/files/jtds/1.2.5/jtds-1.2.5-dist.zip/download

NOTE: For SQL Developer 4.0EA3 and SQL Developer Data Modeler 4.0 (production) you now need jtds-1.3.1. Get it here: http://sourceforge.net/projects/jtds/files/

Then follow these steps:

  1. Download and unzip the file into the main SQL Developer directory (or the directory of your choice).
  2. In SQL Developer go to Tools -> Preferences -> Database -> Third party JDBC Drivers
  3. Click the “add entry” button
  4. Navigate to the jtds-1.2.5.jar file. (or the 1.3.1 file for 4.x installs)
  5. Save and exit preferences.
  6. Close and restart SQL Developer
  7. Open “Add Connection” – there should now be a SQL Server tab.
SQL Developer Preferences

SQL Developer Preferences

With this in place, you can now connect to SQL Server without having to load any other software.

Pretty useful.

Happy Querying!

Kent

P.S. You can connect to other non-Oracle dbs as well. Check out this post by Jeff Smith for even more details.

Additional Notes on SSO errors:

Lots of folks, including me, have had issues getting the native Windows SSO connection to SQL Server to work. Based on answers on the OTN Forum and this post (http://www.oracle-base.com/blog/2013/10/01/sql-developer-4-ea2-connecting-to-sql-server/) I finally got my new 4.x versions to work.

For SQL Developer 4.0EA3, I did as suggested in the article: http://www.oracle-base.com/blog/2013/10/01/sql-developer-4-ea2-connecting-to-sql-server/. I put the ntlmauth.dll where my JDK 1.7 was installed: C:\Program Files\Java\jdk1.7.0_40\jre\bin

For Data Modeler 4.0.13 (production), based on a suggestion from Jeff Smith, I put the dll file here: C:\SQLDeveloper\SQLDeveloper4.0.13\sqldeveloper\sqldeveloper\bin

If I was better at setting windows paths, I am sure there is a better way to do this.

Post Navigation

%d bloggers like this: