Oracle SQL Developer Data Modeler

Frequently Asked Questions

May 2012

This FAQ is split into two main sections. General questions we get asked and a Technical section based on the feedback the team received from the Oracle SQL Developer Data Modeler Early Adopter release cycles. We would like to thank everyone who took the time and effort to post their feedback.

General Questions

Technical Questions

General Questions

What is Oracle SQL Developer Data Modeler?

SQL Developer Data Modeler:

  • Is a data modeling tool that targets users including enterprise and data architects, business data modelers, data quality engineers,application developers, database developers, database administrators
  • Focuses on data modeling and design
  • Supports
    • Conceptual Entity Relationship Diagrams
    • Relational models (Database tables and columns...)
    • Multi-dimensional models
    • Data Types models (SQL99 structured types for logical and relational models, and implemented in the physical model)
    • Physical models (roles, tablespaces, partitioning, triggers, …)
    • Data Flow diagrams
  • Offers extensive import and export facilities, which include support for CA ERwin Data Modeler, Oracle Designer, DDL, and data dictionary
  • Provides
    • Model compare and merge facilities
    • Selective forward and reverse engineering between logical and relational models
    • Domain support
    • Model design rules (logical, relational and physical models)
    • Name abbreviation and standardization
  • Is available as offline (file based) based modeling
  • Includes a reporting repository for querying and running reports against the logical and relational designs

Is the Data Modeler a part of Oracle SQL Developer?

Yes, the product is also fully integrated in Oracle SQL Developer.

How is the product packaged?

SQL Developer Data Modeler provides two packaging choices:

  • Oracle SQL Developer Data Modeler - An independent, standalone tool
  • Oracle SQL Developer - Full data modeling capability

Is SQL Developer still free?

Oracle SQL Developer Data Modeler is independent of SQL Developer and has no impact on the release cycle or installation of Oracle SQL Developer.

Yes, Oracle SQL Developer is still free.

Is SQL Developer Data Modeler an alternative for Oracle Designer?

Oracle Designer supports Design and Generation of Code for Data (Schema) and Application Development. SQL Developer Data Modeler does not include application design and code generation, instead SQL Developer Data Modeler focuses purely on the conceptual data modeling and database design and DDL script generation. Customers who are only focused on the data design might consider SQL Developer Data Modeler as an alternative to Oracle Designer for some of the following reasons:

  • Oracle Designer runs on Windows, whereas SQL Developer Data Modeler runs on multiple platforms (Windows, Linux, Mac OS X)
  • Oracle Designer focuses primarily on the Oracle Database with no additional support for Oracle 10g and Oracle Database 11g new features. SQL Developer Data Modeler supports SQL Server and DB2.
  • Limited third-party database support
  • Oracle Designer is complex to install and set up
  • Oracle Designer is in maintenance mode
  • The Oracle Designer user interface is considered to be dated

Where do I find more information?

Technical Questions

What are the import and export capabilities of the product?

  • SQL Developer Data Modeler support various import options. Select File > Import menu option
    • Import a DDL script file from Oracle, DB2, UDB and SQL Server.
    • Import directly form the data dictionary from Oracle, IBM DB2 and Microsoft SQL Server.
    • You can also use the generic ODBC/JDBC driver for connections for import
    • For multi-dimensional models (facts, dimensions and levels), import from a Cube Views metadata file or an XMLA file
  • Import from Oracle Designer, CA ERwin Data Modeler 4.X files (saved as XML) and VAR files (former Bachman Information Systems)
  • SQL Developer Data Modeler exports to various formats. Select the File > Export menu option:
    • Export a DDL script file for Oracle, DB2, UDB and SQL Server. There is a DDL file editor wizard to help you in defining the set of objects and different options in generation. A compare/merge functionality allows two models to be compared to create the update Alter statements
    • For multi-dimensional models (facts, dimensions and levels) you can export to a Cube Views metadata file, an XMLA file or Oracle Analytical Workspaces (Oracle MOLAP)
    • Export a model to a CSV file that you can use for import in a relational database for querying.

How do I compare two data models?

SQL Developer Data Modeler has a Model Compare and Merge option. You can use this option to compare two SQL Developer Data Modeler models or to compare a model you just created or imported with an existing model. The Compare/Merge option can be found under the Tools -> Compare/Merge menu option.

Steps to create a new model or create a model by importing a script, from the dictionary or open an existing model

  1. Select the Compare/Merge selection window and select an existing SQL Developer Data Modeler model
  2. Select the FROM relational model you want to compare
  3. Select the TO relational model you want to compare
    If you have a SQL Developer Data Modeler model with an Oracle physical model, you can also compare some physical objects
  4. Select OK
  5. The left side shows the FROM Model, the right hand side the TO Model (This is the design you originally opened or created).
  6. Select the objects you want to be used in the Merge process. The DDL preview gives you a preview on the possible resulting script, which you can save.
  7. As long as you don't select the Merge option you can select or deselect objects to merge and you can preview the DDL

What are subviews and displays?

SUBVIEW

  • A subview is a smaller collection of elements than the main model and invariably represent a cohesive grouped or related tables, or entities.
  • You can create a subview when you are working with a complex logical or relational model - these subviews describe only a part of that model.
  • You can define several subviews for a single model, and you can assign entities or tables and views to more than one subview. Links (relations) between two entities or tables are displayed on the complete model and on these subviews to which both referenced entities have been assigned. There is no difference between performing changes in one of the subviews or in the complete model. Any changes in object properties are immediately reflected in the complete model and any relevant subviews.
  • You can remove entities or tables and views from a subview without deleting them from the complete model.

Creating a Subview

  • Drag and drop entities or tables from the browser tree to the relevant diagram.
  • Using another subview as starting point, select "Create SubView As..." command for subviews in the browser.
  • Using selection of objects. Once you have made your selection, you can click right on the entity or table and select Create Subview. You can select objects:
    • Individually, one by one using the Shift key while clicking on each element
    • Using lasso tool to select a group of elements
    • Using the "Select Neighbors" functionality from the context menu. Right-click an Entity or Table, and choose Select Neighbors from the context menu.
  • When using Import -> Data Dictionary and when you select more than one schema, one global relational diagram will be created and one subview for each individual schema. The same is valid for import from an Oracle Designer repository, where a subview is created for each imported application system or folder.

DISPLAY

  • Displays can be added to each subview or main view. These are useful for representing the same set of objects using different notation or level of details. The number of objects represented on display is synchronized with objects in subview (main view) they belong.

How do I work with relationship lines on a diagram?

  • Set AUTOROUTE is set OFF before you can move lines
    • Right-click in the space on the diagram or go to TOOLS > General options: Diagram and set Auto route OFF
  • When you click right on the relationship there are three choices:
    • Straight Line
    • Add Elbow
    • Remove Elbow.
  • A straight line has a start and end point with no elbow. You can move the line by selecting either start or end point.
  • A line may also have one or more elbows:
    • You can add an elbow by a right-click on the line, use the add elbow menu and an elbow is added at the selected point
    • You can move the line by dragging the elbow point, not the start or end point.
    • You can also move the line by selecting that piece of the relationship between two elbow points or between the end or starting point and an elbow and then dragging the line. The start and end points move automatically with the elbow.
    • You can move elbows on a line, select and drag the elbow.
  • Use the Undo and Redo edit menu options

Is there an Auto Layout function?

Yes, in the relational model there are a few auto-layout alternatives. Right-click for the context menu and select one of the layouts in "Auto Layout" menu.

"Use Synonyms" option is available when there are tables referenced by 10 or more foreign keys. Graphical synonyms are created for child tables and together with the parent table and foreign keys they are arranged as a separate group of tables. As result the global picture is simplified and is easier to understand and maintain.

Is there an UNDO function?

There is an Undo and Redo choice on the edit menu. Not all operations are supported for UNDO/REDO.

How are the models currently stored?

SQL Developer Data Modeler stores each model in a standard file base repository. You can store models locally or on a central file server. The models can be source controlled using Subversion which is integrated in the product.

Can I print a model?

Yes, you can print it on paper or print to a file, using the print menu.

Can I create or run reports?

Yes, save your design to the reporting repository. Select File > Export > To Reporting Schema. Add a new connection. This connection can be a schema you already have access to or a new schema that will own the reporting repository for all your designs. If the repository does not yet exist into the schema, the first time you save your design to the connection, the reporting repository is created. On subsequent occasions, the design is just saved to the same schema.

You can now query the details of the tables in the schema using your favorite tool. Oracle SQL Developer has a set of predefined reports installed.

Invoke Oracle SQL Developer. (You can download this from OTN if you don't have it installed) and select Help -> Check for Updates. Now locate the oracle.sqldeveloper.datamodeler_reports.59.46.zip file in the \datamodeler\reports folder, and complete the wizard. Restart SQL Developer. You should now see a set of reports, with your first Design included. Using SQL Developer you can add your own reports.

What can I import from Oracle Designer?

SQL Developer Data Modeler supports direct import from the Oracle Designer repository. The import from Oracle Designer is wizard driven. You need the SID, hostname and Designer username, such as the repository owner and the password to access the Designer repository.

The Data Modeler only imports unversioned or checked in objects. For an application system (folder) only objects belonging to the last checked-in version are imported. If you run into errors here, verify that the application system and files are checked in. If there are errors you may find that the following is true:

  • objects are added but the application system itself is not checked-in
  • the application system is checked-in, but objects are not versioned (have never been checked-in).

The Data Modeler imports following Designer objects:

  • 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 subview in relational model
  • Each diagram that contains entities is transformed into subview in logical model
  • Each diagram that contains object (structured) types is transformed into subview in Data Types model

The Data Modeler does not import tablespaces, views, stored procedures, packages or dataflow diagrams. These elements are planned for a future release.

What is a domain file and can I create my own domain file?

A domain file is used to define the data types for your attributes or columns. Each domain has a name, a synonym, a logical data type, a size, precision, scale. In addition to these you can set:

  • Check constraints
  • Ranges of valid values
  • List of valid values

SQL Developer Data Modeler does not include a default domains file.
NOTE: This is a change from the early adopter releases which had a predefined domains file. This file still exists in the \datamodeler\domains directory and can be imported to provide a default set of values if required.

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.

Are database packages and functions imported into the physical model?

SQL Developer Data Modeler does not import packages and functions at this stage.

Are XMLTYPE tables supported?

SQL Developer Data Modeler does not import XMLTYPE tables.

How can I control default settings and where can I find them?

In the Tools menu there are various default settings. Tools > General Options controls most general setting, such as file save and open locations. You can also set default model and diagram options and DDL script options.

Can I use two monitors?

Yes.

Where can I find sample models or files?

There are a selection of sample models and files on the SQL Developer Data Modeler site. These are from the standard Oracle sample schemas that are shipped together with the Oracle Database 10g and Oracle Database 11g.

Is there support for non-Oracle databases?

  • SQL Developer Data Modeler supports
    • IBM DB2/390 and IBM DB2 LUW (Linux, Unix and Windows)
    • Microsoft's SQL Server 2000 and 2005
  • Support for these databases is the same as the Oracle database support by providing data dictionary and DDL import.
  • There are JDBC/ODBC catalog import facilities for any JDBC/ODBC compliant database.

Can we import and export other formats?

  • Yes, for multi-dimensional models you can import from Cube Views metadata, an XMLA file or re-engineer an Oracle ROLAP.
  • You can export to Oracle AW and ROLAP, to Cube Views metadata or an XMLA file.

Is there Support for Globalization?

Globalization is supported. Designs are saved in Unicode, UTF-8.

Is there a quick way to generate the DDL for one table?

Yes, Select the table and right-click and select DDL Preview

Are Instead-of Triggers imported?

Yes, you'll find them in the physical model under views.

Can I control the File Open and Save options?

Yes, this is under Tools -> General Options

Can I prefix all the tables in the relational model?

Yes, you can add or change the prefix for the relational models. Use the context menu on the relational model and select the Change Object Names Prefix option.