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.
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
Now let’s run my report!
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!
10 Comments
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.
It’s possible they’re not in there… Can you start a thread on the modeler forums?
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.
I think you may get a ‘send search results to new subview’ feature…would you like that?
Yes that could be a very helpful feature. Especially if coupled with the “and neighbors” function. 🙂
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.
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).
Duh on the ORDER BY – I think I had that in before I added the HAVING clause.
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..!