Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8. Enterprise Data Models................................................................................................................... 1

8.0 Welcome ............................................................................................................................................. 1
8.1 Introduction ........................................................................................................................................ 1
8.2 Business Intelligence (BI) .................................................................................................................... 3
8.3 Insurance............................................................................................................................................. 7
8.4 Investment Banking ..........................................................................................................................14
8.5 Local Government.............................................................................................................................21
8.6 Retail .................................................................................................................................................29
8.7 What have we learned ? ...................................................................................................................40

Barry Williams

Principal Consultant

Database Answers Ltd.

barryw@databaseanswers.org

8. Enterprise Data Models

8.0 Welcome

You are invited to follow developments in the Library of Data Models on our Web Site :-
 http://www.databaseanswers.org/data_models/index.htm

You can also join our Database Answers Community

 http://databaseanswers.ning.com/

8.1 Introduction
This Chapter will discuss Enterprise Data Models for a number of different kinds of enterprise.

Page 1
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.1.1 What is this ?

8.1.2 Why is it important ?

Being able to create Enterprise Data Models will help you to handle the most challenging situations that
you might encounter as a Data Modeller.

The Best Practice Approach has these Steps :-

 Create a Top-Level Data Model that represents a business-eye view of the organisation.

- It will include all the Subject Areas, such as Marketing, Finance and CRM.

- Each of these Subject Areas will have its own Data Model.

8.1.3 What will I Learn ?

Enterprise Data Models are very useful because they help to establishing ‘A Single Version of the Truth’.

For example, it is common for organisations to have multiple occurrences of Customer records, with the same
person appearing as Joe Bloggs, Joseph Bloggs, Joey Bloggs and so on.

These can be matched to one common record and all the associated details can be consolidated.

This aspect is discussed in more details in Chapter 11 that covers Master Data Management.

The Approach we adopt is to define a ‘Statement of Objectives’ that will establish the important Things of Interest’
that need to be in the Enterprise Data Model.

This establishes a context from which Subject Areas can be derived. Any change in the Statement of Objectives can
easily be reflected in changes to the Subject Areas.

Page 2
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.2 Business Intelligence (BI)

8.2.1 Statement of Objectives

Our example is our Anglo-Swiss Consulting Company which has offices in London and Zurich.

The typical Statement of Objectives for our Company would be stated in these terms :-

“To provide high-level consulting services to a targeted Clients operating from multiple locations.

To support these Objectives, we have designed a BI approach called BMEWS, which stands for –

“Business Monitoring and Early Warning System”.

“To provide a flexible solution to a range of user requirements for Business Intelligence..”

Therefore, the Subject Areas in the Enterprise Data Model must include :-

Consulting :-

 Clients

 Costs

 Revenues

 Services

 Staff

BMEWS :-

 Data Warehouse and Data Marts

 Key Performance Indicators (‘KPIs’)

 Data Sources and Integration

Page 3
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.2.2 The Enterprise Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_BMEWS/index.htm

Page 4
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.2.3 BI Data Warehouse

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_BMEWS/index.htm

Page 5
Learning Data Modelling by Example Chapter 8) Enterprise Data Models

Page 6
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3 Insurance

8.3.1 Statement of Objectives

The typical Statement of Objectives for an Insurance company would be stated in these terms :-

“To provide a wide range of attractively-priced and economical services to a targeted range of customers., with
economic management of Claims processing and Settlements.”

Therefore, the Subject Areas in the Enterprise Data Model must include :-

 Claims

 Customers

 Policies

 Products and Services

 Staff

 Settlements

8.3.2 The Enterprise Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_insurance/index.htm

Page 7
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.3 The Customers, Claims and Brokers Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/insurance_customers_and_claims/insurance_brokers_erwin_physical.htm

Page 8
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.4 Policies and Claims Subject Area

This Model appears on this page on the Database Answers Web Site :-

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

 Was - http://www.databaseanswers.org/data_models/insurance_personal/index.htm

Page 9
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.5 Types of Insurance - Car, Home and Life Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/insurance_car_home_life/index.htm

Page 10
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.6 Types of Insurance – Marine and Motor Vehicles Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/insurance_general/index.htm

Page 11
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.7 Common Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/insurance_data_warehouses/common_data_model.htm

Page 12
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.3.8 Insurance Data Warehouse

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/insurance_data_warehouses/index.htm

Page 13
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4 Investment Banking

8.4.1 Statement of Objectives

The typical Statement of Objectuves for an Investment Bank would be stated in these terms :-

“To participate in a profitable trading operation by buying and selling stocks, shares and other commodities.”

Therefore, the Subject Areas in the Enterprise Data Model must include :-

 Accounts

 Clients

 Financial Products and Services

 Staff

 Settlements

 Trades or Deals

8.4.2 Enterprise Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_investment_banks/index.htm

Page 14
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.3 Accounts Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/investment_banking/accounts.htm

Page 15
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.4 Customers Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/investment_banking/customers.htm

Page 16
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.5 Deals Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/investment_banking/deals_general.htm

Page 17
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.6 Settlements Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/investment_banking/settlements.htm

Page 18
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.7 Staff Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/investment_banking/staff.htm

Page 19
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.4.8 Data Warehouse

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/banking_data_warehouses/investment_banking_data_warehouse_final_versi
on.htm

Page 20
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5 Local Government

8.5.1 Statement of Objectives

The typical Statement of Obectuves for in Local Government would be stated in these terms :-

“To provide a range of Services to a varied range of non-residents and residents.

To ensure that these Services are provided economically to meet the specific requirements of the community ”

Therefore, the Subject Areas in the Enterprise Data Model must include :-

 Customers or Citizens, with both Residents and non-Residents.

 Services and Products

 Service Delivery

8.5.2 Enterprise Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/index.htm

Page 21
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.3 Children’s Attainments Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/children.htm

Page 22
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.4 Citizens Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/citizens.htm

Page 23
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.5 Citizens and Addresses Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/citizens_and_addresses.htm

Page 24
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.6 Citizens and Services Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/customers_and_services.htm

Page 25
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.7 Education Services Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/education_services.htm

Page 26
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.8 Housing Services Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/housing_services.htm

This Model includes ‘Haringey Council’.

Page 27
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.5.9 Data Warehouse

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_local_government/data_warehouse.htm

Page 28
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6 Retail

8.6.1 Statement of Objectives

The typical Statement of Objectuves for a Retail organisation would be stated in these terms :-

“To offer a range of affordably-priced retail goods and services which are economically distributed and meet the
requirements of a targeted customers.

These will be made available in-store and online.”

The Subject Areas reflect the Statement of Objectives.

The purpose of this Statement of Objectives is to establish the scope of the Enterprise Data Model :-

The objectives are to offer a wide range of affordable Products.

Costs will be controlled to achieve maximum profit potential from a wide range of affordable Products which are
sourced on a 'just-in-time' basis tailored to our target market, with specific Customer profiles.

The Enterprise Data Model will establish the data available for a Data Warehouse to meet Business Intelligence
requirements.

Therefore, the Subject Areas in the Enterprise Data Model must include :-

 Customers

 Distribution

 Inventory

 Marketing

 Products

Page 29
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.2 Enterprise Data Model

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/index.htm

Page 30
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.3 Customers Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/customers_area_model.htm

Page 31
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.4 Customers and Orders Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/customers_and_orders_model.htm

Page 32
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.5 In-Store Shopping Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/in_store_shopping_area_model.htm

Page 33
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.6 Online Shopping Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/online_shopping_area_model.htm

Page 34
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.7 Order History Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/Order_History.htm

Page 35
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.8 Products Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/Subject_Area_for_Products.htm

Page 36
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.9 Products, Stores and Sales Subject Area

This Model appears on this page on the Database Answers Web Site :-

 www.databaseanswers.org/data_models/enterprise_data_model_for_retail/Subject_Area_for_Products_Stores_and_Sales.ht
m

Page 37
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.10 Registered Users (with MDM) Subject Area

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/Registered_Users.htm

Page 38
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.6.11 Data Warehouse

This Model appears on this page on the Database Answers Web Site :-

 http://www.databaseanswers.org/data_models/enterprise_data_model_for_retail/retail_customers_data_mart
.htm

Page 39
 Learning Data Modelling by Example Chapter 8) Enterprise Data Models

8.7 What have we learned ?

In this Chapter we have learned how to design Data Models for large commercial
Enterprises.

These kind of Enterprises are typically very complex and it can seem at first impossible to
design a Data Model that is useful.

In fact, it is common to see Enterprise Data Models that include hundreds of Entities.

It is, of course, very difficult to use these Models.

A good general approach is to start by designing a Top-Level Data Model with a limited
number of Entities.

Then designing a number of Subject Area Models will complement the Top-Level Model to
allow you to cover a wide range of functionality within the Enterprise.

Page 40