13.1 was ist ein data warehouse, motivation ... - dbs.ethz.ch file1012 109 106 103 size vs speed...
TRANSCRIPT
Kap13-1OHO2002
Kap. 13 Data Warehouse
13.1 Was ist ein Data Warehouse, Motivation? 13.2 Data Cube und SQL- Operationen13.3 Cube Operationen13.4 Physischer Entwurf, Implementierung von Cubes
Kap13-2OHO2002
• Zugriff auf und Kombination von Daten aus mehreren unterschiedlichen Quellen,
• komplexe Datenanalyse über mehrere Quellen,• multidimensionale Sichten auf die Daten,
die die jeweilige Perspektive reflektieren,Analyse der zeitlichen Entwicklung, auch wenn Datenquellen sich auf aktuellen Datenbank-Zustand beschränken.
Quelle QuelleQuelle
13.1: Was ist Data Warehousing, Motivation
Kap13-3OHO2002
Daten Analyse (J. Gray)
• Benutzer extra-hiert Daten mit Anfrage aus der Datenbank
• Dann werden Daten analysiert und visualisiert mit Desktop Tools
Spread Sheet
Table
1
1015
1012
109
106
103
Size vs Speed
Access Time (seconds)10-9 10-6 10-3 10 0 103Cache
Main
Secondary
Disc
Nearline Tape Offline
Tape
OnlineTape
104
102
100
10-2
10-4
Price vs Speed
Access Time (seconds)10-9 10-6 10-3 10 0 103
Cache
MainSecondary
Disc
Nearline Tape
OfflineTape
OnlineTape
Size(B) $/MB
Kap13-4OHO2002
OLTP vs. OLAP• OLTP (‘Online Transaction Processing’):
Transaktionsorientierte Datenzugriffe, typischerweise Erfassenvon Daten und Lesezugriffe auf diesen.“Tagesgeschäft bedienen”
Beispiel aus dem Bankbereich: “Wie hoch ist mein Kontostand?”Beispiele für OLTP-Systeme: Buchungssysteme, Lagerverwaltung, Aktien-/Wertpapierhandel.
• OLAP (‘On-line Analytical Processing’): Konsolidierung, Viewing und Analyse der Daten in mehreren Dimensionen, Berichtsgenerierung (RPG, Decision Support)“strategische Entscheidungen unterstützen”
Beispiel: „Was ist der Zusammenhang zwischen Kontostand und Häufigkeit von Buchungen?“
„They (the users) don‘t even know what they want!How can we provide it?“
Kap13-5OHO2002
BeispieltabelleSALES
Model Year Color Sales Chevy 1990 red 5 Chevy 1990 white 87 Chevy 1990 blue 62 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 blue 49 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 blue 71 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 blue 63 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 blue 55 Ford 1992 red 27 Ford 1992 white 62 Ford 1992 blue 39
Kap13-6OHO2002
Beispiel für einen „Bericht“
Sales Roll Up byModel by Year byColor
Model Year ColorSales
by Modelby Yearby Color
Salesby Modelby Year
Salesby Model
Chevy 1994 black 50white 40
901995 black 85
white 115200
290
Kap13-7OHO2002
Operationale Datenbanken vs. Data WarehousesOperationale Datenbanken Data Warehouses
Entste-hung
jeweils für Anwendungs-klasse oder aus bestimmterPerspektive heraus
mehrere Perspektivengleichzeitig
Anforde-rungen
Bekannt vage
Bedeu-tung
alltäglicheGeschäftsabläufe
Entscheidungen desManagements, die sich aufProfitabilität auswirken
Daten-zugriff
Ein Aufruf liefert wenigeZeilen zurück.
Grosse Datenmengen werdenzugegriffen, um das Ergebnis zuermitteln.
Tuning Getuned für häufigeZugriffe auf kleineDatenmengen
Getuned für eher seltene Zugriffeauf grosse Datenmengen
Daten-volumen
Datenbestand wird füroperationales Geschäftgebraucht.
Grosser Datenbestand wird fürstatist. Analysen, Vorhersagen,ad hoc Reports gebraucht.
Kap13-8OHO2002
Operationale Datenbanken vs. Data Warehouses(2)
Operationale DatenbankenData WarehousesDaten-aufbe-
solange es dasTagesgeschäft erfordert
Langfristig, um Reporting überZeiträume oder Vergleiche zuermöglichen.Üblicherweise wird einbestimmter Zeitpunkt in derVergangenheit beschriebenEntscheidungen desManagements, die sich aufProfitabilität auswirkenNicht ganz so hoch wie inProduktionsumgebungen,abhängig davon, ob weltweiterZugriff.Flexibilität
wahrungAktua-lität
auf die Minute
Bedeu-tung
alltäglicheGeschäftsabläufe
Verfüg-barkeit
Hohe Verfügbarkeiterforderlich.
Hohe PerformanceEntwurfsziel
Kap13-9OHO2002
Data Warehousing - warum reichen herkömmliche Datenbank-Konzepte und -
Technologie nicht aus? Im Prinzip reichen sie schon, aber:
• Höheres Abstraktionsniveau durch neue, mächtige Operatoren vereinfachen die Anwendungsentwicklung, insbesondere durch:
Cube, Drill-Down, Roll-Up.
• Direkte Optimierungsmöglichkeit der neuen Operatoren! Abbildung auf Standard-SQL hinsichtlich der Laufzeit nicht akzeptabel.
Kap13-10OHO2002
13.2 Data Cube und SQL-Operationen
• Idee: Cube wird aus relationaler DB gebildet, Vorteil:
Aggregationen werden nahe bei den Daten durchgeführt. Filterung der Daten gleichzeitig möglich
• Visualisisierung und Exploration wird auf dem Daten Cube durchgeführt
1990 19911992
ALLRed
Blue0
50
100
150
200 150-200
100-150
50-100
0-50
Kap13-11OHO2002
Aggregations-Funktionen in relationalen DBMS
• SQL hat Aggregatfunktionensum(), min(), max(), count(), avg()
• Viele Systeme erweitern diese um:statistische Funktionen, Benutzer definierte Funktion, ...
• Hauptidee:Berechne aus allen Werten einer Spalte einen Skalar.
• SyntaxSUM()
select sum(units)from inventory;
Kap13-12OHO2002
Relationaler Group By Operator
• Group By erlaubt Aggregation über Teilmengen von Tupeln
• Resultat ist neue Tabelle, eine Zeile pro Gruppe• Syntax: Table SUM()
A
B
C
D
attributeA A A B B B B B C C C C C D D
select location,
sum(units)from inventorygroup by locationhaving nation = “USA”;
Kap13-13OHO2002
Probleme mit Standard-SQL• Histogramme nötig
• Teilsummen und Gesamtsumme gleichzeitig
drill-down & roll-up reports
• Pivot-Tabellen nötig
• ErkenntnisDies sind keine relationalen Operationen, aber in vielen Berichtsgeneratoren vorhanden
sum
M T W T F S S �AIR
HOTELFOODMISC
�
F() G() H()
Kap13-14OHO2002
Einführung eines „ALL“-Wertes• Warum?
Eine zu SQL passende Erweiterung, um Aggregationen wieder als (flache) Tupel imRelationenmodell darstellen zu können
• Bedeutung des ALL-Wertes? ALL ist Abkürzung für Menge, die durch Nestung entlang der Dimension entstehen würde bei gleichzeitiger Aggregation, also im Beispiel für die Mengen
{red, white, blue} in der Color-Dimension{1990, 1991, 1992} in der Year-Dimension{Chevy, Ford} in der Model-Dimension
Kap13-15OHO2002
SQL mit ALL-Erweiterung für Berichte?
Sales SummaryModel Year Color UnitsChevy 1994 black 50Chevy 1994 white 40Chevy 1994 ALL 90Chevy 1995 black 85Chevy 1995 white 115Chevy 1995 ALL 200Chevy ALL ALL 290
SELECT Model , ALL, ALL, SUM( Sal es)FROM Sal esWHERE Model = ' Chevy'
GROUP BY ModelUNIONSELECT Model , Year, ALL, SUM( Sal es)
FROM Sal esWHERE Model = ' Chevy'GROUP BY Model , Year
UNIONSELECT Model , Year, Col or, SUM( Sal es)
FROM Sal esWHERE Model = ' Chevy'GROUP BY Model , Year, Col or;
Kap13-16OHO2002
Der „Data CUBE Operator“ (J. Gray)...verallgemeinert den Group By-Operator und die Aggregation
CHEVY
FORD 19901991
19921993
REDWHITEBLUE
By Color
By Make & Col
By Make & Year
By Color & Year
By MakeBy Year
Sum
The Data Cube and The Sub-Space Aggregates
REDWHITEBLUE
ChevyFord
By Make
By Color
Sum
Cross TabRED
WHITEBLUE
By Color
Sum
Group By (with total)Sum
Aggregate
Kap13-17OHO2002
Idee: Tupel = Punkt im N-dimensionalen Raum
• N-dimensionale Aggregation (sum(), max(),...)a1, a2, ...., aN, f()übliche Aggregation aus den relationalen Rohdaten
• Super-Aggregation über N-1 dimensionale Sub-CubesALL, a2, ...., aN , f()a3 , ALL, a3, ...., aN , f()...a1, a2, ...., ALL, f()
dies sind die N-1 -dimensionalen Cross-Tabellen.• Super-Aggregation über N-2 dimensionale Sub-Cubes
ALL, ALL, a3, ...., aN , f()...a1, a2 ,...., ALL, ALL, f()
Kap13-18OHO2002
Cube-Beispiel
SALES Model Year Color Sales Chevy 1990 red 5 Chevy 1990 white 87 Chevy 1990 blue 62 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 blue 49 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 blue 71 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 blue 63 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 blue 55 Ford 1992 red 27 Ford 1992 white 62 Ford 1992 blue 39
DATA CUBE Model Year Color Sales ALL ALL ALL 942 chevy ALL ALL 510 ford ALL ALL 432 ALL 1990 ALL 343 ALL 1991 ALL 314 ALL 1992 ALL 285 ALL ALL red 165 ALL ALL white 273 ALL ALL blue 339 chevy 1990 ALL 154 chevy 1991 ALL 199 chevy 1992 ALL 157 ford 1990 ALL 189 ford 1991 ALL 116 ford 1992 ALL 128 chevy ALL red 91 chevy ALL white 236 chevy ALL blue 183 ford ALL red 144 ford ALL white 133 ford ALL blue 156 ALL 1990 red 69 ALL 1990 white 149 ALL 1990 blue 125 ALL 1991 red 107 ALL 1991 white 104 ALL 1991 blue 104 ALL 1992 red 59 ALL 1992 white 116 ALL 1992 blue 110
CUBE
Kap13-19OHO2002
SQL-Erweiterung: CUBE - Operator• Im Beispiel:
• Beachten: Select und Group By wiederholen Aggregationsliste
select model, make, year, sum(units)from car_saleswhere model in {“chevy”, “ford”}and year between 1990 and 1994group by cube model, make, year having sum(units) > 0;
Kap13-20OHO2002
• CUBE hinter Group By mit Attributliste definiert Koordinatenachsen (Dimensionen) des Cube
• erlaubt zusätzliche Aggregationen durch Dimensionshierarchien (e.g., sales by quarter):
select <field list> <aggregate list>from <table expression>where <search condition>group by [ cube | rollup] <aggregate list>having <search condition>
select store, quarter, sum(units)from salesgroup by rollup store,
quarter(date)as quarterand year = 1994;
Syntax
Kap13-21OHO2002
13.3 Cube Operationen, Operationen auf mehrdimensionalen Arrays
CHEVY
FORD 19901991
19921993
REDWHITEBLUE
By Color
By Make & Col
By Make & Year
By Color & Year
By MakeBy Year
Sum
The Data Cube and The Sub-Space Aggregates
REDWHITEBLUE
ChevyFord
By Make
By Color
Sum
Cross TabRED
WHITEBLUE
By Color
Sum
Group By (with total)Sum
Aggregate
Kap13-22OHO2002
Dimensionen, Measures
Dimensionen: Koordinatenachsen (x1, ..., xn) des mehrdimensionalen Arrays (“Datenwürfels”)
Measures: Werte der Funktionen f1(x1,...,xn), f2(...),...In relationaler Terminologie: Measures sind funktional abhängig von den Dimensionen.
Dimensionshierarchie: Vergröberung für die Aggregation, Benannte Partitionierung des Wertebereichs einer Dimension. Beispiele: Stadt -> Kanton
Datum -> Monat -> QuartalDrill-Down, Drill-up: Aggregation entlang der Dimensionshierarchie,
Down für Verfeinerung, Up für Vergröberung
Kap13-23OHO2002
Beispiel Dimensionen vs. MeasuresProdukt
Filiale
DatumIrchel
CentralWipkingen
Tee
Milch
Brot
07.01. 08.01.
52
95Dimensionen:
Produkt, Datum, Filiale
Measure: Umsatz
Kap13-24OHO2002
Operationen auf multi-dimensionalen Daten
• Vorbild: Relationenalgebra: Input: Cube(s), Output: Cube, wenige mächtige Operationen, Mächtigkeit durch geschickte Kombination!
• Transformation zwischen Measures und Dimensions: Pull & PushDabei nötig: elementbildende Funktionen, damit Funktionalabhängigkeit „Dimensions -> Measures“ erhalten bleibt.
• Dimensionsreduktion, falls nur ein Wert vorkommt: Destroysonst: vorher Aggregieren, neue Elemente bilden, Operator Merge!
• Restriktion: Neuer Cube enthält nur nur noch Werte einer Dimension, die ein Filterprädikat erfüllen (naheliegend)
• Join: binäre Operationen!
• Wir verschaffen uns einen Einblick in Merge/Cross-Tab, Push und Pull
Kap13-25OHO2002
Cross-Tabelle
HessenBayern
Saarland
BMW
Audi
Opel
07.01. 08.01.
12
5
15 520
5
12
Marke
Projektion mit Aggregation auf Teilraum
Datum
Bundesland
Kap13-26OHO2002
Push
HessenBayern
Saarland
BMW
Audi
Opel
07.01. 08.01.
<5, 08.01.>
<15,07.01><5, 07.01>
MarkePush-Operation im Beispiel auf Datum: setzt Datumswerte zu bisherigem Measure.
Nutzen: Verwendung von Aggregations-funktionen auf die so erweiterten Measure-Elemente
<12,08.01.>
Datum
Bundesland
Kap13-27OHO2002
Pull
Audi
Opel
07.01. 08.01.
HB
12
5
15 5
<5,B>
<15,H><5,H>
Marke<12,B> Pull-Operation
zieht Element aus Measures heraus und führt damit neue Dimension ein.
Wichtig: mit Push und Pull trans-formiert man zwischen Measures und Dimensionen
BMW
Datum
Bundesland
Kap13-28OHO2002
13.4 Speicherung multi-dimensionaler Daten
• Problem: Wie werden Zellen des Cubes physisch angeordnet?
• Speicherung multi-dimensionaler Arrays als generelle AntwortAggregationsfunktion apriori nicht bekannt.Aggregationsfunktion bekannt
• Indexierung für CubesBitmap-Index
Kap13-29OHO2002
Motivation - BereichsanfragenUmsatz
MA-Anzahl
Datum100
200
300
100’
200’
300’
07.01. 08.01.
Kap13-30OHO2002
Motivation - Bereichsanfragen - 2Umsatz
MA-Anzahl
Datum100
200
300
100’
200’
300’
07.01. 08.01.
Kap13-31OHO2002
Lineare Speicherzuordnung- Illustration
n+1 n+2
... n
n2n2+1
1 2 3
Bevorzugte Aggregationsrichtung = Speicheranordnung.
Für andere Aggregationen: Replikation mit anderer Speicheranordnung
Kap13-32OHO2002
Space-Filling Curves
Row-wise20
31 Peano
10
32
Hilbert10
23 Gray
10
23
Kap13-33OHO2002
Vorausberechnung von Aggregaten
0 1 2 3 4 50 3 5 1 2 2 31 7 3 2 6 8 22 2 4 2 3 3 5
0 1 2 3 4 50 3 8 9 11 13 161 10 18 21 29 39 442 12 24 29 40 53 63
Prefix-Sum Array
Array
Kap13-34OHO2002
Vergröberung durch Blockbildung
0 1 2 3 4 50 3 5 1 2 2 31 7 3 2 6 8 22 2 4 2 3 3 5
0 1 2 3 4 50 - - - - - -1 - 18 - 29 - 442 - 24 - 40 - 63
Blocked Prefix-Sum
Array
Array
Kap13-35OHO2002
Bitmap Indexing• Eigenschaften von Data Warehouses:
Read-Mostly Umgebungen,komplexe Anfragen,grosses Datenvolumen.
Erfordert/ermöglicht genauere Betrachtung von Indexmechanismen
• Ansatz dazu: Bitmap Indexing, d.h.
• Invertierte Listen werden durch Bitvektoren repräsentiert.
• Vorteile:Kompaktheit von Bitvektoren ermöglicht relativ oft, dass Bitmap Indices im Main Memory verwaltet werden können.Operationen auf Bit-Ebene werden Hardware-seitig gut unterstützt.Indices werden auch für Selektionen mit geringerer Selektivität akzeptabel (ähnlich wie im Information Retrieval).
Kap13-36OHO2002
Bitstring-Darstellung der Pointerlisten
Bitmap - ein Bit-Spaltenvektor pro Attributwert
V7 V6 V5 V4 V3 V2 V1 V0V8πA(R) TupelNr
321282207564
000010000000
000000001000
000000000010
000000000100
000000000001
100000000000
010101100000
001000000000
000000010000
Value 2kommt in Tupel 6
vor
123456789101112
Kap13-37OHO2002
Mehrstufigkeit oder Vergröberung durch Cluster-Bildung
Clusterung der Attributwerte: “Verodern” der SpaltenvektorenClusterung der Tupel: “Verodern” der Zeilenvektoren
000010000000
000000001000
000000000010
000000000100
000000000001
100000000000
010101100000
001000000000
000000010000
V8 V7 V6 V5 V4 V3 V2 V1 V0πA(R) TupelNr
321282207564
123456789101112
Kap13-38OHO2002
123456789
101112
Tupel-Nr
Tupel-Cluster-Nr
1
2
3
Ergebnis im BeispielAttribut-Cluster-Nr
1 2 3
0
1
1
1
0
1
1
1
0
V8 V7 V6 V5 V4 V3 V2 V1 V0
Attributwert 1,2 oder 3 kommt in Tupel 5,6,7 oder 8 vor
Kap13-39OHO2002
Literatur
• Jim Gray et al.: Data Cube: A relational aggregation operatior generalizing group-by, cross-tab, and sub-totals. In: Data Mining and Knowlegde Discovery 1, 29-53 (1997).
• A. Datta and H. Thomas: The cube data model: a conceptual model and algebra for on-line analytical processing in data warehouses. Decision Support Systems, 27(3):289--301, 1999.
• M. Gyssens, Laks Lakshmanan: A foundation for multi-dimensional databases, VLDB Conference 1996.
• OLAP Council White Paper.
• DB Hersteller White Papers im Web suchen, z.B. MSDN Library: SQL Server 2000 Data Warehouse and OLAP Components.