Der ETL Prozess

Data Warehousing
und
Data Mining
ETL: Extraction, Transformation, Load
Ulf Leser
Wissensmanagement in der
Bioinformatik
Sprachen für OLAP Operationen
• Sprachen für OLAP Operationen
– Bereitstellung der notwendigen Operationen
– Ökonomisches Design
• Keine vielfach geschachtelten SQL Operationen
• Keine „Programmierung“
• Hauptsächlich zwei Ansätze
– Multidimensional Expressions (MDX)
• Basiert direkt auf MDDM Elementen: Cube, Dimension, Fakt, ...
• Auf dem Vormarsch als Standard
– Erweiterungen von SQL
• Erweiterungen um spezielle Operationen (ROLLUP, CUBE, …)
• Daten müssen relational vorliegen
• „MDDM-unaware“ – Anfragen müssen auf Star-/ SnowflakeSchema formuliert werden
Ulf Leser: DWH und DM, Sommersemester 2007
2
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
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
Ulf Leser: DWH und DM, Sommersemester 2007
3
Struktur einer MDX Query
SELECT <axis-spec1>, <axis-spec2>, ..
FROM <cube-spec1>, <cube-spec2>, ...
WHERE <slice-specification>
• Dimensions (SELECT)
– Angabe der darzustellenden Achsen
– Abbildung in „mehrdimensionale“ Tabellen
• ON COLUMNS, ROWS, PAGES, CHAPTER, ...
– Jede Achsenspezifikation muss eine Menge von Members beschreiben
• Als explizit angegebene Menge (in {})
• Oder durch Funktionen
– Namen von Members werden in [] verpackt
• Wenn nötig zur syntaktischen Abgrenzung
• Cube (FROM)
– Auswahl des Basis-Cubes für die Anfrage
• Slicer (WHERE)
– Auswahl des betreffenden Fakten
– Intuitiv zu lesen als „etwas zu tun haben mit“
Ulf Leser: DWH und DM, Sommersemester 2007
4
Beispiel
Member: Unique Names oder Navigation
Verschiedene Klassifikationsstufen in einer Dim möglich
SELECT {Wein, Bier, Limo, Saft} ON COLUMNS
{[2000], [1999], [1998].[1], [1998].[2]} on ROWS
FROM
Sales
WHERE (Measures.Menge, Region.Germany)
Auswahl des Fakt „Menge“
Beschränkung auf WerteWein
in BRD
Bier
Limo
Saft
2000
1999
Implizite Summierung
1998.1
1998.2
Ulf Leser: DWH und DM, Sommersemester 2007
5
Navigation in den Hierarchien
SELECT {Prodgroup.MEMBERS, [Becks Bier].PARENT} ON COLUMNS
{Year.MEMBERS} on ROWS
FROM Sales
WHERE (Measures.Menge)
Navigationsfunktionen
•
•
•
•
•
MEMBERS:
Knoten einer Klassifikationsstufe
CHILDREN: Kinderknoten eines Klassifikationsknoten
PARENT:
Vaterknoten eines Klassifikationsknoten
LASTCHILD, FIRSTCHILD, NEXTMEMBER, LAG, LEAD...
DESCENDENTS: Rekursives Absteigen
Ulf Leser: DWH und DM, Sommersemester 2007
6
SQL und OLAP
• Übersetzung MDDM in Star- oder Snowflake Schema
• Triviale Operationen
– Auswahl (slice, dice): Joins und Selects
– Verfeinerung (drill-down): Joins und Selects
• Einfache Operation
– Aggregation um eine Stufe: Group-by
• Interessant
– Hierarchische Aggregationen
– Multidimensionale Aggregationen
– Analytische Funktionen (gleitende Durchschnitte etc.)
• Ist alles in SQL-92 möglich, aber nur kompliziert
auszudrücken und ineffizient in der Bearbeitung
Ulf Leser: DWH und DM, Sommersemester 2007
7
Hierarchische Aggregation –2Alle Verkäufe der Produktgruppe „Wein“ nach Tagen,
Monaten und Jahren
Benötigt UNION und eine Query pro Klassifikationsstufe
SELECT
FROM
WHERE
T.day_id, sum(amount*price)
sales S, product P
P.pg_name=„Wein“ AND
P.product_id
= S.product_id
SELECT
T.month_id,
sum(amount*price)
GROUP BY FROM
T.day_id sales S, product P, time T
WHERE
P.pg_name=„Wein“ AND
P.product_id
= S.product_id
AND
SELECT
T.year_id,
sum(amount*price)
T.day_id
FROM = S.day_id
sales S, product P, time T
GROUP BY T.month_id
WHERE
P.pg_name=„Wein“ AND
P.product_id = S.product_id AND
T.day_id = S.day_id
GROUP BY T.year_id
Ulf Leser: DWH und DM, Sommersemester 2007
8
ROLLUP Beispiel
SELECT
FROM
WHERE
GROUP BY
T.year_id, T.month_id, T.day_id, sum(...)
sales S, time T
T.day_id = S.day_id
ROLLUP(T.year_id, T.month_id, T.day_id)
1997
Jan
1
1997
Jan
...
1997
Jan
31
1997
Jan
ALL
1997
Feb
...
1997
March
ALL
1997
...
...
1997
ALL
ALL
1998
Jan
1
1998
...
...
1998
ALL
ALL
...
...
...
ALL
ALL
ALL
Ulf Leser: DWH und DM, Sommersemester 2007
200
300
31.000
450
1.456.400
100
45.000
12.445.750
9
Multidimensionale Aggregation
Verkäufe nach Produktgruppen und Jahren
1998
•
•
•
•
1999
2000
Gesamt
Weine
15
17
13
45
Biere
10
15
11
36
Gesamt
25
32
24
81
sum() ... GROUP BY pg_id, year_id
sum() ... GROUP BY pg_id
sum()
...viele
GROUP
BY year_id
Wie
Queries
sind notwendig
sum()
Ulf Leser: DWH und DM, Sommersemester 2007
?
10
CUBE - Beispiel
SELECT
pg_id, shop_id, sum(amount*price)
FROM
sales S ...
GROUP BY CUBE (S.pg_id, S.shop_id, T.year_id)
Bier
Kreuzberg
ALL
...
Bier
Charlottenburg
ALL
...
Bier
ALL
1997
...
Wein
ALL
1998
...
ALL
Kreuzberg
1997
...
ALL
Charlottenburg
1998
...
Bier
ALL
ALL
...
Wein
ALL
ALL
...
ALL
ALL
1997
...
ALL
ALL
1998
...
ALL
Kreuzberg
ALL
...
ALL
Charlottenburg
ALL
...
ALL
ALL
ALL
...
Ulf Leser: DWH und DM, Sommersemester 2007
11
SQL Analytical Functions
• Erweiterung in SQL3 zur flexibleren Berechnung von Aggregaten
und Rankings
– Ausgabe der Summe Verkäufe eines Tages zusammen mit der Summe
Verkäufe des Monats in einer Zeile
– Rang der Summe Verkäufe eines Monats im Jahr über alle Jahre
– Vergleich der Summe Verkäufe eines Monats zum gleitenden
Dreimonatsmittel
• OLAP Funktionen
– Erscheinen in der SELECT Klausel
– Berechnen für jedes Tupel des Stroms der Groupby-Query einen Wert
– Diese Werte können von neuen, in der SELECT Klausel angegeben
Partitionierungen und Sortierungen abhängen
– Damit kann man unabhängige Gruppierungen in einer Zeile des
Ergebnisses verwenden
• CUBE etc. erzeugen immer neue Tupel
Ulf Leser: DWH und DM, Sommersemester 2007
12
OVER() mit lokaler Partitionierung
• OVER() gestattet die Angabe attributlokaler Partitionen
• Ausgabe der Summe Verkäufe eines Tages im Verhältnis zu
Verkäufen des Jahres
SELECT
FROM
GROUP BY
S.day_id, sum(amount) AS day_sum,
sum(amount) OVER(PARTITION BY YEAR(S.day_id)) AS year_sum,
day_sum/year_sum AS ratio
sales S,
SQL Funktion
S.day_id;
day_id
day_sum
YEAR()
day_id
day_sum
year_sum
Ratio
1.1.1999
500
1.1.1999
500
5.000
0.10
2.1.1999
500
2.1.1999
500
5.000
0.10
3.1.1999
1.000
3.1.1999
1.000
5.000
0.20
1.2.1999
1.500
1.2.1999
1.500
5.000
0.30
2.2.1999
1.500
2.2.1999
1.500
5.000
0.30
1.1.2000
600
1.1.2000
600
1.000
0.60
5.5.2000
400
5.5.2000
400
1.000
0.40
4.000
4.000
1.00
13
1.10.2001
4.000
1.10.2001
Ulf Leser: DWH und DM, Sommersemester
2007
Veranschaulichung
S.day_id, sum(amount), sum(amount) OVER(PARTITION BY YEAR(S.day_id))
GROUP BY S.day_id
GROUP BY YEAR(day_id)
(S.day_id, sum(amount))
FROM sales S
Ulf Leser: DWH und DM, Sommersemester 2007
14
Lokale Sortierung
• Durch lokale Sortierung mit ORDER BY kann die Reihenfolge der
Tupel im inneren Strom pro OVER() Klausel bestimmt werden
• Die Aggregatfunktion iteriert bei einem ORDER BY nur über die
Tupel zwischen dem ersten bis zum aktuellen Tupel (bzgl. der
angegebenen Ordnung)
• Dadurch kann man kumulierte Summen erreichen
• Beispiel: Summe der Verkäufe pro Tag sowie die kumulierten
Gesamtverkäufe nach Tagen, und die kumulierten Verkäufe im
jeweiligen Monat nach Tagen
SELECT
FROM
GROUP BY
S.day_id, sum(amount) AS day_sum,
sum(amount) OVER(ORDER BY S.day_id) AS cum_sum,
sum(amount) OVER(PARTITION BY S.month_id
ORDER BY S.day_id) AS cumm_sum
sales S,
S.day_id;
Ulf Leser: DWH und DM, Sommersemester 2007
15
Dynamische Fenster
• Der Vergleich der Werte des aktuellen Tuples mit den
lokalen Aggregaten zieht immer folgende Tupel in die
Aggregation mit ein
– OVER() ohne Argument: Alle Tupel
– OVER(PARTITION BY): Alle Tupel der gleichen Partition
– OVER(ORDER BY): Alle Tupel zwischen Ordungsbeginn und
aktuellem Tupel
• Das Fenster des Vergleichs kann man auch explizit
angeben
– ROWS BETWEEN … AND …
– Möglich jeweils
• UNBOUND PRECEDING / FOLLOWING: Vom Anfang / bis zum Ende
• K PRECEDING / FOLLOWING: Die k Tupel vorher / nachher
• CURRENT ROW: aktuelles Tupel
• Damit kann man gleitende Durchschnitte bilden
Ulf Leser: DWH und DM, Sommersemester 2007
16
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
-
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
17
Der ETL Prozess
100
80
60
40
20
0
1.
2.
3.
4.
Qr t l. Qr t l. Qr t l. Qr t l.
• Extraction
• Transformation
• Load
Ulf Leser: DWH und DM, Sommersemester 2007
18
ETL - Übersicht
• Daten werden mehrmals physikalisch bewegt
– Von den Quellen zur Staging Area
• Extraktion von Daten aus den Quellen
• Erstellen / Erkennen von differentiellen Updates
• Erstellen von LOAD Files
– Von der Staging Area zur Basisdatenbank
• Data Cleaning und Tagging
• Erstellung integrierter Datenbestände
• Ziele
– Kontinuierliche Datenversorgung des DWH
– Sicherung der DWH Konsistenz bzgl. Datenquellen
• Effiziente Methoden essentiell -> Sperrzeiten minimieren
• Rigorose Prüfungen essentiell -> Datenqualität sichern
Ulf Leser: DWH und DM, Sommersemester 2007
19
1. Extraktion
• Aufgabe: Extraktion von Änderungsdaten aus Quellen
– Ist auf Kooperation der Quellen angewiesen
– Quellen müssen ihre Daten preisgeben
• Überwindet Systemgrenzen
– Zugriff auf HOST Systeme ohne Online Zugriff
• BATCH Jobs, Reportwriter
– Daten in Non-standard Datenbanken
• Keine Anfragesprachen im eigentlichen Sinne
• Programmierung in PL-1, COBOL, Natural, IMS, ...
– Verteiltes (Herrschafts-)Wissen
• Unklare Semantik, Doppelbelegung von Feldern, sprechende
Schlüssel
• Fehlende Dokumentation
– Oftmals kommerzielle Tools vorhanden
• Insb. zum Zugriff auf Standardsoftware
Ulf Leser: DWH und DM, Sommersemester 2007
20
Extraktion
• Zwei wichtige Eigenschaften des Extraktionsschritts
– Zeitpunkt
– Art der extrahierten Daten
• Zu beachten
– Wir betrachten meistens „Flow“-artige DWH
• Alle Veränderungen (Verkäufe, Telefonate, …) sollen im Cube
repräsentiert werden
• Nachträgliche Änderungen sind möglich, aber eher selten
– Stornierungen, Rücksendungen
– Die Situation ist anders bei „Stock“-artigen Daten
• Zustand zu einem bestimmten Zeitpunkt (Lagerbestand)
• Hier müssen die Zeitpunkte festgelegt werden
Ulf Leser: DWH und DM, Sommersemester 2007
21
Zeitpunkt der Extraktion
• Synchrone Extraktion
– Quelle propagiert jede Änderung
• Asynchrone Extraktion
– Periodisch
• Push: Quellen erzeugen regelmäßig Extrakte
– Das ist Standard: Reportgenerierung
• Pull: DWH fragt regelmäßig Datenbestand ab
– Ereignisgesteuert
• Push: Quelle informiert z.B. alle X Änderungen
• Pull: DWH erfragt Änderungen bei bestimmten Ereignissen
– Jahresabschluss, Quartalsabschluss, …
– Anfragegesteuert
• Push: • Pull: DWH erfragt Änderungen bei jedem Zugriff auf die (noch
nicht vorhandenen oder potentiell veralteten) Daten
Ulf Leser: DWH und DM, Sommersemester 2007
22
Art der Daten
• Snapshot: Quelle liefert kompletten Datenbestand
– Lieferantenkatalog, Preisliste, etc. („Stock“-artige Daten)
– Korrekter Import erfordert Erkennen von Änderungen
• Differential Snapshot-Problem
• Alternative: Komplettes Überschreiben (aber IC!)
– Historie kann nicht exakt abgebildet werden
• Warum nicht?
• Log: Quelle liefert jede Änderung seit letztem Export
– Transaktionslogs oder anwendungsgesteuertes Logging
– Kann direkt importiert werden
• Das DWH speichert dann ggf. Ereignis und seine Stornierung
• Nettolog: Quelle liefert das Delta zum letzten Export
– Kann direkt importiert werden
– Bei „Stock“-artigen Daten keine komplette Historie möglich
Ulf Leser: DWH und DM, Sommersemester 2007
23
Datenversorgung
Quelle ...
Technik
Aktualität
DWH
Belastung
DWH
Belastung
Quellen
... erstellt periodisch
Exportfiles
Reports,
Snapshots
Je nach
Frequenz
Eher niedrig
Eher niedrig
... pushed jede Änderung
Trigger,
Changelogs,
Replikation
Maximal
Hoch
Hoch
... erstellt
Extrakte
auf
Anfrage
(Poll)
Vor Benutzung
Sehr schwierig
Maximal
Medium
Medium
Anwendungsgesteuert
Je nachdem
Je nach
Frequenz
Je nach
Frequenz
Je nach
Frequenz
Ulf Leser: DWH und DM, Sommersemester 2007
24
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
-
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
25
Differential Snapshot Problem
[LGM96]
• Viele Quellen liefern immer den vollen Datenbestand
–
–
–
–
Molekularbiologische Datenbanken
Kundenlisten, Angestelltenlisten
Produktkataloge
„Dumme“ Reports
• Problem
– Ständiges Einspielen aller Daten ineffizient
• Häufige Wiederholung derselben Operationen
– Duplikate müssen erkannt und gelöscht werden
• Erfordert vorheriges Löschen oder Überschreiben
• Viele Probleme mit Schlüsseln, IC‘s, …
• Gesucht: Algorithmen zur Berechnung von DELTA-Files
für sehr große Dateien
Ulf Leser: DWH und DM, Sommersemester 2007
26
Das Differential Snapshot Problem (DSP)
• Quelle liefert Snapshots als Files Fi
– Einfache und identische Struktur mit Attributen A1, … An
– Jedes Tupel hat einen Schlüssel k
– File = ungeordnete Menge von Records (K, A1, ... An)
• Definition
Gegeben zwei Dateien F1, F2 gleicher Struktur mit f1=|F1|, f2=|F2|.
Das Differential Snapshot Problem (DSP) besteht darin, die kleinste
Menge O={INS, DEL, UPD}* zu finden für die gilt: O(F1) = F2
• Bemerkung
– INS, DEL, UPD operieren jeweils auf genau einem Tupel identifiziert
über seinen Schlüssel k
• Änderungen in mehreren Attributen eines Tuples zählen wir nur einmal
– O ist im Allgemeinen nicht eindeutig
• O1={ (ins(X)),(del(X)) } ≡ {}
Ulf Leser: DWH und DM, Sommersemester 2007
27
Szenario
Alter Snapshot
INS
INS
DEL
UPD
K104, k,k,...
K4, t,r,...
K202, a,a,...
K102, p,q,...
Neuer Snapshot
K103
K3
K4
K202: ...
DSP - Algorithmus
K103, t,h,...
K104, k,k,...
K102, p,q,...
K3, t,r,...
K202, b,b,...
Ulf Leser: DWH und DM, Sommersemester 2007
DWH
28
Annahmen
• Kostenmodell
– IO Komplexität
– Alle Operationen im Hauptspeicher sind umsonst
– Das Lesen jedes Records kostet 1
• Sequentielles Lesen
– Das Schreiben des DS ignorieren wir
• Das ist immer gleich teuer (wenn wir das kleinste finden …)
– Keine Beachtung von Blockgrößen etc.
• Hauptspeichergröße: m Records
• Files wesentlich größer als Hauptspeicher
• Achtung: Ähnlich zu Joins, aber …
– Wir haben keine Duplikate (0-1 : 0-1 Beziehung)
– Joins erzeugen keine INS, DEL
• Sondern (in gewisser Weise) nur UPD
Ulf Leser: DWH und DM, Sommersemester 2007
29
1. Versuch: DSsmall - kleine Files
• Annahme: Hauptspeicher m >f1 oder >f2
• Algorithmus (sei m>f1)
– F1 komplett in den Hauptspeicher lesen
– F2 sequentiell lesen. Für Record r
• r∈F1: O=O∪(UPD r) (oder ignorieren, wenn keine Änderung)
• r∉F1: O=O∪(INS r)
• A[r]=true
– Abschließend: Alle Records r∈F1 mit A[r]=false: O=O∪(DEL r)
• Anzahl IO:
• f1+f2
• Verbesserungen
• F1 im Speicher sortieren – schnellerer Lookup
Ulf Leser: DWH und DM, Sommersemester 2007
30
2. Versuch: DSnaive – Nested Loop
• Ab jetzt: m<<f1 und m<< f2
• Algorithmus
–
–
–
–
–
Record r aus F1 lesen
r in F2 suchen, dazu F2 sequentiell lesen
r nicht in F2: O=O∪(DEL r)
r verändert in F2 : O=O∪(UPD r)
r unverändert in F2 : ignorieren
• Problem?
– INS wird nicht gefunden
• Lösung
–
–
–
–
Array A mit IDs aus F2 on-the-fly generieren
Für jedes r, das in F2 enthalten ist: A[r]=true
Abschließender Lauf über A; A[r]=false : (INS r)
Nachteil?
• Geht nur, wenn A in den Hauptspeicher passt
• Sonst: Nested Loop in die Gegenrichtung laufen lassen
Ulf Leser: DWH und DM, Sommersemester 2007
31
DSnaive – Verbesserungen
• Kosten?
– Anzahl IO : f1*f2+INS-Erzeugung
• Verbesserungen?
– Suche in F2 abbrechen, wenn r gefunden
• Verbessert die Laufzeit erheblich, Worst-Case Komplexität bleibt
gleich
– Jeweils Partition mit Größe m von F1 laden
• Verbesserung der Kosten auf f1/m*f2
• Ähnlich zu Blocked-Nested-Loop
Ulf Leser: DWH und DM, Sommersemester 2007
32
3. Versuch: DSsort – Sort-Merge
• Sortieren auf Sekundärspeicher
– F1 in Partitionen Pi mit |Pi|=m lesen
– Pi im Hauptspeicher sortieren und als Fi schreiben
– Alle Fi mergen
• Dazu müssen wir vielleicht sehr viele Dateien öffnen
• Kann man umgehen (hierarchisches, externes Sortieren)
1x f
1x f
2xf
• Sortiertes F2 aufheben für nächstes DS
– Pro DS muss dann nur ein F (F2) sortiert werden
• Algorithmus
– Sortierte F1‘ und F2 ‘ öffnen
– Mergen (paralleles Lesen mit Skipping)
f1 + f 2
• Anzahl IO
• f1+5*f2
Ulf Leser: DWH und DM, Sommersemester 2007
33
DSsort2 – Verbesserung
• Sortiertes F1 vorhanden
– Vom letzten Berechnen
• Berechnung von O
– F2 in Partitionen Pi mit |Pi|=m lesen
– Pi im Hauptspeicher sortieren und als F2i schreiben
– Alle F2i mergen und gleichzeitig mit F1 vergleichen
• Dabei sortierte F2i für das nächste Mal schreiben
1x f2
1x f2
f1 + 2*f2
¾ Anzahl IO: f1+4*f2
Geht‘s noch besser ?
Ulf Leser: DWH und DM, Sommersemester 2007
34
4. Versuch: DShash – Partitioned Hash
• Trick: Persistente Sortierung der Pi ist nicht wirklich
notwendig
• Algorithmus
– F2 in Partitionen Pi mit |Pi|=m/2 hashen
• Sicherstellen der Obergrenze für Platzbedarf der
Hash-Buckets ist hier die Schwierigkeit
2x f2
– F1 liegt noch partitioniert vom letzten Mal vor
• Mit derselben Hashfunktion
• Damit hat man genau zwei m/2 große Partitionen von
Records, die alle denselben Hashkey haben
• Partitionen sind nicht sortiert
f1+ f2
– Jeweils P1,i und P2,i parallel lesen und DS berechnen
• Anzahl IO: f1 + 3*f2
Ulf Leser: DWH und DM, Sommersemester 2007
35
Warum nicht einfach ...
• … UNIX diff verwenden?
– diff erwartet / beachtet Umgebung der Records
– Hier: Records sind völlig ungeordnet
• … DSP in der Datenbank lösen?
– Dreimaliges Lesen jeder Relation notwendig
• … je nachdem, wie gut die Datenbank optimiert …
INSERT INTO delta
SELECT ‚UPD‘, ...
FROM F1, F2
WHERE F1.K=F2.K AND K1.W≠F2.W
UNION
SELECT ‚INS‘, ...
FROM F2
WHERE NOT EXISTS ( ...)
UNION
SELECT ‚DEL‘, ...
FROM F1
WHERE NOT EXISTS ( ...)
Ulf Leser: DWH und DM, Sommersemester 2007
36
Vergleich - Eigenschaften
IO
Bemerkungen
DSnaiv
f1 * f2
Außerdem INS-Datenstruktur notwendig
Dssmall
f1 + f2
Nur für kleine Dateien
Dssort2
f1 + 4*f2
Dshash
f1 + 3*f2
Gleichverteilende Hashfunktion notwendig
Partitionsgröße schwierig zu schätzen
Auf keinen Fall größer als 2*m/2 werden
Gefahr, beträchtlich Speicher zu verschwenden
Ulf Leser: DWH und DM, Sommersemester 2007
37
Weitere DS Verfahren
• Anzahl Partitionen / Runs größer als Filehandles des OS
– Hierarchische externe Sortierverfahren
• Kompression
–
•
•
•
Files komprimieren
Größere Partitionen / Runs
Größere Chance, Vergleich in-memory durchzuführen
In Realität schneller (bei unserem Kostenmodell nicht)
• „Windows“ Algorithmus
–
–
–
–
Annahme: Files haben eine „unscharfe“ Ordnung
Merging mit sliding window über beide Files
Liefert u.U. redundante INS-DEL Paare
Anzahl IO: f1+f2
Ulf Leser: DWH und DM, Sommersemester 2007
38
DS mit Zeitstempel
•
•
•
•
Annahme: Records sind (K, A1,...,An,T)
T: Zeitstempel der letzten Änderung
Kann man das für einen O(f2)-Algorithmus ausnutzen?
Algorithmus
– Festhalten von des letzten Update jedes Files (T0)
– F2 sequentiell lesen
• Nur Records mit T > T0 interessant
– Aber: INS oder UPD?
– Weiteres Problem: DEL wird nicht gefunden
• Zeitstempel erspart nur Attributvergleich, aber nicht das
Lesen der Records
Ulf Leser: DWH und DM, Sommersemester 2007
39
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
-
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
40
Load
• Aufgabe
– Effizientes Einbringen von externen Daten in DWH
• Kritischer Punkt
– Load-Vorgänge blockieren unter Umständen die komplette DB
(Schreibsperre auf Faktentabelle)
• Möglichkeiten
– Satzbasiert
– BULK-Load
– Anwendungsspezifische Schnittstellen
• Z.B. SAP
• Aspekte
– Trigger, Integritätsconstraints, Indexaktualisierung
– Update oder Insert?
Ulf Leser: DWH und DM, Sommersemester 2007
41
Satzbasiert
• Standard-Schnittstellen: PRO*SQL, JDBC, ODBC, ...
• Arbeitet im normalen Transaktionskontext
• Trigger, Indexe und Constraints bleiben aktiv
– Manuelle Deaktivierung möglich
• Keine großräumigen Sperren
– Satzsperren
– Sperren können durch Zwischen-COMMIT verringert werden
• In Oracle ist das weniger performant – shadow blocks
• Aber andere Prozesse erhalten wieder eine Chance
• Benutzung von Prepared Statements essentiell
• Teilweise proprietäre Erweiterungen (Arrays) verfügbar
Ulf Leser: DWH und DM, Sommersemester 2007
42
BULK Load
• DB-spezifische Erweiterungen zum Laden großer
Datenmengen
• Läuft (meist) in speziellem Kontext
–
–
–
–
–
–
–
–
Oracle: sqlldr mit DIRECTPATH Option
Komplette Tabellensperre
Keine Beachtung von Triggern oder Constraints
Indexe werden erst nach Abschluss aktualisiert
Kein transaktionaler Kontext
Kein Logging (d.h. kein Recovery etc.)
Checkpoints zum Wiederaufsetzen
Rasend schnell
• Praxis: BULK Uploads
Ulf Leser: DWH und DM, Sommersemester 2007
43
Beispiel: ORACLE sqlldr
Controlfile
LOAD DATA
INFILE 'book.dat'
REPLACE INTO TABLE book
(
book_title POSITION(1) CHAR(35),
book_price POSITION(37) ZONED(4,2),
book_pages POSITION(42) INTEGER,
book_id "book_seq.nextval"
)
Quelle: Oracle 9.2, Dokumentation
Ulf Leser: DWH und DM, Sommersemester 2007
44
BULK Load Beispiel
• Vielfältige Optionen
–
–
–
–
–
–
–
–
–
Behandlung von Ausnahmen (Badfile)
Einfache Datentransformationen
Checkpoints
Optionale Felder
Konditionales Laden in mehrere Tabellen
Konditionales Laden von Records
REPLACE oder APPEND
Paralleles Laden
...
Ulf Leser: DWH und DM, Sommersemester 2007
45
Direct Path
Quelle: Oracle 9.2, Dokumentation
Ulf Leser: DWH und DM, Sommersemester 2007
46
Technik: Quelle – Arbeitsbereich - BasisDB
Quelle 1
RDBMS
Quelle 2
IMS
Rel. Schema Q1
Rel. Schema Q2
Cube;
Integriertes
Schema
• BULK Load nur für ersten Schritt
• Zweiter Schritt
– INSERT INTO ... SELECT ....
– Logging ausschaltbar
– Parallelisierbar
Ulf Leser: DWH und DM, Sommersemester 2007
47
Transformationen beim Laden der Daten
• Extraktion der Daten aus Quelle mit einfachen Filtern
– Spaltenauswahl, Keine Reklamationen, ...
• Erstellen eines LOAD Files mit einfachen
Konvertierungen
– Zahl – String, Integer – Real, ...
• Transformation meist zeilenorientiert
• Vorbereitung für den DB-spezifischen BULK-LOADER
while (read_line)
parse_line
if (f[10]=2 & f[12]>0)
write(file, f[1], string(f[4]), f[6]+f[7],...
...
bulk_upload( file)
Ulf Leser: DWH und DM, Sommersemester 2007
48
Transformationen in Staging Area
• Benutzt SQL
• Effiziente mengenorientierte Berechnungen möglich
• Vergleiche über Zeilen hinaus möglich
– Schlüsseleigenschaft, Namensduplikaterkennung, ...
• Vergleiche mit Daten in Basisdatenbank möglich
– Duplikate, Plausibilität (Ausreißer), Konsistenz (Artikel-Ids)
• Typisch: Tagging von Datensätzen durch Prüf-Regeln
UPDATE sales SET price=price/MWST;
UPDATE sales SET cust_name=
(SELECT cust_name FROM customer WHERE id=cust_id);
...
UPDATE sales SET flag1=FALSE WHERE cust_name IS NULL;
...
INSERT INTO DWH
SELECT * FROM sales WHERE f1=TRUE & f2=TRUE & ...
Ulf Leser: DWH und DM, Sommersemester 2007
49
Quelle – Arbeitsbereich – Cube
Was macht man wo und wann ?
Quelle -> Arbeitsbereich
Arbeitsbereich -> Cube
Art des Zugriffs
Satzorientiert (read)
Mengenorientiert (SQL)
Verfügbare
Datenbasen
Eine Quelle
Viele Quellen
Verfügbare
Datensätze
Quellabhängig: Alle, alle
Änderungen, Deltas
Zusätzlich Cube verfügbar
(für Duplikate etc.)
Programmiersprache
Skripte: Perl, AWK, ... oder
3GL
SQL, PL/SQL
Ulf Leser: DWH und DM, Sommersemester 2007
50
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
•
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
51
Heterogenität
• Zwei Informationssysteme sind heterogen, wenn sie
sich „irgendwie“ unterscheiden
• Verschiedene Ausprägungen von „irgendwie“ =>
verschiedene Arten von Heterogenität
• Informationsintegration =
Überbrückung von Heterogenität
• Erstellung eines homogenen Systems
• Materialisierte Integration
• Oder: Erweckung des Anscheins eines homogenen Systems
• Virtuelle Integration
Ulf Leser: DWH und DM, Sommersemester 2007
52
Heterogenität
DWH
CRMSystem
Lagerverwaltung
Ulf Leser: DWH und DM, Sommersemester 2007
Produktionsplanung
CRM-System
der Tocherfirma
53
Übersicht
• Technische Heterogenität
•
•
Technische Realisierung des Datenzugriffs
Technische Unterschiede in der Darstellung
• Syntaktische Unterschiede
•
•
Unterschiede in der Darstellung
Gleiche Dinge syntaktisch verschieden repräsentieren
• Datenmodellheterogenität
• Strukturelle Heterogenität
•
•
Strukturelle Unterschiede in der Darstellung
Gleiche Dinge verschieden modellieren
• Semantische Heterogenität
•
•
Unterschiede in der Bedeutung von Namen (Schema und Daten)
Gleiches sagen, verschiedenes meinen (oder andersrum)
Ulf Leser: DWH und DM, Sommersemester 2007
54
Syntaktische Heterogenität
• Unterschiedliche Darstellung desselben Sachverhalts
•
•
•
•
•
•
•
Dezimalpunkt oder –komma
Euro oder €
Comma-separated oder tab-separated
HTML oder ASCII oder Unicode
Notenskala 1-6 oder „sehr gut“, „gut“, …
Binärcodierung oder Zeichen
Datumsformate (12. September 2006, 12.9.2006, 9/12/2006, …)
• Überwindung in der Regel nicht problematisch
• Umrechnung, Übersetzungstabellen, …
Ulf Leser: DWH und DM, Sommersemester 2007
55
Datenmodellheterogenität
• Typische Datenmodelle
•
•
•
•
•
•
CSV
Relational (Tupel)
XML (XML)
Non-Standard (ASN.1)
Domänenspezifisch (ACeDB, EXPRESS, OPEN-GIS, …)
Proprietär (UniProt, PDB, …)
• Unterschied: Zum Austausch oder zur Speicherung
• XML als Speicherformat?
• Black-Box-Sicht – was zählt, ist was die Quelle liefert
• Erfordert Konvertierung
• Spezielle Semantik geht unter Umständen verloren
• XML-Schachtelung im relationalen Modell?
Ulf Leser: DWH und DM, Sommersemester 2007
56
Beispiel
„Fast“ dasselbe in verschiedenen Datenmodellen
Ulf Leser: DWH und DM, Sommersemester 2007
57
Strukturelle Heterogenität
• Allgemein
• Gleiche Dinge in unterschiedlichen Schemata ausdrücken
• Andere Aufteilung von Attributen auf Tabellen
• Fehlende / neue Attribute (wenn Intension nicht betroffen ist)
• Setzt intensionale Überlappung voraus („gleiche Dinge“)
• Fast immer mit semantischer Heterogenität verbunden
• Ausnahme: 1:1 Beziehungen
• Spezialfall: Schematische Heterogenität
• Verwendung anderer Elemente eines Datenmodells
• Kann nicht durch SQL überwunden werden
Ulf Leser: DWH und DM, Sommersemester 2007
58
Beispiel
• Verursacht durch verschiedene Abbildungen eines
objektorientierten Modells
• Gleichwertig?
• Nur durch zusätzliche IC
• S1: typ darf nur bestimmte
Werte annehmen
• S1: umsatz darf nicht immer
gefüllt sein (abh. von typ)
• S2: Gleiche film_id darf
nicht in verschiedenen
Tabellen vorkommen
• …
Ulf Leser: DWH und DM, Sommersemester 2007
59
Spezialfall: Schematische Heterogenität
maenner( Id, vorname, nachname)
frauen( Id, vorname, nachname)
Relation vs. Wert
person( Id, vorname, nachname,
maennlich?, weiblich?)
person( Id, vorname, nachname,
geschlecht)
Ulf Leser: DWH und DM, Sommersemester 2007
Relation vs. Attribut
Attribut vs. Wert
60
Integrierte Sichten
• Verlangt viele Verrenkungen
• Sicht muss angepasst werden, wenn neue Filmtypen
vorliegen
• Datenänderungen bedingen Schemaänderungen
• Das will man unbedingt vermeiden
Ulf Leser: DWH und DM, Sommersemester 2007
61
Exotische Probleme?
• Oh nein
• Schema zur Speicherung
von Filmen des
Verleihers „XYZ“
• ACTORS als VARCHAR
• ORIGINAL – bedeutet
was?
• TITLE, YEAR, … an drei
Stellen
• ID-Räume DEUTSCH und
ORIGINAL getrennt?
• …
Ulf Leser: DWH und DM, Sommersemester 2007
62
Exotische Probleme?
• Schema von eachmovie
(HP)
• Eine einzige Tabelle für Filme
• Zusätzliche Informationen
über Benutzer des WebSystems
• Wenig Infos über Filme, aber
Links zu weiteren Quellen
• GENRE sind boolsche
Attribute
• …
Ulf Leser: DWH und DM, Sommersemester 2007
63
Schema des Filmdienst
FILM-PERSONEN ist m:n
FILM-GENRE ist m:n
Personen können mehrere
Namen haben (Aliase,
Künstlernamen)
Ulf Leser: DWH und DM, Sommersemester 2007
Eigene Tabelle für Filmtitel und
Filmtiteltypen (?) 64
Schema der IMDB
ACTOR und ACTRESS in
verschiedenen Tabellen
Beteiligte in eigenen Tabellen
(FD hat Tabelle FUNKTIONEN)
Ulf Leser: DWH und DM, Sommersemester 2007
65
Semantik
• Fremdwörterduden zu “Semantik”
• „Teilgebiet der Linguistik, das sich mit den Bedeutungen
sprachlicher Zeichen und Zeichenfolgen befasst“
• „Bedeutung, Inhalt eines Wortes, Satzes oder Textes“
• Programmiersprachen
• Syntax: EBNF, Grammatiken
• Semantik: Wirkungen der Ausführung; operationale Semantik,
Fixpunktsemantik, …
• Sprache
• Syntaktisch falsch: „Ich esse Butterbrot ein“
• Semantisch falsch: „Ich esse einen Schrank“
Ulf Leser: DWH und DM, Sommersemester 2007
66
Semantik von was?
Name
Extension
Realweltliche Objekte
Ulf Leser: DWH und DM, Sommersemester 2007
Intension
repräsentiert
Konzept
67
Synonyme
• Verschiedene Namen für dasselbe Konzept
– Und die selbe „Menge“ von Objekten
DB1:
Angestellter( Id, Vorname, Name,männlich,weiblich)
DB2:
Person( Id, Vorname, Nachname, Geschlecht)
Ulf Leser: DWH und DM, Sommersemester 2007
68
Homonyme
• Gleiche Namen für verschiedene Konzepte
– Treten oft bei Überschreitung von Domänengrenzen auf
Sekr., Sachbearbeiter,
Bereichsleiter, etc.
DB1:
Angestellter( Id, Vorname, Name, m, w, Funktion)
DB2:
Protein( Id, Sequenz, organismus, Funktion, …)
Transport, Katalyse, Signal, …
Ulf Leser: DWH und DM, Sommersemester 2007
69
Probleme
• Mögliche Beziehungen zwischen Mengen A, B realweltlicher
Objekte, die Konzepte c(A), c(B) repräsentieren
• A=B (Äquivalenz): „semantische“ (echte) Synonyme
• Kreditinstitut, Bank (?)
• Gibt es echte Synonyme?
• A⊆B (Inklusion): c(B) ist Hyperonym (Oberbegriff) zu c(A); c(A) ist
Hyponym zu c(B)
• Tochter ⊆ Kind
• A ∩ B ≠ ∅ ∧ A≠B (Überlappung): Schwierigster Fall
• Küche-Kochnische; Haus-Gebäude; Regisseur-Schauspieler
• A ∩ B = ∅ (Disjunktion): nicht verwandte Begriffe (häufigster Fall)
• Dose-Lohnsteuerjahresausgleich
Ulf Leser: DWH und DM, Sommersemester 2007
70
Semantik: Woher nehmen?
• Was bestimmt die Semantik eines Namens?
• Für Attributnamen
•
•
•
•
•
•
•
•
•
•
Datentyp
Constraints (Schlüssel, FK, unique, CHECK, …)
Zugehörigkeit zu einer Relation
Andere Attribute dieser Relation
Beziehung der Relation zu anderen Relationen
Dokumentation
Vorhandene Werte
Wissen über den Anwendungsbereich
…
Der Kontext
Ulf Leser: DWH und DM, Sommersemester 2007
71
Kontext
• Semantik eines Namens ändert sich mit dem Kontext
• Beispiel
•
•
•
•
Unternehmen A: angestellte( …)
Unternehmen B: mitarbeiter( …)
Mitarbeiter und Angestellte kann man als Synonyme betrachten
Aber: A.angestellte ∩ B.mitarbeiter = ∅
• Wenn Personen nicht in zwei Unternehmen beschäftigt sind
• Erst bei einem Merger von A und B werden A.angestellte und
B.mitarbeiter zu Synonymen
• Sollten dann zu einer Tabelle integriert werden
Ulf Leser: DWH und DM, Sommersemester 2007
72
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
•
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
73
Datenkonflikte
• Datenkonflikt
• Zwei Duplikate haben unterschiedliche Attributwerte für ein
semantisch gleiches Attribut
• Datenkonflikte entstehen
• Innerhalb eines Informationssystems (intra-source)
• Bei der Integration mehrerer Informationssysteme (intersource)
• Besser: Konflikte werden durch Integration aufgedeckt
• Voraussetzung
• Existenz von Duplikaten auf Datenebene
• Betrifft nur identifizierbare Objekte
• Keine Duplikate in Brückentabellen etc.
• Duplikate müssen als solche erkannt werden
• Duplikaterkennung
Ulf Leser: DWH und DM, Sommersemester 2007
74
Datenkonflikte - Beispiel
Konflikt kann auch mit
NULL-Wert herrschen
amazon.de
0766607194
H. Melville
$3.98

$5.99
‚‚
ID
0766607194
Herman Melville
Moby Dick
bol.de
Ulf Leser: DWH und DM, Sommersemester 2007
75
Entstehung von Intra-Source-Konflikten
• Innerhalb eines Informationssystems
• Fehlende Integritätsbedingungen oder Konsistenz-Checks
• Freitextfelder, Kommentarfelder
• Intensional redundante Schemata
• Fehlerhafte Normalisierung
• Falsche Einträge
• Tippfehler, Übertragungsfehler, OCR-Fehler , …
• Sich ändernde Werte – Zeitbezug
• Adressen, Einkommen, Preise, …
• …
Ulf Leser: DWH und DM, Sommersemester 2007
76
Entstehung von Inter-Source-Konflikten
• Bei der Integration von Informationssystemen
• Lokal konsistent aber global inkonsistent
• Andere Datentypen („1“ versus „eins“)
• Andere lokale Schreibweisen oder Konventionen
• Skalen (inch – cm) , Währungen, rechtliche Bedingungen (MWST), …
• Übertragungsfehler
• Fehlerhafte Parser beim ETL
• Verwendung unterschiedlicher Standards
• Produktnummern, Produktklassen, Berufsbezeichnungen, …
• Divergierende Aktualisierungszeitpunkte
• …
Ulf Leser: DWH und DM, Sommersemester 2007
77
Auflösen
amazon.de
0766607194
H. Melville
$3.98

$5.99
‚‚
ID
0766607194
bol.de
Herman Melville
Moby Dick
• Konfliktlösungsfunktionen
• Zum Beispiel durch
• Präferenzordnung über Datenquellen
• Aktualität, Trust (Vertrauen),
Verfügbarkeit, usw.
Ulf Leser: DWH und DM, Sommersemester 2007
78
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
-
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
79
Transformation
• Aufgabe
– Umwandlung der Daten in eine „DWH-gerechte“ Form
• Form follows Function
– Quellen: hoher Transaktionsdurchsatz
– DWH: statistische Analysen
• Arten von Transformationen
– Schematransformation
– Datentransformation
• Transformationen möglich an zwei Stellen
– Transformation der Quell-Extrakte zu Load-Files
– Transformation von der Staging-Area in die Basis-DB
Ulf Leser: DWH und DM, Sommersemester 2007
80
Schematransformationen
• Transformation von Daten eines Schemas in ein anderes
Schema
• Unterschiedliche Modellierung
–
–
–
–
–
Unterschiedliche Normalisierung
Was ist Relation, was Attribut, was Wert ?
Aufteilung von Daten in Tabellen
Redundanzen aus Quellsystemen
Schlüssel
¾ In SQL nur teilweise gut unterstützt
– INSERT hat(te) nur eine Zieltabelle
– SQL greift auf Daten zu, nicht auf Schemaelemente
• Keine Überbrückung schematischer Heterogenität
– Erfordert oftmals Programmierung
Ulf Leser: DWH und DM, Sommersemester 2007
81
Beispiele
quelle(id, name, strasse, plz, umsatz)
kunde(id, name, umsatz)
adresse(id, strasse, plz)
Erfordert zwei Durchläufe der
Quelltabelle
- INSERT INTO kunde ... SELECT
- INSERT INTO adresse ... SELECT
Erfordert zwei Durchläufe der
Quelltabelle
premium_k(id, name, umsatz)
normal_k(id, name, umsatz)
Ulf Leser: DWH und DM, Sommersemester 2007
- INSERT INTO premium_k ...
SELECT ...
WHERE umsatz>=X
- INSERT INTO normal_k ...
SELECT ...
WHERE umsatz<XYZ
82
Vermeidung redundanter INSERTs
• Multi-Table INSERT
INSERT ALL
WHEN umsatz < X
THEN INTO normal_k
WHEN umsatz >= X
THEN INTO premium_k
ELSE
INTO normal_k
SELECT id, name, umsatz
FROM quelle;
• Alternative: PL/SQL (JDBC) Programmierung
– Durchlauf der Quelltabelle mit Cursor
– Conditionales Insert in Zieltabelle
– Cursor meistens langsamer
Ulf Leser: DWH und DM, Sommersemester 2007
83
Transformation bei schematischer Heterogenität
tab1( id, name, geschlecht)
tab2( id, name, M, W)
tab31( id, name)
tab32( id, name)
•
tab1 → tab2
–
–
•
INSERT
SELECT
INSERT
SELECT
INTO tab2 (id, name, ‚T‘, ‚F‘)
… geschlecht=‚maennlich‘
INTO tab2 (id, name, ‚F‘, ‚T‘)
… geschlecht=‚weiblich‘
tab3 → tab1
–
–
INSERT INTO tab1(id, name, ‚weiblich‘) ... SELECT ... FROM tab31
INSERT INTO tab1(id, name, ‚maennlich‘) ... SELECT ... FROM tab32
• Alle Werte müssen zum Zeitpunkt der Anfrageformulierung feststehen
• Neues Geschlecht – Alle Queries ändern
Ulf Leser: DWH und DM, Sommersemester 2007
84
Datentransformationen
• Syntax von Werten
– Datum: 20. Januar 2003, 20.01.2003, 1/20/03
– Codierungen: „1: Adr. unbekannt, 2: alte Adresse, 3: gültige
Adresse, 4: Adr. bei Ehepartner, ...“
– Sprache
– Abkürzungen/Schreibweisen: Str., strasse, Straße, ...
– Datentypen: Real, Integer, String
– Genauigkeit, Feldlänge, Nachkommastellen, ...
– Skalen: Noten, Temperatur, Längen, Währungen,...
• Transformation in SQL recht gut unterstützt
– Vielfältige Funktionen im Sprachstandard
– Stringfunktionen, Decodierung, Datumsumwandlung, Formeln,
Systemvariable, ...
– Funktionen in PL/SQL erstellen – in SQL verwenden
Ulf Leser: DWH und DM, Sommersemester 2007
85
Beispiele
„Leser, Ulf“
„Naumann, Felix“
„Leser“,
„Naumann“,
„Ulf“
„Felix“
INSERT INTO kunden( nachname, vorname)
SELECT SubStr(name, 0, inStr(name,',')-1),
SubStr(name, inStr(name,',')+1)
FROM rawdata;
„Leser“, 12/20/1954
„Naumann“, 18/5/1954
„Leser“,
„Naumann“,
20.12.1954
18.05.1954
INSERT INTO kunden( name, geburtsdatum)
SELECT name,
to_date( birthday, ‚MM/DD/YYYY‘)
FROM rawdata;
„Müller“, ‚m‘, „sehr gut“
„Meier“, ‚w‘, „gut“
„Müller“
„Meier“
0
1
1
2
INSERT INTO schueler(name, geschlecht, klasse)
SELECT name,
decode( upper(sex), ‚M‘, 0, 1),
decode( grade, ‚sehr gut‘, 1, ‚gut‘, 2, ...)
FROM rawdata;
Ulf Leser: DWH und DM, Sommersemester 2007
86
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
-
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
87
Datenqualität
• Was ist Datenqualität?
– „Fitness for use“
– Anwendungsabhängig
• Folgen geringer Datenqualität
– Falsche Prognosen
– Verpasstes Geschäft
• DWH besonders anfällig für Qualitätsprobleme
– Probleme akkumulieren
• Qualität der Ursprungsdaten (Eingabe, Fremdfirmen, ...)
• Qualität der Quellsysteme (Konsistenz, Constraints, Fehler, ...)
• Qualität des ETL-Prozess (Parsen, Transformieren, ...)
– Probleme treten erst bei Konsolidierung zu Tage
– DWH unterstützt strategische Entscheidungen: Hohe
Folgekosten bei Fehlentscheidungen
Ulf Leser: DWH und DM, Sommersemester 2007
88
Data Cleansing im DWH
Daten
quellen
Data
Extraction
Data
Transformation
Data
Loading
Data
Warehouse
...
Data
scrubbing
Lookup
tables
Data
cleansing
Ulf Leser: DWH und DM, Sommersemester 2007
Similarity
Functions
Object
Fusion
89
Beispiel: Kampagnenmanagement
• Probleme im CRM eines Multi-Channel Vertriebs
–
–
–
–
Kunden doppelt geführt
Kunden falsch bewertet
Falsche Adressen
Haushalte / Konzernstrukturen nicht erkannt
• Folgen
– „False positives“: Verärgerte Kunden durch mehrere /
unpassende Mailings
– „False negatives“: Verpasste Gelegenheiten durch fehlende /
falsche Zuordnung (Cross-Selling)
– Sinnlose Portokosten bei falschen Adressen
– …
Ulf Leser: DWH und DM, Sommersemester 2007
90
Aspekte von Datenqualität
1. Datenintegrität: Schlüssel, Fremdschlüssel,
Integritätsbedingungen, ...
2. Fehlende Daten: Fehlende Attributwerte, fehlende Tupel
3. Falsche Daten
–
–
Objektiv falsch: Negative Preise, 32.13.2002, Buchstaben statt Ziffern
Widersprüchliche Werte aus unterschiedlichen Quellen
4. Formatfehler
5. Unplausible Daten: Ausreißer, unerwartete Werte, ...
6. Semantik von NULL Werten
–
–
–
Wert möglich, aber unbekannt: Ist er Professor?
Wert möglich, existiert aber nicht: Er ist kein Professor!
Wert unmöglich: Kinder unter 18 haben keinen Titel
7. Duplikate
8. Schlechte / fehlende Dokumentation
9. …
Ulf Leser: DWH und DM, Sommersemester 2007
91
Data Cleansing Operationen
• Ziel: Verbesserung der Datenqualität
–
–
–
–
Ergänzung fehlender Werte
Korrektur durch Lookup, Neuberechnen, Runden, ...
Erkennen und Löschen „unrettbarer“ Daten
Optimum kaum erreichbar: 80/20 Regel
• DQ muss (und kann) gemessen werden
– DQ Metriken notwendig
– Verbesserungen quantifizieren
• Data Cleansing
– Domänenabhängiger Prozess
– Produkte gibt es vor allem für Adressdaten
Ulf Leser: DWH und DM, Sommersemester 2007
92
Data Cleansing Schritte
[KRRT98]
1. Elementizing
•
•
Zerlegung der Werte
Erste Normalform
2. Standardizing
•
Schreibweisen, Vokabulare, Bezeichnungen, Titel
3. Verifying
•
Fehlende Werte, Cross-Matching, Plausibilität
4. Matching
•
Erkennen gleicher Objekte: Kunden, Lieferanten, ...
5. Household – Matching
•
Erkennen von Organisationen: Haushalte, Abteilungen, Firmen
6. Documenting
Ulf Leser: DWH und DM, Sommersemester 2007
93
Nachvollziehbarkeit
• Daten müssen erklärbar sein
–
–
–
–
–
–
Anwender eines DWH: „Da fehlt ein Produkt im Report“
Analysewerkzeug fehlerhaft?
Report falsch?
Data Mart Definition falsch?
Basisdatenbank unvollständig?
ETL Prozeduren fehlerhaft?
• DC Aktionen müssen nachvollziehbar sein
– Protokollieren der Aktionen durch alle Prozessschritte
– Wiederholbarkeit aller Aktionen bei neuen Daten / Anfragen
• Schwierig: Unsystematische ad-hoc Aktionen
• DC programmieren, keine manuellen Änderungen
– Mühsam, aber notwendig zur Unterstützung kontinuierlicher
Prozesse
Ulf Leser: DWH und DM, Sommersemester 2007
94
Inhalt dieser Vorlesung
•
ETL: Extraction, Transformation, Load
-
•
Extraktion von Daten aus Quellen
Das Differential Snapshot Problem
Laden von Daten in das DWH
Schema- und Datenheterogenität
-
•
Datenkonflikte
Datentransformation
Datenqualität
–
–
Data Cleansing
Duplikaterkennung und der Merge/Purge Algorithmus
Ulf Leser: DWH und DM, Sommersemester 2007
95
Duplikate und Fusion
• Viele Fehler findet man erst durch Vergleich
– Quelle 1: Hans Meyer, Frankfurter Allee 200
– Quelle 2: Hans Meier, Frankfurter Alee 202
• Vergleich erfordert Identifikation verschiedener
Repräsentationen desselben Objekts
• Fusion verschmilzt Duplikate zu einem Objekt
– Nicht-redundante Darstellung: Jedes Objekt ist nur einmal im
Ergebnis repräsentiert
– Homogene Darstellung: Jedes (einwertige) Attribut dieses
Objekts hat nur einen Wert
– Datenfusion: Bestimmte diese Werte aus den Attributwerten der
Duplikate
Ulf Leser: DWH und DM, Sommersemester 2007
96
Duplikate
• Relationale Welt
– Duplikat = Zwei Tupel einer Relation die identische Werte in
allen Attributen besitzen
• Allgemein
– Duplikat = Paar von Tupeln, die demselben Realweltobjekt
entsprechen („synonyme“ Objekte)
• Beispiele
– Personen, Rechnungen, Lieferungen, Bestellungen, …
• Viele kommerzielle Tools
– Vor allem im Bereich Customer Relationship Management (CRM)
• Duplikate von „Duplikaterkennung“
– Record Linkage, Object Identification, Deduplication, Entity
Resolution, …
Ulf Leser: DWH und DM, Sommersemester 2007
97
Einspielen neuer Objekte?
• Duplikatvermeidung: Vor dem Einfügen eines neuen
Tupels prüfen, ob das Objekt schon vorhanden ist
– Nehmen wir Namen als Schlüssel an
• Typischer Ablauf
– FOR $new IN new_cust
SELECT count(*) INTO c FROM cust WHERE name=$new;
if (c == 0)
INSERT INTO cust VALUES( $new, …);
else
// Daten ändern
UPDATE cust SET … WHERE name=$new;
END FOR;
• Effizient?
Ulf Leser: DWH und DM, Sommersemester 2007
98
MERGE
• Für jedes UPDATE zwei Suchen nach $new in cust
• Besser: MERGE („Upsert“)
– MERGE INTO cust C
USING (
SELECT *
FROM new_cust) N
ON (C.name = N.name)
WHEN MATCHED THEN
UPDATE SET …
WHEN NOT MATCHED THEN
INSERT VALUES (…);
• Benötigt für jedes existierende N-Tupel nur einen Zugriff
auf cust
– Cursor ist implizit definiert
– Effizient, wenn Duplikaterkennung einfach ist (Schlüssel)
Ulf Leser: DWH und DM, Sommersemester 2007
99
Duplikate: Das formale Problem
• Gegeben: Tupel T={t1,..,tm} mit Attributen A1,…,Ak
– Kein „semantischer“ Schlüssel
– Komplexere Modelle (Bäume, Graphen) möglich
• Tupel entsprechen Objekten O={o1,…,on}
– Über die wissen wir meistens nichts, man kennt nur die Tupel
– Manche der Tupel sind Duplikate
• Gesucht: eine Funktion dup: TxT→bool
– dup gibt true zurück, wenn Tupel t1 und t2 demselben Objekt o
entsprechen
• Eine Möglichkeit: dedup: T → N
– Jedem Tupel wird eine natürliche Zahl zugeordnet
– Zwei Tupel bekommen dann und nur dann dieselbe Zahl
zugeordnet, wenn sie Duplikate sind
– Berechnung eines identifizierenden Schlüssels
Ulf Leser: DWH und DM, Sommersemester 2007
100
Transitivität
• Im strengen Sinne ist die Duplikateigenschaft transitiv
– (dup(t1,t2)=true ∧ dup(t2,t3)=true) → dup(t1,t3)=true
• Im realen Leben muss man damit vorsichtig sein
–
–
–
–
Man nimmt an, dass Duplikate irgendwie ähnlich sind
Ähnlichkeit misst man durch eine Funktion sim: TxT → [0,1]
Verwend. eines Schwellwerts: dupsim(t1,t2)=true gdw. sim(t1,t2)>t
Aber: Ähnlichkeit ist mitnichten transitiv
• Meier, Meyer, Mayer, Bayer, Bayes, Bades, …
• Meier, Meir, Mer, Er, R, …
• Gut überlegen, bevor man in Algorithmen Transitivität ausnutzt
– Hängt ab vom Vertrauen in sim und in t
– Kann Fehlerraten drastisch erhöhen
Ulf Leser: DWH und DM, Sommersemester 2007
101
Genauigkeit der Duplikaterkennung
• Annahme: Verwendung von sim und Threshold t
• Weitere Annahme: sim(t1,t2) korreliert mit der
Wahrscheinlichkeit, dass t1 und t2 Duplikate sind
– sim irrt sich also meistens nicht oder nur ein wenig
• Dann hängt die Genauigkeit von dupsim von t ab
• Hohes t
– Nahezu alle Duplikate, die man findet, sind auch welche
– Aber man findet viele Duplikate nicht, weil sim nicht groß genug
ist
• Niedriges t
– Man findet praktisch alle Duplikate
– Aber man findet auch viele falsche Duplikate
Ulf Leser: DWH und DM, Sommersemester 2007
102
Prinzipielles Vorgehen
• Annahme: Verwendung einer „guten“ sim
• Um alle Duplikate zu finden, muss man alle Paare
miteinander vergleichen
• Beim Vergleich geht man attributweise vor
– Schema Matching muss also schon vorher erfolgt sein
• Ähnlichkeit zweier Tupel setzt sich aus der Ähnlichkeit
ihrer Attribute zusammen
– Ggf mit Gewichtung der Attribute
Ulf Leser: DWH und DM, Sommersemester 2007
103
Vom Attribut zum Tupel
• Theoretisch könnte man die Ähnlichkeit zweier Tupel als
(gewichtetes) Mittel der Ähnlichkeiten ihrer
Attributwerte definieren
– Oder Verwendung von Fuzzy-Logik oder probablistischen
Modellen
• In der Praxis verwendet man eher Domänen- wissen,
das man in Regeln kodiert
Ulf Leser: DWH und DM, Sommersemester 2007
104
Die Sorted Neighborhood Methode
• Input
– Tabelle mit N Tupeln
– Ähnlichkeitsmaß
• Output
– Cluster äquivalenter Tupel (= Duplikate)
• Problem: Viele Tupel
– Es gibt O(n2) viele Tupelpaare
– Das dauert zu lange
– Außerdem: Nur sehr wenige sind tatsächlich Duplikate
• Idee
– Partitioniere Tabelle geschickt
– Tupelpaare werden nur noch innerhalb einer Partition gebildet
Ulf Leser: DWH und DM, Sommersemester 2007
105
Sorted Neighborhood
[HS98]
• Sorted Neighborhood Algorithmus
– Sortiere Tupel so, dass Duplikate (hoffentlich) nahe beieinander liegen
• Dazu: Erzeuge einen Sortierschlüssel aus relevanten Attributen
– Merge-Phase: Fenster der Größe w über sortierte Liste schieben
– Nur Tupel innerhalb eines Fensters vergleichen
• Man hat also überlappende Partitionen
– Purge-Phase: Duplikate werden verschmolzen, gelöscht, getagged, …
• Komplexität
–
–
–
–
n Tupel
Schlüsselerzeugung sei linear
Sortieren ist O(n*log(n))
Anzahl Vergleiche ist O(n*w)
• N „neue“ Tupel werden mit (w-1) Tupeln im Fenster verglichen
– Zum Vergleich: Naiver Ansatz war O(n2)
Ulf Leser: DWH und DM, Sommersemester 2007
106
1. Schlüsselerzeugung
• Schlüsselerzeugung ist zentral für die Effektivität des Verfahrens
– Ordnung der Schlüssel bestimmt die Partitionen
– Schlechte Reihenfolge – schlechter Recall
• Leider ist überhaupt nicht klar, was ein guter Schlüssel zur
Duplikaterkennung ist
– Implizit erfolgt eine Priorisierung der Attribute durch den Aufbau des
Schlüssels
– Wieder: Domänenwissen
Vorname
Nachname
Adresse
ID
Schlüssel
Sal
Stolpho
123 First St.
456780
STOSAL123FRST456
Mauricio
Hernandez
321 Second Ave
123456
HERMAU321SCND123
Felix
Naumann
Hauptstr. 11
987654
NAUFEL11HPTSTR987
Sal
Stolfo
123 First Street
456789
STOSAL123FRST456
Ulf Leser: DWH und DM, Sommersemester 2007
107
Merge
•
Vorname
Nachname
Adresse
ID
Schlüssel
Mauricio
Hernandez
321 Second Ave
123456
HERMAU321SCND123
Felix
Naumann
Hauptstr. 11
987654
NAUFEL11HPTSTR987
Sal
Stolpho
123 First St.
456780
STOSAL123FRST456
Sal
Stolfo
123 First Street
456789
STOSAL123FRST456
Regelbasierte Entscheidung auf Duplikat, z.B.
–
–
IF last_name(r1) = last_name(r2)
AND edit_distance(first_name(r1), firstname(r2)) < 5,
AND address(r1) = address(r2)
THEN dup(r1,r2)=true
IF (ID(r1) = ID(r2) OR last_name(r1) = last_name(r2))
AND address(r1) = address(r2)
AND city(r1) = city(r2)
AND (state(r1) = state(r2) OR zip(r1) = zip(r2))
THEN dup(r1,r2)=true
Ulf Leser: DWH und DM, Sommersemester 2007
108
Praktischer Aufwand
• Komplexität: O(n*log(n)+n*w)
• Praktisch – IO. Mindestens drei Läufe notwendig
– Schlüssel erzeugen und speichern
– Sortieren
– Fenster schieben
• Man hält immer w Tupel im Speicher
• Beim Verschieben fällt ein Tupel raus und eines kommt hinzu
• Das neue Tupel wird mit w-1 Tupeln im Fenster vergleichen
– Praktisch sollte sich w also an der Blockgröße und der Größe des
Hauptspeichers orientieren
• Nicht nur IO: Teure Operationen
– Schlüssel erzeugen – große Schlüssel müssen viele Werte anfassen
– Vergleichen – komplexe Regeln erfordern viele Einzelvergleiche
– Die Merge-Phase dominiert typischerweise die Gesamtkosten
Ulf Leser: DWH und DM, Sommersemester 2007
109
Problem
• Genauigkeit schlecht
– Sortierkriterium bevorzugt immer Attribute
– Sind erste Buchstaben wichtiger für Identität als letzte?
– Ist Nachname wichtiger als Hausnummer?
• Lösung 1: Fenster vergrößern?
– Dominanz eines Attributes bleibt gleich, aber Laufzeit
verschlechtert sich erheblich
– Folge: keine nennenswerte Verbesserung bei kleiner
Vergrößerung und sehr hohe Kosten bei großer Vergrößerung
• Anderer Vorschlag?
Ulf Leser: DWH und DM, Sommersemester 2007
110
Multipass Verfahren
• Lösung 2: Multipass
– Merge-Purge mehrmals mit verschiedenen Schlüsseln laufen
lassen
– Pro Lauf können kleine w und einfache Schlüssel verwendet
werden
– Duplikatbeziehungen aller Läufe sind gleichwertig
– Für k Läufe benötigt man ~(1+2*k) Scans der Tabelle
• Alle Schlüssel in einem Lauf berechnen
– Weniger effizientes, aber deutliches effektiveres Verfahren als
Single-Pass
Ulf Leser: DWH und DM, Sommersemester 2007
111
Bewertung
• Hier nur Merge betrachtet
– Auch Purge nicht trivial!
• Parallelisierung
– Sortierung parallelisierbar
– Window-Vergleiche partitionierbar
• Merge/Purge in der Datenbank
– Sortierungen müssen materialisiert werden
– Gleichheitsregeln in PL/SQL implementieren
Ulf Leser: DWH und DM, Sommersemester 2007
112
Literatur
• [Bae01] Baer: „ETL Processing with Oracle 9i“, Oracle Corp. White
Paper, 2001
• [RD00] Rahm, Do: „Data Cleaning: Problems and Current
Approaches“, IEEE Data Engineering Builletin, 2000
• [LGM96] Labio, W. and Garcia-Molina, H. (1996). "Efficient
Snapshot Differential Algorithms for Data Warehousing". 22nd
VLDB, Bombay, India. pp. 63-74.
• [LN06] Leser, U. and Naumann, F. (2006)
„Informationsintegration“, dpunkt.verlag Heidelberg.
• [HS98] M. Hernandez and S. Stolfo „Real-world data is dirty: Data
cleansing and the merge/purge problem“. Data Mining and
Knowledge Discovery, 2(1): 9-37.
Ulf Leser: DWH und DM, Sommersemester 2007
113