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.