Title: we are looking for documentation that can help us understand the data model and data structures in the r8 Model Manager mart database for SQL querying.
Description:
How can we access the Model Manager database directly via SQL for a homegrown application? We would like to directly access the Model Manager database via a Java program. We are looking for documentation that can help us understand the data model and data structures in the Model Manager database. Are there any knowledgebase articles to help us?
Solution:
- Please see the attached spreadsheet (scroll down to the bottom) listing some SQL queries that you can use to help figure out how to construct your Model Manager queries.
- To view / access the SQL behind any mart report in r8, you can do the following:
Launch erwin and connect to your mart
Go to Tools | Mart Reports and run the Mart report that you want
That will come up in the erwin Report Viewer
Click the menu Edit | Edit Design.
Crystal Reports will launch with the design of the report present in it.
Now, go to the menu Database | Database Expert, Right click Command, and choose Edit Command.
Now you can view and access the SQL that is behind the report. You can even modify the SQL and save that as a new report, if you like.
- In brief, here is the Architecture of the r7.x / r8.x mart:
Almost 80% - 90% of the data is stored in the m7object and m7objectproperty tables
One holds the objects, the other the many property rows for each type of object
Most strings are stored in m7objectproperty
There are various property types and those are stored in the m7property table
There are classes of objects, and those are stored in the m7class table
The m7library table houses the model names, the model versions, and more
You will probably need to use joins on m7class and m7property in most of your queries.
Please see the example queries for the sample logic.
- Try Reverse Engineering the mart db using erwin to form a model for your Team to view to help understand your joins.
- Another idea is to create a new mart on a second db (no additional mart license is necessary so long as the same users access the second mart) and populate it with a very simple one entity model. Then add a new object / property to that model, save changes to the mart, and query that database.
- Please note that erwin builds many inherited values in a model using memory. Inherited values that flow down relationship chains or from domains or from logical to physical are most likely not stored in a mart. Model Manager will not store a physical name or object unless the inheritance has been manually broken by a modeler, and a unique value typed in by hand. The reason for that is the Model Manager does not store the complete model diagram as it is presented in erwin. It stores only the information required to construct the complete model diagram. Therefore it is not possible to query the Model Manager on most physical or other inherited data unless the inherited data is manually changed or the modeler hardens the physical data beforehand.
More Information:
-
Article ID: 3410 Security Report query for r8 and r7 marts. Produces a report of all the mart users and what levels of security they have been granted in a mart. That Security Report query may also be used to help compare an r8 / r7 mart set up with the upgraded users and profiles in your new r9 mart.
-
Article ID: 3893 We are looking for documentation that can help us understand the data model and data structures in the r9.x mart database for SQL querying.
-