Transcript The Business End of Data Modeling

The Business End of Data
Modeling
Bob Lambert
1419 West Main Street
Richmond, Virginia 23230
804.355.0511
©2010 CapTech Ventures
www.captechventures.com
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
What about Bob?
Manager/Architect at CapTech focusing on system architecture, data modeling, and database
design. Background: mainframe and Oracle PL/SQL development, systems analysis, project/program
management, training, SQL Server data warehousing.
Live in Powhatan where I help my wife run a boarding barn
Play in a small jazz outfit called Super64
Page 2
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
The Business End of Data Modeling
The requirements side of data modeling
• and how it prepares you the database
developer to design and build the right
solution.
When critical business definition elements
are missing
• what you the database professional can
do to produce a successful result.
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 3
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Look like a project you’ve been on?
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 4
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Standish Group Study of Project Outcomes
1994
1996
1998
2000
2002
2004
2006
2009
Successful
16%
27%
26%
28%
34%
29%
35%
32%
Challenged
53%
33%
46%
49%
51%
53%
46%
44%
Failed
31%
40%
28%
23%
15%
18%
19%
24%
http://www.projectsmart.co.uk/the-curious-case-of-the-chaos-report-2009.html
Success: The project is completed on-time and on-budget, with all features and functions as
initially specified.
Challenged: The project is completed and operational but over-budget, over the time estimate,
and offers fewer features and functions than originally specified.
Failed: The project is canceled at some point during the development cycle.
http://findarticles.com/p/articles/mi_hb286/is_6_34/ai_n29049353/?tag=content;col1
Page 5
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Standish’s conclusion
“The three major reasons that a project will succeed are user
involvement, executive management support, and a clear
statement of requirements.”
http://findarticles.com/p/articles/mi_hb286/is_6_34/ai_n29049353/?tag=content;col1
Page 6
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
What are requirements?
1. A shared vision of desired project outcome
• Documented cost/benefit impact
• Endorsed by a business sponsor and supported by key participants
2. Consensus scope, objectives, and constraints, including
• Definition of system boundaries and interfaces
•
Identification of key constraints
3. Definition of the business processes and information required to achieve objectives
4. Identification of the IT application’s role in business processes and information management
• Process automation
• Data management
Page 7
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Projects often shortchange the
Information part of requirements
Although there are exceptions, in my experience,
• Business Requirements Document Templates often lack Data Model sections
• Projects applying object-oriented approaches often focus on behavior via Use Case
analysis. Class models tend not to factor business concepts into normalized units.
• Business reengineering is a process-based technique that often omits data-focused
analysis.
• Application Projects often feature the “data person” who does all the “data stuff”,
while responsibility for functional requirements are shared among the entire team
• Agile teams sometimes focus on minimal requirements analysis in favor of delivery
of perceived value on time
Page 8
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Data Requirements: The Logical Data Model
A graphical representation of
• People, places, and things of interest to the organization
(entities)
• Core business rules governing business entities, the relationships
between them
• Characteristics (attributes) of business entities and relationships
• Independent of any physical implementation
PK
• Low level – detailed and precise
PK,FK1
PK,FK2
PersonId
FirstName
MiddleName
LastName
PersonType
ProjectId
EmployeeId
StartDate
EndDate
PersonType
assigned to / is for
has / is for
Consultant
PK,FK1
PK,FK2
A logical data model can be at different levels of detail
• Conceptual – a general view of business concepts
• High level –further detail, still accessible to a broad audience
Assignment
Person
Contact
PK
EmployeeId
PersonId
ClientShortName
FK1
PersonId
Project
sponsors / sponsored by
PK
ProjectId
FK1
FK1
ProjectName
BudgetAmount
PlannedStartDate
PlannedEndDate
PersonId
ClientShortName
Employs / Employed by
Client
PK
ClientShortName
CiientName
Any resemblance of this data model example to
CapTech’s internal practices is purely accidental
(Names of data model levels vary from source to source)
Page 9
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Logical Data Modeling Promotes IT/Business Alignment
Logical Data Model
Business Process
Definition
Application
Development
Precise
Business
Language
Database
Design
Definition of rational
and efficient business
rules and processes
Business-consistent
database design or
class modeling
Page 10
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Logical Data Model Characteristics and Components
Characteristics:
- A Logical data model is implementation-independent and primitive
Components:
- Entity Relationship Diagram (ERD)
• Entity: a single object or event of significance to the business
• Relationship: a significant association between two entities
• Attribute: any detail that qualifies, identifies, classifies, or expresses the state of an entity
or relationship
- Metadata: Definitions and other supporting information describing objects represented on
an entity relationship diagram.
Page 11
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Implementation Independence
Which of these two statements belongs in a
requirements document? Why?
• “Each accident report includes a photo of the damage
taken by the estimator”
• “Each accident report record will include a field
containing the name of the damage photo file. That file
must be in JPEG format.”
Implementation Independence means:
• “What not How”
• not related to technical specifics
• AKA “logical” or “essential”
© 2008 CapTech Ventures, All rights
reserved.
©2010 CapTech Ventures,
Inc. All rights reserved.
Page 12
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Entities: Business Critical Objects and Events
Definition
- An object or event critical to the business
- A thing of significance, either real or conceptual, about which the business or system being modeled
needs to hold information.
- Entities are the boxes on a logical data model
Categories of entities
- Fundamental: exist on their own without reference to other entities (e.g. employee, building)
- Attributive: require the existence of another entity (employee dependent, cubicle)
- Associative: define the relationship between two other entities (an employee’s cubicle assignment)
- Subtype: a specific entity that is a special case of a more general entity type (“car” might be a subtype of
“vehicle”
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 13
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Relationships = Business Rules
Definition
• An association among two or more entities
• Define how critical business objects and events interact
• Relationships are the lines between boxes on a logical data model
Relationship Notes
• There are three common types: one to one, one to many, and many to many
• There may be more than one relationship between any two entities
Examples
• What happens when a mechanical issue is registered for an aircraft?
• How many students can enroll in a given class?
• How does a customer place an order?
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 14
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Attributes: Describe Entities
Definition
• An identifiable property of an entity
• Consists of a name, data type, domain, and optionally presentation format and default value
Domain
• The set of possible values of an entity
• Identifies any business rules or restrictions that define correct values for the attribute
Good Practices with Attributes
• Attribute names use a standard convention and standard abbreviations
• Each entity is uniquely identified by an attribute or a group of attributes
• Document and maintain attribute metadata
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 15
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Primitiveness: Derived vs. Atomic Attributes
Atomic (or Primitive) Attributes
• Individual object or event: Cost of an item, Quantity purchased
• The deepest level of detail needed by the business
Derived and Summary Data
• Combines characteristics of many objects and events
• Can integrate data across different reporting dimensions
• Defined as a formula based on other data
Use only atomic attributes in logical data modeling
©©2010
2008
CapTech
Ventures,
All rights reserved.
CapTech
Ventures, Inc.
All rights reserved.
Page 16
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Metadata: Business Context for Model Content
Metadata makes the Logical Data Model accessible to
a broad audience
- Why was the model created and what was its scope
- Modeling conventions and quality standards
- Abbreviations
- Entity and attribute definitions
- Model limitations and next steps
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Logical Data Model = Clear Business Language
You can derive a data model from a precise text description of a business area
and
You can derive a precise text description of a business area from a logical data model
Assignment
Person
PK
PK,FK1
PK,FK2
PersonId
FirstName
MiddleName
LastName
PersonType
ProjectId
EmployeeId
StartDate
EndDate
PersonType
assigned to / is for
has / is for
Consultant
PK,FK1
PK,FK2
Contact
PK
EmployeeId
PersonId
ClientShortName
FK1
PersonId
Project
sponsors / sponsored by
PK
ProjectId
FK1
FK1
ProjectName
BudgetAmount
PlannedStartDate
PlannedEndDate
PersonId
ClientShortName
Employs / Employed by
Client
PK
ClientShortName
CiientName
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 18
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
The data model in English
Assignment
Person
PK
A person may be a contact
PK,FK1
PK,FK2
PersonId
FirstName
MiddleName
LastName
PersonType
A client employs many contacts
A project may be sponsored by a client contact
ProjectId
EmployeeId
StartDate
EndDate
PersonType
assigned to / is for
has / is for
An assignment is for a project
A consultant is assigned to a project
Consultant
PK,FK1
PK,FK2
Contact
PK
EmployeeId
PersonId
ClientShortName
FK1
PersonId
Project
sponsors / sponsored by
A project may be internal
PK
ProjectId
FK1
FK1
ProjectName
BudgetAmount
PlannedStartDate
PlannedEndDate
PersonId
ClientShortName
Employs / Employed by
(not associated with a client or contact)
Client
A consultant is a person
PK
ClientShortName
CiientName
Page 19
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
A Detailed Logical Data Model is Normalized
In a good, detailed,
logical data model:
(Informally) a database
is normalized when
•Every entity is identified by a
•No table has repeating groups
natural key
•Every column in a table is
•Every distinct business object
functionally dependent on the
or event is shown as an entity
whole key
•Every attribute describes the
• No column depends on
entity that it belongs to
anything but the key
©2010 CapTech Ventures, Inc. All rights reserved.
Detailed definition
of objects, events,
attributes, and
business rules gets
you a solid first cut
database design
Page 20
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
The Business End of Data Modeling
The requirements side of data modeling
• and how it prepares you the database
developer to design and build the right
solution.
When critical business definition elements
are missing
• what you the database professional can
do to produce a successful result.
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 21
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Your mission
Build a database that is a foundation for meeting business needs, documented or not:
- Deliver to the shared vision of desired project outcome
- Meet project objectives
- Manage data required to support in-scope processes
Design, deploy, and maintain a database that
- Stores and maintains:
- Data about business objects and events
- Attributes describing those objects and events
- Relationships among those objects and events
- Operates efficiently with the application being developed
Page 22
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Six tools for succeeding in database design
without solid requirements
Tool
Notes
Build rapport with business
Friendly (but still professional) contacts make it easier to
address tough questions when needed.
Build step by step in small chunks that deliver
business value
Rework, if needed, is easier and you can align plans with the
business step by step.
Track risks and issues
Tactfully, make sure everyone is aware of the business
questions that aren’t yet resolved.
Take time to normalize before designing
The normalized model translates to business objects, events,
attributes, and rules, making your app more likely to meet
business needs.
You don’t have time to skip documentation
Change happens: how can you change something when you
don’t remember why you did it?
Hold design reviews with business and
technical players
Make sure everyone understands and endorses database
design decisions.
Page 23
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Three Typical Scenarios
The Great Leap
• “We’re very excited about automating our business processes but we
don’t quite know what to expect. How will Irma keep the ledger up to
date when the new system automatically assigns customer numbers?”
Reckless Abandon
• “Budget and schedule are very tight. I need you to start coding as soon as
possible and not waste time on front-end documentation”
Benign Neglect
• “Your business contacts are on 80% travel and will be very tough to reach.
Here’s our two page vision statement. That should give you everything
you need to build the new system.”
©©2010
2008
CapTech
All rights reserved.
CapTech
Ventures, Ventures,
Inc. All rights reserved.
Page 24
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
The Great Leap:
Modernizing Outdated Business Processes
“We’re very excited about automating our business processes but we don’t quite know what to expect. How
will Irma keep the ledger up to date when the new system automatically assigns customer numbers?”
Tool
Notes
Build rapport with business participants
Friendly (but still professional) contacts make it easier to address tough
questions when needed.
Build step by step in small chunks that deliver
business value
Replace the outmoded ledger last. Build trust and understanding by
starting with the easy chunks that make their life easier.
Track risks and issues
Make sure everyone is aware of the business questions that aren’t yet
resolved.
Take time to normalize before designing
Start database design by building your own “just good enough” logical
data model. Translate it to English before reviewing it with business
players.
You don’t have time to skip documentation
Change happens: how can you change something when you don’t
remember why you did it?
Hold design reviews with business and technical
players
Plan these carefully to limit the amount of new “jargon” you expose to
business participants.
Page 25
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Reckless Abandon:
When requirements are devalued
“Budget and schedule are very tight. I need you to start coding as soon as possible and not waste time on
front end documentation”
Tool
Notes
Build rapport with business participants
Friendly (but still professional) contacts makes it easier to address tough
questions when needed.
Build step by step in small chunks that deliver
business value
Rework, if needed, is easier and you can align plans with the business
step by step.
Track risks and issues
Use the risk and issue process as a way to flesh out requirements,
promote risk/issue management as a way to make sure the tight
project stays on track.
Take time to normalize before designing
The normalized model translates to business objects, events, attributes,
and rules, making your app more likely to meet business needs.
You don’t have time to skip documentation
Be careful to avoid any impression that you are “wasting time” in
documentation. Make it as informal and brief as possible, just good
enough for your successor to understand.
Hold design reviews with business and technical
players
Use design reviews as a forum for raising and resolving requirements
questions.
Page 26
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
Benign Neglect:
When business participants are not available
“Your business contacts are on 80% travel and will be very tough to reach. Here’s our two page vision
statement. That should give you everything you need to build the new system.”
Tool
Notes
Build rapport with business participants
Use collaborative tools like discussion boards, shared document storage,
and desktop sharing to enable remote participation. Adjust schedules
as needed to accommodate people in different time zones.
Build step by step in small chunks that deliver
business value
Rework, if needed, is easier and you can align plans with the business step
by step.
Track risks and issues
Tactfully, make sure everyone is aware of the business questions that
aren’t yet resolved.
Take time to normalize before designing
The normalized model translates to business objects, events, attributes,
and rules, making your app more likely to meet business needs.
You don’t have time to skip documentation
Document everything, include summaries for easy review and make
them accessible for remote participants.
Hold design reviews with business and technical
players
Schedule face to face reviews well in advance and plan them carefully to
make meetings valuable to all involved.
Page 27
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
The Business End
“The three major reasons that a project will succeed are
You can improve your chances of overcoming
user involvement, executive management support, and
missing or insufficient requirements by:
a clear statement of requirements.”
One key element of requirements describes the people,
places, and things of interest to the organization,
business rules governing them, the relationships among
 Building rapport with business participants
 Building in small chunks that deliver business value
 Tracking risks and issues
them, and their attributes. This statement of
requirements is the logical data model.
A detailed logical data model is a normalized database
pre-design.
 Taking time to normalize before designing
 Not skipping documentation
 Holding design reviews with business and technical
players
Page 28
©2010 CapTech Ventures, Inc. All rights reserved.
The Business End of Data Modeling, Bob Lambert, SQLSaturday#30, Richmond, VA, 1/30/2010
In conclusion
•For more on the business side of data modeling I
recommend Data Modeling for the Business by Steve
Hoberman et al. See www.stevehoberman.com.
•A surprisingly good reference on risk and issue management:
- http://office.microsoft.com/en-
us/project/HA100072491033.aspx
•This presentation is posted at http://robertlambert.net .
Questions welcome at [email protected] and
[email protected].
•Please rate this talk at www.speakerrate.com/boblambert12
Page 29
©2010 CapTech Ventures, Inc. All rights reserved.
Thanks for attending!
Bob Lambert
[email protected]
1419 West Main Street
Richmond, Virginia 23230
804.355.0511
©2010 CapTech Ventures
www.captechventures.com