Click to edit title

DATA WAREHOUSE
Oracle Data Warehouse –
Datenbank basierte ETL-Prozesse
Reduzieren Sie Ihre Ladezeiten! Organisieren Sie Ihre ETL-Prozesse!
DATA WAREHOUSE
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
Insert Picture Here
• Speichermanagement und Grundlagentechniken
Click to edit title
• Blöcke,
Extents, Segmente, Tablespace
Click to edit Master text
styles
• Direct Path Load, Mengenbasiertes Laden
• Logging / NoLogging
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
2
Das große Klagen
Bzgl. System-Nutzen
• Lieferzeiten der Daten zu lange (Latenzen)
• Zu schwerfällig bei Änderungen
Insert Picture
Click to
edit title
• Informationen
mehrfach
vorhanden
Click
to edit Master
text stylesSichten
• Fehlende
unternehmensweite
• Nicht die richtigen Informationen für die Anwender
• Anwender haben zu wenig unmittelbaren Einfluss auf die Daten
Bzgl. Maintenance und Technik
• Immer teuerer
• Maintenance-Aufwand zu hoch / Personal
• Explodierende Datenmengen -> Storage- / Ladezeitenthematik
Here
ETL ist mehr als nur Daten von A nach B kopieren
nicht Point-To-Point sondern Informationsschaffend
Quellsystem
DWH-System
n-tier
n-tier
Insert Picture Here
Click to edit title
Click to edit Master text styles
Application Server
ETL?
ETL?
4
Application Server
Data Integration
Layer
Enterprise Information
Layer
Click to edit title
Click to edit Master text styles
User View
Layer
BI-Tool Server + Caches
Was definieren wir als ETL-Prozesse?
Insert Picture Here
OLTP + Data Warehouse + BI
von
5
L a t e n z
bis
Reduzieren Sie Ihre
Ladezeiten auf ¼!
Hilfsmittel in der Datenbank (Auflistung)
• Parallelisierung
• Partitioning / Partition Exchange Load (PEL)
• Direct Path Load
• Set-Based SQL
• Pipelined Table Functions
Click
to edit Master text styles
• Materialized Views
• External Tables / Loader
• Transportable Tablespace
• Data Pump
• Database Link
• Direkt FTP-Load
Click to edit title
Insert Picture Here
1. Integrieren
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren / Selektieren
4. Sammeln
• Fachliches integrieren, Sachgebiets-übergreifend
• Identifizieren von identischen oder zusammenhängenden
Informationen
Insert Picture
•
Click to edit title
Synonymen-/Homonymen-Thematik
Click to edit Master text styles
• Aggregationslevel angleichen
• Identifizieren und Angleichen
• Formate, Zustände, Sichtweisen etc...
8
Here
Betrag / Summe
Artikel / Produkt
Artikel / Artikelgruppe
Meter / Kilometer
Lose Stücke / Gebinde
2. Informations-Mehrwerte
• Qualitativ gute Informationen schaffen
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren / Selektieren
4. Sammeln
• Datenqualitäts-Checks
•
•
•
•
•
Vollständigkeit
Datentypen
Referentielle Integrität
Eindeutigkeit
Korrekte Werte
Click to edit title
• Fachliche
Regeln überprüfen
Click to
edit Master
text styles
•
•
•
•
Berechnungen / Aggregationen / Zusammenfassungen
Metadaten anfügen
Monitoring aufbauen
Anreichern und Vermengen mit Referenzdaten
• Lookups
• Marktdaten
• Vergleichszahlen
9
Insert Picture Here
3. Kopieren / Selektieren
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren / Selektieren
4. Sammeln
• 1:1-Datenbewegung (nur in Ausnahmefällen)
• Einfachste Aufgabe
1:1 – Kopien sollten vermieden werden
Insert Picture
to edit
title
• WennClick
doch kopiert
werden
muss: dann In-Database-MengenClick Operationen
to edit Master text styles
• Ohne zusätzliche Logik
• Bei Überwindung von Systemgrenzen
• Zusätzliche Hilfsmittel wie FTP, Transportable Tablespace, Data Pump
• Vorschriften zum Mapping
• Schnittstellen-Konventionen
• Aspekt der Performance
10
Here
4. Sammeln
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren
4. Sammeln
• Einlagern von Daten
• Zeitliche Rahmenvorgaben
• Historisierung
Click von
to Daten
edit title
• Versionieren
• Kategorisieren
/ Inventarisieren
Click
to edit Master
text styles von Daten
• Dokumentieren der eingelagerten Informationen
•
•
•
•
11
Referenzen aufbauen
Alterungs-Eigenschaften berücksichtigen
Dokumentieren
Mehr als nur eine Momentaufnahme
Insert Picture Here
Ziele eines effizienten ETL-Prozesses
• Ressource-schonend
• Rechenzeit, Storage
• Schnell änderbar und pflegbar
Insert
to edit title
• KurzeClick
Laufzeiten
Click
to edit Master
text styles
• Erzeugen
von stimmigen
Abfrage-Ergebnissen
• Erleichterung für BI-Tools
....
Picture Here
Allgemeine Regeln
• Schichtenmodell als Orientierung nutzen – ganzheitlich planen
• Transformationen so früh wie möglich im Verlauf des Schichtenmodells
• Auf die spezifischen Anforderungen und Situationen in den jeweiligen
Insert Picture Here
Click
to edit title
Schichten
reagieren
Click
toSchichten
edit Master
text derselben
styles Datenbank
• Alle
innerhalb
• Daten nur dann bewegen, wenn sich qualitativ etwas verändert.
• Nur diejenigen Daten laden, die wirklich benötigt werden
• Eher selektieren als kopieren
• Prüfungen an wenigen Stellen konzentrieren
• Bei Data Marts prüfen, ob sie permanent bereit gehalten werden
R
T
R
User View Layer
D
S
B
T
Click to edit Master
text styles
B
B
B
B
B
Strategische
Daten
D
Zusammenhängender
Abfragebereich
Enterprise Information Layer
S S
Click Tto edit title
Operative
Daten
14
Data Integration Layer
aufbereiten
integrieren
Flexibilität und schnelles Bereitsstellen
F
Insert
Picture
Here
D
D
D
F
D
F
D
D
Taktische
Daten
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Die Organisation des ETL-Prozesses
Data Integration
Layer
Enterprise Information
Layer
Insert Picture Here
Click
to editStammtitle
Die Masse
Richtig
Click
to edit Master
selektieren
aller
Prüfungen
User View
Layer
text
styles
Referenzdaten
aktualisieren
Nur
Möglichst viele
denormalisierende
Kennzahlen
Joins
in die
Datenbank
Repository
(Glossar, alle Objekte)
Für alle Aktionen den frühest möglichen Punkt finden
15
Angemessen in den Situationen agieren
Data Integration
Layer
Enterprise Information
Layer
Keine to edit title
Referenzdaten
Click
Stammdaten
Daten
Bewegungsdaten
Click to edit Master text styles
(granulare
Transaktionsdaten)
vorberechnete
Kennzahlen
16
User View
Layer
Insert Picture
Dimensionen
Fakten
vorberechnete
Kennzahlen
Here
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Angemessen in den Situationen agieren
Data Integration
Layer
Enterprise Information
Layer
Temporäre
20% Volumen f. viele
Click
to
edit
title
Daten
Kleine Tabellen
Click to edit Master text styles
80% Volumen f.
wenige
große Tabellen
=> partitioniert
17
User View
Layer
Wieder
Insert Picture
herstellbare
Daten
Here
Lade-Aktivitäten an Schichtübergängen
Integration
Persistent
Flüchtige Daten
Clearing-Verfahren,
technisches, logisches,
semantisches Prüfen
Enterprise
Click to edit title
Normalisieren
(Granularisieren)
Historisieren
User View
Kopien / teilpersistent
dynamisch
Denormalisieren
z.T. Aggregieren
Insert Picture Here
Click to edit Master text styles
Generische
Datenstrukturen
(isolierte Tabellen,
teil-ausgeprägte Datentypen)
3 NF Datenstrukturen
(ER-Tabellen,
ausgeprägte Datentypen)
Multidimensionale Modelle
(ER-Tabellen,
ausgeprägte Datentypen)
Aktivierte Constraints
Keine Constraints
Kopieren
Selektieren
18
Mengenbasiertes
Prüfen ohne
Constraints
Umschlüsselung
Lookups -> Referenz-/Stammdaten
Joins
Aufbauen von Distinct-Strukturen
(Normalisieren)
Lookups -> Dimensionsdaten
Joins - Denormalisieren
Verfahren für schnelles ETL in der Datenbank
Data Integration
Layer
Enterprise Information
Layer
Insert
Picture
Here
Bekannte
Kenn-
ClickContraintto editPartition
title
freies
Direct Path
Prüfen mit
in temporäre
MengenTabellen
basiertem
SQL
Exchange
&LOAD
in partit.
Tabellen
(PEL)
Click to edit Master text styles
Selektieren
Statt
kopieren
User View
Layer
Unveränderte
Bewegungsdaten liegen
lassen
zahlen ausGroße
Fakten-Tab. schließlich
über PEL. über
MAVRefresh
Kennzahlen
in die DB
weniger
Koipien in
BI-Tools
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
Insert Picture Here
• Speichermanagement und Grundlagentechniken
Click to edit title
• Blöcke,
Extents, Segmente, Tablespace
Click to edit Master text
styles
• Direct Path Load, Mengenbasiertes Laden
• Logging / NoLogging
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
20
Zuordnung Datenobjekten und Speicher
DB-Objekte
Table
Click to edit title
Click to editPartition
Master text styles
Index
Mview
Insert Picture Here
Speicherobjekte
Click to edit title
Click to edit Master text styles
Insert Picture Here
Die automatische Extent-Vergrößerung
• Automatische Allokierung von weiteren Segmenten
• Exponentielles Vergrößerungs-Mass
select
Insert Picture Here
t.TABLE_NAME,
t.blocks,t.EMPTY_BLOCKS,t.AVG_SPACE,t.AVG_ROW_LEN,t.NUM_ROWS,
Click
to
edit
title
t.pct_free, t.compression,s.EXTENTS,s.bytes seg_bytes,e.blocks ext_blks,e.bytes ext_bytes
Click from
to edit user_segments
Master text
styles
s, user_tables t, user_extents e
where
t.TABLE_NAME = s.segment_name
and e.SEGMENT_NAME = s.SEGMENT_NAME
and t.TABLE_NAME = 'F_UMSATZ';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_ROWS PCT_FREE COMPRESS EXTENTS SEG_BYTES EXT_BLKS EXT_BYTES
----------------- ------------ ----------- -------- -------- -------- ------- ---------- -------- --------F_UMSATZ
277772
0
34 51200000
10 DISABLED
217 2281701376
8
65536
F_UMSATZ
277772
0
34 51200000
10 DISABLED
217 2281701376
128
1048576
F_UMSATZ
277772
0
34 51200000
10 DISABLED
217 2281701376
1024
8388608
F_UMSATZ
277772
0
34 51200000
10 DISABLED
217 2281701376
8192 67108864
Block- und Satzstruktur
Click to edit title
Insert Picture Here
Click to edit Master text styles
Row header
Datenbank Block
Column length
Column value
Empfehlungen bzgl. Spacemanagement
• PCTFREE auf 0 setzen
• In der Regel sind keine späteren UPDATES nötig
• Spart gegenüber dem Default von 10% auch 10% IO und jede Verarbeitung ist
Insert Picture Here
um 10% schneller
• Sollten
dennoch UPDATES
gemacht werden müssen:
to
edit Master
text styles
Click to edit title
Click
• Partitionieren der Tabelle
• Die jüngsten Partitionen mit separatem Tablespace definieren und PCTFREE auf
gewünschten Wert setzen
• Wenn keine UPDATES mehr zu erwarten sind -> umkopieren auf eine Partition mit
einem Tablespace mit PCTFREE=0
• Blocksize hochsetzen 16K, 32K
• Wirkt sich bei Massen-Inserts bei einer gößeren Datenmenge
positiv auf die Performnce aus
High Water Mark
Nach INSERTS:
Extent ID
0
1
2
3
4
Segment
Insert Picture Here
Click to edit title
Click to edit Master text styles
High-water mark
Nach DELETES:
Extent ID
0
1
2
3
4
Segment
Used block
Unused block
Free space
after delete
Wie wird die „High Water Mark“ bestimmt
TOTAL_BLOCKS
Click to edit title
Click
to edit Master
text
styles
Extent ID
0
1
2
UNUSED_BLOCKS
Insert Picture Here
3
4
Segment
High-water mark
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID
Deallocate Space
0
1
2
3
4
Segment
Extent ID
Before
deallocation
Insert Picture Here
Click to edit title
TABLE
tablename
ClickALTER
to edit
Master
text styles
High-water mark
DEALLOCATE UNUSED;
Extent ID
0
1
2
4
Segment
After
deallocation
Used block
3
Unused block
Free space
after delete
Truncate Table
TRUNCATE TABLE tablename
Insert Picture Here
Click to edit title
Click to Extent
edit ID
Master text
styles
0
1
Segment
Free space
High-water mark
Direct Path Load
INSERT /*+APPEND */ INTO DWH.F_UMSATZ
NOLOGGING
Insert Picture Here
SELECT
* FROM
OLTP.BESTELLUNGEN;
Click
to edit
title
Click to edit Master text styles
F_UMSATZ
Server
process
Segment
Used block
Free space after delete
High-water mark
Blocks used by inserted rows
Paralleler Direct Path Load
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+APPEND PARALLEL(F_UMSATZ,2) */
INTO Click
DWH.F_UMSATZ
Insert
to editNOLOGGING
title
SELECT * FROM OLTP.BESTELLUNGEN;
Picture Here
Click to edit Master text styles
Slave
process
Slave
process
F_UMSATZ
Segment
Used block
Free space after delete
High-water mark
Temporary segments
„Convential“ und „Direct Path“ - Load
Instance
SGA
Array
insertClick
Shared pool
Insert
Picture Here
Extent
to edit title
ClickConventional
to edit Master text styles
Table
management
Data
save
Direct
path
High-water mark
Space used only by conventional load
Direct Path / Convential Path
SQL Loader
External Table
Insert Append
CTAS
Convential Path
SQL Command Processing
Click
to edit title
Space Management
O r a c l e
Direct
Path
Benutzer
Click to edit Master
text
styles
Find partial
blocks
Get new extents
Adjust High
Water Mark
Fill partial blocks
Read Database
Blocks
Buffer
Cache
Write Database
Blocks
Database
S e r v e r
Buffer Cache Management
- Manage queues
- Manage contention
Convential Path
• Commits
• Reuse Free Space in Blöcken
• Constraint Checks
• Immer Undo Data / Logging
• Daten zunächst immer in SGA Buffer
• Tabelle für andere Benutzer offen
Direct Path
• Data Save
• Schreiben oberhalb der High Water Marks
• Keine Constraint Checks
• Nur PK, Not Null, Unique Key
• Kein Logging
• Daten nicht in SGA Buffer
• Tabelle gesperrt für andere Benutzer
Insert Picture Here
Testfall
F_Umsatz
F_Umsatz_DP
ARTIKEL_ID
KUNDEN_ID
ZEIT_ID
REGION_ID
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
ARTIKEL_ID
KUNDEN_ID
ZEIT_ID
REGION_ID
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
Click to
edit title
51.100.000
Click to edit MasterSätze
text styles
Insert Picture
Here
51.100.000
SQL> insert into f_umsatz_DP select * from f_umsatz;
51200000 Zeilen erstellt.
Abgelaufen: 00:07:57.73
SQL> insert /*+ APPEND */ into f_umsatz_DP select * from f_umsatz;
51200000 Zeilen erstellt.
Abgelaufen: 00:00:27.24
SQL> insert /*+ APPEND PARALLEL(F_UMSATZ_DP,2) */ into f_umsatz_DP select * from f_umsatz;
51200000 Zeilen erstellt.
Abgelaufen: 00:00:20.68
Sätze
Beliebte Fehler – Direct Path Load
Schreiben einzelner INSERTS
mit APPEND
Create or Replace procedure ABC as
.....
Cursor pos is select .....
....
Begin
open pos;
loop
exit when pos%notfound;
.....
Insert /*+ APPEND */ into Ziel_Tabelle
select ......
......
Commit;
end loop; .....
End;
Click to edit title
Stattdessen INSERTS mit
vielen Sätzen
Insert /*+ APPEND */ into
ziel_Tabelle
select ......
Insert Picture Here
Click to edit Master text styles
Direct Path Load nur mit
echten Mengen und nicht
bei Einzel-Inserts
Beliebte Fehler – Direct Path Load
Batch-Lauf 1
Batch-Lauf 2
(Session 1)
(Session 2)
Click to edit title
Insert /*+ APPEND */ into
F_UMSATZ
select ......
Click to edit Master text styles
Insert Picture Here
Insert /*+ APPEND */ into
F_UMSATZ
select ......
Tabelle
BESTELLUNG
Gegenseitiges Blockieren durch Direct Path Loads auf
In unterschiedlichen Sessions auf die gleiche Tabelle
Das Simulations-Szenario
Tabelle T10
Name Type
------------------F0 NUMBER
F1 NUMBER
F2 NUMBER
F3 VARCHAR2(50)
F4 DATE
F5 VARCHAR2(50)
F6 VARCHAR2(50)
F7 NUMBER
Tabelle T20
~ 10 Millionen Sätze
1, 6 GB, unkompr.
Click to edit title
Click to edit Master text styles
Name Type
------------------F0 NUMBER
F1 NUMBER
F2 NUMBER
F3 VARCHAR2(50)
F4 DATE
F5 VARCHAR2(50)
F6 VARCHAR2(50)
F7 NUMBER
Insert Picture Here
F0
F1 F2 F3
F4
F5
F6
F7
----- --- ---------- ----------------------------------------- --------- ----------------------------------------- --------------------------------- --28839 74
3 IamPFnAz6qnhWZlqao1AHgaR9gQczm4SSvtJn9lU 27-JAN-11 upOLaDSvWuxmv4pFlZsgtEPqgi43uRgI1uQjF7kV x2AFYV3W2QIcxf5mPzl39MpErCZI7rc1eQMXuMs8
42
28840 10 89 ESh7uiu6Hqo6cwqqk9B7D1w9biFR3QjCVDyNWjaq 01-MAY-08 Mcj4QZEVmiG5Qof4eoPwqARLFhlc1xpLmgrAzL5i jvuabLwH44YODTusRR3Huyz7sECCTrLFGZA5QJdD
12
28841 4 59 PA2OpnqxTISxHoHsJ5BZrIJArDGhcKCIi1lAzJyj 17-DEC-10 ebU5ogfehM87oO1f8e1VVrFOjJBsZJEUQLcyOls7 GP5zpIb5EzAsPrT9EuL6tdcJ2BVGbFXtch3F4rkO
58
28842 79 74 P4q95WqLs9yWOdx6yryAt7zNgO8YeGzqmXTLdHJe 26-NOV-09 eTsS6sZdjeZbRWSnjq2m3ivoACc29dQENlVYjtkK DTTfROusF1hU1LLGHNRXWWGwpFlO47zedJWgEdX5
55
............................................................................................................................. ...........................
Der ungünstigste Fall
Einzelinserts – Simuliert über Cursor-Prozedur
CREATE OR REPLACE PROCEDURE PR_x AS
CURSOR crs_T10 IS
SELECT * FROM T10;
bstnr number;
V_F0
NUMBER;
V_F1
NUMBER;
V_F2
NUMBER;
V_F3
VARCHAR2(50);
V_F4
DATE;
V_F5
VARCHAR2(50);
V_F6
VARCHAR2(50);
V_F7
NUMBER;
Lese-Operation T10 Tabelle
Click to edit title
Insert Picture Here
Click to edit Master text styles
Datenbewegung über Variablen
BEGIN
open crs_T10;
loop
FETCH crs_T10 into V_F0 ,V_F1,V_F2,V_F3,V_F4,V_F5,V_F6,V_F7;
insert /*+ NOLOGGING */ into T20 values(V_F0,V_F1,V_F2,V_F3,V_F4,V_F5,V_F6,V_F7);
EXIT WHEN crs_T10%NOTFOUND;
END loop;
END;
Laufzeit: 08:31 (Minuten : Sekunden)
Schreibvorgang
(8:12 System DWH)
Der einfache INSERT
insert into t20 select * from t10;
• INSERT in leere Tabelle
Click to edit title
Laufzeit: 01:46 (Minuten : Sekunden)
Click
to edit Master text styles
• INSERT in gefüllte Tabelle
Laufzeit: 01:58 (Minuten : Sekunden)
• INSERT in gefüllte Tabelle (Wiederholung)
Laufzeit: 01:58 (Minuten : Sekunden)
Insert Picture Here
Logging / Nologging
• INSERT im Archivelog-Modus
Umschalten der DB
shutdown immediate;
startup mount
alter database archivelog;
[alter database noarchivelog;]
alter database open;
Click to edit title
insert into t20 select * from t10;
Click toLaufzeit:
edit 02:56
Master
text
styles
(Minuten
: Sekunden)
Insert Picture Here
(3:10 System DWH)
• INSERT mit NOLOGGING im ARCHIVE-Modus
insert /*+ NOLOGGING */ into t20 select * from t10;
Laufzeit: 01:48 (Minuten : Sekunden)
(Hint nur zu Dokuzwecken. Steuerung des NOLOGGING-Zustands über ALTER DATABASE NOARCHIVELOG.)
Logging / Nologging
• Wird der Archivelog-Modus benötigt oder nicht?
• Relevant für
• Backup
• DataGuard / Golden Gate
• Flashback
to
edit Master text styles
Click to edit title
Click
• Wichtigster Punkt ist: BACKUP
• Abhängig vom Backup-Konzept
Insert Picture Here
Auswirkungen auf das Backup-Konzept
• Plattensicherung
• Oft einfach, weil eingespielte Verfahren
• Grosser Ressourcenverbrauch
Click to edit title
Insert Picture Here
• Alle (DWH-Bereiche) werden gesichtert -> großer Platzbedarf
• Teure Backup-Software
Click to
editimmer
Master
text
• Nicht
sicher,
weil styles
korrupte Datenbank-Blöcke nicht erkannt werden
können
• Man kann ohne Archivlog fahren -> ETL schneller und einfacher
• Sicherung mit RMAN
• Ressourcen-günstigstes Verfahren
• Man muss mit Archivlog fahren
• ETL etwas langsamer
• Massenloads mit Driect Path Load (NOLOGGING) -> separate Sicherung
RMAN- Backup-Verfahren:
Was wird gesichert?
Data Integration Layer
T
R
R
S S
Click Tto edit title
T
Click to edit Master
text styles
Keine Sicherung
User View Layer
Enterprise Information Layer
D
S
B
F Picture
Insert
Here
D
B
D
Keine Sicherung,
Inkremental Backup
wenn Data Marts
nur für Referenz- und
komplett neu aufgebaut
Stammdaten
Werden
große
RMAN
Bewegungsdatentabellen
(Incremental) am besten nach Abschluss des
ETL-Laufes sichern
RMAN (Incremental)
43
D
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Direct Path Load
• Create Table As Select (CTAS)
Click to edit title
Create Table t20 as select * from t10;
Click toLaufzeit:
edit01:00
Master
styles
(Minuten :text
Sekunden)
• INSERT mit APPEND - Hint
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 01:00 (Minuten : Sekunden)
Insert Picture Here
Arbeiten mit Buffer-Caches
• Wiederholtes Laden ohne zuvor die Buffer-Caches zu leeren
• Der SELECT-Teil läuft schneller
Click to edit title
1. Verarbeitung
Click insert
to edit
Master
text
styles
/*+ APPEND
*/ into
t20 select
* from t10;
Laufzeit: 01:00 (Minuten : Sekunden)
2. Verarbeitung
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 00:25 (Minuten : Sekunden)
Insert Picture Here
Verlagern des Ladeprozesses auf SSD-Platten
• Bestimmte Arbeitstabellen des ETL-Prozesses liegen auf
gesonderten SSD-Platten
• Die aktiven Partitionen großer Tabellen
• Temporäre Tabellen
Insert Picture Here
Click to edit title
Click to edit
text Enterprise
styles Information
Data Master
Integration
Layer
CTAS
CTAS
Layer
T
20% R
T
PEL
R
S
S
S
SSD
SSD
T
SSD
80%
D
D
SSD
D
B
PEL
D
F
B
SSD
SSD
T
SSD SSD
User View
Layer
SSD SSD
PEL
A
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
A: Aggregate
Partitionierte
Tabellen
Prüfungen
Verlagern des Ladeprozesses auf SSD-Platten
• Test mit Direct Path Load
CTAS mit SSD
Click to edit title
Create Table T20 as select * from t10;
(Minuten : Sekunden)
Click Laufzeit:
to edit00:10
Master
text styles
APPEND mit SSD
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 00:10 (Minuten : Sekunden)
Insert Picture Here
Alle Messdaten in der Übersicht
Click to edit title
Click to edit Master text styles
Insert Picture Here
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
Insert Picture Here
• Speichermanagement und Grundlagentechniken
Click to edit title
• Blöcke,
Extents, Segmente, Tablespace
Click to edit Master text
styles
• Direct Path Load, Mengenbasiertes Laden
• Logging / NoLogging
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
49
Es gibt 6 Prüf-Kategorien
Tabellen-über
greifende Regeln
Attribut-bezogene Regeln
1.
2.
Not Null / Pflichtfelder
Formatangaben
numeric
Alphanumerisch
Date
Masken
a)
b)
c)
d)
A
3.
4.
Click to edit title
Div. Check Constraint
9.
D
C
E
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
8.
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
12.
13.
14.
Referenz-Zusammenhänge
Verweise auf Sätze einer anderen Tabelle (Relation)
Zeitinvariante Inhalte (z. B. Anz. Bundesländer)
Zeitabhängige Veränderungen
Über die Zeit mit anderen Daten korrelierende Feldinhalte
Verteilungs-/Mengen-bezogene Regeln
F
15.
Verteilung
a)
b)
Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Zeit-/ Zusammenhang-bezogene Regeln
Satz-übergreifende Regeln
6.
7.
Insert Picture Here
Aggregat – Bedingungen
–
Satz-bezogene Regeln
Abhängigkeiten von Werten in anderen
Attributen desselben Satzes
10.
11.
–
5.
Child-Parent (Orphan)
Parent-Child
a)
b)
a)
b)
Wertbereiche
Click to
edit
Master text styles
Ober-/Untergrenzen / Wertelisten
B
Foreign Key
16.
Arithmetische Mittel
Varianz / Standardabweichungen
Qualitätsmerkmale und Mengen
Mengen-basierte Prüfungen mit SQL
Attribut-bezogene Regeln
1.
2.
Not Null / Pflichtfelder
Formatangaben
numeric
Alphanumerisch
Date
Masken
a)
b)
c)
d)
A
3.
4.
Click to edit title
Div. Check Constraint
Wertbereiche
Click to edit
Master text styles
Ober-/Untergrenzen / Wertelisten
–
Satz-bezogene Regeln
B
5.
Abhängigkeiten von Werten in anderen
Attributen desselben Satzes
select
bestellnr,
case
when -- wenn Feld BESTELLNR nicht numerisch
REGEXP_LIKE(BESTELLNR, '[^[:digit:]]')
then 1
else 0
End Num_Check_bestellnr
select
from bestellung;
CASE
WHEN (F1 = 3 and F2 = F3 + F4)
then 1
ELSE 0
end
from fx
Satz-übergreifende Regeln
C
6.
7.
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
8.
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
Insert Picture Here
insert /*+ APPEND */ into err_non_unique_bestellung
select bestellnr from
(select
count(bestellnr) n,
bestellnr
from bestellung
group by bestellnr)
where n > 1;
Umgang mit SQL und PL/SQL im DB-ETL
So nicht ...
Aber z. B. so ...
Create or replace procedure Proc_A
V1
number;
V2
number;
V3
varchar2;
V4
varchar2;
....
Cursor CS as select s1,s2 from tab_src;
Begin
open CS;
loop
fetch CS into v1,v2,...;
select f1 into v3 from tab1;
select f1 into v4 from tab2;
insert into Ziel _tab s1,s2,s3,s4
values(v1,v2,v3,v4);
end;
end;
insert into ziel
select f1, f2, f3, f4 from
(with CS as
select s1 v1,s2 v2 from tab_src
Select tab1.f1 f1 ,tab2.f2 f2, CS.s1 f3,CS.s2 f4
from tab1,tab2,CS
Where...
);
Click to edit title
Click to edit Master text styles
Insert Picture Here
Prüfungen Kategorie A
Attribut-/Column-bezogene Regeln
1. Not Null / Pflichtfelder
2. Formatangaben
a)
b)
c)
d)
numeric
Alphanumerisch
Date
Masken
Click to edit title
Click to edit Master text styles
3. Div. Check Constraint
4. Wertbereiche
Ober-/Untergrenzen / Wertelisten
Insert Picture Here
Prüfen mit oder Ohne Datenbank-Constraints
•
•
•
•
Constraints verlangsamen den Massen-Insert des ETL-Prozesses
=> Ohne Constraints arbeiten
=> Prüfen mit SQL-Mitteln
Insert Picture Here
Click
to
edit
title
=> Prüfen mit DML-Errorlogging
Click to
editbeiMaster
text sinnvoll
styles
• Nur
wenigen Daten
Prüfkonzepte
• Fachliche Prüfungen kaum möglich
• Eventuell zusätzliche Prüfungen nötig
• Einfach implementierbar
• Bessere Performance
• Nur bei aktivierten Constraints
Insert Picture Here
Click to edit title
Stage-Tabelle
Click to edit Master text styles
+ Geprüfte Daten
Kopieren
Statistik
Routine
Date
Number
Varchar2()
Bad
File
55
DML
Error Log
Check
Constraints
Fehlerhafte
Sätze
Statistiken
Error Logging
Kunde
INSERT INTO Kunde
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
Click to edit title
VALUES (......)
LOG ERRORS INTO
kunde_err('load_2004
0802')
Click to edit Master text styles
Kunde_err
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$
56
• Constraints
•
•
•
•
Unique Key / Primary Key
Foreign Key
Insert Picture Here
NOT NULL
Check Constraint
Testfall
Bestellung_Check
Bestellung
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
Click to edit title
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
UNIQUECONSTRAINT
Insert Picture Here
1.100.000
Click to
edit Master text styles
Sätze
100.000
doppelt
Bestellung_Check_Errors
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$
Testfall
begin
dbms_errlog.create_error_log(
dml_table_name
err_log_table_name
);
end;
Click to edit title
=> 'BESTELLUNG_CHECK',
=> 'BESTELLUNG_CHECK_ERRORS'
Insert Picture Here
Click to edit Master text styles
SQL> insert into bestellung_check select * from bestellung
2 LOG ERRORS INTO bestellung_check_errors ('daily_load') REJECT LIMIT 200000
3 ;
1000000 Zeilen erstellt.
Abgelaufen: 00:00:50.63
Die Alternative
1.
create table Bestellung_non_unique as
select bestellnr from
(select count(BESTELLNR) n, bestellnr
from bestellung
group by bestellnr)
where n > 1;
Click to edit title
Insert Picture Here
Tabelle wurde erstellt.
Click to edit Master text styles
Abgelaufen: 00:00:00.49
2.
insert /*+ APPEND */ into bestellung_check
select B.BESTELLNR,B.ORTNR,B.KUNDENNR,B.DATUM , B.ANZAHLPOS
from bestellung B
where B.BESTELLNR not in (select bestellnr from Bestellung_non_unique);
900000 Zeilen erstellt.
Abgelaufen: 00:00:02.26
Zusammen
00:00:03.15
Check Constraint mit Regular Expressions
CREATE TABLE Check_KUNDE (
KUNDENNR
NUMBER,
GESCHLECHT NUMBER,
VORNAME
VARCHAR2(50),
NACHNAME
VARCHAR2(50),
ANREDE
VARCHAR2(10),
GEBDAT
DATE,
ORTNR
NUMBER,
STRASSE
VARCHAR2(50),
TELEFON
VARCHAR2(30)
);
Regel:
Click to edit title
Click to edit Master text styles
Im Kundennamen müssen Buchstaben
vorkommen und
keine reine Zahlenkolonne
Insert Picture Here
ALTER TABLE check_kunde
ADD CONSTRAINT Ch_KD_Name
CHECK(REGEXP_LIKE(NACHNAME, '[^[:digit:]]'));
INSERT INTO check_kunde (Kundennr, Geschlecht, Vorname, Nachname, Anrede, Gebdat,
Ortnr, Strasse, Telefon)
VALUES (9,1,'Klaus','123','Herr','01.01.60',2,'Haupstr.',08923456);
FEHLER in Zeile 1:
ORA-02290: CHECK-Constraint (DWH.CH_KD_NAME) verletzt
• Verwendung von Regular Expressions steigert die Performance
bei Prüfungen
60
Beispiele
* Match 0 or more times
Modus
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
[:alnum:] Alphanumeric characters
Click to edit title
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
Click
to edit Master text styles
[:cntrl:] Control characters (nonprinting)
{m,} Match at least m times
Insert Picture Here
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
Zeichenklassen
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
61
Wichtiges Hilfsmittel für Einzelfeldprüfungen:
CASE-Anweisung
SELECT
CASE
WHEN isnumeric('999') = 1 THEN 'numerisch' ‚
ELSE 'nicht numerisch'‚
END Ergebnis
FROM dual;
CREATE OR REPLACE FUNCTION isnumeric (
p_string in varchar2) return boolean AS
l_number number;
BEGIN
l_number := p_string;
RETURN 1;
EXCEPTION
WHEN others THEN RETURN 0;
END;
Click to edit title
Click to edit Master text styles
62
Insert Picture Here
Hilfsfunktion:
Date_Check
create or replace function IsDate (str
varchar2) return varchar2 is inDate
varchar2(40);
FUNCTION dateCheck (inputDate varchar2,
inputMask varchar2) RETURN varchar2
Click to edit title
Click to edit Master text styles
IS dateVar date;
BEGIN
dateVar:= to_date(inputDate,inputMask);
return 'true';
exception
when others then
return 'false';
END;
• In Verbindung mit der
CASE-Anweisung
BEGIN
inDate:= trim(str);
if dateCheck(inDate, 'mm-dd-yyyy') = 'false'
AND dateCheck(inDate, 'mm-dd-yy') = 'false'
AND dateCheck(inDate, 'yyyy-mm-dd') = 'false'
AND dateCheck(inDate, 'yy-mm-dd') = 'false'
AND dateCheck(inDate, 'yyyy-mon-dd') = 'false‚
AND dateCheck(inDate, 'yy-mon-dd') = 'false‚
AND dateCheck(inDate, 'dd-mon-yyyy') = 'false‚
AND dateCheck(inDate, 'dd-mon-yy') = 'false‚
AND dateCheck(inDate, 'mmddyy') = 'false‚
AND dateCheck(inDate, 'mmddyyyy') = 'false‚
AND dateCheck(inDate, 'yyyymmdd') = 'false'
AND dateCheck(inDate, 'yymmdd') = 'false‚
AND dateCheck(inDate, 'yymmdd') = 'false'
AND dateCheck(inDate, 'yymondd') = 'false‚
AND dateCheck(inDate, 'yyyymondd') = 'false‚
AND dateCheck(inDate, 'mm/dd/yyyy') = 'false'
AND dateCheck(inDate, 'yyyy/mm/dd') = 'false‚
AND dateCheck(inDate, 'mm/dd/yy') = 'false'
AND dateCheck(inDate, 'yy/mm/dd') = 'false‚
AND dateCheck(inDate, 'mm.dd.yyyy') = 'false'
AND dateCheck(inDate, 'mm.dd.yy') = 'false'
AND dateCheck(inDate, 'yyyy.mm.dd') = 'false'
AND dateCheck(inDate, 'yy.mm.dd') = 'false'
then
return 'false';
else
return 'true';
end if;
--exception
--when others then return 'false';
Insert Picture Here
END;
63
Abarbeitungslogik für Einzelfeldprüfung mit CASE
Gepruefte_Daten
Stage-Tabelle
Varchar2()
Feld1
Feld2
Feld3
Temp-Tabelle
Varchar2()
Feld1
Feld2
Feld3
Click to edit title
Kopieren
INSERT INTO
temp_table
SELECT
Click to edit Master
text styles
CASE ....
FROM
Stage_Table
Feld1_is_null
Feld1_is_numeric
Feld2_is_numeric
INSERT ALL
WHEN
Feld_1_is_null =1
into
Error_Daten
Date
Number
Insert Picture
Here
WHEN
Feld_1_is_null=0
into
Gepruefte_Daten
Varchar2()
Error_Daten
Date
Number
Varchar2()
• Temporäre Tabelle ist optional
• Ist wesentlich übersichtlicher
• Erlaubt Kombination von unterschiedlichen Prüfkriterien
64
Abarbeitungslogik mit CASE
OLTP_Kunden
Bestellnr
Menge
INSERT INTO OLTP_Kunden_tmp
SELECT Bestellnr,Menge,Summe,Name,Ort,BestDatum,
CASE WHEN (Bestellnr is NULL)
then 1 ELSE 0
END Bestellnr_isNull,
CASE WHEN (isNumeric(Menge) = 1)
then 1 ELSE 0
END Menge_isNumeric,
CASE WHEN (isNumeric(Summe) = 1)
then 1 ELSE 0
END Summe_isNumeric,
CASE WHEN (Summe is NULL)
then 1 ELSE 0
END Summe_isNull,
CASE WHEN (isDate(BestDatum) = 1)
then 1 ELSE 0
END BestDatum_isDate
FROM OLTP_Kunden;
Click to edit title
Click
to edit Master text styles
Summe
Name
Ort
BestDatum
OLTP_Kunden_tmp
Bestellnr
Menge
Summe
Name
Ort
BestDatum
Insert Picture Here
Bestellnr_isNull
Menge_isNumeric
Summe_isNumeric
Summe_isNull
BestDatum_isDate
...
Prüfungen Kategorie B
Satz-bezogene Regeln
Satz-bezogene Regeln
5. Abhängigkeiten von Werten in anderen Attributen desselben Satzes
Insert Picture Here
Click to edit title
• Lösung: Analog zu Kategorie A über CASE
F4
Click F1
to edit F2
MasterF3
text styles
3
7
3
4
9
5
1
4
select
CASE
WHEN (F1 = 3 and F2 = F3 + F4)
then 1
ELSE 0
end
from fx
1
0
Prüfungen Kategorie C
Satz-übergreifende Regeln
6. Primary Key / Eindeutigkeit
7. Aggregat – Bedingungen
a)
b)
Click to edit title
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Zusammenhänge
Click8. toRekursive
edit Master
text styles
Verweise auf andere Sätze derselben Tabelle (Relation)
Insert Picture Here
6. Eindeutigkeit / PK
Click to edit title
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
POSITIONSNR PK
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Insert Picture Here
Mengenbasiertes Sammeln doppelter Sätze in Fehlertabelle
Click• Lösung:
to edit
Master text styles
insert /*+ APPEND */ into err_non_unique_bestellung
select bestellnr from
(select
count(bestellnr) n,
bestellnr
from bestellung
group by bestellnr)
where n > 1;
D_ARTIKEL
7. Aggregatbildung
F_UMSATZ
•Anforderung: Wenn der Umsatz pro Artikel
unter 20% des Artikelgruppen-Gesamtwertes fällt,
dann ROT
•Lösung:
Mit analytischen Funktionen:
Auf Satzebene über Informationen
von Satzgruppen verfügen
Click to edit title
Click
to edit Master text styles
select Artikelname,
FK
ARTIKEL_ID FK
KUNDEN_ID
FK
FK
ZEIT_ID
FK
REGION_ID
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
PK
SPARTE_NR
ARTIKEL_ID
Insert Picture Here
Artikelgruppe,
Wert,
sum(wert) over (partition by Artikelname) Artikelgesamtwert,
sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert,
case
when
(round(((sum(wert) over (partition by Artikelname))/(sum(wert) over (partition by
Artikelgruppe))*100),0) ) < 20
then 'ROT'
ELSE 'GRUEN'
end Prozent
from Artikel
8. Rekursive Zusammenhänge
•Anforderung:
Die Summe aller
Member_Value-Werte pro Parent
muss gleich dem Group_Value-Wert
des Parent sein.
Click to edit title
•Lösung:
Über Sub-Select
in
dem nach
Parent
Click
to edit
Master
text
styles
gruppiert und summiert wird.
select distinct A.F_key
from
rk A,
(select sum(member_value) sum_member, parent
from rk
where parent != 0 group by group_value, parent) B
where A.member_value = 0 and
A.group_value = B.sum_member;
F_Key
Parent
Member
_Value
Group
_Value
3
9
12
15
4
17
23
28
0
3
3
3
0
4
4
4
0
4
6
2
0
3
8
1
12
0
0
0
15
0
0
0
Insert Picture Here
Prüfungen Kategorie D
Tabellen-übergreifende Regeln
9. Foreign Key
a)
b)
Child-Parent (Orphan)
Parent-Child
Click to edit title
10. Aggregat – Bedingungen
Ober- Untergrenzen von Summen
Click toa)b) edit
text
AnzahlMaster
Sätze pro Intervall
usw.styles
11. Referenz-Zusammenhänge
Verweise auf Sätze einer anderen Tabelle (Relation)
Insert Picture Here
9. Foreign Keys
•Anforderung:
Zu jeder Bestellung muss es einen
Kunden geben.
Click to edit title
BESTELLUNG
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
BESTELLNR PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
Insert Picture Here
Click to edit Master text styles
•Lösung: Sub-Select in Where-Klausel.
insert /*+ APPEND */ into err_orphan_Bestellung
select bestellnr
from bestellung
where
Kundennr not in (select Kundennr from kunde);
10. Aggregatbedingungen
Anzahl Sätze pro Einheit
•Anforderung:
Anzahl Positionen muss
einen bestimmten Wert haben.
Click to edit title
•Lösung: Sub-Select in FROM-Klausel.
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Click
to edit Master text styles
insert /*+APPEND */ into err_anz_pos_Bestellposition
select
BESTELLNR,
anzahl_pos,
bst_ANZAHLPOS
from
(select
bestellnr,
count(positionsnr) Anzahl_pos,
ANZAHLPOS bst_anzahlpos
from Best_Pos
group by bestellnr,ANZAHLPOS)
where Anzahl_pos <> bst_anzahlpos;
Insert Picture Here
Szenario
•
Regel bzgl. der Bestellungen
•
•
•
•
Es darf keine Bestellung ohne Positionen geben.
Bestellnummern müssen eindeutig sein.
Es kann nur Bestellungen mit gültigen Kundennummern geben.
Bestellungen müssen immer in einem Zeitraum +/- 10 Tage von dem Tagestadum liegen.
• Regeln bzgl. der Bestellpositionen
•
•
•
•
•
•
Click to edit title
Insert Picture Here
Der durchschnittliche Wert einer Position muss > 5 sein.
Positionsnummern müssen pro Bestellung lückenlos von 1 beginnend aufsteigen sein.
Es darf nur Bestellpositionen mit einer gültigen Bestellnummer geben.
Es darf nur Bestellpositionen mit einer gültigen Artikelnummer geben.
Rabatt darf nur für Firmenkunden gegeben werden.
Formatprüfungen:
Click to edit Master text styles
•
•
•
Feld Depostelle 3-stellig alphanumerisch und 3-stellig numerisch
Feld Rabatt mus numerisch sein und mindestens den Wert 0 haben
Feld Menge muss gefüllt sein (NotNull) und muss > 0 sein
• Qualitative Prüfungen
•
Wenn der Gesamtwert pro Bestellung muss < 1000 beträgt, dann muss , wenn groesser, dann markieren.
•
Der Gesamtumsatz ist i. d. R. in dem 4ten Quartal am höchsten.
•
Bestellungen haben einen bestimmte Anzahl Positionen.
Die Beispiel - Quellumgebung
ARTIKEL
ARTIKEL_GRUPPEN
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
GRUPPE_NR PK
GRUPPE_NAME
FK
SPARTE_NR
Click to edit title
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
Insert Picture Here
Click to edit Master text styles
BESTELLUNG
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
75
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
BESTELLPOSITION
BESTELLNR FK
POSITIONSNR PK
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Beispielprüfungen
Click to edit title
Click to edit Master text styles
Insert Picture Here
Beispielprüfungen
Click to edit title
Click to edit Master text styles
Insert Picture Here
1. Schritt: Prüfungen von
Tabellen-übergreifenden Beziehungen
ARTIKEL
ARTIKEL_GRUPPEN
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
GRUPPE_NR PK
GRUPPE_NAME
FK
SPARTE_NR
Click to edit title
Click to edit Master text styles
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
21
78
BESTELLUNG
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
Insert Picture Here
18
2
13
BESTELLPOSITION
BESTELLNR FK
POSITIONSNR PK
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
2. Schritt: Prüfungen und Berechnungen von
Satz-übergreifenden Abhängigkeiten
ARTIKEL
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
ARTIKEL_GRUPPEN
GRUPPE_NR PK
GRUPPE_NAME
FK
SPARTE_NR
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
Insert Picture1 Here
13
Click to edit title
Click to edit Master text styles
BESTELLUNG
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
4
21
79
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
18
2
13
BESTELLPOSITION
9
BESTELLNR FK
POSITIONSNR PK
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
14
10
3. Schritt: Feldprüfungen
ARTIKEL
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
ARTIKEL_GRUPPEN
GRUPPE_NR PK
GRUPPE_NAME
FK
SPARTE_NR
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
Insert Picture1 Here
13
Click to edit title
Click to edit Master text styles
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
4
21
80
5
BESTELLUNG
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
6
ANZAHLPOS
8
18
2
BESTELLPOSITION
11
13
9
FK
BESTELLNR 12
POSITIONSNR PK 14
MENGE 15 16 17
FK 18
ARTIKELNR
10
DEPOTSTELLE19
RABATT
20
Regeln bei der Ablauf-Planung
• Alles was mengenbasiert prüfbar ist, kommt zuerst
• Feldbezogene Prüfungen sind nachgelagert
• Bei aufwendigen Joins abklären, ob diese mehrfach benötigt
Insert Picture
Click
edit title
werden,
wennto
ja, dann
Here
• Prüfen
einer Join-Tabelle
Click to
edit Master
text styles
• Nach Möglichkeit diese Prüfungen in eine zeitlich enge Abfolge bringen
• Aufwendige feldbezogene Prüfungen, Prüfungen mit Funktionen
usw. werden in einer einzigen Transformation zusamengefasst
• Bei Datenübernahme aus Datenbanken ist zu prüfen, ob innerhalb
der Zielumgebung noch geprüft werden muss
• Bei Text-Eingabe-Daten die External Table bzw. Loader – Prüfmittel
nutzen
Anordnung und Gruppierung der Ladeschritte des
Beispiel-Szenarios
KUNDE
err_anz_pos_Bestellposition
err_orphan_Bestellung
err_childless_Bestellung
Click to
edit title
err_orphan_PositionArtikel
err_AVG_Pos_Wert
Insert Picture Here
err_seq_pos_Bestellposition
Click
to edit Master text stylesBEST_POS
BESTELLUNG
BESTELLPOSITION
Tmp_ BEST_POS
Tmp2_ BEST_POS
err_non_unique_bestellung
err_kd_Rabatt_ok
err_orphan_Position
err_Wert_Menge
Hauptdatenfluss
Beziehungsprüfungen
Hauptdatenfluss
Hauptdatenfluss
ARTIKEL
err_maske_depotstelle
err_not_null_Menge
err_not_Rabatt_Wert
Auflistung der Laufzeiten für die einzelnen Prüfungen
(unterschiedliche Massnamen)
Click to edit title
Click to edit Master text styles
Insert Picture Here
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
Insert Picture Here
• Speichermanagement und Grundlagentechniken
Click to edit title
• Blöcke,
Extents, Segmente, Tablespace
Click to edit Master text
styles
• Direct Path Load, Mengenbasiertes Laden
• Logging / NoLogging
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
88
Partition Exchange Loading (PEL)
Financial
Temporäre
Tabelle
Production
Human Res.
Click to edit title
P1
P2
P3
P4
Z1
Z2
Z3
Z4
4
4
9
8
Neuer Monat
Insert Picture Zeit
Here
Store
Click
to edit Master text styles
Supplier
Marketing
Service
Parallel Direct Path INSERT
(Set Based)
CREATE TABLE AS SELECT
(CTAS)
CREATE Indizes / Statistiken anlegen
EXCHANGE Tabelle
Monat 13
Monat 12
Monat 11
Monat 10
DROP
PARTITION
Faktentabelle
89
Region
Partition Exchange Loading (PEL)
-- Leere Partition an Zieltabelle hinzufügen
ALTER TABLE Bestellung ADD PARTITION "Nov08"
VALUES LESS THAN (to_date('30-Nov-2008','dd-mon-yyyy'));
-- Neue leere temporäre Tabelle erstellen
CREATE TABLE Bestellung_temp AS
SELECT * FROM Bestellung WHERE ROWNUM < 1;
Click to edit title
Insert Picture Here
Click to edit Master text styles
-- Inhalte laden
INSERT /*+ APPEND */ INTO "PART"."BESTELLUNG_TEMP"
(BESTELLNR, KUNDENCODE, BESTELLDATUM, LIEFERDATUM, BESTELL_TOTAL, AUFTRAGSART, VERTRIEBSKANAL)
VALUES ('2', '3', TO_DATE('23.Nov.2008', 'DD-MON-RR'),
to_date('23.Nov.2008', 'DD-MON-RR'), '44', 'Service', '6');
Commit;
-- Erstellen Index auf temporäre Tabelle
CREATE INDEX Ind_Best_Dat_Nov ON Bestellung_temp ("BESTELLNR") NOLOGGING PARALLEL;
-- Temporäre Tabelle an die Zieltabelle anhängen
ALTER TABLE Bestellung EXCHANGE PARTITION "Nov08"
WITH TABLE Bestellung_temp INCLUDING INDEXES WITHOUT VALIDATION;
90
PEL – Auswirkungen auf die DWH-Verwaltung und
Konzepte
• Angleichung des Partition-Kriteriums auf den Lade-Rythmus und damit die
„Zeit“
• Meist täglicher Load -> Tagespartitionen
• Späteres Merge auf Monatsebene möglich / nötig
Click to edit title
Insert Picture Here
Click
to edit
Master
styles nutzen für
• Zwang
zum
späterentext
Monats-Merge
• Komprimierung
• Reorganisation
• ILM-Konzept
• Eine Partitionierungs-Ebene ist durch das PEL-Verfahren meist schon belegt
• Local-Indizierung (wenn überhaupt gebraucht) entstehen automatisch
Exchange Partition
Data Integration
Layer
Enterprise Information
Layer
Click to edit title
Älteste
Archivieren
(drop partition)
Mai
Click to edit Master text styles
Juni
Checks
Tmp_table
CTAS
Direct Path
Juli
August
September
Oktober
November
Alter table exchange partition
Alter table
add partition
User View
Layer
Insert Picture Here
Aufbau Fakten-Tabellen
Data Integration
Layer
Enterprise Information
Layer
Click to edit title
Älteste
Archivieren
(drop partition)
Tmp_table
CTAS
Direct Path
Juli
August
September
Oktober
November
Tmp_table
Oktober
November
CTAS
Tmp_table
Alter table exchange partition
Insert
Picture Here
Fact-Table
Juni
Juli
August
September
Mai
Click to edit Master text styles
Juni
Checks
User View
Layer
Alter table
exchange partition
Beispiele für Lösungen
Klassische PL/SQL Cursor – Verarbeitung
(Negativ – Beispiel)
Für alle
Bewegeungssätze z. B.
300.000)
Loop
(0,1 Sec /
Lauf)
select
t_Ref_1 (z. B. 10000)
select
t_ref_2 (z. B. 5000)
select
t_ref_3 (z. B. 50)
select
t_ref_4 (z. B. 6000)
cursor
t_Quelle_Stage_1 (z. B. 100000)
select
t_ref_5 (z. B. 8000)
select
t_ref_6 (z. B. 400)
select
t_ref_7 (z. B. 80)
select
t_ref_8 (z. B. 12000)
Click to edit title
Insert Picture Here
Click to edit Master textcursor
styles
t_Quelle_Stage_2 (z. B. 5000)
8,3 Std*
select
t_ref_9 (z. B. 15000)
+
Diverse Updates und
Inserts auf
Protokolltabellen
* Wert aus einer Bank (2003), wäre heute wesentlich weniger
Insert
Faktentabelle
Mengenbasierte Alternative
(Performance – Optimierung bis zu Faktor 20 und mehr)
t_Ref_1
t_Ref_2
t_Ref_3
Click to editt_Ref_4
title
Insert Picture Here
Click to edit Master text t_Ref_5
styles
300.000
Outer
Join
Temp Table
t_Ref_6
t_Ref_7
Fakt Table
Cursor
loop
t_Ref_8
Satzweise
Prüfung
t_Ref_9
Protokoll
Aufgabenstellung:
Lookup-Argumente über Funktionen gewinnen
• Mehrstufige Verarbeitung
Click to edit title
1. Über Funktionen Argumente gewinnen
Click to2. edit
Master intext
styles
Argumente
Lookup-Aufrufe
einbauen
• WITH-Verarbeitung
Insert Picture Here
Insert into ziel select * from
(with lc as
( select
level
V_KAUF_ID,
ran_M_N(0,2000)
V_MITARBEITER_ID,
ran_M_N(1,100024)
V_KUNDEN_ID,
sysdate-ran_M_N(20,1000)
V_Kaufdatum,
ran_M_N(999,1011)
V_PRODUKT_ID,
ran_M_N(0,4)
V_GESAMT_DISCOUNT_PROZENT
from Dual connect by level < 5)
select
L.V_KAUF_ID,
L.V_MITARBEITER_ID,
L.V_KUNDEN_ID,
L.V_Kaufdatum,
L.V_PRODUKT_ID,
M.FILIAL_ID,
M.MANAGER_ID,
F.FILIALLEITER_ID,
F.ORTNR,
P.VK_PREIS-((P.VK_PREIS*1)*L.V_GESAMT_DISCOUNT_PROZENT)/100 Gesamt_wert,
L.V_GESAMT_DISCOUNT_PROZENT
from
LC
L,
d_MITARBEITER
M,
d_filialen
F,
d_produkt
P
where
L.V_MITARBEITER_ID
= M.MITARBEITER_ID
and
M.FILIAL_ID
= F.FILIAL_ID
and
L.V_ProdukT_id
= P.produkt_id)
Click to edit title
Click to edit Master text styles
Insert Picture Here
Beschreiben einer Master/Detail - Beziehung
• Ziel: Die Sätze in der Detail-Tabelle müssen immer einen Parent-Satz
haben
• Lösung:
Click to edit title
Click
Insert Picture Here
• Multiple Insert: Die Information aus einer Quelle steht immer zum Beschreiben
Parentund Child-Tabelle
zur Verfügung.
tovon
edit
Master
text styles
• Beide sind synchronisierbar
BESTELLUNG
BESTELLNR PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
BESTELLPOSITION
BESTELLNR FK
POSITIONSNR PK
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Multiple Inserts verarbeiten / pauschal
INSERT ALL
WHEN 1=1‚
THEN INTO BESTELLUNG
(KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(KUNDENCODE,BESTELL_TOTAL,STATUS)
WHEN 1=1,
THEN INTO BESTELLPOSITION
(BESTELLMENGE,BESTELL_TOTAL,PRODUKT_NR)
VALUES
(BESTELLMENGE, BESTELL_TOTAL, PRODUKT_NR)
SELECT WH_TRANSAKTIONEN.BESTELLMENGE
BESTELLMENGE,
WH_TRANSAKTIONEN.KUNDENCODE
KUNDENCODE,
WH_TRANSAKTIONEN.BESTELL_TOTAL
BESTELL_TOTAL,
WH_TRANSAKTIONEN.STATUS
STATUS,
WH_TRANSAKTIONEN.PRODUKT_NR
PRODUKT_NR
FROM
WH_TRANSAKTIONEN WH_TRANSAKTIONEN ;
Click to edit title
Click to edit Master text styles
100
Insert Picture Here
Multiple Inserts verarbeiten / selektiert
INSERT ALL
WHEN STATUS = 'P'‚
THEN INTO WH_TRANS_PRIVAT
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
WHEN STATUS = 'F'‚
THEN INTO WH_TRANS_FIRMA
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(BESTELLMENGE,KUNDENCODE,BESTELL_TOTAL,STATUS)
SELECT WH_TRANSAKTIONEN.BESTELLMENGE BESTELLMENGE,
WH_TRANSAKTIONEN.KUNDENCODE KUNDENCODE,
WH_TRANSAKTIONEN.BESTELL_TOTAL BESTELL_TOTAL,
WH_TRANSAKTIONEN.STATUS STATUS
FROM
WH_TRANSAKTIONEN WH_TRANSAKTIONEN
WHERE
(WH_TRANSAKTIONEN.STATUS = 'P‚ /*SPLITTER.PRIVATKUNDEN*/)
OR
(WH_TRANSAKTIONEN.STATUS = 'F‚ /*SPLITTER.FIRMENKUNDEN*/);
Click to edit title
Click to edit Master text styles
101
Insert Picture Here
Deltadaten Extrahieren
OLTP
1
DWH
Table
Änderungsdatum
Click to edit title
Trigger
Table
2
Click to edit
Master
text styles
Table
Insert Picture Here
Table
Queue
3
Table
Deltabildung
über MINUS
Table
4
LogFile
Logminer
Table
Queue
5
LogFile
Streams
Queue
Table
6
LogFile
Golden Gate
Queue
Table
Herausforderungen beim Extrahieren
• Unterschiedliche Namen in Quell- und Zielsystemen
• Bewahrung der Konsistenz
Click to edit title
• Zeitpunkt des Ladens kann kritisch sein
Insert Picture Here
Click to
edit Master
• Vollständigkeit
dertext
Datenstyles
• Unterschiedliche GRANTs der User
• Zusätzlicher Netzwerkverkehr
• Meist ist nur das Delta der geänderten Daten gewünscht
• Formate (Datum, Zeichensätze)
103
Einlesetechniken Vorsysteme
EBCDIC
ASCII
SQL-Loader
External Table
Database Link
Click
to
edit
title
Transportable TS
Oracle
Click to edit Master text Datapump
styles
ODBC
JDBC
Gateway
Non
Oracle
API
Applikation
z. B.
SAP R/3
API CALL
Direct Save
SQL
SQL
BS Copy
BS Copy
SQL
SQL
SQL
SQL
Insert Picture Here
Oracle
DWH
Die Quellen
 Nur die Daten laden, die gebraucht
werden
Click to edit
title1:1 Kopien in dieInsert Picture Here
 Keine
Click
to edit Master text Integrations-Schicht
styles
Selektieren
 Nach Möglichkeit vor Eintritt in die
statt
Datenbank Prüfungen durchführen
kopieren
 Bereits geprüfte Daten nicht mehr
prüfen
Nicht so ....sondern
Data Integration
Layer
1:1
T
Click
to edit title
Click 1:1
to edit Master
text styles
T
1:1
1:1
T
Data Integration
Layer
Insert Picture Here
CTAS
T
T
T
Warum?
Logik so früh wie möglich
Techniken für Data Marts / User View Layer
Viele verstecke
Aufwende
Click to edit title
Verlagerung von IT in
Click
to edit Master text styles
die Fachabteilungen
Redundanzen
Ursache vieler nicht
abgestimmter
Kennzahlen
Insert Picture Here
Minimales Bewegen
Große unveränderte
Tabellen liegen lassen
Enterprise Information
Layer
Click to edit title
User View
Insert
Picture Here
Layer
Zugriff auf beide
Click
to edit Master text styles
Schichten
Security mit Bordmittel
anstatt durch Kopieren
lösen
D
D
F
B
D
B
PEL
D
Kennzahlen
Kennzahlen nur als
Materialized Views
User View Layer
Insert Picture Here
Click to edit title
Automatisches Refresh
Click
edit Master text styles
anstattto
ETL
Standardisierte und
stimmige Kennzahlen
Wiederverwenden von
bereits aggregierten
Daten
L1
L2
L3
L4
Der Weg in die BI-Tools
So
Click to edit title
Verhindert unnötiges
Millionen von Sätzen
Insert Picture Here
Click
to edit Master text styles
Kopieren
Keine Verlagerung von
Pseudo-ETL in die BITools
Standardisierte
Kennzahlen
BI-Tool Server + Caches
So viel wie möglich in
der DB vorbereiten
User View Layer
User View Layer
Oder so
WenigeSätze
Weitere Einflussfaktoren und Techniken
• Parallelisierung
-> abhängig von Hardware
-> direktes Steuern über Hints
• Aktuelle Statistiken
-> Source Tabellen
Insert Picture Here
Click to edit->title
auch während des ETL-Laufes
• Ausnutzen
des text styles
Click
to edit Master
Cache-Effektes
-> Organisieren der Abarbeitungsreihenfolge
-> Eventuell Query-Result-Cache nutzen
• Vermeiden von großen
Join-Tabellen
-> eventuell kleine Join-Tabelle
mit wenigen Spalten
Weitere Einflussfaktoren und Techniken
• Schnelle mengen-basierte Prüfungen kommen zuerst,
teuere Prüfungen (Feld-Prüfungen) zuletzt durchführen
• Möglichst viel Hauptspeicher für Join-Operationen
Insert Picture Here
Click
to
edit
title
mit großen Tabellen
Click
to edit Master text styles
• Sort-Area-Size hoch setzen
• Blocksize auf 16 bzw. 32 K
• PCTfree auf Null setzen
• Partition Change Tracking (PCT) für inkrementelles
Refresh der MAVs
Keine unnötige Daten-Transporte ...
Data
Integration
Layer
Vorsystem
Click
tomitedit title
Vorsystem
1:1
Insert Picture Here
Vorrechner
Click to edit Master text styles
1:1
Enterprise
Information
Layer
User View
Layer
User View
1:1 Layer
1:1
1:1
Externe ETL-Server
User View
Layer
... sondern kurze Wege
Freie
Wahlmöglichkeit
für Ort und Art des ETL
Vorsystem
Data Integration
Layer
Enterprise Information Layer
Click
to edit
title
Insert
Picture Here
User View Layer
Click to edit Master text styles
Process neutral / 3 NF
Ein-Datenbank-Server
Externe ETL-Server
(Hauptsächlich zu Dokumentationszwecken)
Zusammenfassung der wichtigsten Techniken
Eine zusammenhängende Datenbank
Data Integration
Layer
Enterprise Information
Layer
20% R R S S S
CTAS
Click Tto edit title
CTAS
T
B
T
PEL
Vorbereitete
temporäre
Tabellen
Partition
Exchange
Konzentration aller Prüfungen
115
CTAS : Create Table As Select
Partitionierte
Tabellen
PEL
A
80%
Vorgelagerte SQL-MengenPrüfungen
basierte
Prüfungen
D
D
B
Partition
Exchange
Aggregatbildung
durch Materialized
Views
Denormalisierung (Joins)
und Aggregate
PEL : Partition Exchange and Load
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
A: Aggregate
D
DPicture Here
Insert
F
PEL
Click to edit Master
text styles
T
User View
Layer
Prüfungen
Zusammenfassung der Techniken bezogen auf das
Schichten-Modell
Data Integration
Layer
Enterprise Information
Layer
Click Tto edit title
CTAS
20% R
R
S
S
S
CTAS
PEL
T
Click to edit Master
text styles
T
T
User View
Layer
Insert
D
DPicture Here
F
B
D
D
B
PEL
PEL
A
PCT
80%
Typ-/FormatPrüfungen
für Texte
Über
Loader bzw.
External TablePrüfungen
Temporäre
Prüf- und
Zwischentabellen
mit Direct
Path Load
(CTAS)
Partition
Exchange
Load als
Weiterverarbeitung
der External Tables
Referenzieren
von großen
Faktentabellen
in die
WarehouseSchicht hinein
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
A: Aggregate
Kennzahlensysteme mit
Materialized
Views
Fast Refresh mit
Partition Change
Tracking (PCT)
Partitionierte
Tabellen
Prüfungen