Oracle SQL Developer Data Modeler

1
(No Transcript)
2
Oracle SQL Developer Data Modeler

• Kris Rice Director of Development

3
Agenda

• Technical and Feature Review
• Focus on Developer Productivity
• Introduction to SQL Developer Data Modeler
• Audience
• Technology
• Review Features
• Update on Packaging

4
Strategic RationaleWhy are we in the Data
Modeling Market?

• Oracle has a strong suite of Database Developer
  Tools
• SQL Developer Data Modeler provides a competitive
  data modeling tool relative to best-of-breed
  niche providers
• Niche vendors have critical mass
• Niche vendors provide full data lifecycle
  development
• Adding data modeling further improves Oracles
  position in database tools by
• Filling current functionality gaps
• Adding specialized domain expertise
• Displacing market leaders
• CA Erwin, MS Visual Studio, MS Visio,
  Embarcadero ER/Studio, Sybase Power Designer,
  IBM Rational Data Architect

5
What is SQL Developer Data Modeler?

• A visual diagramming and data modeling tool
• A single tool for different users and
  functionality
• Data Architect to build logical data models
• Database Developer to model relational models
  (tables and columns)
• DBA to add tablespaces, partitions to the tables

6
Why use Oracle SQL Developer Modeler?

• A single tool with different features and for
  different audiences and users
• A diagram is a powerful communication tool
• Logical Model for architects and users
• Relational model for developers
• Physical model for database administrators
• Viewer for all users
• Extend Oracle product suite
• Improve Application Development
• Will have better quality
• Are easier to maintain
• Have shorter Time to Market

7
Oracle SQL Developer Data Modeler

• Supports a variety of visual models
• Entity Relationship (ERD) - supports Barker and
  Bachman notation (EAR)
• Relational
• DataTypes (SQL99)
• Multi-Dimensional
• Data Flow
• Records details for
• Physical implementation
• Business information
• Domains
• Imports from various sources
• DDL and Dictionary import from Oracle Database
• Oracle Designer repository
• DDL and data dictionary import from Microsoft SQL
  Server, IBM DB2/390 and DB2 LUW
• General JDBC based dictionary import for
  non-Oracle databases
• Ca Erwin Data Modeler
• Exports to various sources
• DDL for Oracle and non-Oracle databases
• Oracle Analytical Workspaces

8
Data Modeler and the Audience

• Analysis (Logical Platform independent)
• Gather data requirements without implementation
  detail
• Create and transform ERD to one or more
  relational designs
• Supports data analysts, enterprise and data
  architects
• Design (Physical)
• Create new or update engineered relational model
• Create one or more physical implementations from
  the relational model
• Generate DDL for implementation
• Supports database administrators
• Read-only Viewer
• Print diagrams and consult model properties
• Supports application developers, business and end
  users

9
Technology

• Technology and Architecture
• Java based
• Independent standalone product
• Database support
• Oracle 9i, 10g, and Oracle Database 11g
• Third-party databases
• Microsoft SQL Server 2000 and 2005
• IBM DB2/390 and DB2 LUW
• Platform support
• Windows
• Linux
• Mac OS X

10
Logical Modeling

• Model entities, attributes and relations
• Support for
• Super type
• Sub types
• Transform one logical to many relational and
  multi-dimensional models
• Support for configurable forward and reverse
  engineering

11
Relational Modeling

• Model tables, columns and FKs
• Create one logical for one or more relational
  models
• Support configurable forward and reverse
  engineering

12
Physical Modeling

• One relational model for many physical models
• Supports
• Oracle Database 9i, 10g and 11g
• Microsoft SQL Server 2000 and 2005
• IBM DB2/390 and DB2 LUW
• DDL file editor supports
• Design Rules
• Object selection
• Drop objects
• Table scripts

13
Forward and Reverse Engineering

• Each logical model to maps to one or more
  relational models
• Each relational model maps to one logical model
• Each relational model maps toone or more
  physical models
• Each physical model maps toone relational model
• Engineering options
• General
• Compare/copy
• Synchronization

14
Modeling DataTypes

• Support for SQL99 (Object Relational Modeling)
• Distinct Types
• (Predefined) Structured Types
• (Predefined) Collection Types
• Used in logical models
• Used in relational models
• Included on import
• Generated in DDL

15
Multi-Dimensional Modeling

• Modeling of Cubes, Dimensions, Levels and
  Hierarchies, Measures and slices
• Start from ROLAP, XMLA or from scratch
• Generate Oracle Analytical Workspaces

16
Working with Data Flow Diagrams

• Data Flow
• External Agents
• Processes
• Primitive
• Composite
• Transformation
• Information Flow
• Information Store

17
Importing

• Oracle Database 9i, 10g and 11g
• Database catalog
• Scripts
• Oracle Designer repository import
• Generic JDBC based Dictionary
• Examples MySQL, Terradata
• SQL Server, DB2 and UDB
• Scripts
• Multi-Dimensional
• Cube views
• XMLA
• Other Modeling tools
• CA ERwin
• Bachman

18
Exporting and Code Generation

• Standard database DDL scripts
• Oracle
• IBM DB2 and UDB
• MicroSoft SQL Server
• Multi-Dimensional Oracle AW, Cube Views and XMLA
• CSV export

19
Controlling the Design Environment

• Selection of tools available
• Domain definition (data types)
• Name abbreviation in the relational model
  (Customer to CUST)
• Compare and merge facilities
• Design Rules
• Tools Options
• Naming standards
• Specify default database
• Controlling constraints
• Physical properties
• Notations (Barker, Bachman)

20
Formatting and General Appearance

• Granular and general control
• Set per item type
• Synchronize treewith diagram
• Set notation

21
Reporting Repository

• Create repository user
• Export design to repository
• Initial export creates repository
• Exports initial version to repository
• SQL Developer support
• Browse repository
• Import reports
• Run shipped reports
• Create your own reports

22
Packaging Choices

• SQL Developer Data Modeler
• Licensed, independent standalone product
• File based
• SQL Developer Data Modeler Viewer
• Free standalone viewer
• SQL Developer Data Modeler Viewer extension
• Integrated into SQL Developer 2.1

23
Finding More Detailwww.oracle.com/technology/prod
ucts/database/datamodeler

• SQL Developer Data Modeler on OTN
• White papers,Oracle by Example (OBE) online
  demos, models and scripts
• www.oracle.com/technology/products/database/datamo
  deler
• SQL Developer Exchange
• Add feature requests http//sqldeveloper.oracle.c
  om
• Forums
• SQL Developer forums.oracle.com/forums/forum.jspa?
  forumID260

24
Summary

• Oracle introduces SQL Developer Data Modeler for
• Logical modeling
• Relational modeling
• Physical modeling
• Forward and reverse engineering
• Data types modeling
• Multi-dimensional modeling
• Data flow diagrams
• Importing and exporting
• Providing control of the design environment
• Exporting design for reporting

25
This presentation is for informational purposes
only and may not be incorporated into a contract
or agreement.