Oracle SQL Developer Data Modeler 3.0 (3.0.0.665)

Release Notes

January 2011

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. The latest release, SQL Developer Data Modeler 3.0 includes the ability to create user defined design rules and transformation scripts. User can also manage their files through integrated support for Subversion, an open-source version control tool. For more detail on the latest features and to learn how to use them see the SQL Developer Data Modeler home page on the Oracle Technology Network.

Platform Support and System Requirements

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

- Microsoft Windows (XP, Vista, Windows 7 including x64)
- Linux
- Mac OS X

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

- JavaTM 1.6 or higher Standard Edition Runtime Environment
- About 125 MB on Hard Disk
- 512 MB at a minimum, 1 GB RAM is recommended

Note:
- We provide the download with and without the Java Runtime. If you download the files without the Java Runtime, you'll be prompted on startup for the path for the JRE. The minimum is JRE 1.6.
- 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 3.0, you can use the Import/Export function in the Tools > Preferences > Data Modeler menu to import the \datamodeler\bin\settings.xml file.

Installing

- Download and unzip the files into a new and empty directory. Do not unzip over any existing installations, should you have them.
- For Linux and Mac OS X installations, the downloaded files do not include a JRE. When prompted, direct the install to your JRE 1.6 directory. If JRE 1.5 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/jdk16_04)
- 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.

Using Save As

If you want to open models built in SQL Developer Data Modeler 2.0, you must use "Save As" before you start making changes in the SQL Developer Data Modeler 3.0. Please note: Early adopter releases are not supported, so if you have been using the early adopter releases, you are advised to update the original models created in SQL Developer Data Modeler 2.0 release.

Printing

You can print your diagrams to a number of file formats, including PDF, PNG, JPG, SVG.

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 script (see the installation directory, \datamodeler\reports) that will apply changes to an existing repository created with Data Modeler 2.0. The update script is not required when an existing repository is deleted and a new repository created.

SQL Developer Data Modeler 3.0 introduces integrated reporting. See New Features for more.

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

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.

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.
- To create a named domains file, select Tools > Domains Administration and click the Select button, next to the Domains File field. Type in a new file name and click open. Now you can add all the new domains to this file as described. In order to use the new file you need to import these domains into your current design, otherwise the default domains file is used.To import a domains file. select File > Import > Domains. You can accept all the domains or select those you want to import individually.

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.

Cloning Physical Models

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.

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.

  • If you save the full design to the Subversion repository using another tool, then when you open SQL Developer Data Modeler, check out the design and commit the design immediately. This commit action sets the repository property against the design within the product.
  • 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. This action sets the repository property against the design within the product and your design is now under version control.

For more information on using Subversion with SQL Developer Data Modeler see the Documentation and the online demonstrations available.

Creating User Defined Design Rules and Transformations

SQL Developer Data Modeler 3.0 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.

The product provides a few samples as illustrations of user defined design rules and transformations. For more information see the Documentation and the online demonstrations available.

New Features

There is a list of new features available for review. Watch the new feature demonstrations available online.

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 2000 or 2005, IBM DB2/LUW V7 or V8, IBM DB2 for OS/390 and z/OS script file
- Supported Oracle Databases, Microsoft SQL Server2000 or 2005, IBM DB2/LUW V7 or V8, IBM DB2 for OS/390 and z/OS and ODBC/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.

The drivers can be downloaded from the following links:

- For Microsoft SQL Server:
   SQL Server 2000 For SQL Server 2000 you must make sure you have installed all three .jar files: msbase.jar, mssqlserver.jar and msutil.jar
   SQL Server 2005 The drivers for SQL Server 2000 can be used for import from SQL Server 2005 database.
- For DB2/LUW:
   IBM Data Server Driver for JDBC and SQLJ The driver for ODBC/JDBC is included in Java, so there is nothing extra to install

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

After installing, the drivers must be set in the Preferences dialog as follows:

- SQL Server 2000 - msbase.jar, mssqlserver.jar, msutil.jar
- SQL Server 2005 - sqljdbc.jar
- DB2/UDB            - db2jcc.jar

Supported Export formats

- Supported Oracle Databases
- Microsoft SQL Server 2000 or 2005
- IBM DB2 V7 and V8 script file
- Multi-dimensional support
    Cube View Metadata
    XMLA and Oracle ROLAP and MOLAP (AW)
- CSV file

Known Issues

We are aware of the following limitations and issues. This list is not exhaustive, but a list of issues that you may encounter.

  • DDL Generation
    • 9132628: DDL generation sequence for table column associated with scope table is incorrect for DB2
  • Diagrams
    • 9978648: Logical diagrammer does not handle 'select all' drag and drop of objects well
    • 9707703: New entities are not aligned with the grid in a diagram
    • 8771771: Problems with ui on mac
  • Domains
    • 10367220: Domains administration: changed properties not stored unless you click "apply" before selecting next element
  • Engineering
    • 9236128: Don't see attributes of subtypes in engineer window anywhere
    • 9192760: Replacing the word separator for relational model with *(star) when reverse engineering not possible
  • Importing
    • 8947581: Error on export to Oracle AW options
    • 8947295: Export model as Microsoft XMLA and import multidimensional model missing
    • 10375217: Export to reporting schema: Create database connection fails
    • 10181913: Generate in DDL not used in compare/merge functionality
    • 11684911: Change in named not null constraint is not detected during merge for existing tables
  • General
    • 10209593: Cannot create PK for nested column - when column is based on object type
    • 10336683: Unique constraint generate and engineer checkboxes - "auto" uncheck?
    • 10216769: Linux: Unable to delete more than one entity from the browser
    • 11665948: Inconsistent initial selection in data dictionary import wizard
  • Logical Models
    • 9170609: Change default preference for relationship to mandatory for target
    • 10247284: FK attribute doesn't follow changes in name of PK attribute or when new is added
    • 9203139: Need undo feature for diagram editors
    • 9482914: Self referencing relations (pigs ear) can not be rearranged on logical diagram
  • Physical Models
    • 9166084: Clone from operation works only on saved status of source model
    • 8845591: Compare/merge updates column's physical properties even if column is unselected
    • 9953350: Adding "v1" suffix to foreign key names in physical model
    • 9953212: Missing column level permissions for Oracle tables
    • 9808343: Permissions dialogs for Oracle functions and packages
  • Relational Models
    • 8839456: Can't browse nested columns
    • 9266599: Change pii to personally identifiable information in column security property
    • 8975504: Check required for 'insert join clause for foreign key' button in view builder
    • 9305814: Compare relational models in one design
    • 11682224: Copy and paste table doesn't work properly after import from database dictionary or DDL script. Workaround: Save and reopen the design.
  • Versioning
    • 10040745: Version history: Compare can be slow to open window

Bugs Fixed

To see a list of bugs fixed and enhancement requests implemented in SQL Developer Data Modeler 3.0, please follow see Bugs Fixed. The list is not exhaustive, but does include a number of issues reported by users. SQL Developer Data Modeler is supported through My Oracle Support for all users with an Oracle Database support license. Your feedback is also welcomed on the Data Modeler OTN forum.

Documentation

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

Finding More Information

There is a dedicated Oracle SQL Developer Data Modeler page. You can also reach this page from the SQL Developer OTN homepage.
Use the Oracle SQL Developer Data Modeler forum on OTN for any discussion or queries.