10.1¨Uberblick

10 Data Warehousing
10.1
Überblick
➢ In klassischen“ Datenbankanwendungen werden Datenbanken im wesentlichen zur
”
Abwicklung des ( operativen“) Tagesgeschäfts verwendet (z.B.: Buchungen, Einkauf/Verkauf,
”
Personal, . . .)
❏ Operationen (Transaktionsprogramme) auf diesen Datenbanken typischerweise mit Zugriff
auf kleine Datenmengen (z.B. Direktzugriff über Kontonummern), Durchführung weniger
Änderungsoperationen bzw. kleiner“ Lesetransaktionen
”
❏ Anforderungen: hoher Transaktionsdurchsatz (mehrere Hundert bis Tausend Transaktionen
pro Sekunde!)
⇒ online transaction processing (OLTP)“
”
➢ Neue, zusätzliche Anwendungsgebiete für große Informationssysteme:
❏ Management-Informationssysteme
❏ Decision-Support
❏ Integration mehrerer operativer Datenbanken
❏ Hier sollen die vorhandenen Daten verdichtet werden, um globalere Zusammenhänge zu
erkennen
❏ Anforderungen: große komplexe Anfragen mit hohem Aggregationsgrad, wenig oder keine
Änderungen
⇒ online analytical processing (OLAP)“
”
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-1
➢ Typische Aspekte:
❏ Große operative Datenbank (> 1 TByte)
❏ Gewünschte Anfragen stellen Daten stark verdichtet aggregiert dar:
✧ relativ teuer
✧ können nicht parallel zum laufenden Betrieb gestellt werden.
✧ sollen unterschiedliche Präzisierungsgrade ermöglichen.
❏ Aus der operativen Datenbank werden regelmässig (etwa 1x täglich oder 1x monatlich)
Daten in eine zweite (wesentlich kleinere) Datenbank, dem Data Warehouse übertragen.
Das Data Warehouse steht dann für beliebige Anfragen zur Verfügung. Gegebenenfalls
wird das Data Warehouse in weitere Komponenten unterteilt, den sog. Data Marts.
OLTP
Online Transaction
Processing
OLAP
Online Analytical
Processing
Decision Support-Anfragen
Data Mining
operationale DB
operationale DB
Data
Warehouse
operationale DB
operationale DB
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-2
➢ Datenbankarchitektur: Ein Data Warehouse enthält üblicherweise zwei Arten von
Informationen:
❏ eine Faktentabelle:
✧ Diese enthält pro Tupel einen einzelnen Geschäftsvorfall (einen Verkauf, einen
Vertragsabschluß, einen Auftrag, . . .)
✧ enthält jeweils Fremdschlüssel der beteiligten Dimensionen sowie weitere Informationen
❏ verschiedene Dimensionen: Jede Dimension beschreibt einen zentralen Aspekt für das
Data Warehouse. Übliche Dimensionen sind: Lieferant, Produkt, Zeit, Region, Filiale,. . .
➢ Ein wesentliches Problem ist etwa die adäquate Modellierung der Dimensionen
❏ Auftretende Probleme:
✧ Dimensionen haben eine hierarchische Gliederungsstruktur:
– Region: Kontinent – Land – Bundesland – Stadt – Straße
– Zeit: Jahr – Monat– Tag
✧ Manche Dimensionen haben mehrere nicht-kompatible Strukturen:
– Jahr – Monat– Tag
– Jahr – Woche – Tag
❏ Dimensionsstrukturen können sich über die Zeit verändern, wenn sich das Anwendungsfeld
verändert ⇒ Anpassung alter Daten/Dimensionsinformation problematisch!
➢ Üblicherweise werden Anfragen an die Fakten-Tabelle im Zusammenhang mit einer oder
mehreren Dimensionen gestellt (mehrfache Joins).
➢ Diese Anfragen enthalten oft Aggregierungen und sind nach den Dimensionen strukturiert
⇒ Multidimensionale Anfragen
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-3
➢ Beispiel:
Abbildung 10-1: Multidimensionale Anfragen
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-4
➢ Datenwürfel ( Cube“): Strukturierte Darstellung der vorhandenen Informationen:
”
Hersteller
Jahr
Abbildung 10-2: Ein Datenwürfel
➢ Übliche Variationen:
❏ Hinzunahme/Wegnahme von Dimensionen: Steuerung durch group by-Klausel
❏ Verfeinerung von Dimensionen
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-5
➢ Da die spezielle Struktur eines Data Warehouse nur unzureichend von relationalen
Datenbanken unterstützt wird, werden bei der Entwicklung adäquater Systeme insbesondere
folgende Entwicklungslinien verfolgt:
❏ ROLAP: Das Data-Warehouse-System wird auf Basis von eines relationalen DBMS
realisiert.
❏ MOLAP: Die Daten werden in einer speziellen mehrdimensionalen Form gespeichert (etwa
mehrdimensionale Arrays).
➢ Gegenwärtig haben viele Datenbankhersteller den SQL-Support fürs Data Warehousing
wesentlich erweitert!
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-6
➢ Wesentliche Anforderung: Das Data Warehouse soll unterschiedlichen Mitarbeitern
und Problemstellungen gerecht werden. Deswegen werden auch unterschiedliche
Anfragewerkzeuge eingesetzt:
❏ Standard-SQL-Aggregierungen
❏ Data Mining Tools (inkl. Neuronalen Netzwerken)
❏ Statistische Analysen
❏ Expertensysteme (Frage/Antwort-Systeme)
➢ Die adäquate Aufbereitung von Dimensionsanfragen ist durchaus aufwendig und nicht
unproblematisch!
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-7
10.2
Adäquater Datenbankentwurf
➢ Dimensionstabellen sind üblicherweise nicht normalisiert, d.h. es gibt eine Faktentabelle mit
zugehörigen Dimensionstabellen. Dies nennt man auch Star-Schema“.
”
Abbildung 10-3: Ein Star-Schema
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-8
➢ Zweck:
❏ Anzahl der Joins wird kleiner.
❏ spezieller Join: star-join kann gut optimiert werden
❏ Auftretende Redundanzen oft unproblematisch, da Dimensionsdaten sich nur wenig ändern
und im wesentlichen Einfügungen enthalten.
➢ Normalisierte Dimensionen werden auch Snowflake-Schema genannt.
➢ Praktiker empfehlen üblicherweise das Starschema!
➢ ggf. weitere Variationen in bestimmten Anwendungsszenarien sinnvoll:
➢ Beispiel: Die Struktur einiger Dimensionen ändert sich sehr häufig: Hier kann es sinnvoll sein,
ggf. diese Dimension durch eine Meta-Dimension zu ersetzen.
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-9
10.3
TPC-D-Benchmark
➢ Decision-Support-Anfragen in einem hypothetischen Handelsunternehmen
➢ Zukünftig wird dieser Benchmark in zwei Benchmarks aufgeteilt (TPC-H und TPC-R).
Abbildung 10-4: Das TPC-D-Datenmodell
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-10
❏ Datenbankgröße skalierbar (SF=1,10,30,100,300,1000), dabei ergibt SF=1 ungefähr 1GB
Nutz-Daten.
❏ 19 Anfragen:
✧ aufsummierender Preisbericht
✧ Gute Lieferanten für bestimmte Teile
✧ nichtabgearbeitete Lieferungen (sortiert nach Priorität)
✧ ...
✧ Erzeugung neuer Bestellungen (SF*1500)
✧ Entfernung von überflüssigen oder überholten Informationen (ebenfalls SF*1500)
❏ Leistungsgrößen:
✧ Systempreis auf 5 Jahre
✧ TPC-D-Powermetrik QppD@Size: Anzahl der sequentiell ausgeführten Anfragen und
Änderungen pro Stunde, gewichtet mit der Skalierung
✧ Durchsatz QthD@Size: Anzahl der Anfragen und Änderungen pro Stunde bei
Parallelisierung
✧ Preis/Leistungsverhältnis (Dollar pro Anfrage pro Stunde): Hierbei wird für die Anzahl
der Anfragen pro Stunde das geometrische Mittel von QppD@Size und QthD@Size
verwendet.
❏ Gegenwärtige Werte (für eine 300-GB-Datenbank):
✧ QppD@300GB=2000, QthD@300GB=1200 bei einem Systempreis von 5 Mio $.
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-11
Beispiel: Veränderung des Marktanteils einer Nation innerhalb zweier Jahre“ (Query 8)
”
(Achtung: Hier SQL-2, nicht Oracle-SQL!)
select
o_year,
sum(case
when nation = "’ then volume
else 0
end) / sum(volume) as mkt_share
from
( select extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,supplier,lineitem,orders,customer,
nation n1,nation n2,region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = "’
and s_nationkey = n2.n_nationkey
and o_orderdate between date ’1995-01-01’ and date ’1996-12-31’
and p_type = "’
) as all_nations
group by o_year
order by o_year;
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-12
Beispiel: Bestimmng des Brutto-Umsatzes bestimmter Produkte“ (Query 19) (Typische
”
Anfrage für Data Mining-Tools)
select
from
where
sum(l_extendedprice* (1 - l_discount)) as revenue
lineitem, part
( p_partkey = l_partkey and p_brand = ’’
and p_container in (’SM CASE’, ’SM BOX’, ’SM PACK’, ’SM PKG’)
and l_quantity >= and l_quantity <= + 10
and p_size between 1 and 5
and l_shipmode in (’AIR’, ’AIR REG’)
and l_shipinstruct = ’DELIVER IN PERSON’)
or
( p_partkey = l_partkey
and p_brand = ’’
and p_container in (’MED BAG’, ’MED BOX’, ’MED PKG’, ’MED PACK’)
and l_quantity >= and l_quantity <= + 10
and p_size between 1 and 10
and l_shipmode in (’AIR’, ’AIR REG’)
and l_shipinstruct = ’DELIVER IN PERSON’)
or
( p_partkey = l_partkey and p_brand = ’’
and p_container in (’LG CASE’, ’LG BOX’, ’LG PACK’, ’LG PKG’)
and l_quantity >= and l_quantity <= + 10
and p_size between 1 and 15
and l_shipmode in (’AIR’, ’AIR REG’)
and l_shipinstruct = ’DELIVER IN PERSON’);
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-13
10.4
Oracle8i-Support für Data Warehousing
10.4.1
Erweiterte Anfrageunterstützung
In Oracle-8i können Rollup- und Cube-Anfragen direkt formuliert werden.
Beispiel:
EMP:
DEPT:
EMPNO
----7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
ENAME
---------SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
DEPTNO
--------10
20
30
40
JOB
MGR HIREDA
SAL
COMM
DEPTNO
--------- --------- ------ --------- --------- --------CLERK
7902 171280
800
20
SALESMAN
7698 200281
1600
300
30
SALESMAN
7698 220281
1250
500
30
MANAGER
7839 020481
2975
20
SALESMAN
7698 280981
1250
1400
30
MANAGER
7839 010581
2850
30
MANAGER
7839 090681
2450
10
ANALYST
7566 091282
3000
20
PRESIDENT
171181
5000
10
SALESMAN
7698 080981
1500
0
30
CLERK
7788 120183
1100
20
CLERK
7698 031281
950
30
ANALYST
7566 031281
3000
20
CLERK
7782 230182
1300
10
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-14
➢ Rollup-Anfragen:
select deptno,job,count(*),sum(sal)
from emp
group by rollup (deptno,job)
DEPTNO JOB
COUNT(*) SUM(SAL)
--------- --------- --------- --------10 CLERK
1
1300
10 MANAGER
1
2450
10 PRESIDENT
1
5000
10
3
8750
20 ANALYST
2
6000
20 CLERK
2
1900
20 MANAGER
1
2975
20
5
10875
30 CLERK
1
950
30 MANAGER
1
2850
30 SALESMAN
4
5600
30
6
9400
14
29025
➢ Wie sieht diese Anfrage ohne rollup aus?
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-15
➢ Bestimmung aller Werte eines Datenwürfels:
select deptno,job,sum(sal)
from emp
group by cube(deptno,job);
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
DEPTNO
-------10
10
10
10
20
20
20
20
30
30
30
30
JOB
SUM(SAL)
--------- --------CLERK
1300
MANAGER
2450
PRESIDENT
5000
8750
ANALYST
6000
CLERK
1900
MANAGER
2975
10875
CLERK
950
MANAGER
2850
SALESMAN
5600
9400
ANALYST
6000
CLERK
4150
MANAGER
8275
PRESIDENT
5000
SALESMAN
5600
29025
10-16
➢ Weitere Beispiele:
select deptno,job,count(*),sum(sal)
from emp
group by rollup (deptno,job)
having grouping(job)=1
DEPTNO JOB
COUNT(*) SUM(SAL)
--------- --------- --------- --------10
3
8750
20
5
10875
30
6
9400
14
29025
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-17
select decode(grouping(dname),1,’All Departements’,dname)
AS departement,
decode(grouping(job),1,’All Jobs’,job)
AS job,
count(*), sum(sal)
from
emp,dept
where emp.deptno=dept.deptno
group by rollup (dname,job)
DEPARTEMENT
---------------ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
All Departements
JOB
COUNT(*) SUM(SAL)
--------- --------- --------CLERK
1
1300
MANAGER
1
2450
PRESIDENT
1
5000
All Jobs
3
8750
ANALYST
2
6000
CLERK
2
1900
MANAGER
1
2975
All Jobs
5
10875
CLERK
1
950
MANAGER
1
2850
SALESMAN
4
5600
All Jobs
6
9400
All Jobs
14
29025
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-18
➢ Top-n/Bottom-n-Anfragen:
❏ oft interessieren nur die besten/schlechtesten Ergebnisse.
❏ Derartige Anfragetypen werden von SQL nicht adäquat unterstützt.
❏ Beliebter Trick: Viele DBMSe nummerieren die Tupel in Tabellen und Anfrageergebnissen.
❏ Risiko: Fehlinterpretation der Nummerierung, Änderung der Implementierung des DBMS,
...
❏ Beispiel: Oracle 8i:
✧ Attribut rownum enthält die Nummer eines Tupels
✧ Folgende Anfrage scheitert:
select ename, sal
from
emp
where rownum < 6
order by sal desc
ENAME
SAL
---------- --------ALLEN
1600
WARD
1250
MARTIN
1250
SMITH
800
✧ In Oracle-8i wird erst rownum gesetzt und dann sortiert!
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-19
✧ Lauffähige Oracle8i-Lösungen:
–
create or replace view emp_view as
select * from emp
order by sal desc
select ename,sal
from
emp_view
where rownum < 6
– oder auch
select ename,sal
from (select * from emp order by sal desc)
where rownum < 6
– Ergebnis:
ENAME
SAL
---------- --------KING
5000
SCOTT
3000
FORD
3000
JONES
2975
BLAKE
2850
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-20
10.4.2
Datenmodellierung
➢ In Oracle-8i können Dimensionen strukturiert werden.
➢ Dies kann in Anfragen und bei der Anfrageoptimierung (Materialisierung von Anfragen)
effizient eingesetzt werden.
➢ Beispiel:
➢ Data-Warehouse-Tabellen:
❏ bundeslaender(bundeslaender code,budeslaender name)
❏ staedte(staedte code,staedte name,bundeslaender code)
❏ produkte(produkte code,produkte name,marke)
❏ verkaeufe(verkauf code,datum,betrag,produkte code,staedte code)
❏ zeit(datum,woche,monat, monats name,quartal,jahr,jahreszeit)
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-21
➢ Flexible Definition von Dimensionen:
create dimension zeit_dim
level datum is zeit.datum
level woche is zeit.woche
level monat is zeit.monat
level quartal is zeit.quartal
level jahreszeit is zeit.jahreszeit
level jahr is zeit.jahr
hierarchy kalender_rollup (
datum child of
monat child of
quartal child of
jahr)
hierarchy jahreszeit_rollup(
datum
child of
jahreszeit child of
jahr)
hierarchy wochen_rollup (
datum child of
woche child of
jahr)
attribute monat determines zeit.monats_name;
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-22
➢ Normalisierte Dimensionen:
create dimension regionen_dim
level staedte_code
is staedte.staedte_code
level staedte_name
is staedte.staedte_name
level bundeslaender_code is bundeslaender.bundeslaender_code
level bundeslaender_name is bundeslaender.bundeslaender_name
hierarchy bundeslaender_rollup (
staedte_code CHILD OF
bundeslaender_code
JOIN KEY staedte.bundeslaender_code REFERENCES bundeslaender_code)
ATTRIBUTE staedte_code determines staedte.staedte_name
ATTRIBUTE bundeslaender_code DETERMINES bundeslaender.bundeslaender_name;
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-23
10.4.3
Optimierung von Data Warehouse-Anfragen
➢ Wesentliches Problem: der Join mit den verschiedenen Dimensionen wird dauernd benötigt.
➢ Idee:
❏ Bestimmte Anfragen kommen immer wieder als Teilanfragen vor.
❏ Diese Anfragen werden als Sicht berechnet und gespeichert! (⇒ Materialized View)
❏ Darauf basierende Anfragen greifen dann nicht mehr auf die Basistabellen, sondern auf
den Materialized View zu.
❏ Dabei werden auch die in der HIERACHY-Klausel Dimensionen mitberücksichtigt!
➢ Beispiel:
create materialized view verkaeufe_summary
enable query rewrite
as select p.marke, b.bundeslaender_code, s.staedte_name, z.monat,
sum(v.betrag) as total_verkaeufe
from verkaeufe v, staedte s, zeit z, bundeslaender b, produkte p
where v.staedte_code = s.staedte_code
and s.bundeslaender_code = b.bundeslaender_code
and v.datum = z.datum
and v.produkte_code = p.produkte_code
group by p.marke, b. bundeslaender_code, s.staedte_name, z.monat;
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-24
Anfrage:
select p.marke, b.bundeslaender_name, z.jahr,
sum(v.betrag) as total_verkaeufe
from verkaeufe v, staedte s, zeit z, bundeslaender b, produkte p
where v.staedte_code = s.staedte_code
and s.bundeslaender_code = b.bundeslaender_code
and v.datum = z.datum
and v.produkte_code = p.produkte_code
group by p.marke, b. bundeslaender_name, z.jahr;
Execution Plan
-------------------------------------------------------------SELECT STATEMENT Optimizer-CHOOSE
SORT (GROUP BY)
HASH JOIN
HASH JOIN
TABLE ACCESS (FULL) OF ’BUNDESLAENDER’
TABLE ACCESS (FULL) OF ’VERKAEUFE_SUMMARY’
VIEW
SORT (UNIQUE)
TABLE ACCESS (FULL) OF ’ZEIT’
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-25
➢ Weitere Optimierungsaspekte:
❏ Spezielle Algorithmen für Star-Joins
❏ Load-Strategien fürs Data Warehouse:
Wie lädt man 1 TByte oder 20 Mio. Transaktionen in vier Stunden in das Data
Warehouse?
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-26
10.5
Weitere Fragestellungen
Im Zusammenhang mit Data Warehouses werden eine ganze Reihe weiterer aktueller
Forschungsfragen behandelt, darunter im Zusammenhang mit dem sog. ETL-Prozess“
”
(Extract–Transform–Load):
➢ (konsistente) Integration von Daten aus verschiedenen Quellen
➢ Auswahl der zu integrierenden Daten
➢ Auswahl einer gemeinsamen Darstellung (Modell)
➢ Bereinigung von Fehlern, Vervollständigung
➢ Erkennen von Änderungen in operationalen DBen
➢ (inkrementelles) Propagieren der Änderungen ins Warehouse
➢ erforderliche Meta-Daten
➢ ...
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-27
ETL-Prozess
data warehouse
Loading
data staging area
Completion
Cleaning
Integration
aux. DB
Completion
Integration
Cleaning
Transformation
Transformation
Completion
Monitoring
&
Extraction
Monitoring
&
Extraction
Monitoring
&
Extraction
operational
DB
operational
DB
operational
DB
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
rules
10-28
10.6
Literaturhinweise
Craig, R.S., J. Vivona und D. Bercovitch (1999). Microsoft Data Warehousing . Wiley.
Debevoise, T. (1999). The data warehouse method. Prentice Hall.
Inmon, W.H., K. Rudin, C. Buss und R. Sousa (1999). Data Warehouse Performance. Wiley.
Kimball, R. (1996). The data warehouse toolkit: practical techniques for building dimensional data warehouses.
Wiley.
Kisseleff, A. (1999). Oracle8i Warehousing . In: 12. DOAG, Stuttgart.
Kurz, A. (1999). Data Warehousing – Enabling Technology . mitp Verlag, Bonn.
TPC (1999). The TPC Benchmark H. http://www.tpc.org.
c M. Scholl, 2001/02 – Informationssysteme: 10. Data Warehousing
10-29