What's New with SQL Developer Data Modeler. Heli Helskyaho HrOUG 2015

(1)

What's New with SQL

Developer Data Modeler

Heli Helskyaho

HrOUG 2015

(2)



Graduated from Helsinki University (Master of Science,

computer science)



Worked with Oracle products since 1993, worked for IT

since 1990



Database!



CEO for Kantamestarit Oy since 2000



CEO for Miracle Finland Oy since 10/2010



Oracle ACE Director since 2013 (ACE since 2011)



Author of the book Oracle SQL Developer Data Modeler

for Database Design Mastery (Oracle Press, 2015)

(3)

(4)

Introduction, Heli



_{Been an Oracle Designer user since 1996 and Data}

Modeler user since 2010



_{Been solving performance problems since 1993}



Absolutely convinced that good database

design

and

documentation

will

reduce

this work.



Absolutely convinced that it is

easier

to solve problems

on database that has been

documented

(5)

What is Data Modeler?



A tool for database design (supports all the ”new” Oracle

features)



Logical, relational, physical models, DDLs,…



Multidimensional models (DW), Data Flows etc.



For

designing

and

documenting

the

data architecture

and

the

database



Support for all the phases in database design and easy

moving from one to another, support for reverse

(6)

What is Data Modeler?



_{Import: Oracle repository, existing databases, Oracle}

Designer, file import (Erwin, Bachman), …



_{Export: DDL, CSV, …}



_{Reporting (in tool itself and also Reporting}

Repository)



_{Standards: Design Rules, Preferences, Naming}

Standards, Domains, …



_{Data Modeler viewer integrated in SQL Developer (3.0}

(7)

(8)



_{Journaling tables and other Table DDL Transformation}

Scripts



_{User Defined Properties}



_Search



_Compare



_{Reverse Engineer}

Agenda for this

presentation

(9)



_{Every version brings new Preferences and Properties}

to better enable you to tune the tool for your needs

Preferences and

Properties

(10)



_{How to create journaling tables in DM 4.1?}

(11)



_{dr_custom_scripts.xml}



from datamodeler\datamodeler\types\



to your Default System Types Directory

(12)

Where is Default

(13)

(14)

(15)

(16)

(17)

(18)

(19)

(20)

Select the scripts

and tables

(21)

(22)

(23)



_Anything!



_{For instance you want to generate DDL for a RDBMS}

that is not supported by Data Modeler? Use this

functionality (instead of create)!

What else can I do with

those Table DDL Transformation

Scripts?

(24)

User Defined Properties

(UDPs)

(25)



_Types.xml



from datamodeler\datamodeler\types\



to your Default System Types Directory

(26)

(27)

(28)

(29)

(30)

(31)

(32)



_{For instance any UDP can be used in search as any}

”normal” property...

Why would I like to

use UDPs?

(33)

(34)



_{Search inside an active model}



View | Model Search



Search icon on tool bar



Search the active pane (incl Message – Log)



Edit | Find



_{Search globally on open designs}



View | Global Search



_{Search the Help}



Help | Search

(35)



_{Search inside an active model}



View | Model Search



Search icon on tool bar



_{Search globally on open designs}



View | Global Search

(36)

(37)

(38)

(39)

(40)

(41)

(42)

(43)

(44)

(45)

(46)

(47)

(48)

(49)

(50)

(51)

(52)

(53)

(54)

Search,

(55)

(56)

(57)

(58)

(59)



_{Search, Properties}

Bonus 1: Search,

(60)

(61)

(62)

Bonus 2: Using Excel

(63)

(64)

(65)



_{Better understanding of what you have}



_{Better quality}



_{Alter DDLs}



_...

Why is compare

(66)



_{Design vs Design}



_{Design vs Database}



_{Database vs Design}

(67)



_{File | Import | Data Modeler Design}



Compares ”everything”



_{Tools | Compare/Merge Models}



Compares only relational and physical models



ALTER DDLs can be generated

Different Compares,

Designs

(68)



_{Synchronize Model With Data Dictionary}



Target: model



_{Synchronize Data Dictionary With Model}



Target: database



_{File | Import | DDL File}



_{File | Import | Data Dictionary}

Different Compares,

Database

(69)

Preferences and

Properties

(70)

(71)

(72)



_{File | Import | Data Modeler Design}

Comparing Designs,

Import

(73)

(74)

(75)

(76)

(77)



_{Tools | Compare/Merge Models}



_{Compares the design that is open and a design you}

select to be compared



_{Compares only relational and physical models}

Comparing Designs,

(78)

(79)

(80)

(81)

(82)

(83)

(84)

(85)

(86)

(87)

(88)

(89)

(90)

(91)

(92)

(93)

(94)



_{If you merge and then realize something was not}

correct, you can always close the design without

saving it.

(95)



_{Synchronize Model With Data Dictionary}

(96)

(97)

(98)

Why some of the

(99)



_{Synchronize Data Dictionary With Model}

(100)

(101)

(102)

(103)

I have renamed a

column in design..

(104)

(105)

(106)

(107)

It’s called a Compare

Mapping

(108)

(109)



_{File | Import | DDL File}

(110)

(111)

(112)

(113)

(114)

(115)



_{File | Import | Data Dictionary}

Comparing Data

Dictionary

(116)

(117)

(118)

(119)

(120)

(121)



_{Use the ”compare” we just learned BUT always}

import to an existing model

(122)



_{Table DDL Transformation Scripts}

_{can be used for}

Journaling Tables and many other purposed



_{User Defined Properties}

_{(UDPs) are easy to define}

and can be used for instance on Search and reporting



_Search

_{can be used for reporting, setting common}

properties and for updating your models based on an

Excel sheet

(123)



_{There are different}

_compares



Design vs Design



Design vs Database



Database vs Design

(124)



_{File | Import | Data Modeler Design}



_{Tools | Compare/Merge Models}



_{Synchronize Model With Data Dictionary}



_{Synchronize Data Dictionary With Model}



_{File | Import | DDL File}



_{File | Import | Data Dictionary}

(125)



_{When you import to an exiting model -> compare}



_{When you import to a new model -> Reverse Engineer}



_{Remember the preferences make a difference}

(126)

THANK YOU!

QUESTIONS?

Email: [email protected]

Twitter: @HeliFromFinland