Oracle SQL Developer Data Modeler: What Tables Aren’t In At Least One SubView?
- by thatjeffsmith
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
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?
Now let’s run my report!
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!
