Dimensional Modelling by Example

Dimensional Modelling by Example

Barry Williams
barryw@databaseanswers.org

Page 1

04/09/2014 13:02

Dimensional Modelling by Example

1. Management Summary ............................................................................................................ 5

1.1 The Purpose of this Book................................................................................................... 5
1.2 The Contents of this Book ................................................................................................. 5
1.3 Three Stages .......................................................................................................................... 5
1.4 What are Dimensional Models ? ..................................................................................... 5
1.5 Data Marts............................................................................................................................... 6
1.6 Best Practice ........................................................................................................................... 7
1.7 Types of Dimensional Models .......................................................................................... 7
1.8 How to use the Dimensional Models ........................................................................... 12
2. Getting Started .......................................................................................................................... 12
2.1 Design Guidelines - a Four-Step Approach .............................................................. 12
2.2 Always use Surrogate Keys ............................................................................................ 12
2.3 Agree an Architecture ....................................................................................................... 13
2.4 Conceptual Data Models .................................................................................................. 18
2.5 Using Dimensional Models for Reports....................................................................... 21
2.6 Dates and Flattened Hierarchies .................................................................................. 22
3. Reaching Maturity ..................................................................................................................... 23
3.1 Semantic Layer ................................................................................................................... 23
3.2 Self-Service BI..................................................................................................................... 25
3.3 Churn - Analysing Churn Rate ....................................................................................... 25
3.4 Promotions Analysing Promotions Effectiveness ................................................ 27
3.5 Conformed Dimensions .................................................................................................... 29
3.6 Systems and Design Patterns ........................................................................................ 30
3.7 Add new Requirements .................................................................................................... 31
4. Keeping Things Ticking Over ................................................................................................ 31
4.1 Governance ........................................................................................................................... 31
Page 2

04/09/2014 13:02

Dimensional Modelling by Example

4.2 Data Quality.......................................................................................................................... 31
4.3 User Involvement ............................................................................................................... 31
Appendix A. Library of Dimensional Models ......................................................................... 32
A.1 Advertising ............................................................................................................................ 32
A.2 Afghanistan Encounters ................................................................................................... 34
A.3 Airline Operations............................................................................................................... 36
A.4 American Studies ............................................................................................................... 38
A.5 Amusement Parks .............................................................................................................. 40
A.6 Anti Money-Laundering .................................................................................................... 42
A.7 Banking Investment ...................................................................................................... 44
A.8 Banking Retail.................................................................................................................. 46
A.9 Boy Scouts ............................................................................................................................ 48
A.10 Clown Registry .................................................................................................................. 50
A.11 Commercial Properties .................................................................................................. 52
A.12 Cruise Ships ....................................................................................................................... 54
A.13 Customers and Car Parts .............................................................................................. 56
A.14 Day at the Olympics ....................................................................................................... 58
A.15 Dog the Bounty Hunter ................................................................................................. 60
A.16 Dog Whisperer .................................................................................................................. 62
A.17 Financial Services ............................................................................................................ 64
A.18 Football ................................................................................................................................ 66
A.19 e-Commerce ..................................................................................................................... 68
A.20 Entertainment ................................................................................................................... 70
A.21 Event Processing .............................................................................................................. 72
A.22 Golf Memorabilia .............................................................................................................. 74
A.23 Gym Training Diaries...................................................................................................... 76
A.24 Hotel Reservations .......................................................................................................... 78
Page 3

04/09/2014 13:02

Dimensional Modelling by Example

A.25 Insurance ............................................................................................................................ 80
A.26 Library Donations ............................................................................................................ 82
A.27 Local Government ........................................................................................................... 84
A.28 Logistics ............................................................................................................................... 86
A.29 Pharmaceutical Companies .......................................................................................... 88
A.30 Pool Hall Management ................................................................................................... 91
A.31 Property Tax Appeal ....................................................................................................... 93
A.32 Public Transport ............................................................................................................... 95
A.33 Puppies Tricks ................................................................................................................... 97
A.34 Radio Stations ................................................................................................................... 99
A.35 Recycling and Garbage Collection ........................................................................... 101
A.36 Restaurant Guides ......................................................................................................... 103
A.37 Retail .................................................................................................................................. 105
A.38 Student Registration ................................................................................................... 107
A.39 Telecomms Companies .............................................................................................. 109
A.40 Tracking Printer Cartridges ........................................................................................ 111
A.41 Traffic Cops and Tickets .............................................................................................. 113
A.42 Travel and Transport .................................................................................................... 115
A.43 Usual Suspects ............................................................................................................... 117
A.44 Utilities ............................................................................................................................... 119
A.45 Vulnerable People .......................................................................................................... 121
A.46 Waste Management ...................................................................................................... 123
A.47 Wedding Parties ............................................................................................................. 125
A.48 Wine .................................................................................................................................... 127
A.49 Yakuza Japanese Organised Crime ..................................................................... 129
A.50 Zoos .................................................................................................................................... 131

Page 4

04/09/2014 13:02

Dimensional Modelling by Example

1. Management Summary
1.1 The Purpose of this Book
The purpose of this book is to offer an introduction to Dimensional Modelling.
It contains some background and theory and a Library of fifty Industry-specific Models to
help you get started in creating something specific to your requirements.
I also teach a Course and provide consulting services based on the contents of his Book.
If you have questions or need help, please feel free to email me.

1.2 The Contents of this Book

The contents of this book include all the major topics of interest in the area of Dimensional
Modelling.

Conformed Dimensions

Fact Tables

Factless Facts

Self-Service BI

Dimension Modelling

Accumulative
Snapshots

Snapshots

Aggregate
Snapshots

1.3 Three Stages

These Topics are covered in three Stages which are covered later in this Book :1. Getting Started
2. Reaching Maturity
3. Keeping Things Ticking Over
1.4 What are Dimensional Models ?
The concept of Dimensional Modelling was developed by Ralph Kimball.in response to a
demand from end-users for an easy way to specify Reports.
This contrasted with the alternative Corporate Information Factory, the vision of
Bill Inmon.

Page 5

04/09/2014 13:02

Dimensional Modelling by Example

This diagram shows a Dimensional Model that Ralph Kimball has published on his Web Site
for a Retail Point of Sales :

http://www.kimballgroup.com/1997/08/02/a-dimensional-modeling-manifesto/

It shows very clearly the approach which can be described very simply as The data that can
be measured are called the Facts and they are stored with the things that can be measured
by, which are called the the Dimensions.
In this simple example. Kimball has kindly given all the Dimensions names ending
Dimensions, and there are three Facts that are called Dollars Sold, Units Sold and Dollars
Cost.

1.5 Data Marts

We use the term Data Mart as an alternative to Dimensional Model.
We consider that they both mean the same but we sometimes use Data Mart in
a way that might include more than one Dimensional Model, especially for a
functional area, such as Sales.
We refer to the majority of our Models as Dimensional Models, but occasionally
we call them Data Marts which we prefer because it is more flexible.

Page 6

04/09/2014 13:02

Dimensional Modelling by Example

1.6 Best Practice

Where appropriate, we consider the Kimball Web Site as our definitive source of
Best Practice in Data Modelling :

http://www.kimballgroup.com/

If you are looking for a good background, this page on Dimensional Modelling
Techniques on the Kimball Web Site is highly recommended :

http://www.kimballgroup.com/data-warehouse-business-intelligenceresources/kimball-techniques/dimensional-modeling-techniques/

Wikipedia is usually worth a look, and here is the entry for Dimensional
Modelling :

http://en.wikipedia.org/wiki/Dimensional_modeling

An excellent writer is Chris Adamson who has published a great book called Star
Schema :

http://www.amazon.com/s/ref=nb_sb_noss/188-67989686571923?url=search-alias%3Daps&fieldkeywords=chris%20adamson%20star%20schema

As a backup reference, we use Discussion Groups in LinkedIn like this one on

DW Dimensional Modelling :

https://www.linkedin.com/groups?gid=1435647&trk=vsrp_groups_res_na
me&trkInfo=VSRPsearchId%3A593656941409771308466%2CVSRPtarget
Id%3A1435647%2CVSRPcmpt%3Aprimary

In LinkedIn, one good thing is that we can ask specific questions if we are
seeking a majority view of Best Practice or Recommended Guidelines.

1.7 Types of Dimensional Models

We can identify five distinct types of Dimensional Models which are discussed below :

Accumulating Snapshot Tables

Aggregate Tables

Fact Tables

Factless Fact Tables

Snapshot Tables

Page 7

04/09/2014 13:02

Dimensional Modelling by Example

1.7.1 Accumulating Snapshot Tables

A common example involves the use of a Stage or Status Dimension which is used to track
progress through the Snapshots.
In the first example, we have added a Stage Dimension and we add records at the
successive Stages of a Passengers progress
1.7.1.1 Airline Example
This example of an Accumulating Snapshot Fact Table for Airline Operations shows how we
can track the progress of a Passenger.
http://www.databaseanswers.org/data_models/airline_operations/accumulating_snapshot.htm

Page 8

04/09/2014 13:02

Dimensional Modelling by Example

1.7.1.2 Student Registration Example
Here we have added a Registration Stage Dimension to help us track registration with the
help of this Accumulating Snapshot.
http://www.databaseanswers.org/data_models/student_registration/accumulating_snapshot.htm

1.7.2 Aggregate Facts

Aggregates are created in response to the requirements of end-users.

For example, Averages, Counts and Totals.
We define these three as default in all our Fact Tables, as well as data for Key Performance
indicators (KPIs). Graphs and Trends
Page 9

04/09/2014 13:02

Dimensional Modelling by Example

1.7.3 Fact Tables

Fact Tables are the most common type and the majority of our Dimensional Models are Fact
Tables.
They store Dimension Data and Fact Data.
1.7.4 Factless Fact Tables

A Factless Fact is one that has no data associated with it. In other words, it has Dimensions
but no Facts.
A common example is an Event, where the occurrence of the Event is itself a Fact.
Such as this Data Mart for Student Registration that we show here.
http://www.databaseanswers.org/data_models/student_registration/student_registration_data_mart_model.htm

Page 10

04/09/2014 13:02

Dimensional Modelling by Example

1.7.5 Snapshot Tables

Snapshot Tables record historic data at periodic intervals, such as Day, Week or
month.
Here we show a Monthly Snapshot for Customers and Car Parts.
http://www.databaseanswers.org/data_models/customers_and_car_parts/monthly_snapshot.htm

Page 11

04/09/2014 13:02

Dimensional Modelling by Example

1.8 How to use the Dimensional Models
In addition to Dimension Models, we have included Entity-Relationship Diagrams,
which we show simply as ERD.
For each ERD we have added a brief description of the Business Rules that define
the Entities or Things of Interest and how they are related.
This is very important because it helps the end-user to understand the kind of
data that is available in a way that is easy and natural to understand.

The first step in applying these Models to your own situation is

Review the Business Rules

Modify the ERD to reflect any changes you make to the Business Rules.

Make the corresponding changes to the Dimensional Models.

2. Getting Started
2.1 Design Guidelines - a Four-Step Approach
Guideline : Follow Plan to establish controlled growth in your dimensional model.
Here is one that is triggered by an Event or a Business Process
1. Establish the users requirements.
2. Determine the grain of the data
3. Identify the Dimensions
4. Identify the Facts
2.2 Always use Surrogate Keys
Guideline : Always use Surrogate Keys for Dimensional Models.
Ralph Kimball (The father of Dimensional Modelling) has published 10 Rules of Dimensional
Modelling.
Number 8 states
Make certain that dimension tables use a surrogate key
He has published a note on Surrogate Keys :

http://www.kimballgroup.com/1998/05/surrogate-keys/

where he states that surrogate keys are essential for joining data in Fact Tables and
Dimension Tables.
In other words, without Surrogate Keys there would be no Dimensional Models.
They are a powerful technique and also offer excellent performance.

Page 12

04/09/2014 13:02

Dimensional Modelling by Example

2.3 Agree an Architecture

This requires consensus on a Layered Data Architecture and Components.

2.3.1 Data Architecture for the Semantic Layer

The Semantic Layer supports Self-service.

This Architecture answers the question :What is the role of the Semantic Layer ?

BI Layer

Semantic Layer (Map technical to business

Terms, Glossary, Report Catalogue, etc.)

Top-Level Data Model

Dimensional Models
/ Data Marts

Data Warehouse

Staging Area

Data Source
Page 13

04/09/2014 13:02

Dimensional Modelling by Example

2.3.2 Subject Areas

Here we discuss the use of Subject Areas as a techniques for designing Dimensional Models.
We take the simple example of Customers, Products and Revenue.
This analysis shows that the three dominant Subject Areas are :1. Customers
2. Products
3. Revenue from Sales of Products to Customers.
Customers

Products

Revenue

The Dimensional Models reflect these three Subject Areas.

Page 14

04/09/2014 13:02

Dimensional Modelling by Example

2.3.3 Conformed Dimensions

Conformed Dimensions are shared between Tables and must have the same values in
order for Data to be retrieved satisfactorily.
For example, date-stamped data in two tables must all be at the same level of
granularity for example, Days, Weeks or Months.
Conformed Dimensions are therefore very important and are frequently Reference Data
(such as Calendars) or Master Data (such as Products).

Ralph Kimball (the father of Dimensional Modelling) has defined 10 Essential Rules : http://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/
Rule Nr. 9 states :Create conformed dimensions to integrate data across the enterprise.

Customer
Customer
Daily Snapshot

Conformed
Dimensions

Customer

Products

Product Sales
Daily Snapshot

Addresses

Locations

Revenue

Product Sales
Facts

Page 15

Invoice
Fact

Invoice
Snapshot

Revenue
Monthly
Fact

04/09/2014 13:02

Dimensional Modelling by Example

This diagram shows an example of Conformed Dimensions in a Customer Sales situation.
These are the three Dimensions that are common (ie Conformed) to the two Dimension Models
Customer, Customer Segment and Product.

Customer

Contract Daily
Snapshot

Customer
Segment

Product Sales
Daily Snapshot

Product (MDM)

This diagram shows an example of Conformed Dimensions from the Data Mart from our Day at the
Olympics.
http://www.databaseanswers.org/data_models/a_day_at_the_olympics/data_marts_with_conformed_dimensions.htm

This Data Mart features Conformed Dimensions for Calendar, Customers and Sports.

2.3.4 Dimension Model Template

Dimensional Models are characterised by a Surrogate ID as the Primary Key.

Without Surrogate ID Dimensional Models would not exist.
Page 16

04/09/2014 13:02

Dimensional Modelling by Example

Dimensional Models follow a generic design, based on Dimensions and Facts,
where the dimensions and Facts are listed alphabetically :-

Fact Table Name

Surrogate ID (PK)
Dimension 1 Entity

Dimension 1 (FK)
Dimension 2 (FK)

Dimension 1 (PK)
Dimension 1 Details

Dimension 3 (FK)
More Dimensions
Fact 1
Fact 2
Fact 3
More Facts

Page 17

04/09/2014 13:02

Dimensional Modelling by Example

2.4 Conceptual Data Models
Conceptual Data Models are very important for helping the end-user to
understand the data that is available and the basic relationships.
They complement the Dimensional Models.
Conceptual Model are a very powerful technique to answer the question :What Data is available in the Semantic Layer ?

2.4.1 Conceptual Data Model for Airline Operations

This is specialty designed to be a User-Friendly Model that shows the structure

of the data that is available to support Analytics, Reports and BI requirements.
It can be described as follows :Passengers make Reservations for Scheduled Flights operated by Airlines.
They take Flights and make Payments which generate Revenue.

Airlines

Passengers

Reservations

Scheduled Flights

Flights

Payments

Revenue
Page 18

04/09/2014 13:02

Dimensional Modelling by Example

2.4.2 Conceptual Data Model for a Retail business

This is also specialty designed to be a User-Friendly Model.

This one can be described as follows :Customers go to Stores and purchase Branded Products for which they make
Payments which generate Revenue.

Customers

Brands

Stores

Products

Purchases

Payments

Revenue

Page 19

04/09/2014 13:02

Dimensional Modelling by Example

2.4.3 Top-Level Data Model for a Telecomms business

This one applies to a Telecomms Business and can be described as follows :Customers sign up for Subscriptions and then take out Contracts (or
Agreements) to purchase Products which are made available through Offerings.
They then use Networks which generates Revenue.

Customers

Products

Subscriptions

Offerings

Agreements

Network Usage

Revenue

Page 20

04/09/2014 13:02

Dimensional Modelling by Example

2.5 Using Dimensional Models for Reports
2.5.1 A Data Warehouse for Airport Management

This shows the Data Model for a Data Warehouse.

In this Section, we discuss the Report that can be produced from this Data Warehouse.
http://www.databaseanswers.org/data_models/airport_management/event_driven_data_warehouse.htm

Page 21

04/09/2014 13:02

Dimensional Modelling by Example

2.5.2 A Basic Report

We can produce a count of Aircraft landing by Airline.

2.5.3 Comparing Scheduled against Actual Landings

If we want to produce a Report that compares Scheduled against Actual Landings we add
data from the Flight Schedules Table.

2.6 Dates and Flattened Hierarchies

We normally show Dates as an Entity called Calendar.
Because it is reference data, we name the Entity Ref_Calendar :-

When it is appropriate, we then expand normally show Dates as a Flattened Hierarchy like this :-

Page 22

04/09/2014 13:02

Dimensional Modelling by Example

3. Reaching Maturity
3.1 Semantic Layer
This is a good point at which to consider introducing a Semantic Layer.
The primary purpose of a Semantic Layer (SL) is to translate technical terms to
business terms which are familiar to end-users.
For example, Churn Rate instead of Deactivation Count divided by Customer
Count.
Providing Self-Service BI makes it essential to provide a Semantic Layer.
This diagram shows the Layered Data Architecture that we use as a point of
reference.
This role clarifies the Role of the Semantic Layer.
BI Layer (Analytics,
Dashboards, Reports)

Semantic Layer
(Mapping Business
to Technical Terms

Dimensional Models /
Data Marts

Data Warehouse

Business Objects pioneered the SL concept, and in a Business Objects

environment, a Semantic Layer (SL) can easily be implemented.

Page 23

04/09/2014 13:02

Dimensional Modelling by Example

3.1.1 A Telecomms Semantic Layer

A Semantic Layer for a typical Telecomms business is shown on this page :http://www.databaseanswers.org/data_models/telecomms/components_in_a_semantic_layer.htm

It has three Data Marts :1. Customers

2. Network Activity
3. Revenue
The Customers Data Mart includes Churn data and the Network Activity includes Promotions
data.
We have used this data in the Self-Service BI that we discuss in the next Section.
This shows the Churn Conceptual Model :-

This shows the Promotion Conceptual Model :-

Page 24

04/09/2014 13:02

Dimensional Modelling by Example

3.2 Self-Service BI

Self-Service BI is a facility provided to end-users which allows them to

select the data they want and the Dashboard or Report they would like to
use to see their data displayed.
They can do this without depending on IT.
Here we discuss how Dimensional Models can be used to provide data for
Self-Service BI.
A Semantic Layer is an important part of Self-Service BI.
It includes a Glossary of Terms, a Report Catalogue and a Semantic Layer.
3.3 Churn - Analysing Churn Rate
* http://www.databaseanswers.org/data_models/retail_customers/customers_area_model.htm
it is proposed to set up a small application using a Key Performance Indicator (KPI) to monitor a
Churn Rate.
The KPI will have a defined Threshold value and when this value is reached an Alert will be sent to a
nominated end-user in the form of an email message.
This email can be received on a mobile phone so that the end-user can be playing golf when he
receives the email.
This shows how the principle of Management by Exception can be applied using todays technology.

3.3.1 What is a Churn Rate ?

A Churn Rate is typically calculated as the total number of Deactivations (the Deactivation Count) in
a given period, divided by the total number of Customers (the Customer Count).
Page 25

04/09/2014 13:02

Dimensional Modelling by Example

3.3.2 Specifications

These Specifications will be reviewed and agreed or modified by the end-user.

It is proposed that the Churn Rate will be monitored on a regular basis, such as daily or in real-time.
Step 1) Define KPI and Threshold

Step 2) Define End-User and Email

Step 3) Monitor KPI and send Email

Step 4) User responds to Email

Step 5 User (optionally) Drills Down

The Self-Service supports the steps defined above, including :1. Define Key Performance Indicator as the Churn Rate with a user-specified Threshold value
such as 20%.
2. Define end-user and email address.
3.3.3 The Data
3.3.3.1 Demonstration data

A small set of demonstration data will be created , following this template :Date and Time

KPI Value

Threshold
Value

Red/Amber/Green

Action

09:00 Aug/01/ 2014

10:00 Aug/01/ 2014
11:00 Aug/01/ 2014
11:00 Aug/01/ 2014

5%
10 %
15 %
20 %

20%
20%
20%
20%

Green
Green
Green
Red

Do nothing
Do nothing
Do nothing
Send Email to end-user

3.3.3.2 Drill-down data

If it is decided to provide a drill-down facility, the required data will need to be provided.

Page 26

04/09/2014 13:02

Dimensional Modelling by Example

3.4 Promotions Analysing Promotions Effectiveness
3.4.1 Approach

We use the Layered Data Architecture in Chapter 5 to identify the activities

involved in his Case Study.

This shows the Layered Data Architecture that we use as a point of
reference.se it.
This shows exactly how we use it.
BI Layer Analytics,

Check what data is available.

Dashboards, Reports

Add what is necessary.

Comply with format, content.

Semantic Layer
Mapping Business
to Technical Terms

Dimensional Models

Add what is necessary,

Comply with 2.1.1 Guidelines.
Enhance Data Models,
comply with Governance

Data Warehouse

3.4.2 Purpose

The purpose of this Case Study is to provide a walk-through to show how to solve a specific BI
requirement.
The requirement that we have chosen is to analyse the effectiveness of a Promotion.
3.4.3 Steps

Step 1. Determine the sources of the required data.

Step 2. If it is in the Data Warehouse, then identify the Tables that will be involved.
Step 3. Add Tables where required.
Step 4. If it is not in the Data Warehouse, then identify the Data Sources and ensure good
quality and availability.
Step 5. Identify the Dimensional Models involved.
Step 6. Add Dimensions and Facts where required.

Page 27

04/09/2014 13:02

Dimensional Modelling by Example

3.4.4 Tables in the Data Warehouse

Here we have added a Table called Product Promotion which has a Foreign Key
relationship to the Product Table.
Guidelines : Maintain the design approach of the existing Third-Normal Form Data
Warehouse, with a surrogate ID field for the Primary Key.

Barrys Product Promotion_

Product_Promotion_Id

Product
Product Id

Product Id (FK)
Date_From
Date_To
Product_Price_Percentage_Reduction
Promotion_Description
Promotion_Objectives
Promotion_Achievements
Other_Promotion_Details

Product Name
Product Desc
SAP Offering Id (FK)
SAP Product Id (FK)
Financial Material Id (FK)

3.4.5 Conceptual View of the Product Sales Daily Snapshot

Here we show a User-Friendly version of the Table shown above.

It tells us that we can analyse any of the Facts by any combination of the Dimensions.
In particular, we can analyse sales of products in periods when there was a Promotion and periods
without a Promotion.

Product Sales Daily Snapshot

DIMENSIONS
Contract Status
Contract
Customer
Customer Segment
Customer Type
Date
Month
Product
Promotion
Retail Outlet
Product Brand
Payment Type
Payment Type Group
FACTS
Contract Activation Count
Contract Deactivation Count
Sales Amount
Sales Value

Page 28

04/09/2014 13:02

Dimensional Modelling by Example

3.5 Conformed Dimensions

Here we analyse the Subject Areas and Dimensional Models to analyse common patterns.
This helps us in the very valuable task of determining Conformed Dimensions.
3.5.1 Components for each System

Nr. System A

System B

System C

Comments

1)
2)

Yes
No

Yes
No

Yes
50%

All three say they have a Semantic Layer but none do.
A list of user-friendly terms is available but without
mapping, it is of limited value.

3)
4)
5)

No
No
Yes

No
No
Yes

Yes
No
Yes

Essential for a Semantic Layer

Consistency will be checked

3.5.2 Dimensional Models Consistency

Code

Nr.

System A

CDS
CMS
RMF
RMF
SDS
SPDS

Customer Daily Snapshot

Consumer Monthly Snapshot
Network Usage Daily Snapshot
Revenue Monthly Fact
Subscriptions Daily Snapshot
Subscriptions Product Daily Snapshot

Yes
Yes

System B

System C

Yes
Yes
Yes
Yes

3.5.3 Dimensions

Dimension
Agreement
Business Area
Calendar Date
Consumer
Customer
Customer Activation
Customer Business Area
Customer Demographic Segment

Customer Segment
Customer Segment Hierarchy
Customer Type
Location
Revenue Segment
Product
Contract
Contract Brand
Contract Business Area
Contract Customer

Yes
Yes

Yes

Yes

Yes

Yes
Yes
Yes
Yes
Yes
Yes
Yes

Yes

Yes

Yes

Yes

Yes
Yes

Yes
Snowflake

Yes
Yes

Yes
Yes

Yes

Yes

Page 29

Yes
Yes
Yes
Yes

04/09/2014 13:02

Dimensional Modelling by Example

Contract
Contract
Contract
Contract

Payment Type
Segment
Type Group
Type Offering

Yes
Yes
Yes
Yes

3.6 Systems and Design Patterns

Here we review the commonality among the three Systems A,B and C.
Specifically we look at the Conceptual Model and the corresponding ERD.
This contributes to establishing a Single View of the Truth.

3.6.1.2 Mapping System A to Conceptual Model

This shows how System A shown above maps to our Top-Level Conceptual Model in Section A.1 in
this Appendix.
The matching Entities are shown in light blue.

Brands

Customers

Subscriptions

Products

Contracts

Product Usage

Revenue (Invoice)

Page 30

04/09/2014 13:02

Dimensional Modelling by Example

3.7 Add new Requirements
This contributes to establishing a Single View of the Truth.
Review the Conceptual Model and the corresponding ERD.

4. Keeping Things Ticking Over

4.1 Governance
It is time to consider Governance now that a stable state has been reached,
where users are receiving standard off-the-shelf Reports and a thriving SelfService user community is able to meet its own demands for Reports and
analytics.
This will involve appointing Data Stewards with assigned responsibilities.
For example, Scripts for creating Dimensional Models with sign-off from a
designated individual, and for checking values for a Single View of the Truth
from the Data Warehouse.

4.2 Data Quality

This includes Profiling and Referential Integrity.
If the Data Warehouse is considered to hold good quality data then it might still
be appropriate to consider putting in place some minimal checks.
For example, checking the min and max for Agreement start and end dates can
be done (relatively) quickly and with simple SQL Scripts.

4.3 User Involvement

At this stage, an active user community should exist and frequent meetings
should take place between users and IT.
The role of IT should include monitoring user activity and advising on situations
that might represent an unacceptable load on a Server.

Page 31

04/09/2014 13:02

Dimensional Modelling by Example

Appendix A. Library of Dimensional Models

In this Appendix, we list Data Models for fifty different Industry Sectors.
We have included links to the Data Models on our Database Answers Web Site :http://www.databaseanswers.org/data_models/index.htm

A.1 Advertising
A.1.1 ERD

http://www.databaseanswers.org/data_models/advertising_systems/index.htm

Page 32

04/09/2014 13:02

Dimensional Modelling by Example

A.1.2 Business Rules

1. Advertisements are placed by Agencies.

2. Advertisements are placed by Channels, such as TV or Print.
3. Advertisements can be part of a Marketing Campaign.
4. Invoices are produced and result in Payments.
5. Advertisements result in Responses.
6. Responses can be analysed by Demographics.

A.1.3 Dimensional Model

http://www.databaseanswers.org/data_models/advertising_systems/advertising_systems_data_mart.htm

Page 33

04/09/2014 13:02

Dimensional Modelling by Example

A.2 Afghanistan Encounters
A.2.1 ERD
http://www.databaseanswers.org/data_models/afghanistan_encounters/index.htm

Page 34

04/09/2014 13:02

Dimensional Modelling by Example

A.2.2 Business Rules

1. The System records details of Afghans and their encounters with the Military.
2. It also records details of links between Afghans and Terrorist Organisations.
3. It also records details of Afghans, their Associates and Meetings with Associates.

A.2.3 Dimensional Model

http://www.databaseanswers.org/data_models/afghanistan_encounters/data_mart.htm

Page 35

04/09/2014 13:02

Dimensional Modelling by Example

A.3 Airline Operations

A.3.1 ERD

This Data Model adopts an Event-oriented Approach that is based on our Canonical Data Model :http://www.databaseanswers.org/data_models/canon_data_models/index.htm

The ERD is on this page :http://www.databaseanswers.org/data_models/airline_operations/index.htm

A.3.2 Business Rules

1. The System records Customer Events involved in Airline Operations.

2. The first Event is that the Customer Makes a Reservation.
3. Then the Customer Checks In, Boards an Aircraft and is then In Flight.
4. Finally, the Customer Disembarks.
This Event-Oriented approach is very flexible.
This starting-point can be extended horizontally i.e. more Customer-related detailed
Events, or Horizontally, to include more details of the events already recorded.

Page 36

04/09/2014 13:02

Dimensional Modelling by Example

A.3.3 Dimensional Model

http://www.databaseanswers.org/data_models/airline_operations/dimensional_model.htm

Page 37

04/09/2014 13:02

Dimensional Modelling by Example

A.4 American Studies

A.4.1 ERD

http://www.databaseanswers.org/data_models/american_studies/index.htm

A.4.2 Business Rules

1. Topics are taught by Teaching Institutions at a number of Places..

2. People sign up to Study Topics.
3. A range of Topics are available, that share an overall structure, shown by the Inheritance
Relationship and the Study Topics Super-Type.
4. Each topic can also have some characteristics that are specific to itself.
They are called Children or Sub-Types of the Inheritance Relationship.

Page 38

04/09/2014 13:02

Dimensional Modelling by Example

A.4.3 Dimensional Model

http://www.databaseanswers.org/data_models/american_studies/data_mart.htm

Page 39

04/09/2014 13:02

Dimensional Modelling by Example

A.5 Amusement Parks

A.5.1 ERD
http://www.databaseanswers.org/data_models/amusement_parks/index.htm

A.5.2 Business Rules

1. Rides are available.

2. Customers use Credit or Debit Cards to buy Tickets to Ride.
3. Staff operate the Rides.
4. Staff also work on Maintenance Schedules for the Rides.

Page 40

04/09/2014 13:02

Dimensional Modelling by Example

A.5.3 Dimensional Model
http://www.databaseanswers.org/data_models/amusement_parks/data_mart.htm

Page 41

04/09/2014 13:02

Dimensional Modelling by Example

A.6 Anti Money-Laundering
A.6.1 ERD
http://www.databaseanswers.org/data_models/anti_money_laundering/index.htm

A.6.2 Business Rules

1. People have Associates.

2. Organisations have Associates.
3. People are involved with Organisations.
4. People and Organisations engage in Transactions that involve Accounts.
5. They play specific Roles in these Transactions.

Page 42

04/09/2014 13:02

Dimensional Modelling by Example

A.6.3 Dimensional Model

http://www.databaseanswers.org/data_models/anti_money_laundering/data_mart.htm

Page 43

04/09/2014 13:02

Dimensional Modelling by Example

A.7 Banking Investment
A.7.1 ERD for General Deals
http://www.databaseanswers.org/data_models/investment_banking/deals_general.htm

A.7.2 Business Rules

1. The focus of this Model is Deals.

2. Deals involve Customers and Staff
3. Deals generate Accounting Entries.
4. Deals result in Settlements from Deals.
5. The Deal General table stores details that are common to all Deals.
6. A Deal is of a particular Deal Type and separate tables hold details for FX Deals,
Financial Instrument Deals and so on.

Page 44

04/09/2014 13:02

Dimensional Modelling by Example

A.7.3 Dimensional Model

This is an example of a Data Mart for FX Deals.

http://www.databaseanswers.org/data_models/investment_banking/poc_fx_Deals_data_warehouse.htm

Page 45

04/09/2014 13:02

Dimensional Modelling by Example

A.8 Banking Retail

A.8.1 ERD

http://www.databaseanswers.org/data_models/retail_banks/top_level_data_model.htm

A.8.2 Business Rules

1. Banks operate Branches.

2. Banks and Branches have Addresses
3. Branches have Customers
4. Customers have Accounts.
5. Transactions take place on these Accounts.

Page 46

04/09/2014 13:02

Dimensional Modelling by Example

A.8.3 Dimensional Model

http://www.databaseanswers.org/data_models/retail_banks/top_level_data_model.htm
This Model features Conformed Dimensions for Accounts, Account Types and Calendar.

Page 47

04/09/2014 13:02

Dimensional Modelling by Example

A.9 Boy Scouts
A.9.1 ERD
http://www.databaseanswers.org/data_models/boy_scouts/index.htm

A.9.2 Business Rules

1. Scouts are young people who are usually associated with an Adult.
2. Scouts can achieve Awards.
3. Scouts make Attendance at Events.
4. Scouts can choose Elective activities.

Page 48

04/09/2014 13:02

Dimensional Modelling by Example

A.9.3 Dimensional Model

http://www.databaseanswers.org/data_models/boy_scouts/dimensional_model.htm

Page 49

04/09/2014 13:02

Dimensional Modelling by Example

A.10 Clown Registry
A.10.1 ERD

http://www.databaseanswers.org/data_models/clown_registry/index.htm

A.10.2 Business Rules

1. People registered as Clowns are stored in the Clowns Table.

2. A Clown can have Acts with specific Features.
3. Clowns accept Bookings to perform at specific Locations for specific Customers.

Page 50

04/09/2014 13:02

Dimensional Modelling by Example

A.10.3 Dimensional Model

http://www.databaseanswers.org/data_models/clown_registry/clown_data_mart.htm

Page 51

04/09/2014 13:02

Dimensional Modelling by Example

A.11 Commercial Properties
A.11.1 ERD
http://www.databaseanswers.org/data_models/commercial_properties/index.htm

A.11.2 Business Rules

1. Properties are Industrial or Retail.

2. Properties are at Locations.
3. Properties have a range of Property Features.
4. Properties are owned by Companies.
5. Real Estate Agents have Clients.
6. Clients sign up for Client Service Contracts involving Properties.
7. A Contract can involve a number of Transactions.
.

Page 52

04/09/2014 13:02

Dimensional Modelling by Example

A.11.3 Dimensional Model

http://www.databaseanswers.org/data_models/commercial_properties/dimensional_model.htm

Page 53

04/09/2014 13:02

Dimensional Modelling by Example

A.12 Cruise Ships

A.12.1 ERD
http://www.databaseanswers.org/data_models/cruise_ships/index.htm

A.12.2 Business Rules

1. Ships offer Cruises.

2. Staff are employed on Cruises.
3. Passengers buy Tickets for Cruises.
4. Passengers are allocated Places on Cruises.

Page 54

04/09/2014 13:02

Dimensional Modelling by Example

A.12.3 Dimensional Model

http://www.databaseanswers.org/data_models/cruise_ships/data_mart.htm

Page 55

04/09/2014 13:02

Dimensional Modelling by Example

A.13 Customers and Car Parts
A.13.1 ERD
http://www.databaseanswers.org/data_models/customers_and_car_parts/index.htm

A.13.2 Business Rules

1. Customers place Orders.

2. Orders contain one or many Parts.
3. Parts are specific to Brands.
4. Parts are ordered from Suppliers
5. Parts are available for Cars.

Page 56

04/09/2014 13:02

Dimensional Modelling by Example

A.13.3 Dimensional Model

http://www.databaseanswers.org/data_models/customers_and_car_parts/dimensional_model_pragmatic_style.htm

In this Dimensional Model, we have adopted the style recommended by a very active
Microsoft Partner called Pragmatic Works, based In Florida.
As you can see, their standard has Dimension Tables prefixed by Dim and Fact table
prefixed by Facts.

Page 57

04/09/2014 13:02

Dimensional Modelling by Example

A.14 Day at the Olympics
A.14.1 ERD
http://www.databaseanswers.org/data_models/a_day_at_the_olympics/index.htm

A.14.2 Business Rules

1. Our Day at the Olympics involved a number of Events.

2. The first Event was to Find my Seat in the competition hall.
3. The second Event was to Have Lunch.
4. The third Event was to Watch the Judo Competition.
We show an Inheritance Relationship with an Events Super-Type and the three SubTypes.

Page 58

04/09/2014 13:02

Dimensional Modelling by Example

A.14.3 Dimensional Model

http://www.databaseanswers.org/data_models/a_day_at_the_olympics/data_marts_with_conformed_dimensions.htm

This Model features Conformed Dimensions for Calendar, Customers and Sports.

Page 59

04/09/2014 13:02

Dimensional Modelling by Example

A.15 Dog the Bounty Hunter

A.15.1 ERD
http://www.databaseanswers.org/data_models/dog_the_bounty_hunter/index.htm

A.15.2 Business Rules

1. The Bounty Hunter is involved in Catching Fugitives.

2. Fugitives have Associates who play specific Roles.
3. A number of Individuals are involved in Catching Fugitives.
These Individuals are usually members of Dogs family.
4. Activities of Catching Fugitives are frequently the subject of TV Recordings.
5. TV Recordings become TV Recording Sales to TV companies around the world.

Page 60

04/09/2014 13:02

Dimensional Modelling by Example

A.15.3 Dimensional Model
http://www.databaseanswers.org/data_models/dog_the_bounty_hunter/dimensional_model.htm

If you want to see a larger size Model, just click on the link above.

Page 61

04/09/2014 13:02

Dimensional Modelling by Example

A.16 Dog Whisperer

A.16.1 ERD
http://www.databaseanswers.org/data_models/dog_whisperer/index.htm

A.16.2 Business Rules

1. The Dog Whisperer specialises in handling Dogs with Dog Problems.

2. Dogs are of specific Breeds.
3. Dogs have a specific State of Mind.
4. Dogs are diagnosed with one or many Problems.
5. Each Problem has possible Problem Treatments.
6. The Dog Whisperer uses some or all of these Treatments for a specific Dog.
7. Dogs belong to Clients.
8. The Dog Whisperer makes one or many visits to each Client.
9. These Visits and Dog Treatment can be made subjects of TV Recording.
10. TV Recordings become TV Recording Sales to TV companies around the world.
Page 62

04/09/2014 13:02

Dimensional Modelling by Example

A.16.3 Dimensional Model

http://www.databaseanswers.org/data_models/dog_whisperer/dimensional_model.htm
If you want to see a larger size Model, just click on the link above.

Page 63

04/09/2014 13:02

Dimensional Modelling by Example

A.17 Financial Services

A.17.1 ERD
http://www.databaseanswers.org/data_models/customers_and_financial_services/index.htm

A.17.2 Business Rules

1. Suppliers offer Products.

2. Products are either Banking or Insurance Products.
3. Banking Products can be either Retail or Investment.
4. Insurance Products can be either for Car, Home, Life or Pets.
5. Products are involved in Events.
6. These Events will always involve Customers.
7. They may also involve Locations and Staff.
8. Events might also involve Payments.
9. Events might also involve Documents.
10. Documents can be either Bank Statements or Insurance Policies.

Page 64

04/09/2014 13:02

Dimensional Modelling by Example

A.17.3 Dimensional Model
http://www.databaseanswers.org/data_models/customers_and_financial_services/dimensional_model.htm

Page 65

04/09/2014 13:02

Dimensional Modelling by Example

A.18 Football
A.18.1 ERD

We were pleased to find that our designs apply to both American Football (the
NFL) and Soccer (Football in the UK and elsewhere outside the US).
http://www.databaseanswers.org/data_models/football_clubs/index.htm

A.18.2 Business Rules

1. Football Clubs play Games.

2. Football Clubs have specific Uniforms
3. Football Clubs have specific Jargon, such a Fumble in the NFL..
4. Football Clubs have a range of Reference Material, such as Footballer Contracts.
Page 66

04/09/2014 13:02

Dimensional Modelling by Example

5. Games always involve two Football Clubs.
6. Games are played at Locations.
7. Games always produce Deliverables such as Goals and Results.
8. Football Clubs have Cub Personnel, including Players.
9. Players are involved in Games as Game Players.
10. Players in Games can play at specific Positions.
11. Games can involve Value Systems, such as Play by the Rules and Win.

A.18.3 Dimensional Model

http://www.databaseanswers.org/data_models/football_clubs/dimensional_model.htm

Page 67

04/09/2014 13:02

Dimensional Modelling by Example

A.19 e-Commerce
A.19.1 ERD
http://www.databaseanswers.org/data_models/e_commerce_shopping_carts/top_level_model.htm

Page 68

04/09/2014 13:02

Dimensional Modelling by Example

A.19.2 Business Rules

1. Customers may have one or many Shopping Carts.

2. A Customer may have zero or one Wish List.
3. Customers may have one or many Payment Methods available.
4. A Shipping Cart can contain one or many Order Items.
5. Order Items are delivered in Shipments.
6. An Invoice is generated for each Shipment.
7. Customers make Payments for Invoices using a Payment Method.

A.19.3 Dimensional Model

http://www.databaseanswers.org/data_models/e_commerce_shopping_carts/dimensional_model.htm

Page 69

04/09/2014 13:02

Dimensional Modelling by Example

A.20 Entertainment
A.20.1 ERD
http://www.databaseanswers.org/data_models/entertainment_top_level/index.htm

A.20.2 Business Rules

1. Suppliers put on Performances.

2. Performances can be either Music Festivals, Sports or Theater Shows.
3. These are Sub-Types of the Performances Super-Type and Inherit the Super-Type
characteristics.
4. Customers can make Performances Bookings at specific Locations.
5. Staff are involved in making these Performances Bookings.
6. Tickets are issued for Performances Bookings.

Page 70

04/09/2014 13:02

Dimensional Modelling by Example

A.20.3 Dimensional Model

http://www.databaseanswers.org/data_models/entertainment_top_level/dimensional_model.htm

Page 71

04/09/2014 13:02

Dimensional Modelling by Example

A.21 Event Processing
A.21.1 ERD
http://www.databaseanswers.org/data_models/complex_event_processing/index.htm

A.21.2 Business Rules

1. Events can involve zero, one or many People.

2. Events can occur in specific Patterns.
3. Patterns of Events are in specific Business Categories.
4. People can be involved in Actions and Events.
5. A People can play specific Roles in Actions and Events
6. Alerts are always associated with people.

Page 72

04/09/2014 13:02

Dimensional Modelling by Example

A.21.3 Dimensional Model

http://www.databaseanswers.org/data_models/complex_event_processing/dimensional_model.htm

Page 73

04/09/2014 13:02

Dimensional Modelling by Example

A.22 Golf Memorabilia

A.22.1 ERD

http://www.databaseanswers.org/data_models/golf_memorabilia/index.htm

Page 74

04/09/2014 13:02

Dimensional Modelling by Example

A.22.2 Business Rules

1. Memorabilia is associated with either Famous People, such as Tiger Woods, or Famous
Places, such as Pebble Beach.
2. Memorabilia Items can be in three major Memorabilia Types :* Golf Balls
* Golf Clubs
* Pottery, which has three Sub-categories
* Mugs
* Plates
* Steins
A.22.3 Dimensional Model
http://www.databaseanswers.org/data_models/golf_memorabilia/dimensional_model.htm

Page 75

04/09/2014 13:02

Dimensional Modelling by Example

A.23 Gym Training Diaries

A.23.1 ERD

http://www.databaseanswers.org/data_models/gym_training_diary/index.htm

Page 76

04/09/2014 13:02

Dimensional Modelling by Example

A.23.2 Business Rules

1. The My Profile table records details of an individuals personal details.

2. Entries in a Training Diary reflect the Training Schedules for an individual.
3. Exercises are recorded in the Training Diary.
4. Entries in a Training Diary also reflect the Level of Training, e.g. Beginner or Advanced.
5. Assessments are made at regular intervals to determine progress towards an individuals
Targets and Goals.
6.A Diet may be drawn up for an Individual.
7.A Diet is made up of specific Diet Items.
8. Measurements are made at dated intervals to record Targets and Goals and
Measurement details.
9. Details of Sessions are recorded in the Sessions Table.
10. A Diary is kept of other non-training details.
11. Logs are kept to log other details.

A.23.3 Dimensional Model

http://www.databaseanswers.org/data_models/gym_training_diary/data_mart.htm

Page 77

04/09/2014 13:02

Dimensional Modelling by Example

A.24 Hotel Reservations

A.24.1 ERD

http://www.databaseanswers.org/data_models/hotels/index.htm
This Model is created in Erwin which allows us to define Many-to-Many Relationships.
For example, we have defined a Many-to-Many between Hotels and Hotel Characteristics.
This means that a Hotel can have many Characteristics (such as a Gym and Internet access) and the
same Characteristics can be found in many Hotels.

Page 78

04/09/2014 13:02

Dimensional Modelling by Example

A.24.2 Business Rules

1. Hotels might (or might not) be in a Hotel Chain

2. Each Hotel is in a specific Country
3. Each Hotel has a Star Rating.
4. A Hotel can have many Characteristics (such as a Gym and Internet access).
5. A Characteristic can be found in many Hotels.
6. Hotels have a number of Hotel Rooms of designated Types.
7. Each Room Type has a Rate that applies for a specified Period of time.
8. Guests make Bookings
9. Bookings become Specific Room Bookings later, usually when Guests arrive at a Hotel.

A.24.3 Dimensional Model

http://www.databaseanswers.org/data_models/hotels/revenue_data_mart.htm

Page 79

04/09/2014 13:02

Dimensional Modelling by Example

A.25 Insurance
A.25.1 ERD

http://www.databaseanswers.org/data_models/insurance_policies_and_claims/index.htm

Page 80

04/09/2014 13:02

Dimensional Modelling by Example

A.25.2 Business Rules

1. Customers take out Policies for a specific period of time.

2. Each Policy is of a defined Policy Type, such as Car, Home or Life.
3.Claims can be made on each Policy.
4.A Claim goes through a series of Claims Processing Stages.
5. This might involve Employees, who might process Claims Documents.
6. Each Stage will have a Stage Outcome, such as Disputed, In Progress or Settled.
A.25.3 Dimensional Model
http://www.databaseanswers.org/data_models/insurance_policies_and_claims/claims_dimensional_model.htm

Page 81

04/09/2014 13:02

Dimensional Modelling by Example

A.26 Library Donations

A.26.1 ERD

http://www.databaseanswers.org/data_models/library_donations/index.htm

A.26.2 Business Rules

1. Donations are made by Donors to Libraries.

2. Donations are used to make Book Purchases from Sellers.
3. Every Book is of a specific Book Genre.
4. The Donations in Purchases table keeps track of the specific Books that are purchased by
each Donation.

Page 82

04/09/2014 13:02

Dimensional Modelling by Example

A.26.3 Dimensional Model
http://www.databaseanswers.org/data_models/library_donations/library_donations_data_mart.htm

Page 83

04/09/2014 13:02

Dimensional Modelling by Example

A.27 Local Government

A.27.1 ERD Model
http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/top_level_model.htm

A.27.2 Business Rules

1.The important focus here is Citizens.

2. Citizens have Addresses.
3. Citizens become Customers when they receive Services.
4. Services are a Super-Type with Education and Housing as examples of Sub-Types,

Page 84

04/09/2014 13:02

Dimensional Modelling by Example

A.27.3 Dimensional Model

http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/data_mart.htm

Page 85

04/09/2014 13:02

Dimensional Modelling by Example

A.28 Logistics
A.28.1 ERD Model

http://www.databaseanswers.org/data_models/logistics_and_shipments/index.htm

A.28.2 Business Rules

1. Customers place Orders.

2. Orders are delivered as Shipments.
3. Shipments are associated with Locations.
4. A Shipment contains one or many Products.
5. A Shipment consists of one or many Shipment Legs.
6. A Shipment Leg is associated with Shipment Leg Documents.
7. Shipment Leg Documents are of Standard Document Types, such as Bills of Lading and
Delivery Notes.

Page 86

04/09/2014 13:02

Dimensional Modelling by Example

A.28.3 Dimensional Model
http://www.databaseanswers.org/data_models/logistics_and_shipments/dimensional_model.htm

Page 87

04/09/2014 13:02

Dimensional Modelling by Example

A.29 Pharmaceutical Companies
A.29.1 ERD Model

http://www.databaseanswers.org/data_models/pharmaceutical_companies/index.htm

A.29.2 Business Rules

1. Pharmaceutical Companies manufacture Products in Factories.

2. Manufactured Products and Products from Suppliers are stored in Warehouses at specific
Locations.
3. Products are then delivered to Retail Outlets according to Delivery Schedules using
Delivery Vehicles.
4. Customers then go to Retail Outlets to purchase Products.
In summary, we can say Pharmaceutical Companies manufacture Products in Factories
that they store in Warehouses and that they deliver to Retail Outlets for Customers to
Purchase.

Page 88

04/09/2014 13:02

Dimensional Modelling by Example

A.29.3 Dimensional Model

http://www.databaseanswers.org/data_models/pharmaceutical_companies/dimensional_model.htm

Page 89

04/09/2014 13:02

Dimensional Modelling by Example

A.29.4 Alternative Dimensional Model

http://www.databaseanswers.org/data_models/pharmaceuticals_dw/index.htm

This is a simpler Data Warehouse for Pharmaceutical Sales.

It shows the common Dimensions of Customers, Products and Sales.
It would be very valuable for you to go through the steps of progressing from this basic
Model to the one shown above.
If you would like my comments on your work please feel free to email me at
barryw@databaseanswers.org.

Page 90

04/09/2014 13:02

Dimensional Modelling by Example

A.30 Pool Hall Management

A.30.1 ERD Model
http://www.databaseanswers.org/data_models/pool_hall_management/index.htm

A.30.2 Business Rules

1. Customers play at a Pool Hall.

2. They can be either Commercial or Personal Customers.
3. They can make Regular Bookings.
4. They book Tables by the hour.
5. They make Payments (or receive refunds) which are Financial Transactions using
Payment Methods.

Page 91

04/09/2014 13:02

Dimensional Modelling by Example

A.30.3 Dimensional Model

http://www.databaseanswers.org/data_models/pool_hall_management/data_mart.htm

Page 92

04/09/2014 13:02

Dimensional Modelling by Example

A.31 Property Tax Appeal

A.31.1 ERD Model
http://www.databaseanswers.org/data_models/property_tax_appeals/index.htm

A.31.2 Business Rules

1. Clients are involved in Matters that require assistance from Attorneys.

2. These Matters relate to Parcels of land in specific Counties.
3. Clients make Appeals about these Parcels.
4. Every Appeal has an Outcome
5. Every Appeal involves a range of Documents of specified Types.

Page 93

04/09/2014 13:02

Dimensional Modelling by Example

A.31.3 Dimensional Model

http://www.databaseanswers.org/data_models/property_tax_appeals/data_mart.htm

Page 94

04/09/2014 13:02

Dimensional Modelling by Example

A.32 Public Transport

A.32.1 ERD Model
http://www.databaseanswers.org/data_models/public_transport/index.htm

A.32.2 Business Rules

1. Passengers make Itinerary Bookings.

2. Payments are made for Bookings.
3. An Itinerary Booking can involve Itinerary Legs which are made up of Legs.
4. Timetables are published by Transport Operators
5. Timetables include Legs of planned Schedules.
6. Timetable Prices are also published.
7. Prices depend on Passenger Categories and Ticket Types.

Page 95

04/09/2014 13:02

Dimensional Modelling by Example

A.32.3 Dimensional Model

http://www.databaseanswers.org/data_models/public_transport/public_transport_data_mart.htm

Page 96

04/09/2014 13:02

Dimensional Modelling by Example

A.33 Puppies Tricks
A.33.1 ERD Model
http://www.databaseanswers.org/data_models/puppies_tricks/index.htm

A.33.2 Business Rules

1. Puppies can do Tricks.

2. Puppies learn Tricks in Kennels.
3. Tricks are performed at specific Skill Levels.
4. Typical Skill Levels are Beginners, Intermediate and Advanced.

Page 97

04/09/2014 13:02

Dimensional Modelling by Example

A.33.3 Dimensional Model

http://www.databaseanswers.org/data_models/puppies_tricks/dimensional_model.htm

Page 98

04/09/2014 13:02

Dimensional Modelling by Example

A.34 Radio Stations
A.34.1 ERD Model
http://www.databaseanswers.org/data_models/radio_stations/index.htm

A.34.2 Business Rules

1. Radio Stations broadcast Music of specific genres.

2. The music can be CD Tracks and MP3 Tracks.
3. Playlists are maintained that determine the music to be played.
4. Information is maintained about Artists, the Artists on CDs and Artists on specific Tracks.
5. Schedules are maintained with Disk Jockeys allocated to Schedules.
6. Music is broadcast according to Scheduled Playlists

Page 99

04/09/2014 13:02

Dimensional Modelling by Example

A.34.3 Dimensional Model

http://www.databaseanswers.org/data_models/radio_stations/radio_stations_data_mart.htm

Page 100

04/09/2014 13:02

Dimensional Modelling by Example

A.35 Recycling and Garbage Collection
A.35.1 ERD Model
http://www.databaseanswers.org/data_models/recycling_and_garbage_collection/index.htm

A.35.2 Business Rules

1. Local Authorities are responsible for the cleanliness of Streets.

2. Properties are on Streets and may or may not have Wheelie Bins.
3. Collection Schedules are maintained for the Collection of Garbage from Properties.
4.Street Collections are made in accordance with the Collection Schedules.
5. Each Street Collection has a Status, e.g. Missed, Successful.
6. A record is maintained of the Amount Collected of each Recycling Category, such as
Clothes, Glass or Paper.
Page 101

04/09/2014 13:02

Dimensional Modelling by Example

A.35.3 Dimensional Model

http://www.databaseanswers.org/data_models/recycling_and_garbage_collection/index.htm

Page 102

04/09/2014 13:02

Dimensional Modelling by Example

A.36 Restaurant Guides
A.36.1 ERD Model
http://www.databaseanswers.org/data_models/restaurant_guide/index.htm

A.36.2 Business Rules

1. Details of Restaurants are published in Guides.

2. These details include the Addess and the Type of Food served,
3. Visitors Comments are recorded, including Star Gradings.
4. Visitors are placed in Visitor Categories, such as Family on Vacation.

Page 103

04/09/2014 13:02

Dimensional Modelling by Example

A.36.3 Dimensional Model

http://www.databaseanswers.org/data_models/restaurant_guide/data_mart.htm

Page 104

04/09/2014 13:02

Dimensional Modelling by Example

A.37 Retail
A.37.1 ERD Model
http://www.databaseanswers.org/data_models/retail_customers/customers_area_model.htm

A.37.2 Business Rules

1. Customer demographics include Countries, Customer Categories and Gender.

2. Customers place Orders.
3. Orders involve Shipments that deliver goods to Customers.
4. Shopping Carts are used to hold Customers goods.
5. Mailshot Campaigns target specific Mailshot Customers
6. Customers choose Customer Payment Methods from a standard list of Payment Methods.

Page 105

04/09/2014 13:02

Dimensional Modelling by Example

A.37.3 Dimensional Model

http://www.databaseanswers.org/data_models/retail_customers/retail_dimensional_model.htm

Page 106

04/09/2014 13:02

Dimensional Modelling by Example

A.38 Student Registration
A.38.1 ERD

http://www.databaseanswers.org/data_models/student_registration/index.htm

A.38.2 Business Rules

1. Students register for Classes.

2. Students have Addresses of specific Address Types, such as Home or Local Address.
3. Students can have Parents or Guardians.
4. Students can choose from a list of standard Payment Methods.

Page 107

04/09/2014 13:02

Dimensional Modelling by Example

A.38.3 Dimensional Model

http://www.databaseanswers.org/data_models/student_registration/student_registration_data_mart_model.htm

Page 108

04/09/2014 13:02

Dimensional Modelling by Example

A.39 Telecomms Companies

A.39.1 ERD

http://www.databaseanswers.org/data_models/telecomms/index.htm

A.39.2 Business Rules

1. Telecomms Services are provided by Telecomms Suppliers to Customers.

2. Events occur that supply these Services.
3. These Events might also involve Documents and Staff.
4. Staff have Job Titles.

Page 109

04/09/2014 13:02

Dimensional Modelling by Example

A.39.3 Dimensional Model

http://www.databaseanswers.org/data_models/telecomms/conformed_dimensions_data_mart.htm

This Model features Conformed Dimensions for Calendar, Locations, Phone Calls and Phone Numbers.

Page 110

04/09/2014 13:02

Dimensional Modelling by Example

A.40 Tracking Printer Cartridges

A.40.1 ERD

http://www.databaseanswers.org/data_models/tracking_printer_cartridges/index.htm

A.40.2 Business Rules

1. Cartridge Manufacturers produce Printer Cartridges.

2. Printer Manufacturers produce Printers.
3. Printers are placed at specific Locations.
4. Cartridges are handled in Printer Cartridge Batches.
5. Users make Requests for assistance with Printers at specific Locations.
6. A Cartridge Inventory is maintained at dated intervals.
7. Re-Order Frequencies are defined for Printer Cartridges.
8. Technicians install Cartridges in Printers.

Page 111

04/09/2014 13:02

Dimensional Modelling by Example

A.40.3 Dimensional Model

This Data Model shows Keys without any data attributes.

http://www.databaseanswers.org/data_models/tracking_printer_cartridges/data_mart.htm

Page 112

04/09/2014 13:02

Dimensional Modelling by Example

A.41 Traffic Cops and Tickets

A.41.1 ERD

http://www.databaseanswers.org/data_models/traffic_cops_and_tickets/index.htm

A.41.2 Business Rules

1. Officers issue Tickets for Violations.

2. Each Ticket has a Status, such as Cancelled, Issued or Paid.
3. A Violation always involves a Document, such as a Ticket.
4. Tickets are issued in relation to Violations Vehicles belonging to Violaters

Page 113

04/09/2014 13:02

Dimensional Modelling by Example

A.41.3 Dimensional Model
http://www.databaseanswers.org/data_models/traffic_cops_and_tickets/law_enforcement_data_mart.htm

Page 114

04/09/2014 13:02

Dimensional Modelling by Example

A.42 Travel and Transport

A.42.1 ERD Model

http://www.databaseanswers.org/data_models/travel_and_transport_top_level/index.htm

A.42.2 Business Rules

1. Suppliers provide Services to Customers.

2. These Services can include Cargo Shipments and Passenger Journies.
3. Customers can make Requests for Services at specific Locations.
4. Staff might be involved, for example in supplying Documents or receiving Payments.
5. Documents can be Delivery Notes or Tickets.

Page 115

04/09/2014 13:02

Dimensional Modelling by Example

A.42.3 Dimensional Model
http://www.databaseanswers.org/data_models/travel_and_transport_top_level/dimensional_model.htm

Page 116

04/09/2014 13:02

Dimensional Modelling by Example

A.43 Usual Suspects

A.43.1 ERD Model
http://www.databaseanswers.org/data_models/usual_suspects/index.htm

Page 117

04/09/2014 13:02

Dimensional Modelling by Example

A.43.2 Business Rules

1. The Usual Suspects get involved in Situations.

2. Deliverables result from the Situations and can include Football Goals ,Touchdowns or
Legal Decisions.
3. The Usual Suspects wear Uniforms that reflect their Roles.
4. They follow Tribal Customs and use Jargon.
5. Their Situations involve Reference Material, such as Contracts or Legal Precedents.
6. Events are associated with Tribal Customs and reflect Value Systems.
7. Value Systems can be Materialistic Goals, Objectives, Religious Principles and so on.
A.43.3 Dimensional Model
http://www.databaseanswers.org/data_models/usual_suspects/data_mart.htm

Page 118

04/09/2014 13:02

Dimensional Modelling by Example

A.44 Utilities
A.44.1 ERD Model
http://www.databaseanswers.org/data_models/utilities/index.htm

A.44.2 Business Rules

1. Utilities companies offer Services (eg Gas and Electricity) that they Deliver to Customers.
2. These Services usually involve Delivery over a widespread Geographic area.
3. Customers take out Contracts for these Services.
4. Meters measure how much Customers use of the Services
5. Customers make Payments for the amount they use.
6. Events occur in the Delivery of the Services, such as checking and maintenance of the
Services.

Page 119

04/09/2014 13:02

Dimensional Modelling by Example

A.44.3 Dimensional Model

http://www.databaseanswers.org/data_models/utilitiess/data_mart.htm

Page 120

04/09/2014 13:02

Dimensional Modelling by Example

A.45 Vulnerable People

A.45.1 ERD Model
http://www.databaseanswers.org/data_models/vulnerable_people/index.htm

A.45.2 Business Rules

1. Vulnerable People might have Carers.

2. They might live in special Accommodation.
3. They can have one or many Vulnerabilities of specified Types.
4. Vulnerabilities have Treatments.
5. The Effectiveness is recorded of Treatment for specific Vulnerabilities for specific People.

Page 121

04/09/2014 13:02

Dimensional Modelling by Example

A.45.3 Dimensional Model

http://www.databaseanswers.org/data_models/vulnerable_people/data_mart.htm

Page 122

04/09/2014 13:02

Dimensional Modelling by Example

A.46 Waste Management

A.46.1 ERD Model
http://www.databaseanswers.org/data_models/waste_management/index.htm

A.46.2 Business Rules

1. Hospitals generate Waste.

2. Hospitals can belong to Hospital Systems.
3. Hospitals maintain Contacts and a Contacts History.
4. Waste Profiles are maintained and Waste Generated is compared to Profiles.
5. Waste Profiles involve Container and Waste Locations.

Page 123

04/09/2014 13:02

Dimensional Modelling by Example

A.46.3 Dimensional Model

http://www.databaseanswers.org/data_models/waste_management/dimension_model.htm

Page 124

04/09/2014 13:02

Dimensional Modelling by Example

A.47 Wedding Parties

A.47.1 ERD Model
http://www.databaseanswers.org/data_models/wedding_parties/index.htm
This is based on our Usual Suspects Model which is described above in Section A.44.

A.47.2 Business Rules

1. Wedding Parties involve Outfits, Participants and Etiquette.

2. They include Jargon, such as Bride and Groom.
3. They also involve Deliverables, such as Wedding Videos.
4 Events can include Value Systems, such as Follow Wedding Etiquette.
5. Wedding Etiquette involves Events, such as Limousine Booking and Receptions

Page 125

04/09/2014 13:02

Dimensional Modelling by Example

A.47.3 Dimensional Model
http://www.databaseanswers.org/data_models/wedding_parties/wedding_parties_data_mart.htm

Page 126

04/09/2014 13:02

Dimensional Modelling by Example

A.48 Wine
A.48.1 ERD Model

http://www.databaseanswers.org/data_models/wine_lists/index.htm

A.48.2 Business Rules

1. Wines are defined by a number of characteristics, including Grape Variety, Year,

Vineyard, Color, Country and Region .

Page 127

04/09/2014 13:02

Dimensional Modelling by Example

A.48.3 Dimensional Model

http://www.databaseanswers.org/data_models/wine_lists/dimensional_model.htm

Page 128

04/09/2014 13:02

Dimensional Modelling by Example

A.49 Yakuza Japanese Organised Crime
A.49.1 ERD
http://www.databaseanswers.org/data_models/yakuza_japanese_crime/index.htm

A.49.2 Business Rules

1. The Yakuza is an organisation which consists of Principal Clans that contain Members.
2. Yakuza is organized in a Hierarchy with four Levels.
,

Page 129

04/09/2014 13:02

Dimensional Modelling by Example

A.49.3 Dimensional Model

http://www.databaseanswers.org/data_models/yakuza_japanese_crime/dimensional_model.htm

Page 130

04/09/2014 13:02

Dimensional Modelling by Example

A.50 Zoos
A.50.1 ERD

http://www.databaseanswers.org/data_models/zoos/index.htm

A.50.2 Business Rules

1. A Zoo contains Animals of specific Breeds.

2. Each Breed has its own Dietary Requirements which define Food Items in Categories.
3. Dietary Requirements for specific Animals in the Zoo are derived from these Food Items.
4. Check-ups are carried out on the Animals.

Page 131

04/09/2014 13:02

Dimensional Modelling by Example

A.50.3 Dimensional Model

http://www.databaseanswers.org/data_models/zoos/dimensional_model.htm

Page 132

04/09/2014 13:02