Oracle SQL Developer Data Modeler: What Tables Aren’t In At Least One SubView?
Posted
by thatjeffsmith
on Oracle Blogs
See other posts from Oracle Blogs
or by thatjeffsmith
Published on Mon, 1 Jul 2013 17:43:35 +0000
Indexed on
2013/07/01
23:11 UTC
Read the original article
Hit count: 796
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!
© Oracle Blogs or respective owner