Introduction

Oracle SQL Developer Data Modeler is a free data modeling and design tool used to increase developer productivity by providing the means to develop logical, multi-dimensional, user defined data types, relational and physical data models. The tool provides full forward engineering, including engineering from Logical ERD models to Relational models and the reverse, importing from the Data Dictionary and DDL script generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules.

Version Updates

Enhancements

• Diagrams:
  • export diagrams to database
  • import diagrams from database (including missing objects);
• Identity column:
  • support for related properties in compare/merge functionality
  • alter statement generation for Oracle Database 12c

Platform Support and System Requirements

Oracle SQL Developer Data Modeler runs on the following operating systems:

• Microsoft Windows (Windows 7 including x64, Windows 8)
• Linux
• Mac OS X

The following requirements must be met to install and run correctly:

• JDK 1.8 or higher
• About 250 MB on Hard Disk
• 512 MB at a minimum, 1 GB RAM is recommended

Note:

• We provide the download with and without the JDK. If you download the files without the JDK, you'll be prompted on startup for the path for the JDK. The minimum is JDK 1.8.
• For the Linux install, download the .rpm file. For the Mac install, download the macosx.tar.gz file.

Before You Start

Before Installing

If you have worked with the production release of SQL Developer Data Modeler 2.0, then you can save and reuse the default settings you made in the Tools > General Options menu, such as the Default Design Directory and Formatting Options. After installing SQL Developer Data Modeler 17.4, you can use the Import/Export function in the Tools > Preferences > Data Modeler menu to import the \datamodeler\bin\settings.xml file.

You need to copy types.xml file from datamodeler\datamodeler\types directory to directory set as default system types directory in preferences when preferences are migrated from earlier version.

JDK 1.8 Requirement

• JDK 1.8 is a required Java version.

Installing

• Download and unzip the files into a new and empty directory. Do not unzip over any existing installations, should you have them.
• For downloaded files that do not include a JDK. When prompted, direct the install to your JDK 1.8 directory. If JDK 1.8 is already installed and is the default, you will not be prompted. In this case edit the datamodeler.conf file in the \datamodeler\datamodeler\bin directory and add SetJavaHome. (e.g. SetJavaHome /usr/lib/jdk18)
• 64-Bit Support: SQL Developer Data Modeler is supported on 64-bit platforms, ensure you use the 64-bit executable available in the zip file. If you use the 64-bit Data Modeler executable, you will need to use a 64-bit JDK.

Versioning

Data Modeler needs to be restarted if "Use Versioning functionality" setting in preferences is changed.

Note: Data Modeler 17.4 doesn't maintain SVN properties thus it's not backward compatible with previous versions in regards to versioning functionality. It's recommended to not use Data Modeler 17.4 together with earlier versions against the same SVN repository.

Mozila Rhino

"Mozila Rhino" script engine is not distributed with JVM any more. It's replaced with "Oracle Nashorn" scripting engine. Engine need to be changed in existing scripts. Other JavaScript engines (like "Google V8") also can be used - here is an explanation how to get "Mozila Rhino" engine (js.jar and js-engine.jar) - https://wiki.openjdk.java.net/display/Nashorn/Using+Rhino+JSR-223+engine+with+JDK8.

Predefined RDBMS

Predefined RDBMS sites are no longer distributed in file defaultRDBMSSites.xml and will be removed from existing versions of that file.

Migration of Preferences

Migration of preferences from existing installation and non default location of "System Types Directory" - following files need to be copied at that location:

datamodeler\datamodeler\types\types.xml - changes - added mappings of logical types to MS SQL Server 2012

datamodeler\datamodeler\types\dr_custom_scripts.xml - changes - new custom design rule and "After Create" script for user defined DDL - generation of journal table and supported trigger.

Using Save As

If you want to open models built in SQL Developer Data Modeler 2.0 and 3.0, you must use "Save As" before you start making changes in the SQL Developer Data Modeler 17.4.

SQL Developer Data Modeler 17.3 can open designs created with 3.1.x version and save them in their original location however further maintenance of such design in Data Modeler 3.1.x version is not recommended due possible lost of information.

"Note" object is no longer represented by separate file in the file system - those files will be deleted upon saving of design.

Printing

You can print your diagrams to a number of file formats, including PDF, PNG, JPG. Version 4.0 does not support printing to SVG.

Custom Unicode font for printing and PDF export can be set in datamodeler.conf and datamodeler64.conf files.

Using the Reporting Repository

You can export your designs to a reporting repository. Read the online help or the FAQ for more information. There is a brief Oracle by Example tutorial that walks you through creating and accessing the reporting repository and running the reports. Search for "Data Modeler" in the Oracle Learning Library for all tutorials.

Note: The reporting schema has been updated and so you can use the update scripts (located in the installation directory, \datamodeler\reports) and depending on your version of repository (starting with version 2.0) to apply related scripts to upgrade it to version 17.4. The update script is not required when an existing repository is deleted and a new repository is created.

SQL Developer Data Modeler 17.4 reporting repository is also supported on Oracle Database Express Edition 11g.

Reports

Custom reports cannot be exported to RTF.

Oracle Designer Import

SQL Developer Data Modeler connects to and imports directly from the Oracle Designer repository.

The following is a list of supported imports from Oracle Designer

• Domains, object (structured) types, collection types
• Entities, relationships, arcs, entity inheritance hierarchies
• Tables, views, foreign keys, arcs
• Triggers for tables and views
• Snapshots, clusters, synonyms, sequences
• Each diagram that contains tables is transformed into a subview in the Relational model
• Each diagram that contains entities is transformed into a subview in the Logical model
• Each diagram that contains object (structured) types is transformed into a subview in the Data Types model
• SQL Developer Data Modeler does not import tablespaces, stored procedures, packages, functions and data flow diagrams from the Oracle Designer Repository. (Support for these are planned for a future release) As these structures are supported in the product, users can import these definitions into SQL Developer Data Modeler by connecting directly to the Oracle Data Dictionary.
• User defined extensions are imported as user defined properties

Domains

• SQL Developer Data Modeler does not include a default domains file.
• To create new domain definitions, select Tools > Domains Administration and click Add to create and add your own domains. These domains are added to the default file, defaultdomains.xml and can be used for all your projects.

Comparing Logical Models

To compare two logical models, ensure you have one model open and then select File > Import > Data Modeler Design. You can now select the logical model for the comparison. Once you have made the selection, you are presented with the Compare Merge dialog.

Comparing Relational Models and Generation Alter DDL scripts

• To compare two relational models, ensure you have one relational model open in the design and then select File > Import > Data Dictionary (or DDL). In the import dialog you get the choice to "Swap Target Model". This drives the point of truth and the resulting merge compare dialog. You can elect either to merge the models or to View DDL to see the DDL to update the data dictionary.
• The current model can also be compared to another Data Modeler design using Tools > Compare /Merge Models.

Security

All passwords set in the physical model are encrypted when saving a design in the file system.

Delete Rules

The Tools > Preferences >Data Modeler > Model > Relational >Default Foreign Key Delete Rule defines the delete rule when a new FK is created. If you create a model by importing a DDL script, then ensure the option is set as required before the import.

Physical Models

• Cloning

  When cloning the physical details from one model to another, ensure you have saved the database you want to clone from. Note you can only clone databases of the same release or lower. i.e. to populate a new Oracle Database 11g physical model, you can select a saved Oracle Database 11g or earlier.

• Column Property

  Column property "Default On Null" is moved from physical model for Oracle Database 12c to relational model.

Adding a Design to a Subversion Repository

You can add your design to a Subversion repository using a few different approaches; either from within SQL Developer Data Modeler, or by using a separate client.

• You are advised to add your designs to the Subversion repository from within the Data Modeler.
  • Create a new remote directory in the Data Modeler and check that out.
  • Open your design in the Data Modeler and save it to the checked out directory. Design will be added under version control and svn:ignore property will be set for design's directories thus preventing *.local files to appear into SVN repository.

IMPORTANT: A design should be fully saved before commit, update and merge operations are done. It is recommended to close and reopen the design after update or merge operations are completed, especially when conflicts are resolved. Do not save design during or after update or merge operations.

Creating User Defined Design Rules and Transformations

SQL Developer Data Modeler allows users to build scripts for user defined design rules and transformations. Object properties are described in "datamodeler/datamodeler/xmlmetadata/doc" directory; use index.html.

Memory Settings

• Memory settings are moved to product.conf file and it's location (for Windows 7/ 64-bit and Data Modeler 4.2.0.XXX) is C:\Users\user_name \AppData\Roaming\datamodeler\17.4
• Memory settings in datamodeler.conf file will go into effect if settings in product.conf are removed.

Supported Import formats

From Other Modeling Tools

• Oracle Designer 9i and above
• CA Erwin Data Modeler 4.x and 7.x
• Sterling COOL: DBA V2.1, Sterling Bsnteam V7.2 and Cayenne Bsnteam V7.2 formats
• For Multi-Dimensional Models
  • Cube Views metadata
  • XMLA

Database Imports

Oracle SQL Developer Data Modeler imports from:
- Supported Oracle Databases, Microsoft SQL Server 2012, IBM DB2/LUW V10, IBM DB2 for z/OS v11 and JDBC compliant data dictionary.

IMPORTANT: Oracle does not ship the non-Oracle drivers.To access the non-Oracle databases, you need to download and add the drivers required.

To add the required drivers : Go to Tools > Preferences > Data Modeler > Third Party JDBC drivers

Supported Export formats

• Script file for supported Oracle Databases, Microsoft SQL Server and IBM DB2
• Multi-dimensional support
  • Cube View Metadata
  • XMLA and Oracle ROLAP and MOLAP (AW)
• CSV file

Support

SQL Developer Data Modeler is supported through My Oracle Support for all users with an Oracle Database support license.

Documentation

You can use the documentation in the Oracle SQL Developer Data Modeler Help or access the online documentation.

Finding More Information

Visit Oracle SQL Developer Data Modeler on OTN for more information. Use the Oracle SQL Developer Data Modeler Forum for any discussion or queries.