Oracle SQL Developer Data Modeler has a multi-user repository that makes it very easy to control and version changes to your data models and designs. However, it’s stored in a Subversion (think files) repository. Many of our users are accustomed to having EVERYTHING stored in the database.
And when something is in a database, then it can be accessed via SQL.
So how can I leverage all the data in my designs without having to use the client tool? What I really want to do is use SQL. And the answer is:
The SQL Developer Data Modeler Reporting Schema
When you have your model, you’re able to save it to your database. Everything but the Physical Model information will be stored in a reporting schema. Yes, we’re going to be adding support for the Physical Model information at a later date.
- Create design
- Build model
- Save model
- Export Model to Reporting Schema
But wait, I don’t have a reporting schema yet!
No worries, after you select the connection for your reporting schema, SQL Developer will build the repository for you automatically. I recommend you create a separate user JUST for the reporting schema.
Before You Write Any SQL, Check Out the Reports First
We supply a few simple reports for you. You can check out the ‘Data Modeler’ Reports node and try running a few of them.
Now if you want to get an idea of what the data looks like in this new reporting schema, you could of course import from the data dictionary to yet ANOTHER model, or you could get lazy like me and just browse the schema. The table and column names are pretty self-descriptive. And if you get even lazier you can copy the supplied reports down to the user reports, and then use the editor to grab the SQL…
SELECT t.table_name "Table_Name", m.model_name "Model", d.design_name "Design", d.published_by "Published_By", to_char(d.date_published, 'YYYY-MM-DD HH24:MI:SS') "Date_Published", t.ovid "Table_Ovid" FROM dmrs_tables t, dmrs_models m, dmrs_designs d, (SELECT design_name, published_by, MAX(date_published) latest FROM dmrs_designs WHERE (:Design IS NULL OR UPPER(design_name) LIKE UPPER(:Design)) AND (:Published_By IS NULL OR UPPER(published_by) LIKE UPPER(:Published_By)) GROUP BY design_name, published_by) z WHERE t.model_ovid = m.model_ovid AND m.design_ovid = d.design_ovid AND d.design_name = z.design_name AND d.published_by = z.published_by AND (:TABLE_NAME IS NULL OR UPPER(t.table_name) LIKE UPPER(:TABLE_NAME)) AND (:Model IS NULL OR UPPER(m.model_name) LIKE UPPER(:Model)) AND (:Design IS NULL OR UPPER(d.design_name) LIKE UPPER(:Design)) AND (:Published_By IS NULL OR UPPER(d.published_by) LIKE UPPER(:Published_By)) AND ((:Date_Published IS NOT NULL AND to_char(d.date_published,'YYYY-MM-DD HH24:MI:SS') LIKE :Date_Published) OR (:Date_Published IS NULL AND 'TRUE' LIKE UPPER(:Most_Recent_Design) AND d.date_published = z.latest) OR (:Date_Published IS NULL AND 'FALSE' LIKE UPPER(:Most_Recent_Design))) ORDER BY "Design","Date_Published" DESC,"Model","Table_Name"
Or You Can Write Your Own SQL, Of Course
Or you can run reports out of the Data Modeler directly from the Reporting Schema instead from out of the designs. This means anyone could run those reports that had access to the reporting schema vs having the actual designs loaded on their machines.
This is a ‘Read Only’ Type of Repository
Meaning, I can’t use DML to update my models. That still happens via SQL Developer Data Modeler.