Chapter 6 The Data Warehouse

1
Chapter 6The Data Warehouse
Jason C. H. Chen, Ph.D. Professor of MIS School
of Business Administration Gonzaga
University Spokane, WA 99223 chen_at_gonzaga.edu
2
6.1 Operational Databases
3
Data Modeling and Normalization

• One-to-One Relationships
• One-to-Many Relationships
• Many-to-Many Relationships

4
Data Modeling and Normalization

• First Normal Form
• Second Normal Form
• Third Normal Form

5
(No Transcript)
6
The Relational Model
7
(No Transcript)
8
(No Transcript)
9
6.2 Data Warehouse Design
10
(No Transcript)
11
Entering Data into the Warehouse

• Independent Data Mart
• ETL (Extract, Transform, Load Routine)
• Metadata

12
Structuring the Data Warehouse The Star Schema

• Fact Table
• Dimension Tables
• Slowly Changing Dimensions

13
(No Transcript)
14
The Multidimensionality of the Star Schema
15
(No Transcript)
16
Additional Relational Schemas

• Snowflake Schema
• Constellation Schema

17
(No Transcript)
18
Decision Support Analyzing the Warehouse Data

• Reporting Data
• Analyzing Data
• Knowledge Discovery

19
6.3 On-line Analytical Processing
20
OLAP Operations

• Slice A single dimension operation
• Dice A multidimensional operation
• Roll-up Aggregation, a higher level of
  generalization
• Drill-down A greater level of detail
• the reverse of a roll-up
• Rotation View data from a new perspective

21
(No Transcript)
22
Concept Hierarchy

• A mapping that allows attributes to be viewed
  from varying levels of detail.

23
(No Transcript)
24
(No Transcript)
25
6.4 Excel Pivot Tables for Data Analysis
26
Creating a Simple Pivot Table
27
(No Transcript)
28
Steps 1,2 (p.198)
29
Steps 2, 3
30
Step 3
31
Step 4
32
Step 5
33
Step 6
34
Step 7
35
Result of Step 7 (p.198)
36
(No Transcript)
37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)
41
Step 1, 2(bottom of p.198)
42
Step 3 (top) and steps 1,2 3 (p.199)
43
Step 4 (p.199)
44
Step 4 (p.199)
45
(No Transcript)
46
Steps 1,2
47
Step 2
48
(No Transcript)
49
Step 3 (p.200)
50
Step 3 - continued (p.200)
51
Step 3 - continued (p.200)
52
Step 3 - continued (p.200)
53
Step 3 - result (p.200)
54
(No Transcript)
55
Pivot Tables for Hypothesis Testing
Younger cardholders purchase credit card
insurance whereas more senior cardholders do not.
56
(No Transcript)
57
Method 1
58
(No Transcript)
59
Method 2- Steps 1,2,3
60
Method 2- Step 4
61
Steps 4,5
62
Step 6
63
Step 7
64
Step 8
65
Result of Method 2
The average age for credit card insurance no is
approximately 41.42, whereas the average age for
credit card insurance yes is approximately 32.33
66
Creating a Multidimensional Pivot Table
Investigate relationships between the magazine,
watch, and life insurance promotions relative to
customer gender and income range.
67
(No Transcript)
68
Steps 1,2,3 (p. 206)
69
Steps 3 (after dragging life insurance promotion
to DropData Items Here. )
Continue dragging watch promotion and magazine
promotion to DropData Items Here.
70
Step 3 (result)
71
Step 4
72
Decision Making steps 1-3, p.207
A total of two customers took advantage of the
life insurance and magazine promotions but did
not purchase the watch promotion.
73
(No Transcript)
74
(No Transcript)
75
Result of p.207
76
(No Transcript)
77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
(No Transcript)
81
(No Transcript)