Conceptual data modeling
the process of capturing the business requirements and showing the concepts graphically
Questions to ask during conceptual data modeling
Questions to ask during CDM:
- What is the problem?
- How do these concepts relate to each other?
- What is the scope?
- What are the key words and definitions?
basic & critical concepts
basic - industry common concepts (customers, employees, products)
critical - something unique (patients for hosptials)
5 Steps to Model Data
F.S.R.M.V - Five, Scope, Relational, Model, Validation
- ask 5 strategic questions
- identify scope
- basic & critical concepts
- business experts
- agree on defintions
- determine relationships between concepts
- relational and/or dimensional
- produce sketch according to busines rules
- 1:1, 1:M, M:M
- decide the most useful database model/type
- conceptually model database to show client
- axis-technique, network model, etc.
- review and confirm conceptual model (validators)
what comes first as-is or to-be
First As-Is, then To-Be
5 strategic questions
PAAAF - Purpose, As-Is, Audience, Analytics, Flexibility
Q1: What is the application going to do?
Q2: “As is” or “to be”?
Q3: Who is your audience?
Q4: Is analytics a requirement?
Q5: Flexibility or Simplicity?
Q1: What is the application going to do?
- Document the answer in max 3 sentences
- Check if you you replacing an exiting systems, or creating something new, or just new functionalities
- Always begin with the end in mind
Q2: “As is” or “to be”?
- Document the business as-is
- Improve the current business (new model) to-be
Can be both…
Q3: Who is your audience?
- Who are the validators?
- Who are the end-users?
- Who are the business experts?
Q4: Is analytics a requirement?
- Example: requesting the Inventory Count, and view it by day or by year
Q5: Flexibility or Simplicity?
flexible - work in various situations
simplistic - needs to be very specific
- For example:
- A flexible system might capture Events, Staff
- A simple system might call them Orders, Warehouse_employees
step 3 in data modeling: relationships (relational, dimensional)
- A OPERATIONAL system automates one or more business process. For example if a claim is automatically processed for an insurance policy, this is an operational system
- A REPORTING system uses the data form one or more operational systems to produce and EVALUATION of how part of the organization are performing
concept table
capturing RELATIONAL concepts
WHO – employees, patients, customers, competitors
WHAT – What are the things of importance to the business? This is the product or service of interest to the enterprise (what the business sells - cars, maintenance, consulting)
WHEN – When is the business in operation? Determine the calendar or time interval in which this concepts is valid (monthly, minutes, …)
WHERE – Where is the business conducted? (email, distribution point, URL)
WHY – Why is the business in business? These events that define the busines: transactions and events - orders, returns, enquiries, claims
HOW – How does the business keep track of events? Documentation - invoices, contracts, agreements
tools for modeling relational vs. dimensional
relational - concept table
dimensional - grain matrix (list the questions: questions, users)
dimensional: list the questions
determine the specific questions that must be answered, and who will ask them
grain matrix
a spreadsheet where
- the measures from the business questions become columns and
- the dimensional levels from each question become rows.
This allows us to see how questions are related to each other
