??sa???? st?? ?p????e? ?ed?

1
??sa???? st?? ?p????e? ?ed?µ????
??af??e?e? ßas?sµ??e? se s?et???? d?af??e?e? t??
????? ?as??e??d?
2
??sa???? OLTP
?a?ad?s?a?? ??a?e???s? ?ed?µ???? µe S???

• S?st?µa ?pe?e??as?a? ??s??????? On-Line
  Transaction Processing (OLTP)
• ??a p???e? s?st?µa p?? pe????e? e??a?e?a ??a
  p????aµµat?sµ? efa?µ????, e?t??es? ?a? d?a?e???s?
  t?? d?s???????
• ??a t?t??a efa?µ??? p??pe? ?a d???e?e? s??e???,
  ?a a?tepe????eta? ap?t?????, e?e??sseta? s??e???,
  e??a? s?????? ?ata?eµ?µ??? ?a? pe???aµß??e?
• ??s? ?ed?µ????
• ???t??
• ??????µµata efa?µ????
• ??a??et??? ???s?µ? ??a t? ?e?t?????a ???e
  ???a??sµ??

3
??sa???? OLTP
OLTP ?e??p????? ?ta??e?a
???t?se ??a t?? ?. ? t?? ??s? 13? ??a LA!
1
???t?se ??a t?? ?. Y t?? ??s? 13? ??a LA!
...
DB
??s?? ta??de???? ??a LA ?
100
4
??sa???? OLTP
OLTP ???pe?a
???e?a
G??s?
DB
??st?t???? ???te?
???
5
??sa???? OLTP
OLTP ?as??? ?a?a?t???st???

• ?????st?? ?????? d?a??s?µ?? ??a t?? e?t??es? µ?a?
  d?s?????a?.
• ????te?e? ap? 10 p??sß?se?? d?s???.
• ?e?????sµ???? a???µ?? ?p?????sµ??.
• ??t? ???? ?e?t???????? apa?t?se??
• 100 on-line Transactions Per Second (TPS) se µ?a
  ?? t?? t???? t?? 1 GB
• ??? ???? ?e?t???????? apa?t?se??
• 50000 TPS se µ?a ?? µe?a??te?? t?? 1 ?B.

6
??sa???? OL?P

• S?st?µata St?????? ?p?f?se?? Decision Support
  Systems (DSS)
• ?p?ß????s? ????? ap?f?se?? µe p????f???e? ?a?
  a?af????
• On-Line Analytical Processing (OLAP)
• ??????t?, ?????? ap?d?s?? p??sßas? ?a? a????s?
  µe????? ????? s???et?? ded?µ???? ap? d?af??et????
  efa?µ????
• ??d???? t?p?? e??t?se??
• ?pt???p???s?/stat?st??? a????s?/p???d??stat?
  a????s?
• ??????? G??s?? (Knowledge Discovery/Data Mining)
• ??e??es? p??t?p?? se te??st?e? ß?se?? ded?µ????
• OLAP Data Mining gt On-line Analytical Mining

7
??sa???? OL?P
?a?ade??µata e??t?se?? OLAP

• ????? ?ta? ? ????? p???se?? a?? pe????? ?a?
  ?at?????a p?????t?? t?? pe?asµ??? ??????
• ??s? s?et????ta? ?? a???se?? t?µ?? t??
  ?p?????st?? µe ta ???d? t?? p???se?? ta 10
  te?e?ta?a ?????a
• ???a ?ta? ta d??a p??ta ?atast?µata se p???se??
  CD
• ??s??? d?s???? p????saµe st?? ?e??p????s? t?
  te?e?ta?? t?ta?t? t?? pe?s???? ??????? se
  ?atast?µata µe ?ata????s? µe?a??te?? ap? 100
  d?s???? µ???a???, ?a? p??? t? ???d?? µa? ap?
  a?t?? t?? p???se??
• ?? p?s?st? ap? t??? pe??te? p?? a????????
  a?a???t??? a???????? ?a? patat???a

8
??sa???? OL?P
?e?t??????? ?a?a?t???st??? ?pa?t?se?? OLAP

• ???sßas? se µe???? ???? ded?µ????
• S?µµet??? a????st???? ?a? ?st?????? ded?µ???? se
  p???p???e? e??t?se??
• ?etaß??? t?? ?pt???? ????a? pa???s?as?? t??
  ded?µ???? (p.?., ap? p???se?? a?? pe????? -gt
  p???se?? a?? tµ?µa ??p.)
• S?µµet??? p???p????? ?p?????sµ?? (p.?.
  stat?st???? s??a?t?se??)
• G?????? ap??t?s? se ?p??ad?p?te ??????? st??µ?
  te?e? ??a e??t?µa ( On-Line).

??? ?a t? pet????µe
9
??sa????
??? ?e?t???? ??µata

• ?p?d?s?
• ?? µ?a p???p???? OLAP e??t?s? ??e?aste? ?a
  ??e?d?se? ??a ???????? p??a?a, t?te ??e? ?? OLTP
  d?s?????e? t?? pe??µ????? µ???? ?a te?e??se?
• ???????????? d?af??? ?a? ete?????e?a
• ?? st?? Oracle ?? t?? marketing ? pe??t?? e??a?
  EMP(??,Name,Surname) ?a? st?? COBOL ?? t??
  p???se?? e??a? ?F?,FullName, ? epe??t?s? de?
  e??a? p??ta e?????...

10
??sa???? ?p????? ?ed?µ????
?p????e? ?ed?µ????

• ??a ?e?t????p???µ??? ?? µe s??p?
• t?? ????????s? (integration) ete???e??? p????
  p????f????? (data sources) gt s???????s? ???? t??
  e?d?af????sa? p????f???a? se µ?a t?p??es?a
• t?? ap?f??? t?? s??????s?? µeta?? OLTP ?a? OLAP
  (DSS) s?st?µ?t?? gt ap?d?s? efa?µ???? ?a?
  d?a?es?µ?t?ta t?? s?st?µat??
• ?p??e? ?a s?µp?????eta? ?a? ap? e?e?d??e?µ??a
  ?eµat??? ?p?s????a (Data Marts) ??a pe?a?t???
  ap?d?s? t?? OLAP efa?µ????

11
??sa???? ?p????? ?ed?µ????
?p????e? ?ed?µ????

• ??a ?? ?p?st?????? ap?f?se??, p?? d?at??e?ta?
  ????st? ap? t?? ?? pa?a????? (operational
  database) e??? ???a??sµ??.
• S. Chaudhuri, U. Dayal, VLDB96 tutorial
• ??a s?????? ded?µ???? p?? ???s?µ?p??e?ta? ??????
  ??a t?? ???? ap?f?se?? se ??a ???a??sµ?, ?a?
  e??a? ?eµat??? p??sa?at???sµ???, ??e?
  ????????µ??a (e??p???µ??a) ded?µ??a, ta ?p??a
  d?at?????ta? se ß???? ?????? ????? ?a
  d?a???f??ta?.
• W.H. Inmon, Building the Data Warehouse, 1992 (?
  efe???t?? t?? ????)

12
??sa???? ?p????? ?ed?µ????
Ge???? ????te?t?????
DW
Data Marts
???? ?ed?µ????
OLTP s?st?µata
OLAP e??a?e?a
13
??sa???? ?p????? ?ed?µ????
???te??µata/?d??t?te?

• ???????????? e?a?µ???s?
• ?? d?af??et???? p???? ded?µ???? t?? ?d???
  ???a??sµ??, µ??te??p????? t?? ?d?e? ??t?t?te? µe
  d?af??et????? t??p???
• ? ?p????? ?ed?µ???? pe???aµß??e? t? s????? a?t??
  t?? ded?µ???? ??t? ap? ??a e?a?µ???sµ??? s??µa
  ß?s??

• ????t?ta ?ed?µ????
• ? p???t?ta t?? ded?µ???? st?? p???? e??a? s????
  p??ß??µat??? (ta ded?µ??a µp??e? ?a µ?? e??a?
  p????, ?a ????? as???pe?e?, ?a e??a? pa???, ?a
  pa?aß?????? t??? ???????? ?a? d?µ????? ?a???e?
  a???p?st?a?, ??p)
• ??e? ß?e?e? ?t? t??????st? 10 t?? ded?µ????
  e??a? p??ß??µat??? st?? p????, µe ap?t??esµa
  ?????oµ???? ap??e?e? t?? 25-40
• ???? t?? e?sa???? st?? ap????e? ded?µ????
  ?a?a??sµ??, ep?s?? ?e?t????e? ?a? ?? ??a
  e?d??µes? s?st?µa st?? ?p??? ?a?a?????µe ta
  ded?µ??a

14
??sa???? ?p????? ?ed?µ????
???te??µata/?d??t?te?
?p?d?s? ?? efa?µ???? OLAP ep?ta?????ta? a? ta
ded?µ??a ???a?????? µe µ? pa?ad?s?a???? t??p???
(p.?., ap?-?a??????p???µ??a) S??? ??a OLTP
(e??et???a, epe?e??as?a d?s???????) ?? s???ete?
OLAP e??t?se?? ?a s????????ta? µe t??
pa?ad?s?a??? OLTP d?s?????e?, µe ap?t??esµa t??
?pe?f??t?s? t?? s?st?µat?? Teµat???
p??sa?at???sµ??? ??at???s? µ??? t?? s?et????
ded?µ????
??a?es?µ?t?ta ?s? pe??ss?te?a a?t???afa t??
ded?µ????, t?s? p?? p??? t? s?st?µa e??a?
d?a??s?µ?, afe??? st?? ?p????? ?ed?µ???? ?a?
afet???? st?? p???? ??a?es?µ?t?ta t? p?s?st?
t?? ?????? p?? t? s?st?µa e??a? se ?e?t?????a ?a?
p??sß?s?µ? st?? efa?µ????. 24x7 ?? OLTP
efa?µ????, se p?????? ???a??sµ??? p??pe? ?a e??a?
d?a??s?µe? 24 ??e? ? 7 µ??e? t? ßd?µ?da (p.?.,
t??pe?e?, ae??p?????? eta??e?e?,...)
15
??sa???? ?p????? ?ed?µ????
???te??µata/?d??t?te?

• ?st????? ?ed?µ??a
• ? ???????? ??????ta? µ?a ap?????? ded?µ????
  e??a? p??? µe?a??te??? ap? ?t? e??? s?st?µat?? se
  ?e?t?????a
• ? ?? ??e? ta t????? ded?µ??a e?? ?? ap????e?
  d?at????? ?a? pa??? ded?µ??a (p? ta p??????µe?a
  5-10 ?????a)

• ???p?p???se??
• ?? t??p?p???se?? st?? p???? ded?µ???? de?
  fa????ta? ?µesa st?? ap????e? ded?µ????, s??????
  pe???d???
• ???? d?? ßas???? ?e?t?????e? a????? f??t?µa
  t?? ded?µ???? (loading) ?a? p??sp??as? ded?µ????
  (access)

16
??sa???? ?p????? ?ed?µ????
OLTP vs OLAP

• OLTP OLAP
• ??µ? Files/DBMSs RDBMS
• ???sßas? SQL/COBOL/ SQL epe?t?se??
• ?????e? p?? ??t?µat?sµ?? ??t??s? ?a?
• ?a??pt??? ?a??µe????? epe?e??as?a p????f.
• e??as??? ??a ???a?? st?at??????
• ??p?? ?ed?µ???? ?ept?µe?? S???pt???, ?????st???
  
• ?e?t???????
• ????? ?ed?µ???? 100 GB 1 TB
• F?s? ?ed?µ???? ???aµ???, ??????ta Stat???,
  ?st?????

17
??sa???? ?p????? ?ed?µ????
OLTP vs OLAP
OLTP OLAP I/O ??p?? ?e?????sµ???
I/O ??tetaµ??? I/Os S???? disk seeks disk
scans ???p?p???se?? S??e?e?? ?e???d????
???µe??se?? ??t??s? ?p?d?s?? Throughput ????
?? ?p????s?? F??t?? ??s?????e? µe ???t?se??
p?? p??sßas? ????? sa?????? e???af?? e
?at?µµ???a e???af?? S?ed?as?
?? ?ate?????µe?? ?ate?????µe?? ap?
?fa?µ??? ap? ?e??e??µe??
18
??sa???? ?p????? ?ed?µ????
OLTP vs OLAP
OLTP OLAP ??p???? ???ste? ?aµ???ßa?µ??
?p. ?????ßa?µ?? ?p. ???s? ??s?
Ad-hoc p???atas?e?asµ???? f??µ?? ????µ
?? ???st?? ?????de? ?e??de? ?st?as? ??sa????
??a???? ?ed?µ???? ?????f?????
19
??sa???? ?p????? ?ed?µ????
S?????s? µe e??p???s? ete???e??? S???

• Wrapper/mediators
• ?e ß?s? t?? e??t?s?, µetaf???eta? a?????a,
  e?te?e?ta? se ???e S??? ?a? ta ap?te??sµata
  e??p?????ta? se µ?a ????? ap??t?s?

20
???t??? ?ed?µ???? ?a? ?e?t?????e?
21
??sa????
?e ???a ????a
??a ap????? ded?µ???? ßas??eta? se ??a
p???d??stat? µ??t??? ded?µ???? (multidimensional
data model) p?? a?apa??st? ta ded?µ??a µe t?
µ??f? e??? ??ß?? ded?µ???? (data cube) ??a?
??ß?? ded?µ???? (data cube) ep?t??pe? t??
µ??te??p???s? ?a? t?? ?e???s? t?? ded?µ???? ap?
p????? ?pt???? ????e? ??ast?se??
(dimensions)- G?a s???e???µ??e? t?µ?? st??
d?ast?se?? µ?a ??t??s? (Measure) a?t? p?? µa?
e?d?af??e? ?a µet??s??µe
?a??de??µa
??ß?? ?O??S??S
Location
Item
Time
??ast?se??
??t??s? ????µ?? ????se?? ??a t?? s???e???µ??e?
d?ast?se?? (Location, Item, Time)
22
???????????? ?e?a???a
?e?a???e? ??ast?se??
???e d??stas? pa???e? t?µ?? ap? d?af??et???
ep?peda, µp??e? ?a e?f?aste? se d?af??et???
ep?peda ?ept?µ??e?a?
??ß?? ?O??S??S
Location
??ast?se?? Product, Region, Date?e?a???e?
d?ast?se??
Country
Industry
Year
Category
Item
Region
Quarter
City
Week
Product
Month
Time
??t??s? ????µ?? ????se?? ??a t?? s???e???µ??e?
d?ast?se?? (Location, Item, Time)
Day
Store
23
???????????? ?e?a???a
?a??de??µa ???????????? ?e?a???a (Concept
Hierarchy) ??a Location
all
all
Europe
North_America
...
region
Mexico
Canada
Spain
Germany
...
...
country
Vancouver
...
...
Toronto
Frankfurt
city
M. Wind
L. Chan
...
office
??t?st???e? ??µ??
?ed?? ??µ??
24
???????????? ???t???
???t??? ?ed?µ???? (S??µa)

• Se s?es?a?? µ??t???
• ???a?e? ??ast?se??
• ???a?a? µe p????f???a s?et??? µe ???e d??stas?
• ?tem (item_name, brand, type),
• ?ime(day, week, month, quarter, year)
• ???a?a? ?e????t?? (Fact Table) ??e? ??
  ?????sµata
• t?? µet??se?? (p? a???µ?? p???se??, t?µ? se
  d?????a, ??p)
• t? p??te??? ??e?d? ???e s?et???? p??a?a
  d?ast?se??
• S??µa ?st??? (Star schema)
• ???a?a? ?e????t?? st? ???t?? p?? s??d?eta? µe
  ??a s????? ap? p??a?e? d?ast?se??

25
???????????? ???t???
?a??de??µa S??µat?? ?ste????

???a?a? Ge????t?? ?O??S??S
time_key
item_key
branch_key
location_key
units_sold
dollars_sold
avg_sales
?et??se??
4 d?ast?se?? (time, item, location, branch)
26
???????????? ???t???
?a??de??µa S??µat?? ?ste????
27
???????????? ???t???
S??µa ??f?da? (Snowflake schema) ??a ße?t??s?
t?? s??µat?? ast??? ?p?? ? ?e?a???a d?ast?se??
?a??????p??e?ta? se ??a s????? ap? µ????te????
p??a?e? d?ast?se??
28
???????????? ???t???
?a??de??µa S??µat?? ??f?da?
item
supplier
???a?a? Ge????t?? ?O??S??S
item_key item_name brand type supplier_key
supplier_key supplier_type
time_key
item_key
branch_key
branch
location_key
branch_key branch_name branch_type
units_sold
dollars_sold
avg_sales
?et??se??
29
???????????? ???t???
?ste??sµ?? Ge????t?? (Fact constellations) ????ap
??? ???a?e? Ge????t?? p?? µ???????ta? t???
???a?e? ??ast?se??, µp????µe ?a t??? d??µe ??
s?????? ap? ast???a ?a? ??a ?? ?ste??sµ?
Ge????t?? ? S??µa Ga?a??a (galaxy schema)
30
???????????? ???t???
?a??de??µa S??µat?? ??f?da?
???a?a? Ge????t?? ???S????
???a?a? Ge????t?? ?O??S??S
time_key
item_key
time_key
shipper_key
item_key
from_location
branch_key
to_location
location_key
dollars_cost
units_sold
units_shipped
dollars_sold
avg_sales
?et??se??
31
??ß?? ?ed?µ????
???????a S???? ? n-D ??ß?? ???eta? ßas????
??ß?e?d?? (base cuboid). St? pa??de??µa ? ??ß??
µe t?? t?sse?e?? d?ast?se?? (Item, Time, Branch,
Location) O 0-D cuboid p?? pe????e? t?
µe?a??te?? ep?ped? pe???????, apex cuboid. ??
p???µa t?? ??ß?e?d?? ??ß?? ded?µ????.
32
??ß?? ?ed?µ????
????µa ??ß?e?d?? ??ß?? ded?µ????
all
0-D(apex) cuboid
time
item
location
supplier
1-D cuboids
time, item
time , location
item, location
location, supplier
2-D cuboids
time, supplier
item, supplier
time, location, supplier
3-D cuboids
time, item, location
time, item, supplier
item, location, supplier
4-D(base) cuboid
time, item, location, supplier
33
?a??de??µa ?e?a?????
34
?pt???p???s? ??ß??
35
?et??se?? - S??a????se??

• ?p?????? t?e?? ?at?????e? µet??se??
• ?ata?eµ?µ??e? (Distributive) a? µp????µe ?a
  d?aµe??s??µe ta ded?µ??a ?a? ?a ?p?????s??µe t?
  s??a????st??? s????t?s? se ???e d?aµ???s?
  ?e????st? ?a? s?ed?? ?µesa ap? a?t?? t?? t?µ?? ?a
  ?p?????s??µe t?? ????? t?µ? ?? count(), sum(),
  min(), max()
• ???eß????? (Algebraic) p??? µp????µe ?a
  ?p?????s??µe t?? ????? t?µ? t?? s????t?s?? ap?
  t?? t?µ?? t?? s????t?s?? st?? d?aµe??se??
  ???s?µ?p????ta? M ?????sµata (?p?? M sta?e??),
  ??. avg(), min_N(), standard_deviation()
• ???st???? (Holistic) de? ?p???e? ????
  (p???p????t?ta) sta?e??? t???? ??a t? ????
  ap????e?s?? p?? ??e?a??µaste ??a t?? ?p?????sµ?
  t?? ?????? t?µ?? ap? t?? t?µ?? st?? d?aµe??se??,
  ??. median(), mode(), rank()

36
?et??se?? - S??a????se??

• ??t?? ap? t?? ?ept?µe?e?? p????f???e? t?? fact
  tables, µp??e? ?a ?p?????s??µe ?a? s??a????se??
  t?? ded?µ???? ??a ?a??te???? ??????? ap????s??.
• G?a pa??de??µa, a? ? fact table e??a?
• SALES(GeographyCode, ProductCode, TimeCode,
  AccountCode, Amount, Unit)
• µp????µe ?a ?p?????s??µe
• AVG(Sales) a?? Region, Product, Quarter
• MAX(Sales) a?? Brand,Month, µe Region Europe
• SUM(Sales) a?? City

37
?as???? ????e??
?a??de??µa
Total annual sales of TV in U.S.A.
Date

2Qtr
1Qtr
sum
3Qtr
4Qtr
TV
Product
U.S.A
PC
VCR
sum
Canada
Country
Mexico
sum
38
S??a????se?? ?ed?µ????
?p??tas? t?? ?p?????t?? ßas???? p??a?a
??s?µ?t?s? t?? a????st???? e???af?? st?? ßas???
(base/basic) fact table µ?a ep?p???? st??? p??
?a e???e? t? ep?ped? s???????s??
????st?? p??a?a?/??? a????sµ?t??
Sales table
sum
Extended Sales table
City-dimension sum table
39
?as???? ???eß????? ????e??

• S??a????st??? ???d?? (Roll up) s???????s? t??
  p????f???a? µet?ßas? ap? ?aµ???te?? se
  ?????te?? ep?ped? ad??µ??e?a? (p.?. ap? day se
  month)
• ??a??t??? ????d?? (Drill down) t? a?t?st??f? t??
  Roll up (p.? month se day)
• ??????t??? ?eµa??sµ?? (Slice) (s?es?a??) ep?????
• ???et?? ?eµa??sµ?? (Dice) (s?es?a??) p??ß???
• ?e??st??f? (Pivot) a?ad??ta?? t?? 2D p??ß????
  t?? p???d??stat?? ??ß?? st?? ?????

40
?as???? ???eß????? ????e??

Roll-up

• ? s??a????st??? ???d?? pe???aµß??e? t??
  ?p?????sµ? µ?a? s???????? t?µ?? ??a µ?a ??s? st??
  ?e?a???a µ?a? d??stas?? ded?µ????.
• G?a pa??de??µa, µe ??a roll-up, ?? p???se?? se
  ep?ped? t?p???? µa?a???? (Store) pa?????? t??
  s???????? p???se?? se ep?ped? p???? (City) ?a?
  a?t?? µe t? se??? t??? µe ??a a??µa roll-up
  pa?????? t?? p???se?? se ep?ped? pe??????
  (Region).

41
?as???? ???eß????? ????e??
Roll-up (?a??de??µa)
?????? ?p?ped? Quarter
?????? ?p?ped? Year SUM(Sales volumes)
42
?as???? ???eß????? ????e??
Drill-Down

• ? ???st?? pe??? ap? ??a a??te?? ep?ped? µ?a?
  d??stas?? p?? ??e? s???e?t??t??? ded?µ??a se ??a
  ?aµ???te?? ep?ped? µe p?? ?ept?µe?? ded?µ??a.
  ????e?ta? ??a t?? a?t?st??f? p???? t?? roll-up.
• G?a pa??de??µa, ?at? t? drill down, ?e????µe ap?
  t?? p???se?? a?? pe????? (Region) ?a? pa?????µe
  t?? a?a??t???? p???se?? a?? p??? (City) ?a? µet?
  t?? p???se?? a?? ?at?st?µa (Store).

43
?as???? ???eß????? ????e??
Drill-down (?a??de??µa)
Item ?p?ped? Industry
Item ?p?ped? Category
44
?as???? ???eß????? ????e??
?e??st??f? (Pivot)

• ??a??a?? t?? ??aµµ?? ?a? t?? st???? t?? ??ß??,
  ?p?? a?t?? pa???s???eta? st?? ?????
• ?e? apa?te?ta? ?a???a? ???? ?p?????sµ?? st? ??

45
?as???? ???eß????? ????e??
Pivot (?a??de??µa)
??a??a?? ??aµµ?? ?a? st????
46
?as???? ???eß????? ????e??
??????t??? (slice) ?a? ???et?? (dice) ?eµa??sµ??

• Slice ?p????? s???e???µ???? t?µ?? se ??p??a
  d??stas? (select)
• ?.?., d???e t? Store 2 ap? ta ?atast?µata ?a? t??
  ß??µ??a??e? Clothing ?a? Cosmetics
• Dicing Sß?s?µ? µ?a? ????????? d??stas??
  (project)
• ?.?., ap? ??a ??ß? p???se?? a?? p?????,
  ?µe??µ???a ?a? pe?????, ?a de???e? ? µ?s?? ????
  p???se?? a?? p????? ?a? ?µe??µ???a.

47
?as???? ???eß????? ????e??
SliceDice (?a??de??µa)
Sales volume
Products
Store1
ElectronicsToys
5,21,9
Q1
ElectronicsToys
8,90,75
Q2
????e t? Store 2 ?a? t?? ß??µ??a??e? Clothing
Cosmetics
48
Fig. 3.10 Typical OLAP Operations
49
?as???? ???eß????? ????e??
Rollup Cube

• ?e?est?? Rollup
• group by rollup product, store, city
• group by product, store, city
• group by store, city
• group by city
• ?e?est?? Cube ??a ????? t??? d??at??? s??d?asµ???
• group by cube product, store, city
• group by ???e ?p?s????? t?? product, store,
  city, a?e???t?ta ap? t? se??? p?? ?d?sa st??
  st??e? a?t?? st?? e?t???

?? ap?t??esµa t?? te?est?? de? pa???e? p??????
µ?????? p??a?e?, a??? ??a? p??a?a µe e???af?? µe
NULL ?p?? de? a?t?st???e? t?µ?
50
?e?est?? Rollup ?a? Cube
select color, make, year, sum(units) from
car_sales where make in chevy, ford
and year between 1990 and 1994 group by cube
color, make, year having sum(units) gt 0
Aggregate
Group By
Sum
(with total)
By Color
RED
Cross Tab
WHITE
By Color
BLUE
Chevy
Ford
RED
WHITE
Sum
BLUE
By Make
1990
FORD
1991
CHEVY
1992
Sum
1993
By Year
By Make
By Make Year
RED
WHITE
BLUE
By Color Year
By Make Color
Jim Gray Adam Bosworth Andrew Layman Microsoft
Hamid Pirahesh IBM
Sum
By Color
51
????te?t?????
52
????te?t?????
?etas??µat?sµ?? (Back-End)
Front-End
DW
???? ?ed?µ????
Data Marts
OLTP s?st?µata
OLAP e??a?e?a
53
????te?t????? ?????? ?p?p?d??
Multi-tier ????te?t?????
Monitor Integrator
OLAP Server
Metadata
Analysis Query Reports Data mining
Serve
?p????? ?ed?µ????
Data Marts
????? ?ed?µ????
???a?? OLAP
Front-End ???a?e?a
?p????e?s? ?ed?µ????
54
????te?t????? ?????? ?p?p?d??
Multi-tier ????te?t?????
Monitor Integrator
OLAP Server
Metadata
Analysis Query Reports Data mining
DSA
Serve
?p????? ?ed?µ????
Data Marts
????? ?ed?µ????
???a?? OLAP
Front-End ???a?e?a
?p????e?s? ?ed?µ????
55
????te?t?????? ????de?

• Sources (?????) ???e p??? ap? t?? ?p??a ?
  ?p????? ?ed?µ???? a?t?e? ded?µ??a.
• Data Staging Area (DSA) ??a ?? st?? ?p??a
  e?te????ta? ?? µetas??µat?sµ?? ?a? ? ?a?a??sµ??
  t?? ded?µ???? p??? t?? f??t?s? st?? ?p?????
  ?ed?µ????
• ?p????? ?ed?µ???? (DW), S??????? ?ed?µ???? ?a
  s?st?µata p?? ap????e???ta? ta ded?µ??a p??
  pa?????ta? p??? t??? ???ste?.
• Data Marts ?p?s????a t?? ap??????
• ??s? ?eta-?ed?µ???? (Metadata Repository) ??
  ?p?s?st?µa ap????e?s?? p????f???a? s?et??? µe t?
  d?µ? ?a? ?e?t?????a ???? t?? s?st?µat??.

56
?e???? ?etap????f???a?

• ?a µeta-ded?µ??a e??a? ta ded?µ??a p?? ??????? ta
  a?t??e?µe?a t?? ap?????? ded?µ????. ?e???????
• ?e????af? t?? d?µ?? t?? ap?????? ded?µ????
• S??µa, ??e??, d?ast?se??, ?e?a???e?, t??
  t?p??es?a t?? data mart ?a? t? pe??e??µe?? t???,
  ??p
• ?e?t??????? µetaded?µ??a
• data lineage (t?? ?st???a t?? ded?µ???? p??
  µetaf?????a? ?a? p???? µetas??µat?sµ??
  ???s?µ?p??????a?), st???e?a ??a t? p?s?
  e??µe??µ??a/p??sfata e??a?, p????f???a ep?ß?e???
  (monitoring) ??a t? ?e?t?????a t?? ap??????
  (stat?st??? st???e?a ?e?t?????a?, error reports,
  audit trails)
• ???? a??????µ??? p?? ???s?µ?p??????a? ??a t??
  pe?????e??
• ??? ape?????s? t?? ?e?t???????? pe??ß?????t??
  st?? ap????? ded?µ????
• ?ed?µ??a s?et??? µe t?? ap?d?s? t?? s?st?µat??
• Business data
• ????t???? ????s??, ?d???t?s?a? ded?µ????, ??p

57
????te?t????? ?etas??µat?sµ??

• Back-End ???a?e?a
• ETL (Extract-Transform-Load) efa?µ???? ?fa?µ????
  p?? e?te???? t?? d?ad??as?e?
• ??a?????,
• µetaf????,
• µetas??µat?sµ??,
• ?a?a??sµ?? ?a?
• f??t?s?? t?? ded?µ????
• ap? t?? p???? st?? ?p????? ?ed?µ????.
• Front-End ???a?e?a
• ?fa?µ???? ?????s?? ?fa?µ???? pa?a????? a?af????,
  OLAP , DSS, Data Mining

58
?etas??µat?sµ??
Back-End ???a?e?a

• Data extraction
• F??e ded?µ??a ap? p???ap???, ete???e?e?? ?a?
  e??te????? p????
• Data cleaning
• ??t?p?sµ?? ?a??? sta ded?µ??a ?a? d?????s? t???
  ?ta? e??a? d??at??
• ?a?ade??µata ?ed?µ??a p?? pa?aß?????? t???
  ?a???e? t?? ß?s?? d?p??e???af??, pa?aß??se??
  p??te???t?? ? ????? ??e?d???, t?µ?? e?t?? ?????,
  pa?aß??se?? ??????? ?a?????, ??p S?????µa ?a?
  s??????se?? ????p? ded?µ??a
• ?µ??e??p???s? ??e?d???
• Data transformation
• ?etat??p? t?? ded?µ???? ap? t? t?p??? format st?
  format t?? ap??????

59
?etas??µat?sµ??
Back-End ???a?e?a

• Load
• ?a????µ?s?, d?µ??????a pe???????, e??p???s?
  (consolidate), ?p?????sµ?? ??e??, ??e????
  integrity, d?µ??????a e??et????? ?a? d?aµe??se??
• ? e??µ???s? / e?sa???? ded?µ???? st?? p???? de
  ???eta? µ?s? SQL, s?????? µ?s? e??a?e??? batch
  loading p?? d?a??t??? ??a ta S???
• Refresh
• ?et?fe?e t?? t??p?p???se?? ap? t?? p????
  ded?µ???? st?? ap????? ded?µ????

60
???a?e?a ??a t?? ?p?st????? ?p?f?se??
Front-End ???a?e?a

• Ad hoc e??t?se?? ?a? a?af????
• ?.?., MS Excel, Oracle Forms,
• OLAP
• pivot tables, drill down, roll up, slice, dice
• Data Mining