Oracle SQL Developer Data Modeler - Feature List

January 2011

Contents
Introduction	Subject Area Management
Logical Models	Impact Analysis
DataTypes Models	Naming Standards
Relational Models	Design Rules
Dimensional Models	Importing
Data Flow Diagrams	Reporting
Physical Models (Database specific)	Integrated Version Support
Domains	Preferences
	Wizards

Introduction

Oracle SQL Developer Data Modeler provides a model driven approach for database design and generation, implemented by integrated set of models – Logical, Data types, Dimensional, Relational, Data Flow diagrams and Physical models for supported Oracle Databases, Microsoft SQL Server 2000 and 2005, IBM DB2/390 and DB2 LUW V7 and V8 representing different aspects or level of details and vendor specifics of the modeled information system.

Below is a high-level list of the features the product supports. To review the new functionality added to SQL Developer Data Modeler 3.0, see the separate New Features document. For more detailed information and product usage, please use the product help or the documentation. There are also online demonstrations and tutorials about many of the features listed.

Logical Models
	ER diagramming with support for Barker and Bachman notation. Partial support for Information Engineering notation.
	Entity hierarchies with three strategies for engineering to relational models
	Box-in-box presentation for hierarchies (supertypes and subtypes)
	Support for mutually exclusive relationships (arcs)
	Volumetric information
	Structured types used in addition to classical ER diagramming
	Engineering to and from relational models (create, compare and synchronize changes)
	Import from Oracle Designer’s repository CA Erwin Data Modeler 4.x models Bachman Enterprise model XMLA scripts Cube Views metadata
	Compare and merge with logical model in another design

DataTypes Models
	Support (create and visualize) for structured, collection and distinct types in SQL99
	Support for inheritance, references and containment of structured types
	Support for specific implementation of the above for Oracle and IBM DB2/UDB
	Definitions used in Logical and Relational model

Relational Models
	Vendor agnostic implementation of concepts in Logical model
	Define sensitive and personally identifiable information
	Define UI defaults at column level
	Volumetric information
	Mutually exclusive (arcs), mandatory (optional) and transferable foreign keys
	Automatic propagation and synchronization of foreign key columns
	Support for Oracle spatial properties
	Visual representation of type substitution on relational diagrams. There is specific implementation for object tables in Oracle and IBM DB2/UDB. Oracle - Defines (restricts) instances of which subtypes can be accommodated by table IBM DB2/UDB - Defines exact hierarchies of typed tables that corresponds to hierarchies of structured types
	Define (restrict) type substitution at column level
	Import DDL scripts or import from database dictionary
	Import from Oracle Designer repository CA Erwin Data Modeler 4.x and CA Erwin Data Modeler 7.x models Bachman DBA design XMLA scripts Cube Views metadata
	Compare and/ or merge to models in other designs, status of database dictionary or status in DDL scripts
	Added support for auto increment and identity columns in the Relational model. This is supported with sequences and triggers in the Oracle physical model.
	Support for dynamic properties – can be created and modified using UI and/or transformation scripts.

Dimensional Models
	Dedicated full featured Dimensional model – star and snowflake schema easily can be built and expressed on detailed and compact diagrams
	Dimensions – support for merging (level can belongs to more than one dimension), shared, fact and role playing dimensions
	Hierarchies – value based hierarchies (parent-child), and regular and ragged level based hierarchies
	Measures – fully, semi and none additive; different aggregation functions on different dimensions ; fact dimension; calculated measures
	Query wizard allows Select statements to be generated from the dimensional model
	Support for Oracle OLAP. This includes specifics like cube partitioning, sparse dimensions, and compressed measures
	Built-in wizards help to define all required object types and view definitions that enable SQL access to dimensional data in Oracle AW (using the OLAP_TABLE interface)
	Bidirectional integration with Oracle physical model - Dimensional model can be created using SQL dimension definitions in physical model or the definitions can be created from the dimensional model

Data Flow Diagrams
	Based on the logical model
	Primitive and composite processes with unlimited levels of decomposition, reusable transformation tasks, triggering events, information stores, external agents
	Simple and component flow; information structures that define transferred data elements (defined in logical model)
	Source – target mapping of data elements processed in primitive processes CRUD dependencies between primitive process and data elements

Domains
	Supports domains with validation rules in form of list of values, range of values and check constraint
	Domains can be managed at installation and design level – sets of domains can be created and used in different designs depending on modeled area
	Easy exchange and synchronization of domains using import of domains functionality
	Domains can be assigned to group of attributes and columns in different models
	Set “Default value” property at domain level. This can be updated at column/attribute level. A List of values defined at domain, column or attribute level is used to pick-up default value.
	Use domains definitions in Data types model.

Subject Area Management
	Subviews can be used to represent objects related to given subject area
	Subviews can be nested(linked) thus allowing to build network (or hierarchy) of related subviews – navigation between linked subviews is supported
	User defined classification types (and related to them color and name prefix) can be used to highlight objects belonging to different subject areas

Impact Analyses
	For Logical and Relational models – unlimited tracking of dependencies of objects, show correspondence (mapping) between tables and entities, columns and attributes, their usage in dimensional model and data flow diagrams, usage inside the model element belongs. This tracking is not limited to first level of dependencies only. For example, the PK column can have dependent foreign key columns and their impact analyses is part of initial dependencies tracking. You can see the usage of the FK column in the definition of the view column, and that column can be used in definition of another view and so on.
	Structured, collection and distinct types – shows their usage in definition of other modeled elements – columns, attributes, tables, entities, structured types and collection types
	Domains – their usage in definition of columns and attributes
	Usage of object in diagrams can be seen using “Go To diagram functionality

Naming Standards
	Glossary for permitted prime, class, modifier and qualifier words
	Name structure for elements in Logical and Relational models
	Model level restrictions for – name length, possible characters, used letter case
	Name translation during engineering between logical and relational models
	Naming templates for table constraints and indexes
	Prefix management

Physical Models
	Initial status of new model can be cloned from existing compatible model

Oracle	Support for following objects – clusters, contexts, dimensions (including levels and hierarchies), directories, disk group, role and user with their privileges and permissions, external tables, materialized views, bitmap join indexes, procedures, packages, functions, triggers and instead of triggers, LOB storage settings, object types and tables, VARRAY and nested tables, partitioned tables, global and local partitioned indexes, subpartition templates, sequences, synonyms, tablespaces (including temporary and undo), rollback segments
	Templates can be defined for storage settings and used for group of objects
	Default templates for tables and indexes can be defined
	Schema controlled generation of DDL
	Generate DDL for database changes
	Generate advanced self controlled DDL script with logging, error masking, and execution window

MS SQL Server	Support for following objects – database, login, user and roles with their privileges and permissions, default, rule, user defined type, computed and identity columns, partitioned tables using partition function and partition scheme, primary and secondary XML indexes, triggers on tables and views, stored procedures, indexes on views, synonyms
	Domains can be created as user defined types
	Extended properties for each object can be defined and generated in DDL
	Database and schema controlled generation of DDL

IBM DB2/390	Support for following objects – alias, aux table, database, buffer pool, owner with privileges and permission settings, volumes, storage groups and related VCATs, triggers, stored procedures, synonyms, sequences, identity columns, index (v7) and table controlled partitioning, tablespaces, summary tables
	Domains can be created as distinct types
	Storage templates can be defined and used for group of objects
	Space calculation – estimates storage capacity needed
	Control files for loading DB2 data (exported) into Oracle can be generated
	Database and schema (owner) controlled generation of DDL

IBM DB2/UDB	Support for following objects – alias, synonym, buffer pool, database, stored procedures, triggers, distinct and structured types, object tables and hierarchy of tables, sequence, schema
	Domains can be created as distinct types
	Schema controlled generation of DDL

Design Rules
	Sets of predefined design rules can be used to validate correctness of information in all models.
	Create user-defined Design Rules.
	Group collections of design rules in Design Rule sets.
	Build libraries of Design Rules.
	Manage custom design rules and transformation scripts through version control.

Importing
	JDBC import from database dictionary and full support in physical models for Oracle, IBM DB2/390, IBM DB2/UDB and MS SQL Server
	Generic JDBC and ODBC/JDBC import
	DDL Scripts for Oracle, IBM DB2/390, IBM DB2/UDB V7 and V8, and Microsoft SQL Server 2000 and 2005
	Dimensional metadata in XMLA and Cube Views files
	Oracle Designer repository
	CA Erwin Data Modeler 4.x models saved as XML

Reporting
	Information in Logical and Relational models (including diagrams in PDF format) and the use of entities and attributes in data flow diagrams can be exported into reporting schema
	Repository reporting supports data types and process models
	Each export creates new versions of the current design
	Predefined design rules and reports can be run using SQL Developer
	Integrated local reports: Run reports from the tool itself. Reports are word xml documents and as such can be opened in MS Word. These reports include entities tables, domains and glossary.

Version Support
	Integrated version control using Subversion for complete design.
	Allows you to compare and merge objects.
	Designs added to versioned folders are placed under version control.
	Users can see pending incoming and outgoing changes.
	Data Modeler recognizes versioned designs.
	Changes made to a versioned design and saved, are revealed in the pending changes dialog.
	Collaborative access and support is provided through tight integration with Subversion.
	Custom rules and transformation scripts are stored in a single file. This file can be managed under version control

Preferences
	Set of preferences to control default properties for models.

Wizards
	Table to View wizard creates views based on tables in a selected relational model. View to Table wizard creates tables based on view definitions a selected relational model. Foreign Key discovery helps find possible foreign keys in a relational model, based on selected criteria.

E-mail this page

Printer View

Oracle SQL Developer Data Modeler - Feature List

Hardware and Software, Engineered to Work Together