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!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

10 Comments

  1. Kiril Bojiloff Reply

    No, no such column in the tables with DMRS_ prefix. I have searched for “comment”, “COMMENT”, “%COMMENT%”. There is only a column called DMRS_DESIGNS.VERSION_COMMENTS, but it is on design level.

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

    • Yes that could be a very helpful feature. Especially if coupled with the “and neighbors” function. 🙂

    • Kiril Bojiloff

      In each subview one has the functionality to store comments. Where exactly are they stored in the DB after exporting the models into the reporting schema?

    • If you have a reporting schema setup, you’ll probably be able to find it faster than me – search the schema for the word comment using the Find DB Object feature, that’ll probably turn it up for you.

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

  4. Prasanna Peshkar Reply

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

Write A Comment