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.
How to query Source Target mapping information used in Transformation tasks in Process model from reporting repository , is it something supported?
Any chance of working around your concluding statement?
“Meaning, I can’t use DML to update my models. That still happens via SQL Developer Data Modeler.”
I have two specific use cases where I would like to update the model data in the reporting repository and then import the changes into the model in SQL Developer Data Modeler.
1. I have two test versions of a database with several hundred tables. The data models of the databases are not identical. In one model I have added the table classification types of all the tables. I would now like to transfer the classification types to the second model for all tables that occur in both models. I can do this via sql in the repository, but I have not found a way to import the repository changes into the model. We have a dozen more such databases, so the time savings are substantial.
2. Being able to colour in tables according to classification type makes large models much easier to understand. I would also like to colour tables according to responsible party (we have an even larger database with many responsible parties). If I could (mis)use the classification columns in the repository to store responsible party and then import the changes that would be real value added. As a workaround I envisage a procedure that copies the classification types from dmrs_tables to another table and then updates the classification type columns with the reponsible party information and then switches the data back when I want to see classification types.
For future versions this would be high on my list of desired features and I am hoping there might even be a workaround to let me do it now. What do you say?
Hi, We are using sql developer data modeler 3.3, In my design, I have entities , a DFD, flows and processes, when I export to a reporting schema, I can see the entities in the DMRS_ENTITIES in the reporting schema, but not the processes and flows.
Anyone have an idea why and how we can fix this?
Thanks in advance.
So DMRS_FLOWS and DMRS_DATA_FLOW_DIAGRAM_INFOS are empty?
How do you create gnaw pictures ?
What is a gnaw picture?
95% of the pictures, including the animated GIFs on here, are created with Techsmith’s SnagIt or Camtasia.
Another option to learn the repository model is to look at the PDFs in \datamodeler\reports\Reporting Schema diagrams.
Using the reporting schema is one of the tips in my KScope13 talk. Sharing the models via the report tool in the modler is however a new trick.
I have used the reports in SQL Developer, copied and modified them, and even just wrote some in SQL.
I have one report that reproduces the old Designer report that turns relationships in the Logical model into the old Barker-style relationship sentences.