Oracle SQL Developer Data Modeler: What Tables Aren’t In At Least One SubView?

thatjeffsmith SQL Developer 6 Comments

Tell Others About This Story:

Organizing your data model makes the information easier to consume. One of the organizational tools provided by Oracle SQL Developer Data Modeler is the ‘SubView.’ In a nutshell, a SubView is a subset of your model.

The Challenge:
I’ve just created a model which represents my entire ____________ application. We’ll call it ‘residential lending.’ Instead of having all 100+ tables in a single model diagram, I want to break out the tables by module, e.g. appraisals, credit reports, work histories, customers, etc.

I’ve spent several hours breaking out the tables to one or more SubViews, but I think i may have missed a few.

Is there an easy way to see what tables aren’t in at least ONE subview?

The Answer

Yes, mostly.

The mostly comes about from the way I’m going to accomplish this task. It involves querying the SQL Developer Data Modeler Reporting Schema.

So if you don’t have the Reporting Schema setup, you’ll need to do so.

Got it?

Good, let’s proceed.

Before you start querying your Reporting Schema, you might need a data model for the actual reporting schema…meta-meta data! You could reverse engineer the data modeler reporting schema to a new data model, or you could just reference the PDFs in \datamodeler\reports\Reporting Schema diagrams directory.

Here's a hint, it's THIS one :)

Here’s a hint, it’s THIS one 🙂

The Query

Well, it’s actually going to be at least 2 queries. We need to get a list of distinct designs stored in your repository. For giggles, I’m going to get a listing including each version of the model. So I can query based on design and version, or in this case, timestamp of when it was added to the repository.

We’ll get that from the DMRS_DESIGNS table:

SELECT DISTINCT design_name, design_ovid, date_published
FROM DMRS_designs

Then I’m going to feed the design_ovid, down to a subquery for my child report.

SELECT name, COUNT(DISTINCT diagram_id) FROM DMRS_DIAGRAM_ELEMENTS
WHERE design_ovid = :dESIGN_OVID AND TYPE = 'Table'
GROUP BY name
HAVING COUNT(DISTINCT diagram_id) < 2
ORDER BY COUNT(DISTINCT diagram_id) DESC

Each diagram element has an entry in this table, so I need to filter on type=’Table.’ Each design has AT LEAST one diagram, the master diagram. So any relational table in this table, only having one listing means it’s not in any SubViews.

If you have overloaded object names, which is VERY possible, you’ll want to do the report off of ‘OBJECT_ID’, but then you’ll need to correlate that to the NAME, as I doubt you’re so intimate with your designs that you recognize the GUIDs 🙂

So I’m going to cheat and just stick with names, but I think you get the gist.

My Model

Of my almost 90 tables, how many of those have I not added to at least one SubView?

Of my almost 90 tables, how many of those have I not added to at least one SubView?

Now let’s run my report!

Voila! My 'BEER2' table isn't in any SubView!

Voila! My ‘BEER2’ table isn’t in any SubView!

It says ‘1’ because the main model diagram counts as a view. So if the count came back as ‘2’, that would mean the table was in the main model diagram and in 1 SubView diagram.

And I know what you’re thinking, what kind of residential lending program would have a table called ‘BEER2?’ Let’s just say, that my business model has some kinks to work out!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 6

  1. Found a way to do this now with the enhanced Find tool. Using the Advanced mode and selecting Tables there is a property called usedInSubviews. Check the box in the dialog to select it but leave the search field blank. Press Find.

    The result is all the tables that have a sub view name = blank (i.e., not used in a sub view). Not completely intuitive but it works and is fast.

    Now I can export the results (in my case 79 new tables) to Excel and use that as a list to find them in the navigator and add them into a sub view.

    1. thatjeffsmith Post
      Author
  2. Just tried this out. Works great, I made one change – I changed the order by to use “name” instead of the count(distinct diagram_id) since the count is always 1 (having count < 2).

    1. thatjeffsmith Post
      Author
  3. Sir,
    Excellent post..! Your posts have been very helpful in getting familiar with SQL Developer and I appreciate your efforts to make SQL developer easily understandable. It’s a great residential lending program..!

Leave a Reply

Your email address will not be published. Required fields are marked *