database & technology 2 _ damien bootsma _ best practices for capturing meta data for prodctive...
TRANSCRIPT
![Page 1: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/1.jpg)
The most comprehensive Oracle applications & technology content under one roof The most comprehensive Oracle applications & technology content under one roof
Metadata Best Prac,ces Modelling best prac1ces for Database Developers
Damien Bootsma
So-ware Consultant
![Page 2: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/2.jpg)
The most comprehensive Oracle applications & technology content under one roof
Overview
• Why? • Metadata to consider capturing • Pushing the boat out… • Q & A
![Page 3: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/3.jpg)
The most comprehensive Oracle applications & technology content under one roof
Model beyond this…
![Page 4: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/4.jpg)
The most comprehensive Oracle applications & technology content under one roof
Why?
• Lack of documentaGon for our DBs • Common data lives in a variety of systems • Lineage and impact across systems is crucial • DB funcGonality is not trivial
![Page 5: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/5.jpg)
The most comprehensive Oracle applications & technology content under one roof
Why?
• Who are your customers?
Data Architects
App / DB Dev
DBAs
ETL
Business Analysts
Network / Ops
Process Analysts
![Page 6: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/6.jpg)
The most comprehensive Oracle applications & technology content under one roof
What’s Metadata?
• Describes the characterisGc of a resource
– More than NAME & DATA TYPE • Data security • Compliance metadata • Stewardship • Governance • Workflow • Reference Values
![Page 7: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/7.jpg)
The most comprehensive Oracle applications & technology content under one roof
Why?
• Good metadata comes from a true understanding of your data!
• Data à InformaGon • InformaGon à Knowledge • Knowledge à Power
![Page 8: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/8.jpg)
The most comprehensive Oracle applications & technology content under one roof
Capturing important Metadata
• Domains • Security & Compliance • Reference Values • Business Rules
![Page 9: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/9.jpg)
The most comprehensive Oracle applications & technology content under one roof
Domains • What are they?
– Define the characterisGc for a type of aVribute – Eg: Name: DESCRIPTION
Data Type: VARCHAR(150) DescripGon: Describe the behavior of the current EnGty
• Benefit> – Consistency: Same domain is applied to all instances of aVribute type
![Page 10: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/10.jpg)
The most comprehensive Oracle applications & technology content under one roof
Domains
• Varchar(30) • Null
• Varchar(20) • Not Null
• Varchar(max) • Null
• Varchar(100) • Not Null
![Page 11: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/11.jpg)
The most comprehensive Oracle applications & technology content under one roof
Domains
Impact Analysis / Inheritance / Where Used
![Page 12: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/12.jpg)
The most comprehensive Oracle applications & technology content under one roof
Security & Compliance • What is it?
– Describing authorisaGon and permissions for specific data
– Describe any compliance mandates applicable to data elements
• Benefit> – Configure DB to enable certain security mechanisms – Develop correct funcGonality based on compliance and security
![Page 13: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/13.jpg)
The most comprehensive Oracle applications & technology content under one roof
Security & Compliance Industries
Financial Services
Healthcare/Pharma
Federal Government
Retail
Energy
Mandates Sarbanes-Oxley
GLBA
Basel II
HIPAA
21 CFR Part 11
PCI DSS
State Data Breach Disclosure Laws
FISMA
Int’l. Data Privacy Laws
FERC/NERC
Guidance
PCAOB
COSO
CobiT
FFIEC
ISO 17799
NIST 800-66
NIST 800-53
DoD STIG
ITIL
![Page 14: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/14.jpg)
The most comprehensive Oracle applications & technology content under one roof
Reference Values
• What is it? – Data such as codes & values that represent correct & possible values for a given aVribute
• Benefit>
– Consistency of data – IdenGfy such data & ensure it is correctly in the DB & not in the logic layer
![Page 15: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/15.jpg)
The most comprehensive Oracle applications & technology content under one roof
Business Rules
• What is it? – Statement that defines or constrains some aspect of the business
• Benefit> – Drives database design and implementaGon – Adds addiGonal context for database development
![Page 16: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/16.jpg)
The most comprehensive Oracle applications & technology content under one roof
Business Rules
• Simple Bank Example: • Create a Stored Procedure that lists all customers eligible for further discounts on their current mortgage
• Business Rule: Employees that hold a mortgage with the bank that have worked for the bank for less than 3 years are not eligible for further discounts
![Page 17: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/17.jpg)
The most comprehensive Oracle applications & technology content under one roof
Storing Metadata
• Where is your metadata? – Other modeling tools – Metadata repository – Source code control – Business apps – Issue tracking systems
• How can you monitor and integrate the sources together
![Page 18: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/18.jpg)
The most comprehensive Oracle applications & technology content under one roof
Where can I find my Metadata?
Modeling & Metadata tools • ER Tools, UML modeling, “Big R” repositories, BPM diagramming, Visio, etc
Social Media Tools • Facebook, twiVer, etc Online CollaboraGon
Tools • Google Wave, Google Sites, EtherPad, Sharepoint, other wiki technology
Issue Tracking • Remedy, Tivoli, JIRA, etc
TradiGonal Business Apps • MS Office, e-‐mail, Lotus, Open Office, Google Docs, etc
![Page 19: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/19.jpg)
The most comprehensive Oracle applications & technology content under one roof
Find the centre…
Modeling & Metadata tools • ER Tools, UML modeling, “Big R” repositories, BPM diagramming, Visio, etc
Social Media Tools • Facebook, twiVer, etc Online CollaboraGon
Tools • Google Wave, Google Sites, EtherPad, Sharepoint, other wiki technology
Issue Tracking • Remedy, Tivoli, JIRA, etc
TradiGonal Business Apps • MS Office, e-‐mail, Lotus, Open Office, Google Docs, etc
???
![Page 20: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/20.jpg)
The most comprehensive Oracle applications & technology content under one roof
Capture “Everything”
• “Everything” that is necessary and nothing more – DefiniGon of “everything” will be based on your audience(s)
– How “everything” is stored will be based on how it is accessed
![Page 21: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/21.jpg)
The most comprehensive Oracle applications & technology content under one roof
Pushing the Boat out… UML
• Sequence Diagrams – Flow of a complex Stored Procedure/FuncGon/Trigger
– Contains appropriate constructs • Tables/Views • Statements (Insert, Select, Delete, Update) • Flow control (branching & Looping)
– Picture is worth a thousand words
![Page 22: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/22.jpg)
The most comprehensive Oracle applications & technology content under one roof
Pushing the boat out… UML
![Page 23: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/23.jpg)
The most comprehensive Oracle applications & technology content under one roof
Summary
• AddiGonal metadata gives us a more thorough view for development
• ‘Everything’: Use Metadata that will help you and fellow developers
• Consider UML as a vehicle for complex funcGonality
• Get Involved! Model unto others…
![Page 24: Database & Technology 2 _ Damien Bootsma _ best Practices for capturing meta data for prodctive database development.pdf](https://reader033.vdocuments.us/reader033/viewer/2022060116/55812326d8b42a65108b50db/html5/thumbnails/24.jpg)
The most comprehensive Oracle applications & technology content under one roof
Thank you