Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Querying Your Oracle SQL Developer Data Modeler Designs

3 Mins Read

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.

So:

  1. Create design
  2. Build model
  3. Save model
  4. Export Model to Reporting Schema
I'm doing this from inside SQL Developer but it's also there in the standalone Modeler application
I’m doing this from inside SQL Developer but it’s also there in the standalone Modeler application

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.

Select your connection (database/user) and go!
Select your connection (database/user) and go!

Voila, reporting schema created!
Voila, reporting schema created!

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.

Supplied Relational Model Reports
Supplied Relational Model Reports

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.

Run your data modeler reports from the reporting schema vs the loaded designs
Run your data modeler reports from the reporting schema vs the loaded designs

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.

Any questions?

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

7 Comments

  1. Peter Reply

    How to query Source Target mapping information used in Transformation tasks in Process model from reporting repository , is it something supported?

  2. Thomas Rahn Reply

    Hi Jeff,

    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?

    Thanks
    Thomas

  3. Mouhamed Elmasry Reply

    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.

    • thatjeffsmith Post Author

      What is a gnaw picture?

      95% of the pictures, including the animated GIFs on here, are created with Techsmith’s SnagIt or Camtasia.

  5. Kent Graziano Reply

    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.

Write A Comment