I loved Fred Rogers. He helped raise me. Nicest guy ever?
So what does he have to do with Oracle SQL Developer Data Modeler? One word, ‘neighbors.’
I got a good question from a new user yesterday:
Is there a quick way to add all the tables (to a subview) directly related to a specific table?
First, you know what a SubView is, yes? It’s what we call a ‘sub model’ – or a subset of model objects. They allow you to work with smaller versions of your model.
So let’s say you have several hundred tables in your relational model, and you want to create a SubView based on a driving table. Maybe that table is the basis of your STAR schema or it’s a fact table. This is very easy to do in just about 5 clicks.
1. Select Your Driving Table
Click #1 – find your table in the diagram and select it. If you need help finding your table in the diagram itself, you can auto-navigate to it by right-clicking on it in the model tree, and selecting ‘Go to Diagram.’
2. Right Click
Click #2 – use your mouse on the driving table to get to the context menu.
3. Select ‘Select Neighbors’
Clicks #3 & #4 – this does exactly what it sounds like.
Now we have to tell SQL Developer what direction we want to go, and how many levels of relationships to navigate.
So that’s either clicks 4 or 4-5 or 4-6 based on if take the defaults or tweak the options here. I’ll say just #4 🙂
4. Right Click Again on any of the ‘Selected’ Tables
That’s click #5
5. Select ‘Create SubView from selected’
Presto-changeo! We now have our new SubView. And maybe that took more than 5 clicks, but it was really, really close. And if you have lots of objects to copy over, then this will definitively save you some time. So let’s go to the land of Make-Believe and check out our new SubView:
There’s Another Way and Fewer Clicks!
You knew there must be a faster way, right?
Creating the new SubView from the diagram takes a few more clicks, but you get to see visually what’s actually going to be moved. The downside – more clicks and the resulting SubView diagram doesn’t re-position the objects to the upper left hand corner of the diagram – they’ll occupy the same coordinates they do in the ‘master’ model diagram.
If you want truly fewer clicks, trust SQL Developer to select the ‘right’ neighbors, and want the SubView diagram to be put into the ‘top’ corner where it’s easier to get to, then right-mouse on your driving table in the tree.
The other ‘difference’ – neighbors will be determined by actual Foreign Key relationships – not just by model diagram links. If you’re in a data warehouse or another environment sans Foreign Keys, this can make a big difference, so pick your poison carefully.
Don’t Forget to Upgrade Your Version of Oracle SQL Developer Data Modeler!
Version 3.3 has been out for a few weeks now. We just made an update available to Oracle SQL Developer users too. If you’re using version 3.2, just use the Help > Check for Updates to grab the v3.3 extension of the data modeler.
The Animated GIF
TL/DR ?
4 Comments
I just tried to update SQL Developer Data Modeler and got 3.1. I have downloaded SDDM several times to it’s own directory (stand alone). The problem there is finding the bin directory to put my tnsnames.ora file under. I downloaded the stand alone with java because otherwise it never liked any of my java.exe files.
The purpose of this exercise is to create data models of our tables used in PeopleSoft.
In your SQL Developer install, find the sqldeveloper.conf file under the bin subdirectory. Open that file and copy the path to the JDK. If it’s a relative path, figure out the full path, then use that same Java install for the standalone copy of the modeler.
SQLDev should find the TNSNames file w/o having to copy anything over to the bin directory. We should be able to get you up and running in less than 5 minutes. If you want help, please feel free to email me at [email protected]
I like youro blog, it has been helpful. I would really like to utilize the “Create a new Subview with neighbors”; but, I have updated the Data Modeler under SQL Developer and can only get Data Modeler version 3.1. Also, since I have updated I can’t drag neighbors any help. Are there switches I need to flip?
Thanks In Advance
Can you try Help > Check for Updates to grab the latest version of SQL Developer Data Modeler (SDDM), which would be 3.3.
As a temporary workaround, you could get the stand-alone copy of SDDM, which I recommend everyone use if their primary requirement is modeling vs having a query tool that can also generate diagrams.