Kapitel 10.2: Datawarehouse

Kap. 10 Anwendungen Relationaler Systeme
Kap 10.2 Datawarehouse (DWH)
Decision Support Systeme fürs Management
DIM2
Online Analytial Processing (OLAP)
S,T
D
3
IM
Dim1_Id
Dim1_L3_Id
Dim1_L2_Id
Dim1_L1_Id
ALL
DIM1
1
DIM2
MD View
Relational View
Dim1_ Dim1_ Dim1_ Dim1_ Dim2_ Dim3_ S
T
L1_Id L2_Id L3_Id Id
Id
Id
(Sales) (Turnover)
S,T
M
DI
3
Dim1_Id
Dim1_L3_Id
Dim1_L2_Id
Dim1_L1_Id
ALL
DIM1
Dim1_Id
Dim1_L3_Id
Dim1_L2_Id
Dim1_L1_Id
ALL
2
MD Model
OLAP Application Level
Schema Transformation
MDQL to ROLAP
Algebra
Translation
ROLAP Middleware
ROLAP Algebra
to SQL
Translation
Relational
Model
RDBMS
Dimensionen mit Hierarchien
PRODUCT
CUSTOMER
DISTRIBUTION
TIME
All Products
All Customer
All Distributions
All Time
Type
Region
Sales
Organization
Year
Brand
Nation
Category
Trade Type
Container
Business Type
Distribution
Channel
Quarter
Month
3
Year
Quarter
Month
s
Bu
ss
in e
e
yp
eT
ad
r
T
on
ti
Na
on
gi
Re
lC
Al
odu
cts
m
Ty
pe
Bra
nd
gor
y
o
ust
Ca
te
Co
nta
ine
r
pe
Ty
Sales
Cost
Quantity
Typische Aggregations-Anfragen
select Brand, Quarter, sum(Sales)
from SalesCube
group by Brand, Quarter
having
sum(Sales) > 100.000
4
Typische Aggregations-Anfragen mit Restriktionen pro
Dimension
select Brand, Quarter, sum(Sales)
from SalesCube
where Year = 2002 and Nation = Germany
group by Brand, Quarter
having
sum(Sales) > 100.000
All Time
Year
Container
Category
Brand
All Customer
Region
Nation
Trade Type
Sales
Cost
Quantity
Business Type
Type
Month
Distribution
Channel
Sales
Organization
All Distribution
Ch
l
5
Sum - Sales
Region
Asia
Nation
China
Japan
Asia Total
Europe
France
Germany
Spain
Europe Total
Total
Year
Quarter
1998
1
2
1998 Total
3
Total
4
1
4
7
2
5
8
1
4
7
2
5
8
3
6
9
pot ( R, G, F )
ì{Year, Quarter , Re gion, Nation}, {Year , Quarter , Re gion}, {Year, Quarter}, ü
G=í
ý
î{Year}, {Year, Re gion, Nation}, {Re gion, Nation}, {Re gion, Nation}, {Nation}þ
6