Working with the SQL Developer Data Modeler Reporting Repository

Open Oracle SQL Developer by expanding the sqldeveloper folder of your installation and double click sqldeveloper.exe.

You need to create a connection. Right-click Connections and select New Connection.

Enter the following and click Test.

Connection Name: <your_sid>_system
Username: system
Password: <your_password>

Select Save Password check box

Hostname: <your_hostname>
SID: <your_sid>

The connection was successful. Click Connect.

Expand <your_sid>_system.

Right-click Other Users and select Create User.

Enter the following and click the Roles tab.

User name: dm1
New Password: dm1
Confirm Password: dm1
Default Tablespace: USERS
Temporary Tablespace: TEMP

Select the Granted, Admin and Default check boxes for DBA and click Apply.

The DM1 user and the grants statements were executed successfully. Click Close.

Open Oracle SQL Developer Data Modeler from the icon on your desktop. If you performed the previous tutorial, and did not exit Oracle SQL Developer Data Modeler, select File > Close to close the model you previously worked on.

Select File >Open.

Select the HR_Schema.dmd file from the sqldev directory and click Open.

The Relational Model will be loaded. Click OK.

The relational model is opened successfully. In the next section, you reverse engineer to create the logical model.

Select File > Export > To Reporting Schema.

You need to create a connection. Click the '+' plus icon.

Enter the following values and click OK.

Name: <your_sid>_dm1
Username: dm1
Password: dm1
Click the Save Password check box
Host: <your_host>
SID: <your_sid>

Select the connection you just created from the list and click OK.

Your model is being export to the reporting schema. When it is complete, the progress window will disappear.

Switch to SQL Developer. You first need to create a connection for the DM1 user. Right-click the orcl_system user and select Properties.

Enter the following and click Connect.

Connection Name:<your_sid>_dm1
Username: dm1
Password: dm1
Check Save Password
Hostname: <your hostname>
SID: <your_sid>

Your connection was created. Click the Reports tab.

Expand Data Modeler Reports.

Expand Design Content > Relational Model. A list of the reports you can run appear. Select Relational Designs.

Select <your_sid>_dm1 from the list of connections and click OK.

You can specify bind values to change the results displayed. In this case you want to take the defaults, click Apply.

A list of the Models appears. Right-click Relational_1 in the Model column and select Reports > Tables.

Review the list of tables. To see a list of the columns within a table, you can drill down to that report. Right-click the JOBS table and select Reports > Columns.

Review the list of columns in the JOBS table. If you click on one of the columns, you see the detail below.

In the next section, you will make a change to the model and export it again to see the changes in the reports.

Switch back to the Data Modeler tool and double-click the JOBS table.

Select Columns.

Select the MIN_SALARY column and click the Remove icon.

Click the Add Column icon.

Column_4 was created. Click the Add Column icon again.

Column_5 was created. Click OK.

Note the changes that you made to the JOBS table.

In order for the changes to appear in the Data modeler reports in SQL Developer, you need to export again. Select File > Export > To Reporting Schema.

Select the orcl_dm1 connection and click OK.

The model was exported successfully. Click OK.

Switch back to SQL Developer and right-click Relational Designs and select Open again.

Right-click the Relational_1 Model and select Reports > Tables.

The list of tables is displayed. Select the JOBS table. Notice the number of Columns in the table details. Right-click the JOBS table and select Reports > Columns.

Review the list of columns. Notice that MIN_SALARY no longer appears and the two columns you added appear.

You can also review a Design Rules report. Under the Report tab in the left navigator, expand Design Rules > Relational Model. Right-click Columns and select Open.

If you have more than one connection, you may receive a dialog asking you which connection you want, select orcl_dm1.

If there is more than one model, you can specify the name. In this case, click Apply.

Select the Relational_1 model. Notice that you see the violations listed in the lower window. In this case, the new columns you created did not have datatypes assigned and that is why they appear.

In the next section, you reverse engineer the Relational Design to create a Logical Data Model and then export it again.

Switch back to the Data Modeler tool and click the Engineer to Logical Modelicon.

You will take the defaults. Click Engineer.

In order for the Logical Data Model objects to appear in the Data modeler reports in SQL Developer, you need to export again. Select File > Export > To Reporting Schema.

Select the <your_sid>_dm1 connection and click OK.

The model was exported successfully. Click OK.

Switch back to SQL Developer and expand Design Content > Logical Model and right-click Logical Designs and select Open.

You can change one of the bind values. Select Most Recent Design (True/False). Notice that the default is True. If you wanted to show all the designs, change the value to False and click Apply.

Notice that all three exports you performed now display. The most recent is listed first. Right-click the HR_Schema Design and select Reports > Entities.

The list of entities appears. Right-click the JOBS table and select Reports > Attributes.

The list of attributes is displayed.

You can also review the Design Rules report for the Logical Model. Under Design Rules, expand Logical Model. right-click Attributes and select Open.

Click Apply.

Select the HR_Schema Design from the list. Notice that the two attributes are listed for Attributes without Datatypes.