Tabelle mit Inhalt wie Cube-View - Beuth Hochschule für Technik

Datenwarenhaus
F. Steyer
1 Motivation und Begriffe
2 Beispiel: Hochschule
2.1 OLTP-Datenbank
2.2 Join-View
2.3 Cube-View und Fragebeispiele
2.4 materialisierte View und synthetische Datenbank
3 Beispiel: Firma
3.1 OLTP-Datenbank
4 Erstellung und Pflege eines Datenwarenhauses
TFH Berlin/Steyer
OLAP
1 Motivation und Begriffe
Es gibt grundsätzlich zwei verschiedene Anwendungsmöglichkeiten einer Datenbank:
Die Datenbank ist ein Informationsspeicher.
Die Datenbank ist ein Modell, das die Realität darstellt.
OLAP
OLAP (Abkürzung für On-line Analytical Processing), Begriff seit 1993 = eine der traditionellen
Benutzungsarten von Datenbanksystemen, versucht sich eine Überblick über viele Daten zu verschaffen
andere Begriffe: Data Warehouse, Data Mining, Decision Support
typische Analyseprobleme sind:
- Mittelwertbildung über einen Zeitraum
- Periodenvergleich
- Minimale und maximale Werte eines Zeitraumes
- Relativer Betrag zu einem Gesamtwert
- Stichwort "Überblick"
Techniken:
Kann mit SQL und Erweiterungen behandelt werden. Zentral ist die Materialisierung von Views.
Bei einem materialisierten System wird ein separater, integrierter Datenbestand permanent bzw. dauerhaft
aufgebaut ("materialisiert"). Ein Materialisieren (und eine dann notwendige inkrementelle Wartung) ist
schneller und billiger als ein ständiges Neuberechnen bei jeder die Integration betreffenden Anfrage.
Ausserdem minimiert es den Netzverkehr.
auch: Aggregierungen, Gruppierungen
Anwendungen
- Versichungen kalkulieren ihre Tarife jährlich neu.
- Banken analysieren die Kontobewegungen.
- Krankenhäuser interessieren sich für Behandlungserfolge.
- Telefonunternehmen überprüfen die Telefonpreise.
- Transportgesellschaften planen den Einsatz.
OLTP
OLTP (Abkürzung für On-line Transaction Processing) dagegen = andere traditionelle Benutzungsart von
Datenbanksystemen, bearbeitet die operationalen Daten einer Organisation, benutzt oft nur wenige Daten
typische Transaktionsprobleme sind:
- korrekte Datenmodellierung
- ständige Aktualität
- Zugriffsschutz und Integrität
- Stichwort "Details"
Techniken:
Datenorganisation mit Bäumen/Indizes
Sperren, Privilegien
Dynamik soviel wie möglich
Anwendungen
- Verwaltung der Organisation
- Geschäftsprozesse
- Waren-Geld-Kreislauf
TFH Berlin/Steyer
OLAP
OLTP
OLAP
Datenquelle
Datenwarenhaus
Der Prozess der Datenanalyse im Unternehmen beschäftigt sich im allgemeinen nicht (nur) mit den
Detaildaten, wie sie aus den einzelnen Geschäftsvorfällen aufgezeichnet wurden, sondern mit konsolidierten
(aggregierten) Daten verschiedenster Ebenen.
3.Dimension
Zeit
Zeit 1998
Zeit
1999
Liefergebiet
1997
Europa Nordamerika
Südamerika
insgesamt
2.Dimension
Produkt
Fleischprodukte
Getränke
Getreideprodukte
Gewürze
Meeresfrüchte
Milchprodukte
Naturprodukte
Süsswaren
112925
151872
62953
73856
85379
157563
78103
103891
49660
77191
26176
23566
30502
52081
12578
48106
Insgesamt
826569
319861
15576
57464
11598
16272
25742
41634
14588
25102
178189
286527
100727
113695
141623
251278
105269
177099
207976 1354406
1.Dimension
1.Dimension: Produkt (Fleischprodukte, Getränke, ... Süsswaren)
2.Dimension: Lieferziel (Europa, Nordamerika, ... Südamerika)
3.Dimension: Zeit (1999, 1998, 1997)
Daher kommt das Wort "Datenwürfel" (cube). Es handelt sich dabei immer um einen Zusammenhang von
drei Dimensionen. Etwa: Stelle den Zusammenhang zwischen einer Dimension x und einer Dimension y dar
über der Zeit. Addiere aber dabei in jeder Dimension die Einzelwerte zu Gruppen, zur x-Gruppe, zur yGruppe, zur Zeit-Gruppe.
TFH Berlin/Steyer
OLAP
2 Beispiel: Hochschule
2.1 OLTP-Datenbank
Dozent
Prüfung
Student
Tabellen
evtl. mit foreign key
Überblicke durch Views (Join-View, Cube-View, materialisierte View)
2.2 Join-View
Fragebeispiel: Welcher Dozent gab welchem Studenten welche Note ?
Ergebnis:
Einzeltreffer, Nettoergebnis
2.3 Cube-View
Interessante Fragen gehen nach Aggregaten der Einzeltreffer
Fragebeispiel: Welche Durchschnittsnote hat ein bestimmter Student ?
Welche Durchschnittsnote gab ein bestimmter Dozent ?
Wie ist der Gesamtdurchschnitt aller Dozenten und aller Studenten ?
Ergebnis:
Tabelle mit Zusätzen, Bruttoergebnis
(Die Erstellung kann aufwendig sein, wenn die Berechnung komplex ist und die
Tabellen gross sind.)
2.4 materialisierte View
Benutzung einer Tabelle mit gleichem Inhalt.
Zeitmessungen für das Lesen aller Zeilen jeweils.
Vergleich des Lesens der Cube-View und das der materialisierten View.
Cube View:
Mat. View:
1.Fall
2.Fall
3.Fall
4 Dozenten
7 Studenten
13 Prüfungen
100 Dozenten
500 Studenten
1000 Prüfungen
1000 Dozenten
50000 Studenten
100000 Prüfungen
453 ms
453 ms
609 ms
593 ms
2203 ms
453 ms
D.h. das Lesen von materialisierten Views wird besser als als das Lesen von normalen Views,
wenn die zugrunde liegenden Tabellen grösser werden.
TFH Berlin/Steyer
OLAP
OLTP-Datenbank
Tabelle Dozent
D1
D2
D3
D4
Meier
Müller
Schulze
Lehmann
1234
8765
8689
2454
101
102
201
205
1234
1234
6110
6110
1630
2557
2799
420
420
580
580
200
143
125
Tabelle Student
S11
S12
S13
S15
S16
S17
S18
Gerber
Pflüger
Peter
Bauer
Linder
Felger
Schlenzer
Tabelle Prüfung
D1
D1
D1
D1
D1
D2
D2
D3
D3
D3
D4
D4
D4
S11
S13
S15
S16
S18
S12
S15
S16
S17
S18
S16
S17
S18
MATH
MATH
PHYS
PHYS
MATH
C
C
GRAF
GRAF
GRAF
JAVA
JAVA
JAVA
3.0
2.0
2.3
1.7
3.0
3.3
2.3
1.7
3.0
4.0
2.3
3.3
2.3
andere Darstellung der OLTP-Datenbank
Dozent
Meier
3.0
-
2.0
2.3
1.7
-
3.0
Müller
-
3.3
-
2.3
-
-
-
Schulze
-
-
-
-
1.7
3.0
4.0
Lehmann
-
-
-
-
2.3
3.3
2.3
Gerber Pflüger Peter
TFH Berlin/Steyer
Bauer Linder Felger Schlenzer
Student
OLAP
Join-View
create view joinview (dname, sname, note)
as select dname, sname, avg(note)
from dozent, student, prüfung
where dozent.dnr=prüfung.dnr and student.snr=prüfung.snr
Lehmann
Lehmann
Lehmann
Felger
Linder
Schlenzer
3.300000
2.300000
2.300000
Meier
Meier
Meier
Meier
Meier
Bauer
Gerber
Linder
Peter
Schlenzer
2.300000
3.000000
1.700000
2.000000
3.000000
Müller
Müller
Bauer
Pflüger
2.300000
3.300000
Schulze
Schulze
Schulze
Felger
Linder
Schlenzer
3.000000
1.700000
4.000000
TFH Berlin/Steyer
OLAP
Cube-View
create view cubeview (dname, sname, note)
as select dname, sname, avg(note)
from dozent, student, prüfung
where dozent.dnr=prüfung.dnr and student.snr=prüfung.snr
group by dname, sname with cube
Lehmann
Lehmann
Lehmann
Lehmann
Felger
Linder
Schlenzer
NULL
3.300000
2.300000
2.300000
2.633333
Meier
Meier
Meier
Meier
Meier
Meier
Bauer
Gerber
Linder
Peter
Schlenzer
NULL
2.300000
3.000000
1.700000
2.000000
3.000000
2.400000
Müller
Müller
Müller
Bauer
Pflüger
NULL
2.300000
3.300000
2.800000
Schulze
Schulze
Schulze
Schulze
Felger
Linder
Schlenzer
NULL
3.000000
1.700000
4.000000
2.900000
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Bauer
Felger
Gerber
Linder
Peter
Pflüger
Schlenzer
2.300000
3.150000
3.000000
1.900000
2.000000
3.300000
3.100000
NULL
NULL
2.630769
andere Darstellung der OLTP-Datenbank mit Zusatzinformationen
Dozent

3.0
3.3
2.0
2.3
1.9
3.1
3.1
2.6
Meier
3.0
-
2.0
2.3
1.7
-
3.0
2.4
Müller
-
3.3
-
2.3
-
-
-
2.8
Schulze
-
-
-
-
1.7
3.0
4.0
2.9
Lehmann
-
-
-
-
2.3
3.3
2.3
2.6
Gerber Pflüger Peter
TFH Berlin/Steyer
Bauer Linder Felger Schlenzer

Student
OLAP
Fragebeispiele an die Cube-View
Welchen Gesamtdurchschnitt hat Dozent Lehmann für alle Studenten ?
select dname, sname, note
from cubeview
where dname = 'Lehmann'
and sname IS NULL
Lehmann
NULL 2.6
Welchen Gesamtdurchschnitt hat Student Gerber bei allen Dozenten ?
select dname, sname, note
from cubeview
where sname = 'Gerber'
and dname IS NULL
NULL
Gerber 3.0
Was ist der Gesamtdurchschnitt für alle Dozenten und alle Studenten ?
select dname, sname, note
from cubeview
where dname = NULL
and sname IS NULL
NULL
NULL 2.6
Dozent

3.0
3.3
2.0
2.3
1.9
3.1
3.1
2.6
Meier
3.0
-
2.0
2.3
1.7
-
3.0
2.4
Müller
-
3.3
-
2.3
-
-
-
2.8
Schulze
-
-
-
-
1.7
3.0
4.0
2.9
Lehmann
-
-
-
-
2.3
3.3
2.3
2.6
Gerber Pflüger Peter
TFH Berlin/Steyer
Bauer Linder Felger Schlenzer

Student
OLAP
materialisierte View
create table matview (dname char(10), sname char(10), note decimal(2,1))
insert into matview select * from cubeview
Tabelle mit Inhalt wie Cube-View
Lehmann
Lehmann
Lehmann
Lehmann
Felger
Linder
Schlenzer
NULL
3.3
2.3
2.3
2.6
Meier
Meier
Meier
Meier
Meier
Meier
Bauer
Gerber
Linder
Peter
Schlenzer
NULL
2.3
3.0
1.7
2.0
3.0
2.4
Müller
Müller
Müller
Bauer
Pflüger
NULL
2.3
3.3
2.8
Schulze
Schulze
Schulze
Schulze
Felger
Linder
Schlenzer
NULL
3.0
1.7
4.0
2.9
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Bauer
Felger
Gerber
Linder
Peter
Pflüger
Schlenzer
2.3
3.2
3.0
1.9
2.0
3.3
3.1
NULL
NULL
2.6
TFH Berlin/Steyer
OLAP
Erzeugen einer synthetischen Datenbank
import java.sql.*;
import java.util.*;
import java.lang.Math.*;
public class db2erzeugen
{ public static void main(String[] args) throws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection("jdbc:odbc:olapdb","student","student");
Statement s = c.createStatement();
// 100 Dozenten erzeugen
int i, j;
for (i=1;i<=100;i++)
{ String sql = "insert into dozent values (" + i + ",'x',1,1)";
s.execute(sql);
}
// 500 Studenten erzeugen
for (i=1;i<=500;i++)
{ String sql = "insert into student values (" + i + ",'x',1,1)";
s.execute(sql);
}
// 1000 Prüfungen erzeugen (für jeden Dozenten 10)
double d, n;
int st;
for (i=1;i<=100;i++)
{
for (j=1;j<=10;j++)
{ //Studentennummern als Zufallszahlen zwischen 1 und 500
d = Math.random();
d = d * 500;
st = (int) d;
st = st + 1;
//Noten als Zufallszahlen zwischen 1 und 5,0
d = Math.random();
d = d * 50;
n = (int) d;
d = n/10 + 1;
String sql = "insert into prüfung values (" + i + "," + st + ",'x'," + d + ")";
s.execute(sql);
}
}
c.close();
}
}
TFH Berlin/Steyer
OLAP
3 Beispiel: Firma
3.1 OLTP-Datenbank
Kunde
Verkauf
Zeit
Artikel
Tabellen
Überblicke durch Views (Join-View, Cube-View, materialisierte View)
3.2 Join-View
Fragebeispiel: Welcher Artikel wurde wann wo gekauft ?
Ergebnis:
Einzeltreffer, Nettoergebnis
3.3 Cube-View
Interessante Fragen gehen nach Aggregaten.
Fragebeispiel: Artikelsumme an einem bestimmten Tag ?
Artikelsumme von einem bestimmten Kunden ?
Artikelsumme an allen Tagen und allen Kunden ?
Ergebnis:
Tabelle mit Zusätzen, Bruttoergebnis
(Die Erstellung kann aufwendig sein, wenn die Berechnung komplex ist und die
Tabellen gross sind.)
3.4 materialisierte View (noch nicht untersucht)
TFH Berlin/Steyer
OLAP
OLTP-Datenbank
Tabelle Artikel mit Kategorienerweiterungen
aid
aname
kategorie
auslaufartikel
1
2
3
4
5
nein
nein
nein
nein
ja
Chai
Chang
Aniseed Syrup
Chef Antons Cajun Seasoning
Chef Antons Gumbo Mix
Getränke
Getränke
Gewürze
Gewürze
Gewürze
Tabelle Kunde mit Gebietserweiterungen
kid
firma
ort
land
region
1
2
3
4
5
Berlin
Mexico D.F.
Mexico D.F.
London
Stockholm
Deutschland
Mexiko
Mexiko
Großbritannien
Schweden
Europa
Südamerika
Südamerika
Europa
Europa
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Antonio Moreno Taqueria
Around the Horn
Berglunds Snabbköp
Tabelle Zeit mit Gruppierungen
zid
woche monat quartal jahr
saison
2004-01-01
2004-01-01
2004-01-01
2004-01-01
2004-01-01
nein
nein
nein
nein
nein
35
36
37
37
37
8
9
9
9
9
3
3
3
3
3
2004
2004
2004
2004
2004
Tabelle Verkauf
aid
kid
zid
menge umsatz
1
2
3
2
2
15
5
20
878
60
1
3
2
1
3
2004-01-01
2004-01-01
2004-01-01
2004-01-01
2004-01-01
684.00
376.00
24.00
684.00
684.00
andere Darstellung der OLTP-Datenbank
Artikel
Zeit
x
z
-
z
z
z
-
z
x
-
z
-
z
-
-
-
x
-
-
-
z
z
-
z
x
-
-
-
z
z
-
z
y
y
y
y
y
y
y
TFH Berlin/Steyer
Gebiet
OLAP
4 Erstellung und Pflege eines Datenwarenhauses
1
OLTP-Datenbank analysieren (Datenmodell, Änderungsverhalten)
2
Analyse-Anforderungen feststellen (Stern-Teil-Modell bilden mit Fakten- und
Dimensionstabellen)
3
Analyse-Abfrage-View definieren und testen (Aufbaudauer)
4
evtl. materialisieren (Erstellungsdauer, Aktualisierungsdauer, Aktualisierungshäufigkeit)
5
Erweiterungen: neue Dimension hinzufügen
neue Dimensionsgruppe an eine bestehende Dimension anhängen
Dimension ersetzen
TFH Berlin/Steyer