Data Warehouses Anfragen in Operativen Systemen und Data

Data Warehouses
Sommersemester 2011
Melanie Herschel
[email protected]
Lehrstuhl für Datenbanksysteme, Universität Tübingen
Anfragen in Operativen Systemen und Data
Warehouses
Anfragen
Operative
Datenbank
UPDATE
• Transaktionale Anfragen
SET
• Insert, Update, Delete, Select
WHERE
AND
• Anfragen über einzelne / wenige Tupel
• Anfrageoptimierung siehe VL Datenbanken II
Order
amount = amount + 1
OrderID = 1
BookID = 204
Jahr
• Analytische Anfragen
Data
Warehouse
• Bulk-Insert und Select
• Typische Anfragetypen (drill down, slice, dice, ...)
• Spezialisierte Optimierungsverfahren
2007
2006
Region
2005
2004
...
North America
Asia
Europe
2003
2002
Books CDs DVDs ...
Produktgruppe
2
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Gegeben: ein Multidimensionales Datenmodell
Beispiel eines multidimensionalen Datenmodells (Cube und Dimensionshierarchien)
Datenwürfel
(feinste Granularität)
Dimensionshierarchien
Zeit
Produkt
BlueRay
DVD
2
...
3
3
2
...
5
3
5
4
4
...
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
...
Ma
Ja
i
2
Ap
ril
2
Mä
2
Fachliteratur
Fe
2
rz
2
b.
2
n.
Musik
2
Ort
...
Berlin
Lyon
Paris
Stuttgart
Produkte
Jahr
Typen
Quartal
Ort
Kategorien
Monat
Stadt
Zeit
3
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Ziel: Beantworten Analytische Anfragen
• Erinnerung: Das Data Warehouse integriert Daten, um eine globale Sicht
über Geschäftsprozesse zu erlangen, die für Analysezwecke verwendet
werden soll.
• Im Unterschied zu Anfragen auf “klassichen” relationalen Daten in
operativen Systemen folgen analytische Anfragen oft gewissen
Mustern.
• Man unterscheidet zwischen OLTP (Online Transaction Processing)
Anfragen in operativen Datenbanken und OLAP (Online Analytical
Processing) Anfragen in Data Warehouses.
4
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Beispiele für OLAP Anfragen
• Navigation innerhalb eines Cubes
z.B. “Anzahl verkaufter Kinderbücher im Januar, unabhängig vom Ort”
• Navigation durch verschiedene Granularitätsebenen
z.B. “Verkaufszahlen pro Produkttyp (Musik, Film, ...) in einem Quartal
pro Standort”
• Navigation durch mehrere korrelierte Cubes
z.B. “Gesamtverkaufszahlen in Läden und im Internet”, wobei wir zwei
Cubes (Produkt / Zeit / Stadt) und (Produkt / Zeit / Kunde) haben.
• Rankinganfragen
z.B. “Die 10 beliebteste Bücher im Jahr 2009 pro Land”.
• In diesem ersten Teil von Kapitel 4 besprechen wir häufige
Anfragemuster für OLAP Anfragen genauer.
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Kapitel 4.1
Typische Anfragen
OLAP Operationen
• Innerhalb eines Cubes
• Über mehrere Cubes
• Pivot
Sprachen für OLAP
• MDX
• SQL Erweiterungen
6
5
Annahmen
• In diesem Abschnitt nehmen wir an, dass das Data Warehouse Schema (=
Schema der dispositiven Datenbasis) nur einen einzelnen Datenwürfel
beinhaltet.
• Zur Einfachheit der Darstellung verwenden wir stets maximal drei
Dimensionen. Die hier vorgestellten Konzepten gelten aber auch für
Datenwürfel mit beliebig vielen Dimensionen.
• Zur Einfachheit der Darstellung verwenden wir eine Kennzahl pro Fakt. Im
Allgemeinen können beliebig viele Kennzahlen pro Fakt existieren.
7
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Überblick Besprochener Anfragemuster
• Alle in diesem Abschnitt besprochenen Anfragemuster dienen zur
Navigation im multidimensionalen Datenmodell.
• Roll-up / Consolidate: Navigiere zu Daten gröberer Granularität
• Drill-down: Navigiere zu Daten feinerer Granularität
• Drill-out / Split: Expandiere Daten durch weitere Dimensionen
• Drill-in / Merge: Reduziere Dimensionalität der Daten
• Slice: Selektiere Daten basierend auf einer Einschränkung einer
Dimension.
• Dice: Selektiere Daten basierend auf Einschränkungen mehrerer
Dimensionen.
8
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Definition
Roll-Up (auch Consolidate)
Gegeben sei ein n-dimensionaler Datenwürfel C = (DS, M) = ({D1, ..., Dn}, {M1, ..., Mp}) mit den
Dimensionen entsprechenden Granularitäten G = {l1, l2, ...,ln}, wobei li der Ebene des Schemas {D1, ...,
Dm, TopD; "} der Dimension Di entspricht, 1 ! li ! m+1.
Während einer Roll-Up Operation (auch Consolidate genannt) wird für eine Menge DSup ⊆ DS zu
einer gröberen Granularität navigiert, d. h. es gilt
∀ Di ∈ DSup, Dj ∈ DS, i = j, i < m + 1: lj < li ! m+1
und
∀ Di ∉
DSup,
Dj ∈ DS, i = j: li = lj
• Ein Roll-up entspricht also einem Hinauszoomen aus dem aktuellen Datenwürfel, was dazu
führt, dass das Ergebnis einen geringeren Detailgrad hat,
z.B. Rollup des Würfels (Produktkategorie / Monat / Stadt) zu (Produkttyp / Monat / Stadt).
• Dieses Hinauszoomen geschieht entlang einer oder mehrerer Dimensionen, z.B. Roll-Up
Beispiel oben oder Roll-up zu (Produkttyp / Quartal / Stadt).
• Es ist auch möglich, zur Wurzel TopD einer oder mehrerer Dimensionen zu navigieren.
Geschieht dies entlang aller Dimensionen, beinhaltet das Ergebnis nur noch einen Fakt.
9
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Beispiel - Roll-Up einer Einzelnen Dimension
Roll-Up einer einzelnen Dimension
Produkt
(Kategorie)
2
2
...
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
i
...
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Roll-Up der Produkt-Dimension:
(Kategorie / Monat / Stadt)
! (Typ / Monat / Stadt)
Produkt
(Typ)
Ort
(Stadt)
...
Medien
Lyon
Paris
Stuttgart
Berlin
i
...
Ma
Ap
ril
.
rz
Mä
n.
Bücher
Ja
Ma
rz
ril
Ap
Mä
Fe
b
Ja
.
2
n.
DVD
Fe
b
BlueRay
Zeit
(Monat)
10
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Beispiel - Roll-up Mehrerer Dimensionen
Roll-Up mehrerer Dimension
Produkt
(Kategorie)
BlueRay
2
2
...
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
...
Ma
Ap
Mä
Fe
Ja
i
3
ril
3
rz
2
b.
2
n.
DVD
Roll-Up der Produkt
und der Jahr Dimension:
(Kategorie / Monat / Stadt)
! (Typ / Quartal / Stadt)
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Produkt
(Typ)
Ort
(Stadt)
...
Medien
Lyon
Paris
Stuttgart
Berlin
Q2
Q1
Bücher
Zeit
(Quartal)
11
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Beispiel - Roll-up zur Wurzel aller Dimensionen
Roll-Up zur Wurzel aller Dimensionen
Produkt
(Kategorie)
BlueRay
DVD
2
2
3
2
2
...
3
2
...
3
5
4
4
...
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
i
...
Ma
rz
ril
Ap
Mä
Fe
b
Ja
.
5
n.
Musik
Fachliteratur
Roll-Up zur Wurzel
aller Dimensionen:
(Kategorie / Monat / Stadt)
! (Produkte / Zeit / Ort)
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Produkt
(Produkt)
Produkte
Ort
(Ort)
Ort
Zeit
Zeit
(Zeit)
12
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Ableitung von Kennzahlen
• Wiederhohlung:
• Kennzahl gehört zu einem Fakt, dieser hat wiederum eine gewisse Granularität.
• Zwei Eigenschaften einer Kennzahl:
• Numerischer Wert
• Formel, mit der die Kennzahl aus anderen abgeleitet werden kann.
• Bei einem Roll-Up navigieren wir von einer feineren Granularität G1 der Fakten zu einer
gröberen Granularität G2 der Fakten.
• Die Formel, die für Kennzahlen von Fakten der Granularität G2 definiert ist (falls
vorhanden) wird angewandt, um den numerischen Wert abzuleiten.
• Dabei ist allerdings die Eigenschaft der Summierbarkeit (additiv, semi-additiv oder
nicht additiv) der Kennzahlen zu beachten.
• Im Fall semi-additiver oder nicht-additiver Kennzahlen lassen sich ggf. Kennzahlen nur
aus Basisdaten (Daten feinster Granularität in allen Dimensionen) ableiten.
13
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Roll-Up
Ableitung von Kennzahlen
Leiten Sie die Kennzahlen des aus dem Roll-Up resultierenden Cubes ab (nur anhand sichtbarer Werte)
Produkt
(Kategorie)
DVD
2
2
3
2
2
4
3
2
3
3
5
4
4
5
2
2
2
2
2
3
Kinder
2
2
3
3
2
2
Belletristik
5
3
5
4
4
2
Ma
i
Ju
ni
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Produkt
(Typ)
Ort
(Stadt)
...
Medien
Lyon
Paris
Stuttgart
Berlin
Bücher
Q1
rz
ril
Ap
Mä
Fe
b
Ja
.
5
n.
Musik
Fachliteratur
Roll-Up der Produkt
und der Jahr Dimension:
(Kategorie / Monat / Stadt)
! (Typ / Quartal / Stadt)
Ort
(Stadt)
Q2
BlueRay
Zeit
(Quartal)
14
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Down
• Drill-Down ist die inverse Operation zu Roll-Up.
• Ermöglicht Navigation von einem gröberen Detailgrad zu einem feineren Detailgrad
(Hineinzoomen) entlang mindestens einer Dimension.
• Z.B. von (Produkttyp / Quartal / Stadt) zu (Kategorie / Monat / Stadt).
Drill-Down
Gegeben sei ein n-dimensionaler Datenwürfel C = (DS, M) = ({D1, ..., Dn}, {M1, ..., Mp}) mit den
Dimensionen entsprechenden Granularitäten G = {l1, l2, ...,ln}, wobei li der Ebene des
Schemas {D1, ..., Dm, TopD; !} der Dimension Di entspricht, 1 ! li ! m+1.
Während einer Drill-Down Operation wird für eine Menge DSDOWN ⊆ DS zu einer feineren
Granularität navigiert, d. h. es gilt
∀ Di ∈ DSup, Dj ∈ DS, i = j: li < lj
und
∀ Di ∉
DSup,
Dj ∈ DS, i = j: li = lj
15
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Down
Beispiel - Drill-up Mehrerer Dimensionen
Roll-Up mehrerer Dimension
Produkt
(Typ)
Ort
(Stadt)
...
Medien
Lyon
Paris
Stuttgart
Berlin
Produkt
(Kategorie)
BlueRay
2
...
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
i
...
ril
rz
Mä
Ja
.
2
n.
DVD
Fe
b
Drill-Down der Produkt
und der Jahr Dimension:
(Typ / Quartal / Stadt)
! (Kategorie / Monat / Stadt)
2
Ma
Zeit
(Quartal)
Ap
Q2
Q1
Bücher
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
16
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Zusammenfassung Roll-Up / Drill-Down
• Operatoren zur Navigation zwischen unterschiedlichen Granularitäten
entlang einer oder mehrerer Dimensionen.
• Roll-Up: Weniger Detailgrad
• Drill-Down: Mehr Detailgrad
• Ermittlung der Kennzahlen (wenn Ergebniscube nicht materialisiert):
• Bei Roll-Up: mögliche Ableitung anhand der Detaildaten im Eingabecube.
• Bei Drill-Down: Ableitung aus Daten feinster (und materialisierter)
Granularität
• In beiden Fällen bleibt die Dimensionalität des Datenwürfels unverändert,
d.h. die Anwendung eines Roll-Up / Drill-Down auf einen n-dimensionalen
Cube resultiert in einem n-dimensionalen Cube.
• Ausnahme: Navigation von oder zur Wurzel mindestens einer Dimension.
17
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Out / Split
• Bei einem Drill-Out (auch Split genannt) wird ein n-dimensionaler
Datenwürfel durch k weitere Dimension mit feinerer Granularität
als TopD ergänzt, wobei k >= 1.
! Ergebnis ist ein (n+k)-dimensionaler Datenwürfel
• Wie bei Drill-Down wird auch hier zu detaillierteren Daten navigiert.
• Im Gegensatz zu einem Drill-Down wird der Detailgrad nicht wie
bisher durch eine feinere Granularität erhöht, sondern durch eine
Anreicherung dimensionaler Information.
18
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Out / Split
Drill-Out des 1D-Würfels (Kategorie) zum 2D-Würfel (Kategorie / Quartal)
Produkt
(Kategorie)
Drill-Out der
Zeit-Dimension
Produkt
(Kategorie)
BlueRay
BlueRay
30
DVD
40
DVD
Musik
20
Musik
Fachliteratur
15
Kinder
Belletristik
10
15
5
10
3
3
4
4
Fachliteratur
5
4
3
3
20
Kinder
5
5
5
5
50
Belletristik
10
20
20
10
Q4
10
Q3
10
Q2
5
Q1
5
Zeit
(Quartal)
19
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-In / Merge
• Inverse Operation zu Drill-Out.
• Bei einem Drill-In (auch Merge genannt) werdem einem ndimensionalen Datenwürfel k Dimension entfernt, wobei k < n.
! Ergebnis ist ein (n-k)-dimensionaler Datenwürfel
• Wie bei Roll-Up wird auch hier der Detailgrad reduziert.
• Im Gegensatz zu einem Roll-Up wird der Detailgrad nicht wie bisher
durch eine gröbere Granularität reduziert, sondern durch ein
Entfernen dimensionaler Information.
20
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-In / Merge
Drill-In des 2D-Würfels (Kategorie / Quartal) zum 1D-Würfel (Kategorie)
Produkt
(Kategorie)
Produkt
(Kategorie)
BlueRay
BlueRay
30
DVD
40
4
Musik
20
3
Fachliteratur
15
5
5
Kinder
20
20
10
Belletristik
50
10
10
15
5
10
Musik
3
3
4
Fachliteratur
5
4
3
Kinder
5
5
10
20
DVD
Q1
Belletristik
Q4
10
Q3
5
Q2
5
Drill-In der
Zeit-Dimension
Zeit
(Quartal)
21
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Slice
• Realisiert eine Ausschnittsbildung gemäß den in einer Dimension
vordefinierten Begriffswelten (gegeben durch das Dimensionsschema)
• Parameter des Slice-Operators ist ein Filterkriterium, das Punkt- oder
Bereichsanfragen über eine Dimension beschreibt.
• Filterkriterium mit AND-, OR-, NOT-Konstruktionen.
Z.B. Monat > Feb. AND Monat <= Nov. AND Monat <> April
• Filterkriterium auf einer oder mehreren Hierarchieebenen der Dimension
Z.B. Land = DE AND PLZ = 10179
• Notation Slice(p, D): Selektiere nur die “Scheiben” entlang der Dimension D,
die dem Filterkriterium p entsprechen.
22
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Slice
Slice mit einfachem Filterkriterium (Monat = Feb.)
Produkt
(Kategorie)
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
...
Ma
Ap
Mä
Fe
Ja
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Produkt
(Kategorie)
BlueRay
Zeit
(Monat)
2
...
DVD
2
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
Ja
n.
Slice(Monat = Feb., Zeit)
2
i
3
...
5
ril
Musik
Ma
...
Ap
2
i
3
ril
3
rz
2
b.
2
n.
DVD
rz
...
b.
2
Mä
2
Fe
BlueRay
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
23
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Slice
Slice mit einer Disjunktion als Filterkriterium (Monat = Feb. OR Monat = April)
Produkt
(Kategorie)
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
i
...
Ma
rz
ril
Ap
Mä
Fe
b
Ja
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Produkt
(Kategorie)
BlueRay
2
2
...
DVD
2
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
Ja
n.
Slice(Monat = Feb.
OR Monat = April,
Zeit)
Ort
(Stadt)
i
...
...
2
Ma
3
ril
3
Ap
2
.
2
n.
DVD
rz
...
.
2
Mä
2
Fe
b
BlueRay
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
24
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Slice
Slice über die Zeitdimension, bestehend aus zwei Hierarchien (Kalender- & Bilanzjahr)
Zeit
Produkt
(Kategorie)
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
li
Ju
...
Ap
Ja
...
5
ril
3
n.
5
...
Musik
Monat
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
Slice(
Produkt
(Kategorie)
BlueRay
2
2
...
DVD
2
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
Ja
Kalenderjahr = 2011
AND (Kalenderquartal = Q1
OR Kalenderquartal = Q2)
, Zeit)
Tag
li
...
...
2
Ju
3
Kalenderquartal
...
3
Bilanzquartal
ril
2
...
...
2
2
Kalenderjahr
Ap
DVD
2
n.
BlueRay
Bilanzjahr
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
25
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Dice
• Anwendung von Slice-Operatoren entlang mehrerer Dimensionen.
• Z.B. Slice(Monat = Jan., Zeit) UND Slice(Stadt = Berlin, Ort)
• Bei Slice haben wir stets “Scheiben” des Würfels ausgeschnitten, da nur
entlang einer Dimension gefiltert wurde.
• Mittels Dicing können nun beliebige “Unterwürfel” ausgeschnitten werden.
26
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Dice
Dice entlang zweier Dimensionen
Produkt
(Kategorie)
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
...
Ma
Ap
Mä
Fe
Ja
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Produkt
(Kategorie)
BlueRay
Zeit
(Monat)
2
...
DVD
2
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
Ja
n.
Slice(Monat = April, Zeit)
UND
Slice(Stadt = Berlin
OR Stadt = Stuttgart, Ort)
2
i
3
...
5
ril
Musik
Ma
...
Ap
2
i
3
ril
3
rz
2
b.
2
n.
DVD
rz
...
b.
2
Mä
2
Fe
BlueRay
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
27
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Dice
Dice entlang drei Dimensionen
Produkt
(Kategorie)
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
li
Ju
...
Ap
Ja
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
BlueRay
2
2
...
DVD
2
2
3
3
2
...
Musik
5
3
5
4
4
...
Fachliteratur
2
2
2
2
2
...
Kinder
2
2
3
3
2
...
Belletristik
5
3
5
4
4
...
Ja
Slice(Quartal = Q1 OR Quartal = Q2, Zeit)
AND
Slice(Produkttyp = Medien, Produkt)
AND
Slice(Stadt = Berlin OR Stadt = Stuttgart)
Produkt
(Kategorie)
li
Musik
...
...
Ju
2
...
3
...
3
ril
2
...
2
n.
DVD
ril
...
...
2
Ap
2
n.
BlueRay
Ort
(Stadt)
...
Lyon
Paris
Stuttgart
Berlin
Zeit
(Monat)
28
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Kapitel 4.1
Typische Anfragen
OLAP Operationen
• Innerhalb eines Cubes
• Über mehrere Cubes
• Pivot
Sprachen für OLAP
• MDX
• SQL Erweiterungen
29
Korrelierte Datenwürfel
• Bisher haben wir stets einen Datenwürfel betrachtet.
• In einem Data Warehouse werden üblicherweise mehrere Datenwürfel
gespeichert. Zum Beispiel
• Ein Würfel mit Dimensionen (Zeit, Produkt, Ort) zur Verwaltung von
Verkaufszahlen nach Zeit, Produkt und Filiale.
• Ein Würfel mit Dimensionen (Zeit, Produkt, Onlinekunde) zur Verwaltung
von Online-Verkaufszahlen von Produkten an Kunden zu gegebener Zeit.
Warum kein Würfel (Zeit, Produkt, Ort, Kunde)?
30
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Korrelierte Datenwürfel
• Korrelierte Datenwürfel teilen sich eine oder mehrere conformed
dimensions.
Conformed dimensions (übereinstimmende Dimensionen)
Zwei Dimensionen D und D’ sind übereinstimmend wenn für ihre Wertebereiche
dom(D) ⊆ dom(D’) oder dom(D’) ⊆ dom(D) gilt.
Beispiel übereinstimmender und nicht übereinstimmender Dimensionen
conformed
Produkte
Produkte
Bücher
Bücher
Medien
Kinder Roman
Musik
DVD
Produkte
Medien
Kinder Roman Belletristik
Musik
Bücher
Medien
DVD
not conformed
Produkte
Produkte
Bücher
Bücher
Medien
Kinder Roman
Musik
Wissenschaft
DVD
Medien
Roman
Musik
DVD
31
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Korrelierte Datenwürfel
Korrelierte Datenwürfel
Zwei Datenwürfel sind DC = (DS, M) und DC’ = (DS’, M’) korreliert, wenn mindestens ein
Dimensionspaar (Di, Dj), wobei Di ∈ DS, Dj ∈ DS’ übereinstimmt.
Sei CD die Menge aller Dimensionspaare
{(Di, Dj) | Di ∈ DS, Dj ∈ DS’, Di übereinstimmend mit Dj}.
Beispiel zweier korrelierter Datenwürfel mit CD = {(Produkt, Produkt), (Zeit, Zeit)}
Produkt
(Kategorie)
Produkt
(Kategorie)
BlueRay
DVD
ril
Ma
i
...
Ap
Ja
n.
Fe
b.
Mä
rz
Belletristik
Zeit
(Monat)
...
Kinder
Lyon
Paris
Stuttgart
Berlin
Belletristik
ril
Ma
i
...
Lyon
Paris
Stuttgart
Berlin
Ap
...
Kinder
Lager
Fachliteratur
n.
Fe
b.
Mä
rz
Fachliteratur
Ja
Ort
(Stadt)
Musik
Zeit
(Monat)
32
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Across
• Der Drill-Across Operator wird über mehrere korrelierte Datenwürfel angewandt.
• Er dient dazu, Kennwerte der Fakten der korrelierten Würfen zu kombinieren um
eine globalere Analyse (über mehrere Würfel hinweg) durchzuführen.
• Die Kennzahlen müssen sinnvoll kombiniert werden!
• Z.B. kann das bestverkaufte Produkt über Filialhandel und Onlinehandel hinweg
dadurch bestimmt werden, indem die Kennzahlen (jeweils Verkaufszahl) der
Cubes (Zeit, Produkt, Ort) und (Zeit, Produkt, Onlinekunde) summiert werden.
• Der von einem Drill-Across zweier Würfel DC = (DS, M) und DC’ = (DS’, M’)
zurürckgegebene Datenwürfel besteht aus
• Der Menge der Dimensionen {gröbste(Di, Dj) | (Di, Dj) ∈ CD }
(siehe Folie 32 für die Definition von CD)
• Der Menge der Kennzahlen M ∪ M’ bzw. daraus abgeleiteter Kennzahlen
33
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Across
Drill Across Beispiel 1: Gesamtverkauf (Filial- + Onlineverkauf) von Produkten nach Zeit
Produkt
(Kategorie)
BlueRay
DVD
Ort
(Stadt)
Musik
Fachliteratur
BlueRay
...
Kinder
Lyon
Paris
Stuttgart
Berlin
Ja
n.
Fe
b.
Mä
rz
Ap
ril
Ma
i
...
Belletristik
Produkt
(Kategorie)
DVD
Musik
Zeit
(Monat)
Fachliteratur
Kinder
Produkt
(Kategorie)
DVD
Musik
Kunde
Fachliteratur
Platin
Kinder
Gold
Silber
Standard
Neu
Ja
n.
Fe
b.
Mä
rz
Ap
ril
Ma
i
...
Belletristik
Zeit
(Monat)
Ap
Ja
n.
Fe
b.
Mä
rz
BlueRay
ril
Ma
i
...
Belletristik
Zeit
(Monat)
Ableitung der Kennzahlen:
verkaufszahl(m,k) =
"si ∈ Stadt verkaufszahl(m,k,si) +
"ki ∈ Kunde verkaufszahl(m,k,ki)
34
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Drill-Across
Drill-Across Bsp. 2: Bei welchen Produkten liegt der Lagerbestand stets über 60% der Anzahl verkaufter Produkte?
Verkaufszahlen
Produkt
(Kategorie)
BlueRay
DVD
Ort
(Stadt)
Musik
Fachliteratur
...
Kinder
Lyon
Paris
Stuttgart
Berlin
ril
Ma
i
...
Ap
Ja
n.
Fe
b.
Mä
rz
Belletristik
Produkt
(Kategorie)
Zeit
(Monat)
Lagerbestände
Lager
Fachliteratur
...
Kinder
Lyon
Paris
Stuttgart
Berlin
Ja
n.
Fe
b.
Mä
rz
Ap
ril
Ma
i
...
Belletristik
Zeit
(Monat)
35
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Kapitel 4.1
Typische Anfragen
OLAP Operationen
• Innerhalb eines Cubes
• Über mehrere Cubes
• Pivot
Sprachen für OLAP
• MDX
• SQL Erweiterungen
36
Pivot
• Bei einem Pivot (auch Rotation) wird konzeptuell der Datenwürfel um eine Achse
gedreht.
• Dieser Operator wird hauptsächlich für anschauliche Navigation bzw. zur ReportGenerierung (Menge von 2D-Spreadsheets verwendet).
• Dieses Pivoting ist eigentlich Aufgabe der Präsentationssoftware.
Pivot des Würfels (Zeit, Produkt, Ort) in (Ort, Produkt, Zeit)
Produkt
(Kategorie)
Produkt
(Kategorie)
BlueRay
BlueRay
DVD
Belletristik
Jan.
...
n
Be
rl
utt
St
ril
Ma
i
...
rz
Zeit
(Monat)
Ap
Mä
n.
Ja
Fe
b.
in
Belletristik
...
Kinder
...
Lyon
Paris
Stuttgart
Berlin
Ly
o
Kinder
Fachliteratur
rt
...
ris
Fachliteratur
Zeit
(Monat)
Pa
Ort
(Stadt)
Musik
Musik
ga
DVD
April
März
Feb.
Ort
(Stadt)
37
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Kapitel 4.1
Typische Anfragen
OLAP Operationen
• Innerhalb eines Cubes
• Über mehrere Cubes
• Pivot
Sprachen für OLAP
• MDX
• SQL Erweiterungen
38
OLAP Operationen
• Die bisher besprochenen OLAP Operationen arbeiten auf Würfeln
• Navigation entlang der Dimensionshierarchie: Roll-Up, Drill-Down
• Erweiterung / Reduktion der Dimensionalität: Split / Merge
• Selektion: Slice, Dice
• Kombination von Daten mehrerer Würfel: Drill-Across
• Weitere analytische Anfragen, die zu OLAP Operationen gezählt werden
• Gleitende Durchschnitte
• Arbeiten auf Zeitreihen
• Relative Wertevergleiche
39
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Sprachen für OLAP Operationen
• Allgemein
• Bereitstellung der notwendigen Operationen
• Deklarative Ansätze
• Kein vielfach geschachteltes SQL, weniger verbos als Standard SQL
• Im Wesentlichen zwei Ansätze
• Multidimensional Expressions (MDX):
Baut auf dem multidimensionalen Datenmodell (MDDM) auf (Würfel,
Dimensionen, Fakten, ...)
• Erweiterungen von SQL:
Anfragen über relationales Star- / Snowflake-Schema
40
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX
• MDX = Multidimensional Expressions:
Microsoft’s Vorschlag “OLE DB for OLAP”
• Eigene Anfragesprache
• Standard ohne feste Semantik (by example)
• MDDM Konzepte als First-Class Elemente
• Dadurch kompaktere Anfragen als mit SQL
• SQL-artige Syntax (aber nicht identische Semantik)
• Sehr mächtig und komplex
• Erzeugung der Objekte (DDL) erfolgt anderweitig:
DSO Interface (Decision Support Objects) von SQL Server
• Wird von vielen kommerziellen Tools zur Kommunikation mit OLAP Datenbank
benutzt.
nach [Ulf Leser, VL Data Warehouses und Data Mining, SS 2007]
41
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Grundprinzip und Struktur
• Eine MDX Anfrage erzeugt einen mehrdimensionalen Report
• Z.B. ein 2D Spreadsheet
• Z.B. Mehrere Tabs mit 2D Spreadsheets zur Darstellung von 3 Dimensionen
• Grundstruktur ist wie bei SQL eine SELECT-FROM-WHERE Klausel
• Aber andere Semantik als bei SQL!
Allgemeine MDX Grundstruktur
SELECT
FROM
WHERE
<axis-spec1>, <axis-spec2>, ...
<cube-spec1>, <cube-spec2>, ...
( <selection-specification> )
42
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Grundprinzip und Struktur
Einfaches MDX Beispiel (Beispielcube & Dimensionen siehe Folie 3)
SELECT
{ Berlin, Stuttgart } ON ROWS
{ [1. Quartal], [2. Quartal].CHILDREN } ON COLUMNS
FROM
VerkaufsWürfel
WHERE
( Measures.Verkauf,
Zeit.[2010],
Produkte.Produkte
)
Aggregation der Kennzahl
(measure) Verkauf über
vordefinierte Funktion (z.B. SUM)
Einschränkung der ZeitDimension (nur Zeitraum 2010)
Selektion relevanter Produkte.
Hier alle, da TopD Ebene gewählt
1. Quartal 2010
Berlin
Stuttgart
April 2010
Mai 2010
Juni 2010
numerische Zellenwerte
43
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Klammern und SELECT
• Klammern in MDX
• {} - Menge von Elementen für die Erzeugung einer Ergebnisdimension
• [] - Repräsentation von Leer- und Sonderzeichen & nicht numerische
Interpretation von Zahlen.
• () - Liefert Tupel für die WHERE-Klausel
• SELECT - Achsendimensionen
• Beschreibung des Ergebnisraum
• Jeder Dimension der Ergebnisses wird präsentationsgerecht eine Rolle
zugewiesen
ON COLUMNS, ON ROWS, ON PAGES, ON SECTIONS, ON CHAPTERS
• Eine Dimension im Ergebnis entspricht einer (Auswahl einer) Dimension im
MDDM über ein oder mehrere Hierarchieebenen.
{Berlin, Stuttgart} aus Ort-Dimension und Stadt-Ebene
{[1. Quartal], [2. Quartal].CHILDREN} aus Zeit-Dimension,
Quartal- und Monat-Ebene
44
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX FROM und WHERE
• FROM - Würfelspezifikation
• Menge von Datenwürfeln, die zur Konstruktion des Ergebniswürfels
notwendig sind.
• Falls mehr als ein Würfel referenziert wird, wird implizit ein
multidimensionaler Verbund durchgeführt, wobei jeweils zwei Datenwürfel
mindestens eine gemeinsame Dimension aufweisen müssen (siehe auch
korrelierte Datenwürfel)
• WHERE - Restriktion
• Einschränkung der in der FROM-Klausel spezifizierten Datenwürfel
• Einschränkung durch Angabe einer Liste von Klassifikationsknoten aus so
genannten Slicer-Dimensionen.
• Kennzahlen sind “normale” Elemente der speziellen Measures-Dimension
(diese kann in der WHERE-Klausel, aber auch in FROM-Klausel verwendet
werden)
45
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Mengenausdrücke
• Aufzählung von Knoten der dimensionalen Hierarchie
• Einzelne Knoten müssen nicht aus der gleichen Ebene stammen
• Z.B. liefert {Belletristik, Kinder, Medien} eine Mischung aus
Kategorien und Typen der Produkt-Dimension.
• Elementausdrücke zur Erzeugung von Mengen von Knoten
• CHILDREN: Kindknoten eines Klassifikationsknoten
z.B. [2. Quartal].CHILDREN = {April, Mai, Juni}
• MEMBERS: Knoten einer Klassifikationsstufe
z.B. Zeit.Quartal.MEMBERS = {[1. Quartal], ... , [4. Quartal]}
• PARENT: Vaterknoten eines Klassifikationsknoten
z.B. Belletristik.PARENT = {Bücher}
• ...
46
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Mengenausdrücke
• Schachtelung von Mengen mittels CROSSJOIN
• Projektion zweier Dimensionen in eine
• Semantik: Alle möglichen Kombinationen (Kreuzprodukt)
CROSSJOIN Beispiel
SELECT
CROSSJOIN({Berlin, Stuttgart}
{Kiosk, [Mega-Store]} ON ROWS
{ 2010.CHILDREN } ON COLUMNS
FROM
VerkaufsWürfel
WHERE
( Measures.Verkauf, Zeit.[2010], Produkte.Medien )
1. Quartal 2010
Kiosk
Berlin
Mega-Store
Kiosk
Stuttgart
Mega-Store
2. Quartal 2010 3. Quartal 2010 4. Quartal 2010
numerische Zellenwerte (Anzahl Verkäufe von
Produkten von Typ Medien im Zeitraum 2010
47
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Mengenausdrücke
• Relative Auswahl zur Nutzung einer Ordnung innerhalb dimensionaler
Strukturen.
• LASTCHILD - letzte Kind eines Knotens
z.B. Bücher.LASTCHILD = {Fachliteratur}
• NEXTMEMBER - nächster Nachbar auf gleicher Ebene
z.B. [1. Quartal 2010].NEXTMEMBER = {[2. Quartal 2010]}
• LEAD(x) - Nachbar in “positiver Entfernung” x auf gleicher Ebene
z.B. [November 2010].LEAD(2) = {[Januar 2011]}
• [x]:[y] - Bereichseinschränkung zwischen x und y
z.B. [1992]:[2002] beschränkt den zu betrachtenden Zeitraum auf die
Jahre 1992 bis 2002.
48
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Mengenausdrücke
• Methoden für dimensionale Schemata zur Nutzung einer Ordnung innerhalb
dimensionaler Strukturen.
• LEVEL - Ermittlung der Schemaebene
z.B. Belletristik.LEVEL = {Kategorien},
Medien.LEVEL = {Typen}
• LEVELS(x) - Nutzung der internen Nummerierung der Klassifikationsstufen
zum Zugriff auf Schemaebene x
z.B. LEVELS(1) = {Jahr}
49
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Konditionierte Ergebnisproduktion
• Konstruktion des Ergebniswürfels in Abhängigkeit von berechneten Ergebnissen.
• Top-k Anfragen
• TOPCOUNT
• TOPRECENT
• TOPSUM
• Allgemeine Filter-Bedingungen
• FILTER
50
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Konditionierte Ergebnisproduktion
Beispiel der TOPCOUNT Funktion
Ermittlung des Umsatzes der fünf verkaufsstärksten Bundesländer in Deutschland in 2010
(Annahme: Hierachie Land-Bundesland-Stadt)
SELECT
{Measures.Umsatz} ON COLUMNS
{TOPCOUNT(Deutschland.CHILDREN, 5, Measures.Verkauf)}
ON ROWS
FROM
VerkaufsWürfel
WHERE
( Measures.Umsatz,[2010])
Beispiel der Filter Funktion
Zeige Verkaufszahlen für Bundesländer an, deren Umsatz im Jahr 2002 höher als im Jahr
2001 war. Hier bezieht sich der Umsatz auf den Umsatz pro Quartal in der Produktkategorie
Medien.
SELECT
FILTER(Deutschland.CHILDREN,
([2002], Umsatz) > ([2001], Umsatz) ) ON COLUMNS
Quartal.MEMBERS ON ROWS
FROM
VerkaufsWürfel
WHERE
( Verkauf,[2002], Produkte.Medien )
51
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Temporale Auswertung
• Addressierung von Zeitpunkten und - orten
• PERIODSTODATE(k, d)
Alle Knoten vom Beginn der Klasse k bis zum Datum d
z.B. PERIODSTODATE(Quartal, [15. November 2002] = 1.10. - 15.11.2022
• LASTPERIODS(k, d)
Letzte k Zeitabschnitte vor Datum d (k und d auf gleicher Hierarchieebene).
z.B. LASTPERIODS(3, [Sept. 2002]) =
{[Juni 2002], [Juli 2002], [Aug. 2002]}
• PARALLELPERIOD(k, n, d)
z.B. PARALLELPERIOD(Jahre, 3, [Sept. 2002]) = {[Sept. 1999]}
52
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Temporale Auswertung
• Auswertung
• Kovarianz
• Korrelation
• gleitender Durchschnitt
• ...
53
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
MDX Fazit
• Hohe Komplexität
• Mächtige Sprache
• Direkte Anlehnung an MDDM
• Weit verbreitet
• Schnittstelle zwischen OLAP GUI und DB-Server
• Unterstützt von Microsoft, Cognos (IBM), BusinessObject (SAP), ...
54
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Kapitel 4.1
Typische Anfragen
OLAP Operationen
• Innerhalb eines Cubes
• Über mehrere Cubes
• Pivot
Sprachen für OLAP
• MDX
• SQL Erweiterungen
55
SQL und OLAP
• Übersetzung eines MDDM in Star- oder Snowflake Schema
• Operationen mit Standard SQL (SQL-92)
• Auswahl (slice, dice): Joins und Selects
• Verfeinerung (drill-down): Joins uns Selects
• Aggregation um eine Stufe: Group-By
• OLAP-Erweiterungen von SQL
• Mehrfachgruppierungen ! in dieser Vorlesung
• Analytische Funktionen ! in der Übung
• Prinzipiell auch in SQL-92 möglich, aber nur kompliziert
auszudrücken und ineffizient in der Bearbeitung
56
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Beispiel
Beispiel eines Star Schemas
Produkt
Produkt
PID Kategorie
Typ
1
Belletristik
Bücher
DVD
2
Kinder
Bücher
Musik
3
Fachliteratur
Bücher
Fachliteratur
4
Musik
Medien
Kinder
5
DVD
Medien
Belletristik
6
BlueRay
Medien
BlueRay
PID
ZID
OID
#Verkäufe Gewinn
ZID Monat Quartal
Jahr
1
1
1
5
30
1
Jan10 Q1 2010
2010
1
1
2
5
37
2
Feb10 Q1 2010
2010
1
1
3
5
45
...
1
1
4
5
20
2
1
1
2
33
... ...
...
Ort
...
ParisLyon
Berlin Stuttgart
Ja
n.
Fe
b.
Mä
rz
Ap
ril
Ma
i
...
Faktentabelle
Zeit
Ort
2
1
2
2
35
OID
Stadt
2
1
3
2
40
1
Berlin
2
1
4
2
35
2
Stuttgart
...
...
...
...
...
3
Paris
1
2
1
3
22
4
Lyon
...
...
...
...
...
Zeit
Kennzahlen (Verkäufe & Gewinn) für
Belletristik im Januar 2010 in Stuttgart.
Alle weiteren Kombinationen von
Produktkategorien und Orten im
Januar. 2010.
Beginn der Kombinationen für Februar
2010(danach auch für alle weiteren
Monate)
57
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
• Erinnerung zu GROUP BY (siehe VL Datenbanken I)
• GROUP BY <att1>, <att2>, ..., <attn> partitioniert die von der
Anfrage betrachteten Daten anhand der Werte in Attributen att1 bis attn.
• Wird GROUP BY verwendet, können in der SELECT-Klausel (i) die Attribute
att1 bis attn vorkommen plus (ii) aggregierte Werte anderer Attribute.
• Aggregation mittels SUM, COUNT, AVG, MIN, MAX
Beispiel einer GROUP-BY Anfrage - Gesamtgewinn mit Büchern nach Jahr, Quartal und Monat?
SELECT
FROM
WHERE
AND
AND
GROUP BY
Jahr, Quartal, Monat, SUM(Gewinn)
Faktentabelle, Zeit, Produkt
Produkt.PID = Faktentabelle.PID
Zeit.ZID = Faktentabelle.ZID
Produkt.Typ = ‘Bücher’
Jahr, Quartal, Monat
Summe nur für Tage (unterteilt
nach Monaten / Jahren)
Keine Summe pro Monat / pro Jahr
58
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Beispiel einer GROUP-BY Anfrage - Gesamtgewinn mit Büchern nach Jahr, Quartal und Monat
SELECT
FROM
WHERE
AND
AND
GROUP BY
UNION
SELECT
FROM
WHERE
AND
AND
GROUP BY
UNION
SELECT
FROM
WHERE
AND
AND
GROUP BY
Monat AS Time, SUM(Gewinn) AS Gewinn
Faktentabelle, Zeit, Produkt
Produkt.PID = Faktentabelle.PID
Zeit.ZID = Faktentabelle.ZID
Produkt.Typ = ‘Bücher’
Monat
Time
Monate
Quartal AS Time, SUM(Gewinn) AS Gewinn
Faktentabelle, Zeit, Produkt
Produkt.PID = Faktentabelle.PID
Zeit.ZID = Faktentabelle.ZID
Produkt.Typ = ‘Bücher’
Quartal
Jahr AS Time, SUM(Gewinn) AS Gewinn
Faktentabelle, Zeit, Produkt
Produkt.PID = Faktentabelle.PID
Zeit.ZID = Faktentabelle.ZID
Produkt.Typ = ‘Bücher’
Jahr
Quartale
Jahre
Gewinn
Jan. 2010
150
Feb. 2010
300
...
...
Dec. 2010
400
Jan. 2011
300
...
...
Q1 2010
500
...
...
Q4 2011
800
2010
2000
2011
2300
59
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gewünschtes Ergebnis - Gesamtgewinn mit
Büchern nach Jahr, Quartal und Monat
Jahr
Quartal
Monat
Mehrfachgruppierung 2: Gewinn pro Quartal
und Produkttyp und Quartal und Ort in 2010
Gewinn
Quartal
Typ
Ort
Gewinn
2010
Q1
Jan.
150
Q1 2010 Bücher
-
50
2010
Q1
Feb.
300
...
Bücher
-
...
2010
Q1
März
50
Q4 2010 Bücher
-
70
...
...
...
...
Q1 2010 Medien
-
40
2011
Q1
Jan.
300
...
Medien
-
...
...
...
...
...
Q4 2010 Medien
-
60
2010
Q1
-
500
Q1 2010
-
Berlin
60
...
...
...
...
...
-
Berlin
...
2011
Q4
-
800
Q4 2010
-
Berlin
50
2010
-
-
2000
Q1 2010
-
Stuttgart 20
2011
-
-
2300
...
-
...
Nutzung einer
Dimensionshierarchie in SQL?
...
Aggregation entlang mehrerer
Dimensionskombinationen in SQL?
60
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
• SQL Erweiterung zur Unterstützung von Mehrfachgruppierungen
• GROUPING SETS - Gruppierungsmengen
Explizite Angabe einer Menge von Gruppierungskombinationen
• CUBE - Multidimensionale Gruppierung
Generierung aller Gruppierungskombinationen der als Parameter
übergebene Attribute
• ROLLUP - Hierarchische Multidimensionale Gruppierung
• Kombination dieser Klauseln mit bekannten SQL-Klauseln (GROUP
BY, HAVING, CASE, ...)
61
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gruppierungsmengen
Grouping Sets
...
GROUP BY GROUPING SETS ((<set1_a1>, ..., <set1_am>),
(<set2_a1>, ..., <set2_an>),
...)
• Explizite Angabe der gewünschten Gruppierungen
• Jede Gruppierungsmenge (<seti_a1>, ... ) gibt eine
gewünschte Gruppe an
• Äquivalent zu UNION einzelner GROUP BY mit “Auffüllen” der Werte nicht
überlappender Attribute der einzelnen Gruppierungsmengen mit NULL.
• Abfragen der gruppierenden Spalten mittels GROUPING() Funktion:
GROUPING(<attr>) = 0 wenn nach <attr> gruppiert, sonst 1.
62
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gruppierungsmengen
Mehrfachgruppierung 2: Gewinn pro Quartal
und Produkttyp und Quartal und Ort in 2010
Quartal
Typ
Ort
Gewinn
SELECT
Q1 2010 Bücher
-
50
...
Bücher
-
...
Q4 2010 Bücher
-
70
Q1 2010 Medien
-
40
...
-
...
-
60
Medien
Q4 2010 Medien
Mehrfachgruppierung 2 in SQL unter Verwendung
von Gruppierungsmengen
Q1 2010
-
Berlin
60
...
-
Berlin
...
Q4 2010
-
Berlin
50
Q1 2010
-
Stuttgart 20
...
-
...
Quartal,
Typ,
Ort,
SUM(Gewinn)AS GEWINN
FROM
Faktentabelle FT,
Zeit, Produkt
WHERE
Produkt.PID = FT.PID
AND
Zeit.ZID = FT.ZID
AND
Zeit.Jahr = ‘2010’
GROUP BY GROUPING SETS (
(Quartal, Typ),
(Quartal, Ort)
)
...
63
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gruppierungsmengen
Mehrfachgruppierung 2 mit Ausgabe der entsprechenden Gruppen
Quartal
Typ
Ort
Gewinn
GQ
GT
GO
Q1 2010 Bücher
-
50
0
0
1
...
-
...
0
0
1
...
Q1 2010
-
Berlin
60
0
1
0
...
-
Berlin
...
0
1
0
Mehrfachgruppierung 2 mit Ausgabe der entsprechenden Gruppen in SQL
SELECT
Quartal, Typ, Ort, SUM(Gewinn) AS GEWINN
GROUPING(Quartal) AS GQ, GROUPING(Typ) AS GT, GROUPING(Ort) AS GO
FROM
...
WHERE
...
GROUP BY GROUPING SETS ((Quartal, Typ), (Quartal, Ort))
64
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gruppierungsmengen
• Gruppierungssemantik bei Gruppierungsmengen
• GROUP BY A, B ≣ GROUP BY GROUPING SETS ((A, B))
! Gruppe (A, B)
• GROUP BY GROUPING SETS (A, B), (A, C), (A))
≣
GROUP BY A, GROUPING SETS ((B), (C), ( ))
! Gruppen (A) " ( (B), (C), ( )) = (A, B), (A, C), (A)
• GROUP BY GROUPING SETS ((A, B), (B, C)),
GROUPING SETS ((D, E), (D), ())
! Gruppen ( (A, B), (B, C) ) " ( (D, E), (D), ( ) )
= (A, B, D, E), (A, B, D), (A, B), (B, C, D, E), (B, C, D), (B, C)
65
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Gruppierungsmengen
Formulieren Sie eine SQL Anfrage, die das unten dargestellte Ergebnis berechnet
Typ
Jahr
Stadt
Buch
-
-
Buch
2010
Berlin
Buch
2010
Stuttgart 20
-
#Verkäufe
50
30
Medien
-
Medien
2010
Berlin
Medien
2010
Stuttgart 10
40
30
-
-
Berlin
-
-
Stuttgart 30
80
66
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Multidimensionale Gruppierung
Multidimensionale Gruppierung mittels CUBE()
...
GROUP BY CUBE (<a1>, ..., <an>)
• Bildet alle Gruppierungskombinationen der als Parameter übergebenen
Attribute
• Z.B. bildet CUBE(A, B, C) Gruppen (A, B, C), (A, B), (A, C), (B, C),
(A), (B), (C), ( ))
• Insgesamt 2n Gruppen
• In Kombination mit Aggregation wird CUBE() zur Berechnung der
Summen sämtlicher Kombinationen genutzt.
67
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Multidimensionale Gruppierung
Gewinn nach (jeder Kombination von)Quartal, Produkttyp und Ort in 2010
Quartal
Typ
Ort
Gewinn
Q1 2010
Bücher
Berlin
10
Q1 2010
Bücher
Stuttgart
20
...
...
Q1 2010
...
...
...
Bücher
-
20
...
-
...
Q1 2010
-
Berlin
80
Q1 2010
-
Stuttgart
50
...
-
...
...
Berlin
50
-
Bücher
-
Bücher
Stuttgart
-
80
...
...
...
Q1 2010
-
-
160
...
-
-
...
-
Bücher
-
200
-
Medien
-
300
-
-
Berlin
200
-
-
Stuttgart
100
-
-
...
-
-
-
Mehrfachgruppierung 2 in SQL unter Verwendung
von Gruppierungsmengen
SELECT
Quartal, Typ, Ort,
SUM(Gewinn)AS GEWINN
FROM
Faktentabelle FT,
Zeit, Produkt
WHERE
Produkt.PID = FT.PID
AND
Zeit.ZID = FT.ZID
AND
Zeit.Jahr = ‘2010’
GROUP BY CUBE (Quartal, Typ, Ort)
...
68
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Multidimensionale Gruppierung
• Gruppierungssemantik beim CUBE-Operator
• GROUP BY CUBE(A, B, C)
≣
GROUP BY GROUPING SETS ((A,B,C), (A,B), (A,C),
(B,C), (A), (B), (C), ())
• GROUP BY CUBE(A, B), CUBE(B, C)
! Gruppen ( (A, B), (A), (B), ( ) ) " ( (B, C), (B), (C), ( ))
= ( A, B, B, C ), (A, B, B), (A, B, C), (A, B),
(A, B, C), (A, B), (A, C), (A),
(B, B, C), (B, B), (B, C), (B),
(B, C), (B), (C), ( )
= (A, B, C), (A, B), (A, B, C), (A, B),
Duplikate in Kombinationen
(A, B, C), (A, B), (A, C), (A),
(z.B. (A, B, C), (A, B), (B), ...)
(B, C), (B), (B, C), (B),
werden nicht eliminiert!
(B, C), (B), (C), ( )
• GROUP BY GROUPING SETS(CUBE(A, B), CUBE(B,C))
≣
GROUP BY GROUPING SETS ((A,B), (A), (B), (), (B,C), (B), (C), ())
69
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Multidimensionale Gruppierung
Formulierung einer SQL Anfrage mit dem CUBE-Operator
Geben Sie eine SQL Anfrage an, die die Anzahl DVD-Verkäufe im ersten Quartal 2010 aggregiert
nach Monat und Ort ausgibt. Das Ergebnis der Anfrage entspricht demnach den Daten, die das
unten gezeigte Spreadsheet veranschaulicht.
Jan. 10
Feb. 10
März
Summe
Berlin
20
25
22
67
Stuttgart
10
15
17
42
Paris
30
25
30
85
Lyon
10
12
10
32
Summe
70
77
79
226
70
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Hierarchische Multidimensionale Gruppierung
Multidimensionale Gruppierung mittels ROLLUP()
...
GROUP BY ROLLUP (<a1>, ..., <an>)
• In herkömmlichem SQL kann man UNION über k Subanfragen
formulieren, um hierarchische Gruppierung füe k Dimensionsstufen zu
erhalten (siehe z.B. Folie 59).
• Aufwändig zu formulieren, zu berechnen (k Scans der Faktentabelle)
und ungünstige Reihenfolge für Reports (und nur schwer zu sortieren).
• ROLLUP berechnet hierarchische Aggregation in günstiger Reihenfolge.
• Attribute <a1>, ..., <an> stammen aus einer Dimensionshierarchie
und werden in absteigender Granularität angegeben.
71
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Hierarchische Multidimensionale Gruppierung
Gesamtgewinn mit Büchern nach Jahr,
Quartal und Monat
Jahr
Quartal
Monat
Gewinn
2010
Q1
Jan.
150
2010
Q1
Feb.
300
2010
Q1
März
50
...
...
...
...
2011
Q1
Jan.
300
...
...
...
...
2010
Q1
-
500
...
...
...
...
2011
Q4
-
800
2010
-
-
2000
2011
-
-
2300
-
-
-
4300
SQL Anfrage mit ROLLUP
SELECT
Jahr, Quartal, Monat,
SUM(Gewinn)AS GEWINN
FROM
Faktentabelle FT,
Zeit, Produkt
WHERE
Produkt.PID = FT.PID
AND
Zeit.ZID = FT.ZID
AND
Produkt.Typ = ‘Bücher’
GROUP BY ROLLUP (Jahr, Quartal, Monat)
72
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Hierarchische Multidimensionale Gruppierung
• Gruppierungssemantik beim CUBE-Operator
• GROUP BY ROLLUP(A1, A2, ..., An)
≣
GROUP BY
GROUPING SETS ( (A1, ..., An-1, An), (A1, ..., An-1), ...,
(A1, A2), (A1), () )
wobei die funktionalen Abhängigkeiten An " An-1 " ... " A2 " A1 gelten
• GROUP BY ROLLUP(A1, ..., An), ROLLUP(B1, ..., Bm)
! Gruppen ( (A1, ..., An), ..., (A1, A2), (A1), ( ) )
"
( (B1, ..., Bn), ..., (B1, B2), (B1), ( ) )
73
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Mehrfachgruppierungen
Hierarchische Multidimensionale Gruppierung
Formulierung einer SQL Anfrage mit hierarchischer multidimensionaler Gruppierung
Geben Sie eine SQL Anfrage an, die die Anzahl
Von Verkäufen von Medien-Produkten nach Jahr,
Monat, Kategorie und Stadt wie in der rechten
Beispieltabelle aufschlüsselt. Verwenden Sie dabei
keine GROUPING SETS Klausel.
Jahr
Monat
Kategorie
Stadt
Verkauf
2010
Dez.
Musik
-
200
2010
Dez.
DVD
-
150
2010
Dez.
BlueRay
-
50
2010
Dez.
-
-
400
2011
Dez.
-
-
450
2010
-
-
-
2000
2011
-
-
-
3000
-
-
-
5000
2010
-
-
Berlin
2010
-
-
Stuttgart 300
2010
-
-
...
...
2011
-
-
Berlin
1500
2011
-
-
...
...
-
-
-
Berlin
4000
-
-
-
Stuttgart 2000
-
-
-
...
-
1000
...
74
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen
Zusammenfassung
• OLAP-Operationen
• Navigation entlang der Dimensionshierarchie: Roll-Up, Drill-Down
• Erweiterung / Reduktion der Dimensionalität: Split / Merge
• Selektion: Slice, Dice
• Kombination von Daten mehrerer Würfel: Drill-Across
• OLAP-Sprachen
• MDX - Orientiert an MDDM und Spreadsheets
• SQL Erweiterungen: GROUPING SETS, CUBE, ROLLUP
75
Data Warehouses | SS 2011 | Melanie Herschel | Universität Tübingen