acct 6910 building business intelligence systems an introduction to data warehouse
DESCRIPTION
3 Why Data Warehouse Why Database??TRANSCRIPT
Acct 6910
Building Business Intelligence Systems
An Introduction to Data Warehouse
2
Agenda
Why Data Warehouse
What is Data Warehouse
Current practice of data warehouse
3
Why Data Warehouse
Why Database??
4
Why Data WarehouseProblems with current database practices: Problem 1: Isolated databases
distributed in an enterprise
Sales
CRM
Inventory
Sub-problems:• Data Inconsistency• No comprehensive view of enterprise’s data sources – information island
5
Why Data Warehouse Problem 1: Isolated databases
distributed in an enterprise
Sales
CRM
Inventory
Sub-problems:• Data Inconsistency• Performance
6
Why Data Warehouse Problem 2: Historical data is archived in
offline storage systems
Sales
Sub-problems:• Historical data is always needed to support business decisionsArchiv
e
Historical Sales Data
7
Why Data WarehouseCustomer
Places
1
Order
M
Contain
1
OrderLine
M
Order
M
Product
1
Belong to
M
Product Category
1
8
Why Data Warehouse A marketing manager wants to know
sales amount distribution by product category and customer state in July?
Query???
9
Why Data Warehouse Problem 3: Database is designed to
process transactions but not to answer decision support queries
Complex queries
Bad query performance
10
What is Data WarehouseData Warehouse is designed to solve
problems associated with current database practices:
Problem 1: Isolated databases distributed in an enterprise
Sales
CRM
Inventory
Extract, Integrate and Replicate
Data Warehouse
11
Why Data Warehouse Problem 2: Historical data is archived in
offline storage systems
Sales Archiv
e
Historical Sales Data
Data Warehouse
Integrate Historical Data with Current Data
12
What is Data Warehouse Problem 3: Database is designed to process transactions but not to answer
decision support queries
Solution: In data warehouse, organize data in subject –oriented way rather than process-oriented way – dimensional modeling.
13
What is Data Warehouse
Customer
Places
1
Order
M
Contain
1
OrderLine
M
Order
M
Product
1
Belong to
M
Product Category
1
Sales
Product
Time
Customer
ER Modeling
Dimensional Modeling
14
What is Data Warehouse
Data Warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management’s decision making process.
1. Subject-oriented means the data warehouse focuses on the high-level entities of business such as sales, products, and customers. This is in contrast to database systems, which deals with processes such as placing an order.
15
What is Data Warehouse
2. Integrated means the data is integrated from distributed data sources and historical data sources and stored in a consistent format.
3. Time-variant means the data associates with a point in time (i.e., semester, fiscal year and pay period)
4. Non-volatile means the data doesn’t change once it gets into the warehouse.
16
What is Data Warehouse Data Warehouse Data Base
Purpose Decision Support Transaction Processing
Data Organization
Subject Oriented Process Oriented
Data Model Dimensional Modeling ER Modeling
Time Span Historical and Current Data
Current Data
Query processing
Scan a substantial subset of data
Scan a small set of data
Operation Read-only (add new data periodically)
Read & Update
17
Current Practice of DW*
Expected DW market value is 2002 will grow to $113.5 billion.
Average DW development cost is $1.5 million and average maintenance cost is $0.5 million.
* Source: H.J. Watson, “ Current Practicing in Data Warehousing”, I.S. Management, 2001
18
Current Practice of DW*
Sponsorship for the DW projectSponsor PercentageVP of a business unit 39.8CIO 26.9Business unit manager 16.7CEO 11.1Other 25.0
* Source: H.J. Watson, “ Current Practicing in Data Warehousing”, I.S. Management, 2001
19
Current Practice of DW*
DW Benefits Less effort to produce better information Better decisions Improvement of business processes Supporting for accomplishments of
strategic business objectives
* Source: H.J. Watson, “ Current Practicing in Data Warehousing”, I.S. Management, 2001
20
Reading:“ The Data Warehouse Toolkit” – Chapter 1