Extraktion, Transformation und Laden

Data-Warehouse-Technologien
Prof. Dr.-Ing. Kai-Uwe Sattler1
Prof. Dr. Gunter Saake2
Dr. Veit Köppen2
1 TU Ilmenau
FG Datenbanken & Informationssysteme
2 Universität Magdeburg
Institut für Technische und Betriebliche Informationssysteme
Letzte Änderung: 16.10.2016
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
0–1
Teil IV
Extraktion, Transformation, Laden
Extraktion, Transformation, Laden
Extraktion, Transformation und Laden
1
ETL-Prozess
2
Extraktion von Daten aus Quellen
3
Laden von Daten
4
Transformationsaufgaben
5
Schematische Heterogenität
6
Datenfehler
7
ELT
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–1
Extraktion, Transformation, Laden
ETL-Prozess
ETL: Überblick
Zwei Schritte
I
Von den Quellen zur Staging Area
F
F
F
I
Von der Staging Area zur Basisdatenbank
F
F
I
I
Extraktion von Daten aus den Quellen
Erstellen / Erkennen von differentiellen Updates
Erstellen von LOAD Files
Data Cleaning und Tagging
Erstellung integrierter Datenbestände
Kontinuierliche Datenversorgung des DWH
Sicherung der DWH Konsistenz bzgl. Datenquellen
Effiziente Methoden essentiell → Sperrzeiten minimieren
Rigorose Prüfungen essentiell → Datenqualität sichern
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–2
Extraktion, Transformation, Laden
ETL-Prozess
ETL-Prozess
Häufig aufwendigster Teil des Data Warehousing
I
I
I
I
Vielzahl von Quellen
Heterogenität
Datenvolumen
Komplexität der Transformation
F
F
I
Schema- und Instanzintegration
Datenbereinigung
Kaum durchgängige Methoden- und Systemunterstützung, jedoch
Vielzahl von Werkzeugen vorhanden
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–3
Extraktion, Transformation, Laden
ETL-Prozess
ETL-Prozess
Extraktion: Selektion eines Ausschnitts der Daten aus den Quellen
und Bereitstellung für Transformation
Transformation: Anpassung der Daten an vorgegebene Schemaund Qualitätsanforderungen
Laden: physisches Einbringen der Daten aus dem
Datenbeschaffungsbereich in das Data Warehouse (einschl.
eventuell notwendiger Aggregationen)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–4
Extraktion, Transformation, Laden
ETL-Prozess
Definitionsphase des ETL-Prozesses
Analysebedarf
OLTP
Legacy
Externe
Quellen
Quelldatenanalyse
Auswahl der
Objekte
Datenmodell und
Konventionen
Erstellen der
Transformation
Abbildung
Schlüsseltransf.
Normalisierung
Dokumentation,
operativer
Datenkatalog
Datenquellen
Regelwerk für
Datenqualität
MetadatenManagement
Transformationsregeln
Erstellen der
ETL-Routinen
ETL-Jobs
DWH
Erfolgskriterien
für Laderoutinen
Repository
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–5
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Extraktion
Aufgabe
I
I
Regelmäßige Extraktion von Änderungsdaten aus Quellen
Datenversorgung des DWH
Unterscheidung
I
I
Zeitpunkt der Extraktion
Art der extrahierten Daten
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–6
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Zeitpunkt
Synchrone Benachrichtigung
I
Quelle propagiert jede Änderung
Asynchrone Benachrichtigung
I
Periodisch
F
F
I
Ereignisgesteuert
F
F
I
Quellen erzeugen regelmäßig Extrakte
DWH fragt regelmäßig Datenbestand ab
DWH erfragt Änderungen vor jedem Jahresabschluss
Quelle informiert alle X Änderungen
Anfragegesteuert
F
DWH erfragt Änderungen vor jedem tatsächlichen Zugriff
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–7
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Art der Daten
Flow: alle Änderungen im DWH integrieren
I
I
Verkaufspositionen, Lieferungen
Änderungen mit aufnehmen
Stock: Zeitpunkt ist essentiell muss festgelegt werden
I
I
Mitarbeiteranzahl zum Monatsende einer Filiale
Lagerbestand zum Jahresende
Value per Unit: Abhängig von Unit und anderen Dimensionen
I
I
Währungskurs zu einem Zeitpunkt
Goldpreis an einem Börsenplatz
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–8
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Art der Daten
Snapshots: Quelle liefert immer kompletten Datenbestand
I
I
I
Neuer Lieferantenkatalog, neue Preisliste, etc.
Änderungen erkennen
Historie korrekt abbilden
Logs: Quelle liefert jede Änderung
I
I
Transaktionslogs, Anwendungsgesteuertes Logging
Änderungen effizient einspielen
Netto-Logs: Quelle liefert Netto-Änderungen
I
I
I
Katalogupdates, Snapshot-Deltas
Keine vollständige Historie möglich
Änderungen effizient einspielbar
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–9
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Zeitpunkt der Datenversorgung
Quelle . . .
Technik
Aktualität
DWH
Belastung
DWH
Belastung
Quellen
erstellt periodisch FiBatchläufe,
Je
nach
Niedrig
Niedrig
Viele
Systeme (Mainframe)
nicht online zugreifbar
les
Snapshots
Frequenz
propagiert jede ÄnTrigger, RepliMaximal
Hoch
Sehr hoch
Widerspricht
DWH-Idee: Mehrbelastung der Quellen
derung
kation
erstellt Exvor BeSehr schwierig
Maximal
Medium
Medium
trakte auf
nutzung
Anfrage
bisher nicht
Anwen- Technisch
AnwendungsJeeffizient
nachdurchführbar
Je
nach
Je
nach
dungsgegesteuert
Frequenz
Frequenz
Frequenz
steuert
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–10
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Extraktion aus Legacy-Systemen
Sehr anwendungsabhängig
Zugriff auf Host-Systeme ohne Online-Zugriff
I
Zugriff über BATCH, Reportwriter, Scheduling
Daten in Non-Standard-Datenbanken ohne APIs
I
Programmierung in PL-1, COBOL, Natural, IMS, . . .
Unklare Semantik, Doppelbelegung von Feldern, sprechende
Schlüssel, fehlende Dokumentation, Herrschaftswissen bei
wenigen
Aber: Kommerzielle Tools vorhanden
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–11
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Differential Snapshot Problem
Viele Quellen liefern immer den vollen Datenbestand
I
I
I
Molekularbiologische Datenbanken
Kundenlisten, Angestelltenlisten
Produktkataloge
Problem
I
I
Ständiges Einspielen aller Daten ineffizient
Duplikate müssen erkannt werden
Algorithmen um Delta-Files zu berechnen
Schwierig bei sehr großen Files
[Labio Garcia-Molina 1996]
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–12
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Szenario
Quellen liefern Snapshots als File F
I
Ungeordnete Menge von Records (K, A1 , . . . , An )
Gegeben: F1 , F2 , mit f1 = |F1 |, f2 = |F2 |
Berechne kleinste Menge O = {INS, DEL, UPD}∗ mit O(F1 ) = F2
O nicht eindeutig!
O1 = {(INS(X)), ∅, (DEL(X))} ≡ O2 = {∅, ∅, ∅}
Differential Snapshot Problem
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–13
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Szenario
F1 K4, t, r, ...
K102, p, q, ...
K104, k, k, ...
K202, a, a, ...
F2 K3, t, r, ...
Differential
Snapshot
Algorithmus
INS K3
DEL K4
INS K103
UPD K202: ...
K102, p, q, ...
K103, t, h, ...
K104, k, k, ...
K202, b, b, ...
DWH
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–14
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Annahmen
Berechnung einer konsekutiven Folge von DS
I
Files am 1.1.2010, 1.2.2010, 1.3.2010, . . .
Kostenmodell
I
I
I
Alle Operationen im Hauptspeicher sind umsonst
IO zählt mit Anzahl Records: sequenzielles Lesen
Keine Beachtung von Blockgrößen
Hauptspeichergröße: M (Records)
Filegrößen |Fx | = fx (Records)
Files i.d.R. größer als Hauptspeicher
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–15
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DSnaive – Nested Loop
Berechnung von O
I
I
Record R aus F1 lesen
F2 sequenziell lesen und mit R vergleichen
F
F
R nicht in F2 → O := O ∪ (DEL(R))
R in F2 → O := O ∪ (UPD(R)) / ignorieren
Problem: INS wird nicht gefunden
I
I
I
Hilfsstruktur notwendig
Array mit IDs aus F2 (on-the-fly generieren)
R jeweils markieren, abschließender Lauf für INS
Anzahl IO: f1 · f2 + δ
Verbesserungen?
I
I
Suche in F2 abbrechen, wenn R gefunden
jeweils Partition mit Größe M von F1 laden:
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
f1
M
· f2
Letzte Änderung: 16.10.2016
4–16
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DSsmall – kleine Files
Annahme: Hauptspeicher M > f1 (oder f2 )
Berechnung von O
I
I
F1 komplett lesen
F2 sequenziell lesen (S)
F
F
F
I
S ∈ F1 : O := O ∪ (UPD(S)) / ignorieren
S 6∈ F1 : O := O ∪ (INS(S))
S in F1 markieren (Bitarray)
Abschließend: Records R ∈ F1 ohne Markierung: O := O ∪ (DEL(R))
Anzahl IO: f1 + f2 + δ
Verbesserungen
I
F1 im Hauptspeicher sortieren
c Sattler / Saake / Köppen
schnellerer Lookup
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–17
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DSsort – Sort-Merge
Allgemeiner Fall: M f1 und M f2
Annahme: F1 ist sortiert
Sortieren auf Sekundärspeicher von F2
I
I
I
I
F2 in Partitionen Pi mit |Pi | = M lesen
Pi im Hauptspeicher sortieren und schreiben in F i („Runs“)
Alle F i mischen p
Annahme: M > |F2 | → IO: 4 · f2
Sortiertes F2 aufheben für nächstes DS (wird dort F1 )
I
Pro DS muss nur F2 sortiert werden
Berechnung von O
I
I
Sortierte F1 und F2 öffnen
Mischen (paralleles Lesen mit Skipping)
Anzahl IO: f1 + 5 · f2 + δ
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–18
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DSsort2 – Verschränkung
Sortiertes F1 vorhanden
Berechnung von O
I
I
I
F2 in Partitionen Pi mit |Pi | = M lesen
Pi im Hauptspeicher sortieren und schreiben in F2i
Alle F2i mischen und gleichzeitig mit F1 vergleichen
Anzahl IO: f1 + 4 · f2 + δ
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–19
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DShash – Partitioned Hash
Berechnung von O
I
I
F2 in Partitionen Pi mit |Pi | = M/2 hashen
Hashfunktion muss garantieren:
Pi ∩ Pj = ∅, ∀i 6= j
I
I
I
I
Partitionen sind „Äquivalenzklassen“ bzgl. der Hashfunktion
F1 liegt noch partitioniert vor
F1 und F2 wurden mit derselben Hashfunktion partitioniert
Jeweils P1,i und P2,i parallel lesen und mischen
Anzahl IO: f1 + 3 · f2 + δ
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–20
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Warum nicht einfach . . .
UNIX diff?
I
I
diff erwartet / beachtet Umgebung der Records
Hier: Records sind völlig ungeordnet
in der Datenbank mit SQL?
I
Dreimaliges Lesen jeder Relation notwendig
INSERT INTO delta
SELECT ’UPD’, ...FROM F1, F2
WHERE F1.K = F2.K AND F1.W <> F2.W
UNION
SELECT ’INS’, ...FROM F2
WHERE NOT EXISTS (...)
UNION
SELECT ’DEL’, ...FROM F1
WHERE NOT EXISTS (...)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–21
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Vergleich – Eigenschaften
DSnaiv
IO
f1 · f2
DSsmall
DSsort2
DShash
f1 + f2
f1 + 4 · f2
f1 + 3 · f2
Bemerkungen
außer Konkurrenz, extra Datenstruktur
notwendig
nur für kleine Dateien
überlappungsfreie Hashfunktion, Partitionsgröße schwierig zu schätzen, Verteilungsannahmen (Sampling)
Erweiterung von DShash für „schlechtere“ Hashfunktionen bekannt
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–22
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
Weitere DS Verfahren
Anzahl Partitionen / Runs größer als File-Handles des OS
I
Hierarchische externe Sortierverfahren
Kompression: Files komprimieren
I
I
I
Größere Partitionen / Runs
Größere Chance, Vergleich im Hauptspeicher durchzuführen
In Realität schneller (Annahmen des Kostenmodells)
„Windows“ Algorithmus
I
I
I
I
Annahme: Files haben eine „unscharfe“ Ordnung
Mischen mit Sliding Window über beide Files
Liefert u.U. redundante INS-DEL Paare
Anzahl IO: f1 + f2
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–23
Extraktion, Transformation, Laden
Extraktion von Daten aus Quellen
DS mit Zeitstempel
Annahme: Records sind (K, A1 , . . . , An , T)
T: Zeitstempel der letzten Änderung
Erstellen von O
I
I
I
I
Festhalten von Talt : Letztes Update (max{T} von F1 )
F2 sequenziell lesen
Entries mit T > Talt interessant
Aber: INS oder UPD?
Weiteres Problem: DEL wird nicht gefunden
Zeitstempel erspart nur Attributvergleich
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–24
Extraktion, Transformation, Laden
Laden von Daten
Laden
Aufgabe
I
Effizientes Einbringen von externen Daten in DWH
Kritischer Punkt
I
Ladevorgänge blockieren unter Umständen das komplette DWH
(Schreibsperre auf Faktentabelle)
Aspekte
I
I
I
I
Trigger
Integritätsbedingungen
Indexaktualisierung
Update oder Insert?
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–25
Extraktion, Transformation, Laden
Laden von Daten
Satzbasiert
Benutzung von Standard-Schnittstellen:
PRO*SQL, JDBC, ODBC, . . .
Arbeitet im normalen Transaktionskontext
Trigger, Indexe und Constraints bleiben aktiv
I
Manuelle Deaktivierung möglich
Keine großräumigen Sperren
Sperren können durch COMMIT verringert werden
I
Nicht bei Oracle: Leseoperationen werden nie gesperrt (MVCC)
Benutzung von Prepared Statements
Teilweise proprietäre Erweiterungen (Arrays) verfügbar
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–26
Extraktion, Transformation, Laden
Laden von Daten
BULK Load
DB-spezifische Erweiterungen zum Laden großer Datenmengen
Läuft (meist) in speziellem Kontext
I
I
I
I
I
I
I
Oracle: DIRECTPATH option im Loader
Komplette Tabellensperre
Keine Beachtung von Triggern oder Constraints
Indexe werden erst nach Abschluss aktualisiert
Kein transaktionaler Kontext
Kein Logging
Checkpoints zum Wiederaufsetzen
Praxis: BULK Uploads
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–27
Extraktion, Transformation, Laden
Laden von Daten
Beispiel: ORACLE sqlldr
LoaderKontrollDatei
Input
InputDatafiles
Dateien
LogDatei
Input
Schlechte
Datafiles
Dateien
SQL*Loader
Input
Abgelehnte
Datafiles
Dateien
Datenbank
Indexe
Tabellen
[Oracle 11g Dokumentation]
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–28
Extraktion, Transformation, Laden
Laden von Daten
Beispiel: ORACLE sqlldr (2)
Control-File
LOAD DATA
INFILE ’bier.dat’
REPLACE INTO TABLE getraenke (
bier_name POSITION(1) CHAR(35),
bier_preis POSITION(37) ZONED(4,2),
bier_bestellgroesse POSITION(42) INTEGER,
getraenk_id "getraenke_seq.nextval"
)
Datenfile: bier.dat
Ilmenauer Pils
Erfurter Bock
Magdeburger Weisse
Anhaltinisch Flüssig
c Sattler / Saake / Köppen
4490
6400
1290
8800
Data-Warehouse-Technologien
100
80
20
200
Letzte Änderung: 16.10.2016
4–29
Extraktion, Transformation, Laden
Laden von Daten
BULK Load Beispiel
Vielfältige Optionen
I
I
I
I
I
I
I
I
I
Behandlung von Ausnahmen (Badfile)
Datentransformationen
Checkpoints
Optionale Felder
Konditionales Laden in mehrere Tabellen
Konditionales Laden von Records
REPLACE oder APPEND
Paralleles Laden
...
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–30
Extraktion, Transformation, Laden
Laden von Daten
Direct Path Load
SQL*Loader
SQL*Loader
Schreibe
Datenbank-Block
Generiere SQLKommandos
Direkter
Pfad
Benutzerprozesse
Generiere SQLGeneriere SQLGeneriere SQLKommandos
Kommandos
Kommandos
Konventioneller
Pfad
Oracle Server
SQL-Kommando Verarbeitung
Speichermanagement
Hole neue Ausmaße
Finde partielle Blöcke
Passe Füllstand an
Befülle partielle Blöcke
Puffer Cache Management
- Manage Queues
- Löse Konflikte auf
Datenbank-Blöcke
lesen
Puffer-Cache
Datenbank-Blöcke
schreiben
Datenbank
[Oracle 11g Dokumentation]
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–31
Extraktion, Transformation, Laden
Laden von Daten
Multi-Table-Insert in Oracle
Einfügen in mehrere Tabellen bzw. mehrfach (z.B. für Pivoting)
INSERT ALL
INTO Quartal_Verkauf
VALUES (Produkt_Nr,
INTO Quartal_Verkauf
VALUES (Produkt_Nr,
INTO Quartal_Verkauf
VALUES (Produkt_Nr,
INTO Quartal_Verkauf
VALUES (Produkt_Nr,
SELECT ... FROM ...
c Sattler / Saake / Köppen
Jahr || ’/Q1’, Umsatz_Q1)
Jahr || ’/Q2’, Umsatz_Q2)
Jahr || ’/Q3’, Umsatz_Q3)
Jahr || ’/Q4’, Umsatz_Q4)
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–32
Extraktion, Transformation, Laden
Laden von Daten
Multi-Table-Insert in Oracle (2)
Bedingtes Einfügen
INSERT ALL
WHEN ProdNr IN
(SELECT ProdNr FROM Werbe_Aktionen)
INTO Aktions_Verkauf
VALUES (ProdNr, Quartal, Umsatz)
WHEN Umsatz > 1000
INTO Top_Produkte VALUES (ProdNr)
SELECT ... FROM ...
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–33
Extraktion, Transformation, Laden
Laden von Daten
Merge in Oracle
Merge: Versuch eines Inserts, bei Fehler (durch Verletzung einer
Schlüsselbedingung) → Update
MERGE INTO Kunden K USING Neukunden N
ON (N.Name = K.Name AND N.GebDatum = K.GebDatum)
WHEN MATCHED THEN
UPDATE SET K.Name = N.Name, K.Vorname=N.Vorname,
K.GebDatum=N.GebDatum
WHEN NOT MATCHED THEN
INSERT VALUES (MySeq.NextVal, N.Name,
N.Vorname, N.GebDatum)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–34
Extraktion, Transformation, Laden
Laden von Daten
Der ETL-Prozess: Transformationsaufgaben
Einsatz
-fähige
Quellen
DataWarehouse
Extraktion, Transformation, Laden
Extraktion
Integration
Aggregation
DataWarehouse
1
2
3
5
4
Zwischenspeicher
Instanzextraktion
und Transformation
Instanzabgleich
und Integration
Filterung,
Aggregation
Scheduling, Logging, Monitoring, Recovery, Backup
Legende:
1
3
Instanz-Charakteristika
(reale Meta-Daten)
4
Abbildungen von Quell- auf
Zielschemata
2
Translationsregeln
5
Filterungs- und Aggregationsregeln
Meta-Datenfluss
Datenfluss
[Rahm Do 2000]
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–35
Extraktion, Transformation, Laden
Laden von Daten
Technik: Quelle – Datenbereinigungsbereich –
BasisDB
Quelle 1:
RDBMS
Quelle 2:
IMS
Rel. Schema
Q1
Rel. Schema
Q2
Datenwürfel,
Integriertes
Schema
BULK Load meist nur für initiale Beladung
Folgende Beladungen
I
I
I
INSERT INTO ...SELECT ...
Logging ausschaltbar
Parallelisierbar
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–36
Extraktion, Transformation, Laden
Laden von Daten
Transformationsaufgaben
Bei der Extraktion
I
I
I
Einfache Konvertierungen (für LOAD - File)
Satzorientierung (Tupel)
Vorbereitung für BULK Loader –> meist Scripte oder 3GL
Im Datenbeschaffungsbereich
I
I
I
I
I
Mengenorientierte Berechnungen
Inter- und Intra-Relationenvergleich
Vergleich mit Basisdatenbank → Duplikate
Tagging der Datensätze
SQL
Laden in die BasisDB
I
I
Bulk-Load
satzorientierte Inserts mit ausgeschaltetem Logging
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–37
Extraktion, Transformation, Laden
Laden von Daten
Aufgabe: Quelle – Datenbereinigungsbereich –
BasisDB
Was macht man wo und wann?
I
Keine definierte Aufgabenteilung vorhanden
Art des Zugriffs
Verfügbare Datenbasen
Verfügbare Datensätze
Programmiersprache
c Sattler / Saake / Köppen
Extraktion
Laden
Quelle → Datenbereitstellungsbereich
Datenbereitstellungsbereich
→ Basis-DB
Satzorientiert
Eine Quelle (Updatefile)
Quellabhängig: Alle, alle Änderungen, Deltas
Skripte: Perl, AWK, . . . oder
3GL
Mengenorientiert
Viele Quellen
Zusätzlich Basis-DB verfügbar
Data-Warehouse-Technologien
SQL, PL/SQL
Letzte Änderung: 16.10.2016
4–38
Extraktion, Transformation, Laden
Transformationsaufgaben
Transformation
Problem
I
I
Daten im Datenbereinigungsbereich nicht im Format der
Basisdatenbank
Struktur der Daten unterschiedlich
F
F
F
Datenbereinigungsbereich: Quellnahes Schema
Basis-DB: Multidimensionales Schema
Strukturelle Heterogenität
Aspekte
I
I
Datentransformation
Schematransformation
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–39
Extraktion, Transformation, Laden
Transformationsaufgaben
Daten- und Schemaheterogenität
Hauptdatenquelle: OLTP-Systeme
Sekundärquellen:
I
I
Dokumente in firmeninternen Altarchiven
Dokumente im Internet via WWW, FTP
F
F
Unstrukturiert: Zugriff über Suchmaschinen, . . .
Semistrukturiert: Zugriff über Suchmaschinen, Mediatoren, Wrapper
als XML-Dokumente o.ä.
Grundproblem: Heterogenität der Quellen
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–40
Extraktion, Transformation, Laden
Transformationsaufgaben
Aspekte der Heterogenität
Verschiedene Datenmodelle
I
I
I
Bedingt durch autonome Entscheidung über Anschaffung von
Systemen in den Unternehmensbereichen
Verschiedene und verschieden mächtige Modellierungskonstrukte,
D.h. Anwendungssemantik in unterschiedlichem Ausmaß erfassbar
Abbildung zwischen Datenmodellen nicht eindeutig
Beispiel: Relationenmodell vs. objektorientierte Modellierung vs.
XML
Vorname
Name
PLZ
Kunde
c Sattler / Saake / Köppen
Kunde
Name
Vorname
PLZ
...
Data-Warehouse-Technologien
Kunde
Name
PLZ
Vorname
Letzte Änderung: 16.10.2016
4–41
Extraktion, Transformation, Laden
Transformationsaufgaben
Aspekte der Heterogenität (2)
Unterschiedliche Modellierungen für gleiche Sachverhalte der
Realwelt
I
I
Bedingt durch Entwurfautonomie
Selbst im gleichen Datenmodell verschiedene Modellierungen
möglich, z.B. durch unterschiedliche Modellierungsperspektiven der
DB-Designer
Kunde
Name
Vorname
...
Kunde
Name
Vorname
Geschlecht
...
Mann
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Frau
Letzte Änderung: 16.10.2016
4–42
Extraktion, Transformation, Laden
Transformationsaufgaben
Aspekte der Heterogenität (3)
Unterschiedliche Repräsentation der Daten
I
I
I
I
Unterschiedliche Datentypen möglich
Unterschiedliche Umfang der unterstützten Datentypen
Unterschiedliche interne Darstellung der Daten
Auch unterschiedliche „Werte“ eines Datentyps zur Repräsentation
derselben Information
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–43
Extraktion, Transformation, Laden
Transformationsaufgaben
Datenfehler-Klassifikation
Datenfehler
Einzelne Datenquellen
Integrierte Datenquellen
Schemaebene
Datenebene
Schemaebene
Datenebene
Fehlende
Integritätsbedingungen,
schlechtes Schema
Design
Fehler in
Datenträgern
Heterogene
Datenmodelle und
-schemata
Überlappende,
widersprüchliche und
inkonsistente Daten
- Unzulässiger Wert
- Attributabhängigkeit
verletzt
- Eindeutigkeit verletzt
- Referenzielle Integrität
verletzt
-
Fehlende Werte
Schreibfehler
Falsche Werte
Falsche Referenz
Kryptische Werte
Eingebettete Werte
Falsche Zuordnung
Widersprüchliche Werte
Transpositionen
Duplikate
Datenkonflikte
- Strukturelle
Heterogenität
- Semantische
Heterogenität
- Schematische
Heterogenität
- Widersprüchliche Werte
- Unterschiedliche
Repräsentationen
- Unterschiedliche
Genauigkeit
- Unterschiedliche
Aggregationsebenen
-Duplikate
[Rahm Do 2000, Leser Naumann 2007]
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–44
Extraktion, Transformation, Laden
Schematische Heterogenität
Schematische Heterogenität
Ursache: Entwurfsautonomie
I
I
I
I
I
unterschiedliche Modellierung
Unterschiedliche Normalisierung
Was ist Relation, was Attribut, was Wert?
Aufteilung von Daten in Tabellen
Redundanzen aus Quellsystemen
Schlüssel
In SQL nicht gut unterstützt
I
I
I
INSERT hat nur eine Zieltabelle
SQL greift auf Daten zu, nicht auf Schemaelemente
Erfordert meist Programmierung
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–45
Extraktion, Transformation, Laden
Schematische Heterogenität
Schema Mapping
Datentransformation zwischen heterogenen Schemata
I
I
I
Altes aber immer wiederkehrendes Problem
Üblicherweise schreiben Experten komplexe Anfragen oder
Programme
Zeitintensiv
F
F
Experte für die Domäne, für Schemata und für Anfrage
XML macht alles noch schwieriger: XML Schema, XQuery
Idee: Automatisierung
I
I
Gegeben: Zwei Schemata und ein high-level Mapping dazwischen
Gesucht: Anfrage zur Datentransformation
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–46
Extraktion, Transformation, Laden
Schematische Heterogenität
Warum ist Schema Mapping schwierig?
Generierung der „richtigen“ Anfrage unter Berücksichtigung
I
I
I
des Quell und Ziel-Schemas,
des Mappings
und der Nutzer-Intention: Semantik!
Garantie, dass die transformierten Daten dem Zielschema
entsprechen
I
I
Flach oder geschachtelt
Integritätsbedingungen
Effiziente Datentransformation
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–47
Extraktion, Transformation, Laden
Schematische Heterogenität
Schema Mapping: Normalisiert vs. Denormalisiert
1:1-Assoziationen werden unterschiedlich dargestellt
I
I
Durch Vorkommen im gleichen Tupel
Durch Fremdschlüsselbeziehung
Bier
bID
name
alkoholgehalt
Produkt
pID
name
hersteller
produktsorte
Produktsorte
pFK
bezeichnung
SELECT bID AS pID, name, NULL AS hersteller,
NULL AS produktsorte FROM Bier
UNION
SELECT NULL AS pID, NULL AS name, NULL AS hersteller,
bezeichnung AS produktsorte FROM Produktsorte
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–48
Extraktion, Transformation, Laden
Schematische Heterogenität
Schema Mapping: Normalisiert vs. Denormalisiert (2)
Bier
bID
name
alkoholgehalt
Produkt
pID
name
hersteller
produktsorte
Produktsorte
pFK
bezeichnung
SELECT bID AS pID, name, NULL AS hersteller,
bezeichnung AS produktsorte
FROM Bier, Produktsorte
WHERE bID = pFK
Nur eine von vier möglichen Interpretationen!
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–49
Extraktion, Transformation, Laden
Schematische Heterogenität
Schema Mapping: Normalisiert vs. Denormalisiert (3)
Produkt
name
hersteller
produktsorte
Bier
bID
name
alkoholgehalt
Produktsorte
pFK
bezeichnung
Erfordert Generierung von Schlüsseln: Skolemfunktion SK, die
einen bzgl. der Eingabe eindeutigen Wert liefert (z.B.
Konkatenation aller Werte)
Bier
:= SELECT SK(name) AS bID, name,
NULL AS alkoholgehalt FROM Produkt
Produktsorte := SELECT SK(name) AS pFK,
produktsorte AS bezeichnung FROM Produkt
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–50
Extraktion, Transformation, Laden
Schematische Heterogenität
Schema Mapping: Geschachtelt vs. Flach
1:1-Assoziationen werden unterschiedlich dargestellt
I
I
D.h. geschachtelte Elemente
Durch Fremdschlüsselbeziehung
Bier
bID
name
alkoholgehalt
Produkt
pID
name
produktsorte
Produkt
name
hersteller
produktsorte
Produktsorte
bezeichnung
c Sattler / Saake / Köppen
Bier
name
Produktsorte
bezeichnung
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–51
Extraktion, Transformation, Laden
Schematische Heterogenität
Schwierigkeiten
Beispiel: Quelle(ID, Name, Strasse, PLZ, Umsatz)
Zielschema #1
Kunde(ID, Name, Umsatz)
Adresse(ID, Strasse, PLZ)
Erfordert 2 Durchläufe der Quelltabelle
INSERT INTO Kunde ... SELECT ...
INSERT INTO Adresse ... SELECT ...
I
Zielschema #2
PremKunde(ID, Name, Umsatz)
NormKunde(ID, Name, Umsatz)
Erfordert 2 Durchläufe der Quelltabelle
INSERT INTO PremKunde ... SELECT ... WHERE Umsatz>=X
INSERT INTO NormKunde ... SELECT ... WHERE Umsatz<X
I
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–52
Extraktion, Transformation, Laden
Schematische Heterogenität
Schwierigkeiten (2)
Schema
P1(Id, Name, Geschlecht)
P2(Id, Name, M, W)
P31(Id, Name), P32(Id, Name)
P1 → P2
INSERT INTO P2 (id, name, ’T’, ’F’) ... SELECT ...
INSERT INTO P2 (id, name, ’F’, ’T’) ... SELECT ...
P3 → P1
INSERT INTO P1(id,
SELECT ... FROM
INSERT INTO P1(id,
SELECT ... FROM
name, ’weiblich’) ...
P31
name, ’männlich’) ...
P32
Anzahl Werte muss feststehen; Neues Geschlecht – Alle Anfragen
ändern
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–53
Extraktion, Transformation, Laden
Datenfehler
Datenfehler
Eindeutigkeit
verletzt
Person
Ort
Unterschiedliche
Repräsentation
KNr
34
34
35
Name
Meier, Tom
Tina Möller
Tom Meier
PLZ
39107
Widersprüchliche
Werte
Geb.datum
21.01.1980
18.04.78
32.05.1969
Alter
35
29
27
Geschl.
M
W
F
Ort
Magdeburg
36996
Spanien
95555
Illmenau
Referentielle
Integrität verletzt
Telefon
999-999
763-222
222-231
PLZ
39107
36999
39107
unvollständig
Email
null
null
[email protected]
Duplikate
Fehlende Werte
(z.B. Default-Werte)
Falsche oder
unzulässige Werte
Schreib- oder
Tippfehler
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–54
Extraktion, Transformation, Laden
Datenfehler
Vermeidung von Datenfehlern
Vermeidung von
falschen Datentypen
falschen Werte
fehlenden Werten
ungültigen Referenzen
Duplikaten
Inkonsistenzen
veralteten Daten
durch
Datentypdefinition,
domain-Constraints
check
not null
foreign key
unique, primary key
Transaktionen
Replikation, materialisierte Sichten
Dennoch in der Praxis:
I
I
I
I
Fehlen von Metadaten, Integritätsbedingungen, . . .
Eingabefehler, Unkenntnis, . . .
Heterogenität
...
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–55
Extraktion, Transformation, Laden
Datenfehler
Phasen der Datenaufbereitung
Dimensionsreduktion /
Sampling
Nutzung
Sammlung/
Auswahl
Aggregation /
FeatureExtraktion
Duplikaterkennung und
Merging
DQ-Probleme
identifizieren/
quantifizieren
Fehlerarten/
-ursachen
erkennen
Fehlerkorrektur
Data Profiling
Transformation
Diskretisierung
Standardisierung/
Normalisierung
Data Cleaning
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–56
Extraktion, Transformation, Laden
Datenfehler
Data Profiling
Analyse von Inhalt und Struktur einzelner Attribute
I
Datentyp, Wertebereich, Verteilung und Varianz, Vorkommen von
Nullwerten, Eindeutigkeit, Muster (z.B. dd/mm/yyyy)
Analyse von Abhängigkeiten zwischen Attributen einer Relation
I
I
I
„unscharfe“ Schlüssel
Funktionale Abhängigkeiten, potenzielle Primärschlüssel,
„unscharfe“ Abhängigkeiten
Notwendigkeit:
F
F
Keine expliziten Integritätsbedingungen spezifiziert
Jedoch in Daten in den meisten Fällen erfüllt
Analyse von Überlappungen zwischen Attributen verschiedener
Relationen
I
Redundanzen, Fremdschlüsselbeziehungen
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–57
Extraktion, Transformation, Laden
Datenfehler
Data Profiling (2)
Fehlende bzw. falsche Werte
I
I
Ermittelte vs. erwartete Kardinalität (z.B. Anzahl von Filialen,
Geschlecht von Kunden)
Anzahl der Nullwerte, Minimum / Maximum, Varianz
Daten- bzw. Eingabefehler
I
I
Sortierung und manuelle Prüfung
Ähnlichkeitstests
Duplikate
I
Tupelanzahl vs. Attributkardinalität
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–58
Extraktion, Transformation, Laden
Datenfehler
Data Profiling mit SQL
SQL-Anfragen für einfache Profiling-Aufgaben
Schema, Datentypen: Anfragen an Schemakatalog
Wertebereich
select min(A), max(A), count(distinct A)
from Tabelle
I
I
I
Datenfehler, Defaultwerte
select Ort, count(*) as Anz
from Kunden group by Ort order by Anz
F
F
Aufsteigend: Eingabefehler, z.B. Illmenau: 1, Ilmenau: 50
Absteigend: undokumentierte Default-Werte, z.B. AAA: 80
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–59
Extraktion, Transformation, Laden
Datenfehler
Data Cleaning
Erkennen & Beseitigen von Inkonsistenzen, Widersprüchen und
Fehlern in Daten mit dem Ziel der Qualitätsverbesserung
Auch Cleansing oder Scrubbing
Bis zu 80% des Aufwandes in DW-Projekten
Cleaning im DW: Teil des ETL-Prozesses
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–60
Extraktion, Transformation, Laden
Datenfehler
Re
ge
lba
sie
r
hu
ng
sa
na
l ys
e
zie
Be
sa
na
l ys
e
Konsistenz
Min, Max, Mittel,
Median, Standardabweichung, ...
Schlüsseleindeutigkeit
Redundanzfreiheit
Normalisierungsgrad
(1.,2. und 3. NF),
Duplikatprüfung
e in
Eindeutigkeit der
Primär- bzw.
Kandidatenschlüssel
it
ke
ltig
Gü
Datentyp-,
Feldlängen- und
Wertebereichskonsistenzen
te
Integritätsverletzungen,
Waisen (Orphans),
Kardinalitäten
Korrektheit
mittels statistischer Kontrolle
er
W
Referenzielle
Integrität
er
er
hr
Ab
hä
ng
igk
eit
Geschäfts- und
Datenregeln (Defekte)
me
ze
Einheitlichkeit
Formatanalyse (für
numerische Attribute,
Zeiteinheiten und
Zeichenketten)
Sp
alt
e
Vollständigkeit
Füllgradanalyse der
Entitäten und Attribute
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
te
Genauigkeit
Analyse der Stelligkeiten
(Gesamt- und Nachkommastellen für
numerische Attribute)
er
rW
Eindeutigkeit
Analyse der Metadaten
lne
na
na
l ys
e
Konsistenz
mittels regelbasierter Analyse
it
ke
ltig
Gü
te
An
al
ys
e
Datenqualität und Datenbereinigung
Letzte Änderung: 16.10.2016
4–61
Extraktion, Transformation, Laden
Datenfehler
Normalisierung und Standardisierung
Datentypkonvertierung: varchar → int
Kodierungen: 1: Adresse unbekannt, 2: alte Adresse, 3: gültige
Adresse, 4: Adresse bei Ehepartner, . . .
Normalisierung: Abbildung in einheitliches Format
I
I
I
Datum: 03/01/11 → 01. März 2011
Währung: $ → e
Zeichenketten in Großbuchstaben
Zerlegung in Token: “Saake, Gunter” → “Saake”, “Gunter”
Diskretisierung numerischer Werte
Domänenspezifische Transformationen
I
I
I
I
Codd, Edgar Frank → Edgar Frank Codd
Str. → Straße
Adressen über Adressdatenbanken
Branchenspezifische Produktbezeichnungen
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–62
Extraktion, Transformation, Laden
Datenfehler
Datentransformation
In SQL gut unterstützt
I
I
I
Vielfältige Funktionen im Sprachstandard
Stringfunktionen, Decodierung, Datumsumwandlung, Formeln,
Systemvariable, . . .
Funktionen in PL/SQL erstellen - in SQL verwenden
Daten
"Pause, Lilo"
"Prehn, Leo"
SQL
⇒
⇒
"Pause", "Lilo"
"Prehn", "Leo"
INSERT INTO kunden (nachname, vorname)
SELECT SubStr(name, 0, inStr(name,’,’)-1),
SubStr(name, inStr(name,’,’)+1)
FROM rawdata;
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–63
Extraktion, Transformation, Laden
Datenfehler
Duplikaterkennung
Identifikation von semantisch äquivalenten Datensätzen, d.h. die
das gleiche Realwelt-Objekt repräsentieren
Auch: Record Linkage, Object Identification, Duplicate Elimination,
Merge/Purge
I
I
Merge: Erkennen von Duplikaten
Purge: Auswahl /Berechnung des „besten“ Vertreters pro Klasse
KundenNr
3346
3346
5252
5268
⊥
⊥
c Sattler / Saake / Köppen
Name
Just Vorfan
Justin Forfun
Lilo Pause
Lisa Pause
Ann Joy
Anne Scheu
Adresse
Hafenstraße 12
Hafenstr. 12
Kuhweg 42
Kuhweg 42
Domplatz 2a
Domplatz 28
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–64
Extraktion, Transformation, Laden
Datenfehler
Duplikaterkennung: Vergleiche
Typische Vergleichsregeln
if ssn1 = ssn2 then match
else if name1=name2 then
if firstname1=firstname2 then
if adr1=adr2 then match
else unmatch
else if adr1=adr2 then match_household
else if adr1=adr2 then
...
Naiver Ansatz: „Jeder-gegen-jeden“
I
I
I
O(n2 ) Vergleiche
Maximale Genauigkeit (je nach Regeln)
Viel zu teuer
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–65
Extraktion, Transformation, Laden
Datenfehler
Duplikaterkennung: Prinzip
R
Matches (M)
Partitionierung
des
Suchraums
r1,s1
r2,s2
r1, r2, r3, ...
r3,s3
RxS
S
s , s , s , ...
1 2 3
...
Vergleichsfunktion
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Non Matches
(U)
Letzte Änderung: 16.10.2016
4–66
Extraktion, Transformation, Laden
Datenfehler
Partitionierung
Blocking
I
I
Aufteilung des Suchraums in disjunkte Blöcke
Duplikate nur innerhalb eines Blockes
Sortierte Nachbarschaft
I
I
[Hernandez Stolfo 1998]
Sortierung der Daten anhand eines gewählten Schlüssels
Vergleiche in einem gleitenden Fenster
Multi-Pass-Technik
I
Transitive Hülle über verschiedene Sortierungen
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–67
Extraktion, Transformation, Laden
Datenfehler
Sortierte Nachbarschaft
1
Berechne einen Schlüssel pro
Datensatz
I
I
Bsp: SSN + „ersten 3 Zeichen von
Name“ + ...
Beachtung typischer Fehler: 0-O,
Soundex, Nachbartasten, ...
2
Sortiere nach Schlüssel
3
Laufe Liste sequenziell ab
Vergleiche innerhalb eines Fensters W,
|W| = w
4
I
w
w
Mit welchen Tupeln muss wirklich
verglichen werden?
Komplexität
I
I
Schlüsselerzeugung: O(n), Sortieren: O(n · log(n)); Vergleichen:
O((n/w) · (w2 )) = O(n · w);
Gesamt: O(n · log(n)) oder O(n · w)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–68
Extraktion, Transformation, Laden
Datenfehler
Sortierte Nachbarschaft: Probleme
Genauigkeit schlecht
I
I
I
Sortierkriterium bevorzugt immer Attribute
Sind erste Buchstaben wichtiger für Identität als letzte?
Ist Nachname wichtiger als Hausnummer ?
Window vergrößern?
I
I
Keine Hilfe
Dominanz eines Attributes bleibt gleich, aber Laufzeit
verschlechtert sich schnell
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–69
Extraktion, Transformation, Laden
Datenfehler
Multi-Pass-Technik
Sortieren nach mehreren Kriterien und Identifikation von
Duplikaten
Bildung der transitiven Hülle der Duplikate bis zu gegebener
Länge
B
A
C
1. Lauf: „A matches B“
2. Lauf: „B matches C“
B
A
Transitivität: „A matches
C“
C
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–70
Extraktion, Transformation, Laden
Datenfehler
Vergleichsfunktionen
Vergleichsfunktionen für Felder (String A und B), u.a.:
I
I
I
Editierdistanz: Anzahl der Editieroperationen (Einfügen, Löschen,
Ändern) für Änderung von A in B
q-Grams: Vergleich der Mengen aller Teilstrings von A und B der
Länge q
Jaro-Distanz und Jaro-Winkler-Distanz: Berücksichtigung von
gemeinsamen Zeichen (innerhalb der halben Stringlänge) und
transponierten Zeichen (an anderer Position)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–71
Extraktion, Transformation, Laden
Datenfehler
Edit-Distanz
Levensthein-Distanz:
I
I
Anzahl der Editieroperationen (Einfügen, Löschen, Ändern) für
Änderung von A in B
Beispiel:
edit_distance(“Qualität”, “Quantität”) = 2
⇒ update(3,’n’)
⇒ insert(4,’t’)
I
Anwendung:
select P1.Name, P2.Name
from Produkt P1, Produkt P2
where edit_distance(P1.Name, P2.Name) <= 2
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–72
Extraktion, Transformation, Laden
Datenfehler
q-Gramme
Menge aller Substrings der Länge q
Qualität3 := { __Q, _Qu, Qua, ual, ali, lit, itä, tät, ät_, t__ }
Beobachtung: Strings mit kleiner Edit-Distanz haben viele
gemeinsame q-Gramme, d.h. für Edit-Distanz = k mind.
max(|A|, |B|) − 1 − (k − 1) · q
gemeinsame q-Gramme
Positionale q-Gramme: Ergänzung um Position im String
Qualität := { (-1, __Q), (0, _Qu), (1, Qua), ... }
I
Filterung für effizienten Vergleich:
F
F
F
COUNT: Anzahl der gemeinsamen q-Gramme
POSITION: Positionsunterschied zwischen korrespondierenden
q-Grammen ≤ k
LENGTH: Differenz der Stringlängen ≤ k
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–73
Extraktion, Transformation, Laden
Datenfehler
Datenkonflikte
Datenkonflikt: Zwei Duplikate haben unterschiedliche
Attributwerte für semantisch gleiches Attribut
I
Im Gegensatz zu Konflikten mit Integritätsbedingungen
Datenkonflikte entstehen
I
I
Innerhalb eines Informationssystems (intra-source) und
Bei der Integration mehrerer Informationssysteme (inter-source)
Voraussetzung: Duplikat, d.h. Identität schon festgestellt
Erfordert: Konfliktauflösung (Purging, Reconciliation)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–74
Extraktion, Transformation, Laden
Datenfehler
Datenkonflikte: Entstehung
Mangels Integritätsbedingungen oder Konsistenz-Checks
Bei redundanten Schemata
Durch partielle Informationen
Bei Entstehung von Duplikaten
Nicht korrekte Einträge
I
I
Tippfehler, Übertragungsfehler
Falsche Rechenergebnisse
Obsolete Einträge
I
Unterschiedliche Aktualisierungszeitpunkte
F
F
I
Ausreichende Aktualität einer Quelle
Verzögerte Aktualisierung
Vergessene Aktualisierung
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–75
Extraktion, Transformation, Laden
Datenfehler
Datenkonflikte: Behebung
Referenztabellen für exakte Wertabbildung
I
Z.B. Städte, Länder, Produktnamen, Codes...
Ähnlichkeitsmaße
I
Bei Tippfehlern, Sprachvarianten (Meier, Mayer,...)
Standardisieren und Transformieren
Nutzung von Hintergrundwissen (Metadaten)
I
I
Z.B. Konventionen (landestypische Schreibweisen)
Ontologien, Thesauri, Wörterbücher zur Behandlung von
Homonymen, Synonymen, . . .
Bei der Integration
I
I
Präferenzordnung über Datenquellen nach Aktualität, Trust
(Vertrauen), Öffnungszeiten usw.
Konfliktlösungsfunktionen
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–76
Extraktion, Transformation, Laden
ELT
ETL vs. ELT
ELT = Extract-Load-Transform
I
I
I
Variante des ETL-Prozesses, bei dem die Daten erst nach dem
Laden transformiert werden
Ziel: Transformation mit SQL-Anweisungen in der Zieldatenbank
Verzicht auf spezielle ETL-Engines
E
LT
Quellen
Data Warehouse
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–77
Extraktion, Transformation, Laden
ELT
ELT
Extraktion
I
I
I
Für Quellsystteme optimierte Abfragen (z.B. SQL)
Extraktion ebenfalls mit Monitoren überwacht
Automatische Extraktion schwieriger (z.B. bei
Datenstrukturänderungen)
Laden
I
I
I
Parallele Verarbeitung der SQL-Statements
Bulk Load (Annahme: keine Schreibzugriffe im Zielsystem)
Keine satzbasierte Protokollierung
Transformation
I
I
I
Ausnutzung von Mengenoperationen der
DW-Transformationskomponente
Komplexe Transformationen mittels prozeduraler Sprachen (z.B.
PL/SQL)
Spezifische Statements (z.B. CTAS von Oracle)
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–78
Extraktion, Transformation, Laden
ELT
Zusammenfassung
ETL als Prozess der Überführung von Daten aus Quellsystemen
in das DWH
Themen von ETL und Datenqualität machen typischerweise 80%
des Aufwands von DWH-Projekten aus!
I
I
Langsame Anfragen sind ärgerlich
Falsche Ergebnisse machen das DWH nutzlos
Teil des Transformationsschrittes
I
I
Schemaebene: Schema Mapping bzw. Schematransformation
Instanzebene: Datenbereinigung
c Sattler / Saake / Köppen
Data-Warehouse-Technologien
Letzte Änderung: 16.10.2016
4–79