data-ed online: a practical approach to data modeling

TITLE

PRODUCED BY

DATA BLUEPRINT 10124-C W. BROAD ST, GLEN ALLEN, VA 23060CLASSIFICATION DATE SLIDE

2/14/2012© Copyright this and previous years by Data Blueprint - all rights reserved!

EDUCATION

Welcome!

Date: March 13, 2012Time: 2:00 PM ETPresenter: Dr. Peter AikenTwitter: #dataed

1

Practical Data Modeling

TITLE

PRODUCED BY



EDUCATION

Meet Your Presenter: Dr. Peter Aiken

2

• Internationally recognized thought-leader in the data management field with more than 30 years of experience

• Recipient of the 2010 International Stevens Award

• Founding Director of Data Blueprint (http://www.datablueprint.com)

• Associate Professor of Information Systems at Virginia Commonwealth University (http://vcu.edu)

• President of DAMA International (http://dama.org)

• DoD Computer Scientist, Reverse Engineering Program Manager/Office of the Chief Information Officer

• Visiting Scientist, Software Engineering Institute/Carnegie Mellon University

• 7 books and dozens of articles• Experienced w/ 500+ data management practices in 20 countries

#dataed

2/14/2012DATA BLUEPRINT 10124-C W. BROAD ST, GLEN ALLEN, VA 23060 EDUCATION

Practical Data Modeling

Dr. Peter Aiken: Practical Data Modeling

TITLE

PRODUCED BY



EDUCATION

Practical Data ModelingThis presentation provides you with an understanding of the data modeling and data development components of data management. Participants will understand how the analysis, design, implementation, deployment, and maintenance of data solutions should be approached in order to maximize the full value of the enterprise data resources and activities. Architecting in quality is imperative at this level and complements a subset of project activities within the system development lifecycle (SDLC) focused on defining data requirements, designing data solution components, and implementing these components. Participants will understand the difficulties organizations experience when interacting with data development efforts and how to best incorporate these efforts into specific data projects.

4

#dataed

TITLE

PRODUCED BY



EDUCATION

Outline1. Data Management Overview2. What is Data Modeling? What is Data

Development?3. Why are they important and how do they

compare?4. Where do they fit in as data management

components?5. Data Modeling/Development Frameworks6. Data/Information Architecture Building

Blocks7. Guiding Principles & Best Practices8. Improving Data Modeling and Data

Development within Your Organization: 7 Mistakes You Cannot Afford to Make

9. Take Aways, References & Q&A

5

Tweeting now: #dataed

TITLE

PRODUCED BY



EDUCATION

The DAMA Guide to the Data Management Body of Knowledge

6

Data Management Functions

Published by DAMA International• The professional

association for Data Managers (40 chapters worldwide)

DMBoK organized around • Primary data

management functions focused around data delivery to the organization

• Organized around several environmental elements

TITLE

PRODUCED BY



EDUCATION

The DAMA Guide to the Data Management Body of Knowledge

7

Environmental Elements

Amazon:http://www.amazon.com/DAMA-Guide-Management-Knowledge-DAMA-DMBOK/dp/0977140083Or enter the terms "dama dm bok" at the Amazon search engine

TITLE

PRODUCED BY



EDUCATION

What is the CDMP?• Certified Data Management

Professional• DAMA International and ICCP• Membership in a distinct group made

up of your fellow professionals• Recognition for your specialized

knowledge in a choice of 17 specialty areas

• Series of 3 exams• For more information, please visit:

– http://www.dama.org/i4a/pages/index.cfm?pageid=3399

– http://iccp.org/certification/designations/cdmp

8

#dataed

TITLE

PRODUCED BY



EDUCATION

Data Management

9

#dataed

TITLE

PRODUCED BY



EDUCATION

Data Management

10

Manage data coherently.

Share data across boundaries.

Assign responsibilities for data.Engineer data delivery systems.

Maintain data availability.

Data Program Coordination

Organizational Data Integration

Data Stewardship Data Development

Data Support Operations

#dataed

TITLE

PRODUCED BY



EDUCATION








11


TITLE

PRODUCED BY



EDUCATION

Summary: Data Development

12

from The DAMA Guide to the Data Management Body of Knowledge © 2009 by DAMA International#dataed

TITLE

PRODUCED BY



EDUCATION

Data Development Definition

13

• Analysis, design, implementation, deployment, and maintenance of data solutions to maximize the value of the data resources to the enterprise

• Subset of SDLC – defining and implementing data solution components– Primarily databases and data structures but includes screens,

reports, interfaces – Now is recognized to include data virtualization, portals, XML

delivery, etc.• Example:

data definition language(DDL)

#dataed

TITLE

PRODUCED BY



EDUCATION

Data Modeling Definition

14

• Modeling = Analysis and design method used to– Define and analyze data requirements– Design data structures that support these

requirements

• Model = set of data specifications and related diagrams that reflect requirements and designs– Representation of something in our

environment– Employs standardized text/symbols to

represent data attributes (grouped into data elements) and the relationships among them

– Integrated collection of specifications and related diagrams that represent data requirements and design


TITLE

PRODUCED BY



EDUCATION








15


TITLE

PRODUCED BY



EDUCATION

Data Modeling• Modeling = complex process involving

interaction between people and with technology that don’t compromise the integrity or security of the data

• Good data models accurately express and effectively communicate data requirements and quality solution design

• Modeling approach (guided by 2 formulas):– Purpose + audience = deliverables– Deliverables + resources + time = approach

16


TITLE

PRODUCED BY



EDUCATION

Data Models Facilitate

2. Communicationo Data model is a bridge to understanding data between people

with different levels and types of experience. o Helps understand business area, existing application, or impact

of modifying an existing structureo May also facilitate training new business and/or technical staff

3. Scopeo Data model can help explain the data concept and scope of

purchased application packages

17

1. Formalizationo Data model documents a single,

precise definition of data requirements and data-related business rules


TITLE

PRODUCED BY



EDUCATION

Data Models: Same But Different• Models that include the same data may differ by• Scope: Express a perspective about data in

terms of:– Function: business view vs. application view– Realm: process, department, division, enterprise or

industry– Time: current state, short-term future, long-term future

• Focus:– Conceptual view: Basic and critical concepts– Logical view: Detailed but independent of context– Physical view: Optimized for a specific technology/use

18


TITLE

PRODUCED BY



EDUCATION

Data Model Uses

19

from The DAMA Guide to the Data Management Body of Knowledge © 2009 by DAMA International

• Use data models to specify the data required for information needs

• Data flows through business processes packaged in information products

• Data contained in these products must meet business requirements

#dataed

TITLE

PRODUCED BY



EDUCATION

Data Models Used to Support Strategy

20

• Flexible, adaptable data structures• Cleaner, less complex code• Ensure strategy effectiveness measurement• Build in future capabilities• Form/assess merger and acquisitions strategies

Adapted from Introduction to Data Modeling by Clive Finkelstein in Information Engineering Strategic Systems Development 1992

Employee Employee

Sales Manager Manager

Staff Line

#dataed

TITLE

PRODUCED BY



EDUCATION

Data Models and Business Rules

21

Person Job Class

Employee Posi:on

BR1) Zero, one, or more EMPLOYEES can be associated

with one PERSON

BR2) Zero, one, or more EMPLOYEES can be associated with one JOB CLASS;

BR3) Zero, one, or more EMPLOYEES can be associated with one POSITION

BR4) One or more POSITIONS can be associated with one JOB CLASS.

Moo

nligh:

ng

Job Sharing

TITLE

PRODUCED BY



EDUCATION



compare?4. Where do they fit in as data

management components?5. Data Modeling/Development Frameworks6. Data/Information Architecture Building




22


TITLE

PRODUCED BY



EDUCATION

Data Management Functions

23


TITLE

PRODUCED BY



EDUCATION

Data Modeling and Design Quality Management

24

• Implement development/test database changes• Create and maintain test data• Migrate and convert data• Build and test information products• Build and test data access services• Validate information requirements• Prepare for data deployment


Analysis

Design

Build

Test

Maintain

#dataed

• Data modeling is used to articulate data architecture components

• Data architectures are comprised of components – usually expressed as models

• Styles of data modeling exist – this is a challenge– IE or information engineering– IDEF1X used by DoD– ORM or object role modeling– UML or unified modeling language

• Data models are useful– In stand-alone mode– As components of a larger information architecture

TITLE

PRODUCED BY



EDUCATION

Data Modeling and Data Architecture

25

#dataed

!" !" !" !"

PRODUCED BY

DATA BLUEPRINT 10124-C W. BROAD ST, GLEN ALLEN, VA 23060CLASSIFICATION

EDUCATIONDATE SLIDE


26

Data Architectures produce and are made up of models that are developed in response to organizational needs

Organizational Needs

become instantiated and integrated into an Data/Information

Architecture

!"#$%&'($")*+,-.&)/.012%.&."-,3

authorizes and articulates sa

tisfy

spe

cific

org

aniz

atio

nal n

eeds

#dataed

TITLE

PRODUCED BY



EDUCATION

• Consider the opposite question:– Were your systems explicitly designed to

be integrated or otherwise work together?– If not then what is the likelihood that they

will work well together?– In all likelihood your organization is spending between

20-40% of its IT budget compensating for poor data structure integration

– They cannot be helpful as long as their structure is unknown

• Two answers1. Achieving efficiency and effectiveness goals2. Providing organizational dexterity for rapid implementation

How do Data Models Support Organizational Strategy?

27

#dataed

TITLE

PRODUCED BY



EDUCATION

How are Data Models Expressed as Architectures?• Attributes are organized into entities/objects

– Attributes are characteristics of "things"– Entitles/objects are "things" whose information is managed in support of

strategy– Examples

• Entities/objects are organized into models– Combinations of attributes and entities are structured to represent

information requirements– Poorly structured data, constrains organizational information delivery

capabilities– Examples

• Models are organized into architectures– When building new systems, architectures are used to plan development– More often, data managers do not know what existing architectures are

and - therefore - cannot make use of them in support of strategy implementation

– Why no examples?

28

#dataed

TITLE

PRODUCED BY



EDUCATION




components?5. Data Modeling/Development Frameworks6. Data/Information Architecture Building Blocks7. Guiding Principles & Best Practices8. Improving Data Modeling and Data



29


TITLE

PRODUCED BY



EDUCATION

The Data Model Pyramid

Source: Steve Hoberman & George McGeachie, Key Features Needed in a Data Modeling Tool; http://www.tdan.com/view-articles/15768

30

#dataed

TITLE

PRODUCED BY



EDUCATION

Disposition Data Map

31

#dataed

TITLE

PRODUCED BYDATA BLUEPRINT 10124-C W. BROAD ST, GLEN ALLEN, VA 23060

CLASSIFICATION

EDUCATIONDATE SLIDE


Data Map of DISPOSITION• At least one but possibly more system USERS enter the DISPOSITION facts into the system.• An ADMISSION is associated with one and only one DISCHARGE.• An ADMISSION is associated with zero or more FACILITIES.• An ADMISSION is associated with zero or more PROVIDERS.• An ADMISSION is associated with one or more ENCOUNTERS.• An ENCOUNTER may be recorded by a system USER.• An ENCOUNTER may be associated with a PROVIDER.• An ENCOUNTER may be associated with one or more DIAGNOSES.

ADMISSION Contains information about patient admission history related to one or more inpatient episodes

DIAGNOSIS Contains the International Disease Classification (IDC) of code representation and/or description of a patient's health related to an inpatient code

DISCHARGE A table of codes describing disposition types available for an inpatient at a FACILITY

ENCOUNTER Tracking information related to inpatient episodes

FACILITY File containing a list of all facilities in regional health care system

PROVIDER Full name of a member of the FACILITY team providing services to the patient

USER Any user with access to create, read, update, and delete DISPOSITION data

32

TITLE

PRODUCED BY



EDUCATION

Attributes & Definitions

33

Entity: BED Data Asset Type: Principal Data EntityPurpose: This is a substructure within the

Room substructure of the Facility Location. It contains information about beds within rooms.

Source: Maintenance Manual for File and Table Data (Software Version 3.0, Release 3.1)

Attributes: Bed.Description Bed.Status Bed.Sex.To.Be.Assigned Bed.Reserve.ReasonAssociations: >0-+ RoomStatus: Validated

BEDBed.Id #Bed.Descrip:onBed.StatusBed.Sex.To.Be.AssignedBed.Reserve.Reason

Attributes arranged into an entity named "bed" – the attribute Bed.Id is the means used to identify a unique occurrence of bed

Attributes displayed in a manner encouraging their reuse as perhaps in a CASE-tool or metadata repository – A purpose statement describing why the organization is maintaining information about these "business things" – Sources of information about it –(A partial) List of the attributes or characteristics of the entity – Associations with other data items; this is read as ROOM contains zero or more BEDS

TITLE

PRODUCED BY



EDUCATION

ANSI-SPARK 3-Layer Schema

34

1. Conceptual - Allows independent customized user views:

– Each should be able to access the same data, but have a different customized view of the data.

2. Logical - This hides the physical storage details from users:

– Users should not have to deal with physical database storage details. They should be allowed to work with the data itself, without concern for how it is physically stored.

3. Physical - The database administrator should be able to change the database storage structures without affecting the users’ views:

– Changes to the structure of an organization's data will be required. The internal structure of the database should be unaffected by changes to the physical aspects of the storage.

For example, a changeover to a new DBMS technology. The database administrator should be able to change the conceptual or global structure of the database without affecting the users.

PRODUCED BY

DATA BLUEPRINT 10124-C W. BROAD ST, GLEN ALLEN, VA 23060CLASSIFICATION

EDUCATIONDATE SLIDE


35

Data Modeling is used throughout the Systems Development Lifecycle


Analysis

Design

Build

Test

Maintain#dataed

TITLE

PRODUCED BY



EDUCATION








36


TITLE

PRODUCED BY



EDUCATION

Data Modeling & Development Building Blocks

3745

Illustration from The DAMA Guide to the Data Management Body of Knowledge p. 37 © 2009 by DAMA International#dataed

üü ü üü ü ü

üü ü üü ü ü

TITLE

PRODUCED BY



EDUCATION

Summary: Data Development

38


TITLE

PRODUCED BY



EDUCATION

Goals and Principles

39

1. Identify and define data requirements.

2. Design data structures and other solutions to these requirements.

3. Implement and maintain solution components that meet these requirements.

4. Ensure solution conformance to data architecture and standards as appropriate.

5. Ensure the integrity, security, usability, and maintainability of structured data assets.


TITLE

PRODUCED BY



EDUCATION

Data Modeling/Development Activities1. Data modeling, analysis and solution

design1) Analyze information requirements2) Develop and maintain conceptual models3) Develop and maintain logical models4) Develop and maintain physical models

2. Detailed data design1) Design physical databases2) Design information products3) Design data access services4) Design data integration services

40


TITLE

PRODUCED BY



EDUCATION

Data Modeling/Development Activities, cont’d3. Data model and design quality management

1) Develop data modeling and design standards2) Review data model and database design quality3) Manage data model versioning and integration

4. Data implementation1) Implement development/test database changes1) Create and maintain test data2) Migrate and convert data3) Build and test information products4) Build and test data access services5) Validate information requirements6) Prepare for data deployment

41


TITLE

PRODUCED BY



EDUCATION

Primary Deliverables• Data Requirements and

Business Rules• Conceptual Data Models• Logical Data Models and

Specifications• Physical Data Models and

Specifications• Meta-data (Business and

Technical)• Data Modeling and DB design

Standards• Data Model and DB Design

Reviews• Data Integration Services• Data Access Services

• Version Controlled Data Models

• Test Data• Development and Test

Databases• Information Products• Migrated and Converted Data

42


TITLE

PRODUCED BY



EDUCATION

Primary Deliverables become Reference Material

43


TITLE

PRODUCED BY



EDUCATION

Data Modeling/Dev. Roles & Responsibilities

44

Suppliers:• Data Stewards and SMEs• IT Steering committee• Data Governance Council• Data Architects and Analysts• Software Developers• Data Producers• Information Consumers

Participants:• Data Stewards and SMEs• Data Architects and Analysts• Database Administrators• Data Model Administrators• Software Developers• Project Managers• DM Executives and other IT Management

Consumers:• Data Producers• Knowledge Workers• Managers and Executives• Customers• Data Professionals• Other IT Professionals


TITLE

PRODUCED BY



EDUCATION

Data Modeling/Development Technology

45

Testing ToolsData Profiling Tools

Data Modeling ToolsOffice Productivity Tools

Model Management ToolsSoftware Development Tools

Database Management SystemsConfiguration Management Tools


TITLE

PRODUCED BY



EDUCATION








46


TITLE

PRODUCED BY



EDUCATION

Guiding Principles1. Data development activities are

an integral part of the software development lifecycle (SDLC).

2. Data modeling is an essential technique for effective data management and system design.

47


3. Conceptual and logical data modeling express business and application requirements, while physical data modeling represents solution design.

4. Data modeling and database design balances tradeoffs and needs.

5. Data professionals should collaborate with other project team members to design information products and data access and integration interfaces.

#dataed

TITLE

PRODUCED BY



EDUCATION

Guiding Principles, cont’d6. Data modeling and database

design should follow documented standards

7. Design reviews should review all data models and designs, in order to ensure they meet business requirements and follow design standards.

48


6. Data models represent valuable knowledge resources (metadata). Carefully manage and control them through library, configuration, and change management to ensure data model quality and availability.

7. DBAs and other data professionals play important roles in the construction, testing, and deployment of databases and related application systems.

#dataed

TITLE

PRODUCED BY



EDUCATION








49


TITLE

PRODUCED BY



EDUCATION

7 Mistakes You Can’t Afford to Make Enterprise Data Modeling

Source: Karen Lopez, InfoAdvisors; @datachick

1. Forgetting that an enterprise architecture is a living framework• Traceability is key to realizing the benefits of an

enterprise data management program: Any team member should be able to trace a business concept from the logical model to the physical model to the physical implementation of that concept

2. Keeping data models invisible• In order to deliver business value, a data

management effort must be accessible, understandable and shareable.

• Models need to be available in an easily searchable manner.

50

Source: Karen Lopez http://www.information-management.com/newsletters/enterprise_architecture_data_model_ERP_BI-10020246-1.html?pg=2

TITLE

PRODUCED BY



EDUCATION

7 Mistakes You Can’t Afford to Make3. Assuming that business users can’t understand or

review models• Business users need to be able to access and digest data

models so they can make informed business decisions• It is key to give them data model viewing and reporting

capabilities• Remember: business users who see models regularly are

more likely to support the allocation of resources to future efforts

4. Thinking that data models are only about databases• Both logical and physical models support more than just

databases• Allowing team members to import/export metadata

contributes to a model-driven design environment and establishes integration of model metadata with other platforms

51


#dataed

TITLE

PRODUCED BY



EDUCATION

7 Mistakes You Can’t Afford to Make, cont’d5. Throwing models “over the wall”

• A modeler is the mediator between business requirements and physical implementations

• He/She should be involved in how requirements are captured as well as implemented

6. Forgetting about the sizzle• One of the main benefits of effective enterprise data

management is better communication• Models should be interesting and the successful data

modeler must never underestimate the value of sizzle• Presentations of models must be clear and

understandable• Adding color and diagramming objects customizes

models and allows for a more engaging and enjoyable user review process

52


#dataed

TITLE

PRODUCED BY



EDUCATION

7 Mistakes You Can’t Afford to Make, cont’d7. Thinking of them as “your” models

• Most critical mistake is treating data models as if the modeler personally owns them

• Models belong to the business and are tended to by the modelers. This means:• Share them openly• Provide access to those who want it• Keep extra printouts available• Offer training on how to read them• Make every effort to make them clear and understandable

53


Treating models as technical specifications that are understood only by developers and DBAs will not provide the benefits of an enterprise architecture #dataed

TITLE

PRODUCED BY



EDUCATION








54


TITLE

PRODUCED BY



EDUCATION

References

55

TITLE

PRODUCED BY



EDUCATION

References

56

TITLE

PRODUCED BY



EDUCATION

References

57

TITLE

PRODUCED BY



EDUCATION

Questions?

58

It’s your turn! Use the chat feature or Twitter (#dataed) to submit

your questions to Peter now.

+ =

TITLE

PRODUCED BY



EDUCATION

Upcoming Events

59

April Webinar:Data Operations Management: Turning your Challenges Into SuccessApril 10, 2012 @ 2:00 PM ET/11:00 AM PT

May Webinar:How Safe is Your Data? Data Security WebinarMay 15, 2012 @ 2:00 PM ET/11:00 AM PT

Sign up here:• www.datablueprint.com/webinar-schedule • www.Dataversity.net

Brought to you by:

data-ed online: a practical approach to data modeling

Technology

data requirements

data solutioncomponents

data management components

data development efforts

data management practices

data management field

data management overview2

practical data modelingdr