9 use cases for Oracle SQL Developer Data Modeler

Data Modeler is known to be an amazing tool for database design. But are there any other use cases for the tool? In this presentation Heli will explain how to use the tool for database design and 8 other use cases.

Transcript

1. 9 use cases for Oracle SQL Developer Data Modeler Heli Helskyaho, Miracle Finland Oy Riga DevDay 22.1.2015 © Miracle Finland Oy
2. Introduction, Heli • 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 • Board member for OUGF since 2001 • Chairperson for OUGF since 2007 (vice-chair for years before that) • Ambassador/Spokesperson for EOUC since late 2007 • Oracle ACE Director since 2013 (ACE since 2011) © Miracle Finland Oy
3. Copyright © Miracle Finland Oy
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 © Miracle Finland Oy
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 engineering • Support for version control and multiuser environment: XML files and Subversion © Miracle Finland Oy
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 integrated in SQL Developer (3.0 - >), also reports © Miracle Finland Oy
7. © Miracle Finland Oy
8. 9 use cases for Data Modeler 1) Database design (ER, DFD) 2) Documenting existing databases without documentation 3) Documenting existing databases with documentation 4) Data architecture 5) Standardizing (domains, preferences, glossaries,...) 6) Improving quality (design rules, versioning, compare models...) 7) Efficency, cost effectivity (notations, transformations, compares,...) 8) Tuning SQL 9) Reporting © Miracle Finland Oy
9. 1. Database design • ER • DFD © Miracle Finland Oy
10. Logical model © Miracle Finland Oy
11. © Miracle Finland Oy
12. Entity © Miracle Finland Oy
13. © Miracle Finland Oy
14. © Miracle Finland Oy
15. Relationship © Miracle Finland Oy
16. © Miracle Finland Oy
17. © Miracle Finland Oy
18. From logical to relational © Miracle Finland Oy
19. © Miracle Finland Oy
20. © Miracle Finland Oy
21. © Miracle Finland Oy
22. Relational model © Miracle Finland Oy
23. © Miracle Finland Oy
24. Table © Miracle Finland Oy
25. © Miracle Finland Oy
26. © Miracle Finland Oy
27. © Miracle Finland Oy
28. © Miracle Finland Oy
29. © Miracle Finland Oy
30. © Miracle Finland Oy
31. Column © Miracle Finland Oy
32. © Miracle Finland Oy
33. © Miracle Finland Oy
34. Primary Key © Miracle Finland Oy
35. © Miracle Finland Oy
36. Foreign Key © Miracle Finland Oy
37. © Miracle Finland Oy
38. From relational to physical © Miracle Finland Oy
39. © Miracle Finland Oy
40. © Miracle Finland Oy
41. Physical model © Miracle Finland Oy
42. © Miracle Finland Oy
43. Properties © Miracle Finland Oy
44. © Miracle Finland Oy
45. © Miracle Finland Oy
46. © Miracle Finland Oy
47. © Miracle Finland Oy
48. © Miracle Finland Oy
49. © Miracle Finland Oy
50. © Miracle Finland Oy
51. Physical to DDLs © Miracle Finland Oy
52. © Miracle Finland Oy
53. © Miracle Finland Oy
54. © Miracle Finland Oy
55. © Miracle Finland Oy
56. © Miracle Finland Oy
57. Data Flow Diagram © Miracle Finland Oy
58. 2. Documenting Existing Database without Documentation • From database © Miracle Finland Oy
59. Data Dictionary © Miracle Finland Oy
60. © Miracle Finland Oy
61. © Miracle Finland Oy
62. © Miracle Finland Oy
63. DDL © Miracle Finland Oy
64. © Miracle Finland Oy
65. © Miracle Finland Oy
66. © Miracle Finland Oy
67. © Miracle Finland Oy
68. © Miracle Finland Oy
69. © Miracle Finland Oy
70. • Merge -> commit to model • Close -> ignore (I did not want to import it) • Let’s import to an empty design © Miracle Finland Oy
71. © Miracle Finland Oy
72. Result: • No Logical Model • One Relational Model • But we also want to have a logical model... © Miracle Finland Oy
73. © Miracle Finland Oy
74. © Miracle Finland Oy
75. © Miracle Finland Oy
76. 3. Documenting Existing Database with documentation • From another tool © Miracle Finland Oy
77. © Miracle Finland Oy
78. © Miracle Finland Oy
79. © Miracle Finland Oy
80. Note • You can also combine the documentation from another tool with the documentation you have in a database to get a better documentation of the database in Data Modeler © Miracle Finland Oy
81. 4. Data Architecture • To identify and recognize data, data structures and their relationships • Masterdata, transactional data • -> general picture of the data in our organization, vocabulary/glossary © Miracle Finland Oy
82. General ER Diagram © Miracle Finland Oy
83. Glossary based on Logical model © Miracle Finland Oy
84. © Miracle Finland Oy
85. Data Flow Diagram © Miracle Finland Oy
86. 5. Standardizing • Domains • Preferences • Glossaries • ... © Miracle Finland Oy
87. Domains © Miracle Finland Oy
88. © Miracle Finland Oy
89. © Miracle Finland Oy
90. © Miracle Finland Oy
91. © Miracle Finland Oy
92. Preferences © Miracle Finland Oy
93. © Miracle Finland Oy
94. © Miracle Finland Oy
95. © Miracle Finland Oy
96. © Miracle Finland Oy
97. © Miracle Finland Oy
98. © Miracle Finland Oy
99. Design properties © Miracle Finland Oy
100. © Miracle Finland Oy
101. © Miracle Finland Oy
102. Glossaries © Miracle Finland Oy
103. © Miracle Finland Oy
104. © Miracle Finland Oy
105. 6. Improving Quality • Design rules • Versioning • Compares • (Preferences • Glossaries • ...) © Miracle Finland Oy
106. Design Rules © Miracle Finland Oy
107. © Miracle Finland Oy
108. © Miracle Finland Oy
109. Custom Design Rules © Miracle Finland Oy
110. Rule sets © Miracle Finland Oy
111. Version control (Subversion) © Miracle Finland Oy
112. © Miracle Finland Oy
113. © Miracle Finland Oy
114. © Miracle Finland Oy
115. © Miracle Finland Oy
116. © Miracle Finland Oy
117. Compare • Two models • Model vs Database • Database vs Model © Miracle Finland Oy
118. Compare/Merge Models © Miracle Finland Oy
119. Synchronize © Miracle Finland Oy
120. 7. Efficency, cost effectivity • Notations • Transformations • Compares • ... © Miracle Finland Oy
121. Notation © Miracle Finland Oy
122. Transformation © Miracle Finland Oy
123. © Miracle Finland Oy
124. 8. Tuning SQL © Miracle Finland Oy
125. 9. Reporting ..for many purposes.. – Yourself – End users (analysis) – Reviews – Your colleague – ... © Miracle Finland Oy
126. © Miracle Finland Oy
127. © Miracle Finland Oy
128. © Miracle Finland Oy
129. © Miracle Finland Oy
130. © Miracle Finland Oy
131. Search © Miracle Finland Oy
132. Search © Miracle Finland Oy
133. © Miracle Finland Oy
134. Conclusions • Data Modeler is a good tool; good support for iterative processes • Enables documenting and versioning (and comparing the versions) • Enables multiuser environment • Is free to use • Support for other databases as well • Many appliances for this tool • I see no reason not to use it! © Miracle Finland Oy
135. OTN • http://www.oracle.com/technetwork/develop er-tools/sql-developer/learnmore/index.html • http://www.oracle.com/technetwork/develop er-tools/datamodeler/learnmore/index.html © Miracle Finland Oy
136. Self study, Oracle Learning Library SQL Developer Data Modeler Release 3.1 Self Study - All Demos http://apex.oracle.com/pls/apex/f?p=44785:24: 1368386639082001::NO:24:P24_CONTENT_ID ,P24_PREV_PAGE:5966,2 © Miracle Finland Oy
137. Self study, Oracle Learning Library • Re-engineering Your Database Using Oracle SQL Developer Data Modeler 3.1 • http://apex.oracle.com/pls/apex/f?p=44785:2 4:11524540948794::NO:24:P24_CONTENT_ID, P24_PREV_PAGE:6266,2 • Etc. © Miracle Finland Oy
138. Users Guide • http://docs.oracle.com/cd/E29463_01/doc.33 /e35120.pdf • http://docs.oracle.com/cd/E29463_01/doc.33 /e35120/toc.htm (April 2013) © Miracle Finland Oy
139. Start Page • And do not forget the Start Page, links to ”everywhere” © Miracle Finland Oy
140. Thank You! Questions? heli@miracleoy.fi @helifromfinland Helifromfinland.wordpress.com © Miracle Finland Oy