Data Warehousing

1
Data Warehousing OLAP
2
Data Warehousing
3
Data Warehousing? ??
?? OLTP ???
programming
4
Data Warehouse? ???? ?? ?? ????
DSS
Data Mining
EIS
??/??
DW
?? ?? ???
?? Reports
Data Processing (Transaction)
Information Processing
OLTP? DB? OLAP? DB(DW)? ??
5
44
Data Warehouse

• ?? ? ????? ??? ?? ?? ??? ????? ???? ??????
• ?? ??? ??
• ??? ??? ?? ??? ??
• ??? ?? ?? ??
• ?? ??? ??
• ????

??? ??? ??? ??? ??? ??(Dimensional model)
6
Data Warehouse
7
45
??? ??
???
Jan
time
Product
Feb
C
Mar
B
A
Wong
Dewitt
Stonebreaker
Sales person
8
??? ??

• Star schema

Sales Fact
Product Dimension
Time Dimension
product_key description brand category
time_ key product_key store_key dollars_sold units
_solds dollars_cost
time_key day_of_week month quarter year holiday_fl
ag
Store Dimension
store_key store_name address floor_plan_type
Q ?? ??? ? ???? ?? ? ?? ????
9
Data Warehouse? ??

• ????? (subject-oriented)
• ???(integrated)
• ???(time variant, historical)
• ????(non-volatile)

10
Data Warehouse? ??

• ?? ?? (Subject oriented)
• OLTP???? ???? ???? ?? (process/function oriented)
  ?? ????
• DSS? ??? ?? ???? ??? ?? ????
• DW? Data Model? ???? ???? ????
• ??? ??
• DSS? ??? ?? ???? DW?? ???

Operational
Data Warehouse
??
????
????
??
????
??
11
Data Warehouse? ??

• ??? ?????? (Integrated)
• ??? (table ?? column)
• ??
• ??
• ??? ??? ?? (source)

?? ?? ????
??
?? A ??cm ?? B ??inch
cm
??
?? A ?, ? ?? B m, f ?? C 0, 1
m , f
12
Data Warehouse? ??

• ??? (Time variant)
• OLTP ?? ??? ???? ??
• DW ????? ??? ?? ??? ??? ?? ??? ?
• ?? ???? ?? ?? ???
• ??? ?? ???
• ?(key)? ?? ??? ????? ?

??? ???? ?? 24?
update
append
??? ???? ?? 24?
OLTP
DW
13
Data Warehouse? ??

• ??? ???? ?? (nonvolatile)
• ??? ??? ??? ??
• cf) ??? ??? ?????
• ???? ?? (load)? ??
• ???? ?? ???? ??

OLTP
DW
update
insert
load
access
delete
14
Data Warehouse? ??
highly summarized
10??? ???? ?? ???
lightly summarized
5??? ??? ?? ???
?? ?? ??? ??? (current detail)
Current Detail
OLTP ??? load
?????? ?? ??? ??? (older detail)
15
OLAP (On-Line Analytic Processing)
OLAP ???? ?? ??(?????? ??)
???? ????
Information Source
Information Source
Information Broker
16
OLAP ??

• FASMI
• Fast Analysis of Shared Multidimensional
  Information
• Fast
• interactive system (5 20? ??? ??? ??)
• keep a chain of thought
• Analysis
• Ad hoc. Analysis , Flexible end-user calculation
• Shared
• multiple access , one-write, multiple-read
• security ??
• Multidimensional
• MDB?? ?? ???? ?????? ??
• eg) ????? ?? ??,??,???,???? ???? ??

17
OLAP? ??

• ???? ??
• Aggregation
• Ad hoc query
• Trend analysis
• Drill-down, roll-up
• Pivoting
• Charting
• Event Mapping
• Ranking

18
OLAP? Data Warehouse

• Data Warehouse
• ??? ??? ??
• OLAP
• DW? ?? ??? ???, ???? ??
• ??
• ??, ???? client/server???? coding?? ?? ??
  1/10??? ?? ?? ??
• ?????(Ad-hoc)? ???(??)? ??(complex SQL)
• ??? ?? ???(End User Computing)? ??? ??
• easy use

19
OLAP? ??

• Relational OLAP (R-OLAP)
• Multidimensional OLAP (M-OLAP)

20
ROLAP
RDBMS
???

C
??
B
A
1 2 3
21
ROLAP? ??
Meta Data
RDBMS
????(SQL)
??? ??
??? ??? ?? ??(Cross- Tabulation ??)
??? ?? ??
ROLAP engine
22
MOLAP
OLAP ??
??? ??
??? Database
??? ?? ??
23
OLAP Tool
24
OLAP Tool
25
OLTP ??? DW ??? ??(1/2)
OLTP
DW

,
???
????
??
???
???
????
,
??
??????
????
??

(RDBMS)
(RDBMS)
??
??
???
???



???
??
????
??
???
??




???
??
?????
????
??
??
???



subject oriented
???
??
??
??????
??
???
??
load and access

/

???
??
???
??
??

?????
??
Record locking No Record locking
26
OLTP ??? DW ??? ??(2/2)
OLTP
DW




????
????
???
??
?????
???
??

??
???



Gbyte

Gbyte

Tbyte
??????
??
?
??
??
?
3090days 510years


10

???
??
???
??
??
????




???
???
??
????
????
??????
????

?????
??


TPC-C
TPC-D
???
??
??
?
(Benchmarking)
Transaction-tuned Information Tuned
27
CRM (Customer Relationship Management) Systems
28
CRM ??

• ??
• ??? ??? ?? ??? ??, ????, ???? ???? ?? ???
• ?? ??? ?? ??? ??
• ??
• One-to-One Marketing ?? ???
• cf) Tell and Sell Mass Marketing

29
CRM? ?

• ?? ???
• ???? ?? ??? ??? ????
• ??? ??? ?? ??? ???? ??? ?????
• ?? ?? ??
• ?? ?? ??/???? ? ??
• ???? ??
• ?? ?? ??? ??? ???? ?? ??? ??? ??? ?? ??
• ?? ?? ??
• ?? ??? ??? ??? ??? ?? ?? ?? ??? ??

30
CRM Services

• Direct Mail
• ??? ??? ?? ???? ?? ???
• My page
• ??? ??? ?? ???? ? ??? ???
• Automatic Response
• ?? ??? ?? ?? ?? ???

31
CRM ????
Learning (Query/Report, OLAP, Data Mining)
Action
32
CRM ???
???? ??
??? ??
??
??