1 / 44

SQL Developer Data Modeler Overview and New Features in version 4.0

SQL Developer Data Modeler Overview and New Features in version 4.0. David Last Data Modeler development team. Legal .

valin
Télécharger la présentation

SQL Developer Data Modeler Overview and New Features in version 4.0

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Developer Data Modeler Overview and New Featuresin version 4.0 David LastData Modeler development team

  2. Legal The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

  3. Agenda • Brief Overview • New diagram display options • Improved support for External Tables • Improved Search functionality • Oracle 12c database – Identity columns • Oracle 12c database – Masking/Redaction

  4. Oracle SQL Developer Data Modeler Overview • Technology and Architecture • Java based • Common IDE Framework • Metadata stored in XML Files • Database support • Oracle 9i, 10g, 11g and 12c • Third-party databases: Microsoft SQL Server, DB2, UDB • Platform support • Windows • Linux • Mac OSX

  5. Oracle SQL Developer Data Modeler Overview Strategy and Analysis Import Models Domains Data Type DFD ERD Multidimensional Logical Reporting Database Design Physical Star Schema Relational DDL Script Data Dictionary

  6. SQL Developer Data Modeler Releases

  7. New diagram display options • Can include Table/Column comments • Can include Classification Types • Can mark Table or Column as “deprecated” • Can include attributes of a Column that is defined as a Structured Type (new in DM v 4.0.2)

  8. Organization EXTERNAL for Relational Tables • Tables on the Relational Model diagram can be generated as External tables (by setting the Table’s Organization property to “EXTERNAL”) • This is new in Data Modeler v 4.0.2 - previously External Tables were defined only in the Physical Model, and did not appear on the diagram.

  9. Search functionality – simple search Simple and Advanced search : Simple search: • Just type the word you are looking for and all searchable properties of all objects are checked for match; dynamic properties are also checked • Content of the property is checked, if property is another object (Domain, Structured type, …) then the name of that object is checked • The name of the property is checked if content is “true”, “yes”, “y” – using “partit” as search pattern on physical model will return all partitioned tables • Search could be triggered in two ways – set in preferences; could be slow for large and complete designs

  10. Search functionality – advanced search Advanced search: • Narrowed to specific object type and properties • AND/OR expressions can be constructed • Available at model level Regular expressions can be used in both modes

  11. Search functionality – global search Global search: • Spans all open designs and models • Only simple search is available • Located at top right corner in standalone version • Available through menu “View>Data Modeler>Global Search” in SQL Developer (with DM 3.3)

  12. Search functionality – global search

  13. Search functionality – model level Model level search: • Searches through the whole model not just current diagram • For relational model – searches through all open physical models • Activated using “Find” icon on tool bar (or Ctrl-F on diagram) or “Find” in context menu for the model in browser • Advanced mode can be used to search on specific properties

  14. Search functionality – result • Search result (model level) can be pinned, another window will appear on next search • Search conditions can be saved and reused later • Search result can be filtered by model (in global search) and object type • Double click on a row in search result will bring up properties editor for that object • Selecting a row in result list will show diagrams where that object (or its container) is used – double click on diagram will focus on object on that diagram • Filtering on model and object type will unlock another functionality – “Properties” (setting common properties on objects) and “Reports”

  15. Search functionality – result

  16. Search functionality – setting common properties • “Old Value” shows if there are common properties set, empty otherwise • Three types of data – String (text), boolean (true/false) and object (domain, schema/user, tablespace …) – additional dialog appear to select related object • More than one properties can be set at once • Data type can be only one of domain, logical type, distinct type, structured type or collection type

  17. Search functionality – setting common properties

  18. Search functionality – custom reports • Custom reports can contain only one type of objects – columns, tables, entities, sequences… ; this is the reason they are available only when search result is filtered by model and object type • Report templates can be created for each type of object • Optimization on boolean values – true and false can be replaced with provided constants • Supported formats – HTML, PDF, RTF and Excel (XLS, XLSX) • Standard report can be generated on search result if there is a standard report for objects in search result

  19. Search functionality – custom reports, templates • Up to 30 properties can be included, properties in red are read-only, those in blue could be read-only when edited in Excel • Order of columns and custom names can be set • Sort order can be defined – by Entity and Name in example • Column width is for HTML reports

  20. Search functionality – custom reports

  21. Search functionality – custom reports, Excel file • Reports in XLS and XLSX format can be edited in Excel • Read-only properties will remain read-only in Excel • Properties that represent object (in blue in template – domain, schema/user, tablespace …) will be presented with look up field

  22. Search functionality – custom reports, Excel file • Updated Excel file can be returned back to the source model – context menu for the model in browser • Verification that file is for the same model • Log will be generated for updates done during import • Be aware that changes in data type of FK column/attribute won’t be applied because they inherit data type from referred column/attribute

  23. Oracle Database 12c – identity column • Data Modeler supports identity (auto increment) columns in Oracle Database 11g through usage of sequence and trigger. Native support for identity column in Oracle Database 12c just brings another option to consider. • Data Modeler 4.0 provides options to use the new database feature or stay with sequence trigger approach

  24. Oracle Database 12c – identity column:Trigger – the traditional approach CREATE TABLE Course ( Course_ID NUMBER NOT NULL ) ; ALTER TABLE Course ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ; CREATE SEQUENCE Course_Course_ID_SEQ START WITH 1 NOCACHE ORDER ; CREATE OR REPLACE TRIGGER Course_Course_ID_TRG BEFORE INSERT ON Course FOR EACH ROW WHEN (NEW.Course_ID IS NULL) BEGIN :NEW.Course_ID := Course_Course_ID_SEQ.NEXTVAL; END; /

  25. Oracle Database 12c – identity column:new IDENTITY clause CREATE TABLE Course ( Course_ID NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER ) NOT NULL ) ; ALTER TABLE Course ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ;

  26. Oracle Database 12c – identity column:using DEFAULT clause CREATE SEQUENCE Course_Course_ID_SEQ START WITH 1 NOCACHE ORDER ; CREATE TABLE Course ( Course_ID NUMBER DEFAULT Course_Course_ID_SEQ.NEXTVAL NOT NULL ) ; ALTER TABLE Course ADD CONSTRAINT Course_PK PRIMARY KEY ( Course_ID ) ;

  27. Who’s looking at my sensitive data?

  28. Redaction • Enables to mask (redact) data that is returned from the queries issued by low privileged users or application • Part of Oracle data security • Real-time • works well in dynamic production environment • All referential-integrity of back-end data is preserved during the redaction process • Different masking styles through different policies • To comply with industry regulations • Payment Card Industry Data Security Standard (PCI-DSS) • Sarbanes-Oxley Act

  29. Oracle Data Redaction Partial Before After

  30. Oracle Database 12c - masking Data Modeler 4.0 supports 2 ways of masking, extending current support for defining and visualizing of sensitive data • REDACTION policies – a redaction policy can operate only for a single table, providing masking definition(s) and condition when it's applied, for one or more columns belonging to that table. • TSDP (Transparent Sensitive Data Protection) policies - TSDP policy can protect multiple columns belonging to multiple tables, and more than one TSDP policy can be used to protect columns belonging to one table. However it's important to note that TSDP policies are implemented using Redaction policies and therefore it's not possible to have Redaction policy and TSDP policy protecting one and the same table. • Implementation for both approaches starts with masking templates

  31. Masking Templates • Masking templates for Character, Numeric and Date columns can be defined • “Tools>Masking Templates Administration” • Used in Redaction and TSDP policies

  32. Redaction policy • Definition of masking type and masking template at column level • Only masking template related to column data type (Character, Numeric and Date) are available for particular column

  33. Redaction policy

  34. TSDP policy Following steps are required • Templates • To create sensitive type - sensitive types appear as kind of classification for associated with them columns. Sensitive types are not "sensitive" to data type of columns associated to them. • Associate columns to sensitive type – directly from sensitive type dialog or through domain defined as “sensitive” • To create TSDP policy • Associate sensitive types to TSDP policy Templates, Sensitive types and TSDP policies can be used in all designs

  35. TSDP policy, sensitive type • Can be created in the browser

  36. TSDP policy, sensitive type

  37. TSDP policy, sensitive domain • Domain can be associated with “sensitive type” and later assigned to columns/attributes

  38. TSDP policy, create

  39. TSDP policy, sub-policies • It’s a collection of sub-policies – can be restricted to specific schema, table, data type

  40. TSDP policy, sensitive types

  41. Learn More.. • SQL Developer Data Modeler on OTN - http://www.oracle.com/technetwork/developer-tools/datamodeler • SQL Developer Data Modeler community forum https://forums.oracle.com/forums/forum.jspa?forumID=1317 • Oracle Learning Library http://apex.oracle.com/pls/apex/f?p=44785:2:0:FORCE_QUERY::2,RIR,CIR:P2_TAGS:Data%20Modeler • Jeff Smith http://www.thatjeffsmith.com/ • Kris Rice http://krisrice.blogspot.co.uk/

More Related