Click to edit title

DATA WAREHOUSE
Oracle Data Warehouse –
Datenbank basierte ETL-Prozesse
DATA WAREHOUSE
Themenübersicht 1/2
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
• Zusätzliche Techniken für schnelles und sicheres Laden
• Partition Exchange and Load (PEL)
• Multiple Inserts
2
Themenübersicht 2/2
Datenbank-basierte ETL-Prozesse
• Zugriff auf Quellsystemen
• Umgang mit Deltadaten
• Kopiertechniken / SQL Loader / Data Pump / External Tables
/ Transportable Tablespace
Click to edit title
Insert Picture Here
Click to edit Master text styles
• Planung und Organisation des ETL Prozesses
• Schichten als Planungsgrundlage
• Umgang mit separaten ETL-Tools und Lade-Engines
• Weitere Datenbank-Techniken
•
•
•
•
Ersatz von Aggregat-Tabellen durch MAVs
Table Functions
Pivoting
Merge
• Zusammenfassung
3
Das große Klagen
System-Nutzen
• Lieferzeiten der Daten zu lange (Latenzen)
• Zu schwerfällig bei Änderungen
Insert Picture Here
• Informationen
vorhanden
Click tomehrfach
edit title
• Fehlende
unternehmensweite
Click
to edit Master
text styles Sichten
• Nicht die richtigen Informationen für die Anwender
• Anwender haben zu wenig unmittelbaren
Einfluss auf die Daten
Maintenance Technik
• Immer teuerer
• Maintenance-Aufwand zu hoch / Personal
• Explodierende Datenmengen -> Storage- / Ladezeitenthematik
Flexibilität und schnelles Bereitsstellen
Enterprise Information Layer
S
T
S
Click to edit title
B
B
D
B
B
D
B
D
F
D
Insert
Picture
Here
T
Click to edit Master text styles
Operative
Daten
D
S
B
Strategische
Daten
Zusammenhängender
Abfragebereich
R
R
T
User View Layer
aufbereiten
integrieren
Data Integration Layer
F
D
F
D
D
Taktische
Daten
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
5
Lade-Aktivitäten an Schichtübergängen
Integration
Persistent
Flüchtige Daten
Clearing-Verfahren,
technisches, logisches,
semantisches Prüfen
Enterprise
Normalisieren
(Granularisieren)
Click to edit title
User View
Kopien / teilpersistent
dynamisch
Denormalisieren
Historisieren
z.T. Aggregieren
Insert Picture Here
Click to edit Master text styles
Generische
Datenstrukturen
(isolierte Tabellen,
teil-ausgeprägte Datentypen)
Keine Constraints
Kopieren
Selektieren
6
Mengenbasiertes
Prüfen ohne
Constraints
3 NF Datenstrukturen
(ER-Tabellen,
ausgeprägte Datentypen)
Multidimensionale Modelle
(ER-Tabellen,
ausgeprägte Datentypen)
Aktivierte Constraints
Umschlüsselung
Lookups -> Referenz-/Stammdaten
Joins
Aufbauen von Distinct-Strukturen
(Normalisieren)
Umschlüsselung
Lookups -> Dimensionsdaten
Joins - Denormalisieren
Hilfsmittel in der Datenbank (Auflistung)
• Parallelisierung
• Partitioning / Partition Exchange Load (PEL)
• Direct Path Load
Insert Picture Here
Click
to
edit
title
• Set-Based SQL
Click
to edit Master
styles
• Pipelined
Tabletext
Functions
• Materialized Views
• External Tables / Loader
• Transportable Tablespace
• Data Pump
• Database Link
• Direkt FTP-Load
1. Integrieren
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren
4. Sammeln
• Identifizieren von identischen oder
zusammenhängenden Informationen
• Synonymen-/Homonymen-Thematik
•
Click to edit
title
Aggregationslevel
angleichen
Click•toIdentifizieren
edit Master
styles
und text
Angleichen
• Formate, Zustände, Sichtweisen etc...
9
Betrag / Summe
Artikel / Produkt
Insert Picture Here
Artikel / Artikelgruppe
Meter / Kilometer
Lose Stücke / Gebinde
2. Informations-Mehrwerte
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren
4. Sammeln
• Qualitativ gute Informationen schaffen
• Datenqualitäts-Checks
• Vollständigkeit
• Datentypen
• Referentielle Integrität
Eindeutigkeit
to•edit
Master text styles
• Korrekte Werte
Click to edit title
Click
Insert Picture Here
• Fachliche Regeln überprüfen
• Berechnungen / Aggregationen / Zusammenfassungen
• Anreichern und Vermengen mit Referenzdaten
• Lookups
• Marktdaten
• Vergleichszahlen
10
3. Kopieren
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren
4. Sammeln
• 1:1-Datenbewegung
• Einfachste Aufgabe
• Mengen-Operationen
•
Click
to edit
Ohne
zusätzliche
Logik title
Click to edit Master text styles
• Überwindung von Systemgrenzen
• Vorschriften zum Mapping
• Schnittstellen-Konventionen
• Aspekt der Performance
11
Insert Picture Here
1. Integrieren
2. Informations-Mehrwerte
3. Kopieren
4. Sammeln
4. Sammeln
• Einlagern von Daten
• Zeitliche Rahmenvorgaben
• Historisierung
Click to
edit
title
Versionieren
von
Daten
Insert Picture Here
•
Click
to edit Master/ text
styles
• Kategorisieren
Inventarisieren
von Daten
• Dokumentieren der eingelagerten Informationen
•
•
•
•
12
Referenzen aufbauen
Alterungs-Eigenschaften berücksichtigen
Dokumentieren
Mehr als nur eine Momentaufnahme
Daten-nahe Transformation im DWH
Den richtigen Platz finden
Quellsystem
DWH-System
n-tier
n-tier
Insert Picture Here
Click to edit title
Click to edit Master text styles
Application Server
ETL?
ETL?
13
Application Server
Themenübersicht 1/2
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
• Zusätzliche Techniken für schnelles und sicheres Laden
• Partition Exchange and Load (PEL)
• Multiple Inserts /
14
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
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
UNUSED_BLOCKS
Insert Picture Here
Click to edit title
Click to edit Master text styles
Extent ID
0
1
2
3
4
Segment
High-water mark
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID
Deallocate Space
Extent ID
0
1
2
3
4
Segment
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 edit Master text styles
Extent ID
0
1
Segment
Free space
High-water mark
Direct Path Load
INSERT /*+APPEND */ INTO DWH.F_UMSATZ
NOLOGGING
Insert Picture Here
SELECT
FROM
Click* to
editOLTP.BESTELLUNGEN;
title
Click to edit Master text styles
Server
process
F_UMSATZ
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 DWH.F_UMSATZ NOLOGGING
Insert Picture Here
Click to edit title
* FROM
ClickSELECT
to edit Master
text OLTP.BESTELLUNGEN;
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
Shared pool
Array
insert
Insert
Picture Here
Extent
Click to edit title
management
ClickConventional
to edit Master text styles
Table
Data
save
Direct
path
High-water mark
Space used only by conventional load
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
Click51.100.000
to edit Master text styles
Sätze
Insert Picture Here
51.100.000
Sätze
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
Direct Path / Convential Path
SQL Loader
External Table
Insert Append
CTAS
Direct
Path
O r a c l e
Click
Convential Path
• Commits
• Reuse Free Space in Blöcken
Convential Path
• Constraint Checks
SQL Command Processing
Picture Here
/ Logging
Space
Management
Click
to edit title• Immer Undo DataInsert
• Daten zunächst immer in SGA Buffer
Get new
extents
to
edit
Master
text
Find partial
blocks styles
Adjust High
Fill partial blocks
• Tabelle für andere Benutzer offen
Water Mark
Benutzer
Read Database
Blocks
Buffer
Cache
Write Database
Blocks
Database
S e r v e r
Buffer Cache Management
- Manage queues
- Manage contention
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
Beliebte Fehler
Schreiben einzelner INSERTS
mit APPEND
Create or Replace procedure ABC as
.....
Cursor pos is select .....
....
Click
to edit Master text styles
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
Insert Picture
select ......
Here
Direct Path Load nur mit
echten Mengen und nicht
bei Einzel-Inserts
Beliebte Fehler
Batch-Lauf 1
Batch-Lauf 2
(Session 1)
(Session 2)
Click to edit title
Insert /*+ APPEND */
into
ClickF_UMSATZ
to edit Master
select ......
text styles
Picture
Insert Insert
/*+ APPEND
*/
into
F_UMSATZ
select ......
Here
Tabelle
BESTELLUNG
Gegenseitiges Blockieren durch Direct Path Loads auf
In unterschiedlichen Sessions auf die gleiche Tabelle
Space Management
Automatische Verwaltung
des freien Platzes
ASSM
Click to edit title
Click to edit Master text styles
Insert Picture Here
MSSM
Freelists werden gepflegt
• Empfehlung: Locally Managed Tablespace mit ASSM
• Prüfung ob MSSM bei Massen-Inserts schneller ist
Bitmap managed Storage
Locally managed Tablespace ohne Freelists
Click to edit title
Click to edit Master text styles
Insert Picture Here
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 um 10% schneller
Insert Picture
Click
to
edit
title
• Sollten dennoch UPDATES gemacht werden müssen:
• Partitionieren
Tabelle
Click to edit
Master der
text
styles
• 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
Here
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)
Click to edit
title
Insert Picture Here
t_Quelle_Stage_1 (z. B. 100000)
Click to edit Master textcursor
styles
t_Quelle_Stage_2 (z. B. 5000)
cursor
8,3 Std*
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)
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
Das Simulations-Szenario
Tabelle T10
Name Type
------------------F0 NUMBER
F1 NUMBER
F2 NUMBER
F3 VARCHAR2(50)
toF4edit
DATE
Master text
F5 VARCHAR2(50)
F6 VARCHAR2(50)
F7 NUMBER
Tabelle T20
~ 10 Millionen
Sätze
Click to edit title
Click
styles
Name Type
------------------F0 NUMBER
F1 NUMBER
Insert Picture
F2 NUMBER
F3 VARCHAR2(50)
F4 DATE
F5 VARCHAR2(50)
F6 VARCHAR2(50)
F7 NUMBER
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
Insert Picture Here
Click to edit title
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;
Schreibvorgang
Laufzeit: 08:31 (Minuten : Sekunden)
Der einfache INSERT
insert into t20 select * from t10;
• INSERT in leere Tabelle
Laufzeit:
01:46
: Sekunden)
Click
to(Minuten
edit title
Insert Picture Here
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)
Logging / Nologging
• INSERT im Archivelog-Modus
Click
to select
edit *title
insert
into t20
from t10;
Insert Picture Here
Click toLaufzeit:
edit Master
text styles
02:56 (Minuten : Sekunden)
• INSERT mit NOLOGGING im ARCHIVE-Modus
insert /*+ NOLOGGING */ into t20 select * from t10;
Laufzeit: 01:48 (Minuten : Sekunden)
Logging / Nologging
• Wird der Archivelog-Modus benötigt oder nicht?
• Relevant für
• Backup
• DataGuard / Golden Gate
Click to edit title
• Flashback
Click to edit Master text styles
• 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
• Alle (DWH-Bereiche) werden gesichtert -> großer Platzbedarf
Insert Picture
• Teure Backup-Software
Click to edit title
• Nicht immer sicher, weil korrupte Datenbank-Blöcke nicht erkannt
Click to
edit Master text styles
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 müssen mit NOLOGGING gefahren werden -> separate
Sicherung
Here
RMAN- Backup-Verfahren:
Was wird gesichert?
Data Integration Layer
User View Layer
Enterprise Information Layer
R: Referenztabellen
T
T
R
R
S
D
S
Click to edit title
S: Stammdaten
D
Insert Picture
Here
F
B
D
B
Inkremental Backup
nur für Referenz- und
Stammdaten
D: Dimensionen
F: Fakten
T
Keine Sicherung,
wenn Data Marts
komplett neu aufgebaut
Werden
RMAN
große
(Incremental) Bewegungsdatentabellen
am besten nach Abschluss des
ETL-Laufes sichern
RMAN (Incremental)
40
T: Transfertabellen
B: Bewgungsdaten
S
Click to edit Master text styles
Keine Sicherung
D
Direct Path Load
• Create Table As Select (CTAS)
Click
tot20edit
title* from t10;
Create
Table
as select
Insert Picture Here
Click toLaufzeit:
edit Master
text styles
01:00 (Minuten : Sekunden)
• INSERT mit APPEND - Hint
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 01:00 (Minuten : Sekunden)
Arbeiten mit Buffer-Caches
• Wiederholtes Laden ohne zuvor die Buffer-Caches zu
leeren
• Der SELECT-Teil läuft schneller
Click to edit title
Insert Picture Here
1. Verarbeitung
Click to edit Master text styles
insert /*+ 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)
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
User View
Layer
R: Referenztabellen
T
20% R
R
S
S
S
D
T: Transfertabellen
D
S: Stammdaten
SSD
T
B: Bewgungsdaten
SSD SSD
SSD
T
B
D
B
T
PEL
80%
D
SSD
SSD
SSD
SSD
F
PEL
SSD
SSD
PEL
A
D: Dimensionen
F: Fakten
A: Aggregate
Partitionierte
Tabellen
Prüfungen
Verlagern des Ladeprozesses auf SSD-Platten
• Test mit Direct Path Load
CTAS mit SSD
Create Table T20 as select * from t10;
Click to edit title
Insert Picture Here
Laufzeit:
00:10 (Minuten
: Sekunden)
Click to
edit Master
text styles
APPEND mit SSD
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 00:10 (Minuten : Sekunden)
Alle Messdaten in der Übersicht
Click to edit title
Click to edit Master text styles
Insert Picture Here
Themenübersicht 1/2
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
• Zusätzliche Techniken für schnelles und sicheres Laden
• Partition Exchange and Load (PEL)
• Multiple Inserts /
46
Varianten von Regeln / Prüfungen
Attribut-bezogene Regeln
Tabellen-übergreifende Regeln
1.
2.
Not Null / Pflichtfelder
Formatangaben
9.
a)
b)
c)
d)
10. Aggregat – Bedingungen
3.
4.
numeric
Alphanumerisch
Date
Masken
Click to edit title
Div. Check Constraint
Wertbereiche
Click to• edit
Master
text styles
Ober-/Untergrenzen
/ Wertelisten
Satz-bezogene Regeln
5.
Abhängigkeiten von Werten in anderen
Attributen desselben Satzes
Satz-übergreifende Regeln
6.
7.
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
8.
Rekursive Zusammenhänge
•
47
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Verweise auf andere Sätze derselben Tabelle
(Relation)
Foreign Key
a)
b)
Child-Parent (Orphan)
Parent-Child
a)
b)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Insert Picture Here
11. Referenz-Zusammenhänge
•
Verweise auf Sätze einer anderen Tabelle (Relation)
Zeit-/ Zusammenhang-bezogene
Regeln
12. Zeitinvariante Inhalte
Anz. Bundesländer
13. Zeitabhängige Veränderungen
14. Über die Zeit mit anderen Daten korrelierende
Feldinhalte
Verteilungs-/Mengen-bezogene Regeln
15. Verteilung
a)
b)
Arithmetische Mittel
Varianz / Standardabweichungen
16. Qualitätsmerkmale und Mengen
Varianten von Regeln / Prüfungen
Attribut-bezogene Regeln
Tabellen-übergreifende Regeln
1.
2.
9.
Not Null / Pflichtfelder
Formatangaben
a)
b)
c)
d)
A
3.
4.
numeric
Alphanumerisch
Date
Masken
a)
b)
D
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
E
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
C
8.
Rekursive Zusammenhänge
•
48
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Verweise auf andere Sätze derselben Tabelle
(Relation)
Child-Parent (Orphan)
Parent-Child
10. Aggregat – Bedingungen
a)
b)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Insert Picture Here
11. Referenz-Zusammenhänge
•
Verweise auf Sätze einer anderen Tabelle (Relation)
Zeit-/ Zusammenhang-bezogene
Regeln
12. Zeitinvariante Inhalte
Satz-übergreifende Regeln
6.
7.
Foreign Key
Anz. Bundesländer
13. Zeitabhängige Veränderungen
14. Über die Zeit mit anderen Daten korrelierende
Feldinhalte
Verteilungs-/Mengen-bezogene Regeln
15. Verteilung
F
a)
b)
Arithmetische Mittel
Varianz / Standardabweichungen
16. Qualitätsmerkmale und Mengen
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
Check
Constraint
Click3.toDiv.
edit
Master
text styles
4. Wertbereiche
Ober-/Untergrenzen / Wertelisten
Insert Picture Here
Prüfen mit oder Ohne Datenbank-Constraints
• Constraints verlangsamen den Massen-Insert des ETLProzesses
• => Ohne Constraints arbeiten
Insert Picture Here
• => Prüfen
Clickmit
to SQL-Mitteln
edit title
• =>
Prüfen
mit DML-Errorlogging
Click
to edit
Master
text styles
• Nur bei wenigen Daten sinnvoll
Prüfkonzepte
• Einfach implementierbar
• Bessere Performance
• Nur bei aktivierten Constraints
• Fachliche Prüfungen kaum möglich
• Eventuell zusätzliche Prüfungen
nötig
Insert Picture Here
Click to edit title
Stage-Tabelle
+ Geprüfte Daten
Click to edit Master text styles
Statistik
Routine
Date
Kopieren
Number
Varchar2()
Check
Constraints
Bad
File
51
DML
Error Log
Fehlerhafte
Sätze
Statistiken
Error Logging
Kunde
INSERT INTO Kunde
VALUES (......)
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
Click to edit title
LOG ERRORS INTO
kunde_err('load_20040802
')
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$
52
• Constraints
•
•
•
•
Unique Key / Primary Key
Foreign Key
NOT NULL
Insert Picture Here
Check Constraint
Testfall
Bestellung
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
Click to edit title
Bestellung_Check
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
UNIQUECONSTRAINT
Insert Picture Here
Click1.100.000
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;
Insert Picture Here
Click
to edit title
Tabelle wurde
erstellt.
Click
to edit00:00:00.49
Master text styles
Abgelaufen:
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:
Im Kundennamen müssen Buchstaben
vorkommen und
keine reine Zahlenkolonne
Insert Picture Here
Click to edit title
TABLE check_kunde
Click to edit Master text styles ALTER
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
57
Beispiele
* Match 0 or more times
? Match 0 or 1 time
Modus
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
Insert
Picture
\n Cause the previous expression
to be repeated
n times Here
Click to edit title
[:alnum:] Alphanumeric characters
Click
to edit Master text styles
[:alpha:] Alphabetic characters
Zeichenklassen
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
58
Wichtiges Hilfsmittel für Einzelfeldprüfungen:
CASE-Anweisung
SELECT
CASE
WHEN isnumeric('999') = 1 THEN 'numerisch' ‚
ELSE 'nicht numerisch'‚
Insert Picture
END Ergebnis
FROM
Click
to dual;
edit Master text styles
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
59
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
IS dateVar date;
Click to edit Master text styles
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;
60
Abarbeitungslogik für Einzelfeldprüfung
mit CASE
Gepruefte_Daten
Temp-Tabelle
Stage-Tabelle
Varchar2()
Kopieren
INSERT INTO
temp_table
Varchar2()
Feld1
Feld1
Click
to
edit
title
Feld2
Feld2
SELECT
Feld3
Click to editFeld3
Master
text
styles
CASE
....
FROM
Stage_Table
INSERT ALL
WHEN
Feld_1_is_null =1
into
Error_Daten
Date
Number
Insert Picture
Here
WHEN
Feld1_is_null
Feld_1_is_null=0
Feld1_is_numeric
into
Feld2_is_numeric
Gepruefte_Daten
Varchar2()
Error_Daten
Date
Number
Varchar2()
• Temporäre Tabelle ist optional
• Ist wesentlich übersichtlicher
• Erlaubt Kombination von unterschiedlichen Prüfkriterien
61
Abarbeitungslogik mit CASE
OLTP_Kunden_tmp
Bestellnr
OLTP_Kunden
Bestellnr
Menge
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
Summeto edit Master text styles
Click
Name
Ort
BestDatum
Summe
Insert
Name Picture Here
Ort
BestDatum
Bestellnr_isNull
Menge_isNumeric
Summe_isNumeric
Summe_isNull
BestDatum_isDate
...
Beispiel mit graphischer Modellierung:
Einsatz von CASE und Zwischentabelle
Click to edit title
Click to edit Master text styles
63
Insert Picture Here
Prüfungen Kategorie B
Satz-bezogene Regeln
Satz-bezogene Regeln
5. Abhängigkeiten von Werten in anderen Attributen desselben Satzes
Insert Picture Here
• Lösung: Analog zu Kategorie A über CASE
Click to edit title
F4
Click F1
to edit F2
MasterF3text styles
3
9
7
5
3
1
4
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)
8.
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Click
to edit title
Rekursive
Zusammenhänge
andere Sätze derselben Tabelle (Relation)
Click to Verweise
edit auf
Master
text styles
Insert Picture Here
6. Eindeutigkeit / PK
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
DEPOTSTELLE
RABATT
Click to edit title
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
ARTIKEL_ID FK
unter 20% des Artikelgruppen-Gesamtwertes fällt,
KUNDEN_ID FK
ZEIT_ID FK
dann ROT
•Lösung: Mit analytischen Funktionen:
Auf Satzebene über Informationen
von Satzgruppen verfügen
Click to edit title
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME
SPARTE_NR
ARTIKEL_ID PK
REGION_ID FK
KANAL_ID FK
UMSATZ
MENGE
UMSATZ_GESAMT
Insert Picture Here
Click
to edit Master text styles
select Artikelname,
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.
•Lösung: Über Sub-Select in dem nach Parent
gruppiert und summiert wird.
Click to edit title
Click to edit Master text styles
F_Key
Parent
Member Group
_Value _Value
3
9
12
15
4
17
23
28
0
3
3
3Insert
0
4
4
4
0
12
4
0
6
0
Picture
2
0 Here
0
15
3
0
8
0
1
0
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;
Prüfungen Kategorie D
Tabellen-übergreifende Regeln
9. Foreign Key
a)
b)
Child-Parent (Orphan)
Parent-Child
10. Aggregat – Bedingungen
a)
b)
Click to edit title
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Click11.toReferenz-Zusammenhänge
edit Master text styles
Verweise auf Sätze einer anderen Tabelle (Relation)
Insert Picture Here
9. Foreign Keys
BESTELLUNG
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
•Anforderung:
Zu jeder Bestellung muss es einen
Kunden geben.
Click to edit title
BESTELLNR PK
ORTNR
KUNDENNR FK
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.
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
DEPOTSTELLE
RABATT
to
edit title
•Lösung: Click
Sub-Select
in FROM-Klausel.
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
Prüfung auf Eindeutigkeit:
Beispiel graphisch und manuell
Click to edit title
Beispiel
entfernen
Insert Picture Here
Click to edit Master text styles
INSERT INTO el_kunde (kundennr,vorname,nachname,ortnr,strasse,telefon)
SELECT src2.nummer,src2.name,src2.name,src2.nummer,src2.name,src2.nummer FROM SRC2,
(SELECT nummer FROM
(SELECT count(nummer) n, nummer FROM src2 group by nummer)
WHERE n = 1) doppelte
WHERE src2.nummer = doppelte.nummer;
72
Herausfiltern und Protokollieren von
Feldern mit dem Wert „NOT NULL“
CASE
WHEN F2 IS NULL THEN 1
ELSE 0
END
Click to edit title
Click to edit Master text styles
73
Insert Picture Here
Prüfen auf Eindeutigkeit der Eingabesätze
Es dürfen nur Sätze geladen werden, die einmal im Quell-Bestand vorkommen
Click to edit title
Insert Picture Here
Click to edit Master text styles
SELECT F1 FROM
(SELECT count(F1) n,F1 FROM s GROUP BY F1)
WHERE n > 1;
74
Inhaltliche Abhängigkeit von zwei Feldern
Die satzübergreifende Reihenfolge von den Werten einer Spalte muss mit der
Reihenfolge in einer anderen Spalte übereinstimmen
Click to edit title
Click to edit Master text styles
75
Insert Picture Here
Summenvergleich graphisch
Bestellung
Bestellnummer (PK)
Gesamtsumme
Bestellposition
Click to edit
title
Bestellnummer (FK)
Positionssumme
Click to edit Master text
styles
76
=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.
Bestellpositionen
Click
toder
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
SPARTE_NR FK
Click to edit title
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
Insert Picture Here
Click to edit Master text styles
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
78
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
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
SPARTE_NR FK
Click to edit title
ARTIKEL_NAME
ARTIKEL_ID PK
GRUPPE_NR FK
PREIS
Insert Picture Here
18
Click to edit Master text styles
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
21
81
BESTELLUNG
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
2
13
BESTELLPOSITION
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
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
SPARTE_NR FK
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
1
Insert Picture Here
13
Click to edit title
18
Click to edit Master text styles
BESTELLUNG
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
21
82
4
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
2
13
BESTELLPOSITION
9
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
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
SPARTE_NR FK
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR PK
1
Insert Picture Here
13
Click to edit title
18
Click to edit Master text styles
6
KUNDE
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
21
83
2
BESTELLUNG
5
4
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
6
8
BESTELLPOSITION
11
13
9
12
BESTELLNR FK
POSITIONSNR PK 14
MENGE 15 16
17
ARTIKELNR FK 18
DEPOTSTELLE
10
19
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 werden, wenn ja, dann
Insert Picture Here
Click
Click
to
edit
title
Prüfen einer Join-Tabelle
•
to
edit Möglichkeit
Master text
styles
• Nach
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 err_orphan_PositionArtikel
to edit title
err_AVG_Pos_Wert
Insert Picture Here
err_seq_pos_Bestellposition
Click
to edit Master text styles
BEST_POS
BESTELLUNG
BESTELLPOSITION
Tmp_ BEST_POS
Tmp2_ BEST_POS
err_non_unique_bestellung
err_kd_Rabatt_ok
err_orphan_Position
err_Wert_Menge
err_maske_depotstelle
Hauptdatenfluss
Beziehungsprüfungen
Hauptdatenfluss
Hauptdatenfluss
ARTIKEL
err_not_null_Menge
err_not_Rabatt_Wert
Umsetzung der Prüfungen
Click to edit title
Click to edit Master text styles
Siehe separaten Ausdruck
Insert Picture Here
Umsetzung der Prüfungen
Click to edit title
Click to edit Master text styles
Siehe separaten Ausdruck
Insert Picture Here
Umsetzung der Prüfungen
Click to edit title
Insert Picture Here
Click to edit Master text styles
Siehe separaten Ausdruck
Umsetzung der Prüfungen
Click to edit title
Insert Picture Here
Click to edit Master text styles
Siehe separaten Ausdruck
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 1/2
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
• Zusätzliche Techniken für schnelles und sicheres Laden
• Partition Exchange and Load (PEL)
• Multiple Inserts /
91
Partition Exchange Loading (PEL)
Financial
Temporäre
Tabelle
Production
Human Res.
Neuer Monat
P1 Z1 4
Click to edit
title
P2 Z2 4
Insert Picture Here
Zeit
Store to edit Master text
P3 Z3
9
Click
styles
Supplier
Marketing
Service
P4 Z4 8
Parallel Direct Path INSERT
(Set Based)
CREATE TABLE AS SELECT
(CTAS)
CREATE Indizes / Statistiken anlegen
EXCHANGE Tabelle
• Unvergleichbar schnell!
92
Monat 13
Monat 12
Monat 11
Monat 10
DROP
PARTITION
Faktentabelle
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'));
1
-- Neue leere temporäre Tabelle erstellen
CREATE TABLE Bestellung_temp AS
SELECT * FROM Bestellung WHERE ROWNUM < 1;
2
-- Inhalte ladenClick to edit title
Insert Picture Here
INSERT 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;
Click to edit Master text styles
3
-- 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;
93
5
4
PEL – Auswirkungen auf die DWH-Verwaltung
und Konzepte
• Angleichung des Partition-Kriteriums auf den LadeRythmus und damit die „Zeit“
• Meist täglicher Load -> Tagespartitionen
• Späteres Merge auf Monatsebene möglich / nötig
Insert
Click to edit title
Picture Here
• Zwang zum späteren Monats-Merge nutzen für
Click to edit Master text styles
• Komprimierung
• Reorganisation
• ILM-Konzept
• Eine Partitionierungs-Ebene ist durch das PELVerfahren meist schon belegt
• Local-Indizierung (wenn überhaupt gebraucht)
entstehen automatisch
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
t_Ref_8
t_Ref_9
Fakt Table
Cursor
loop
Satzweise
Prüfung
Protokoll
Aufgabenstellung:
Lookup-Argumente über Funktionen gewinnen
• Mehrstufige Verarbeitung
1. Über Funktionen Argumente gewinnen
2. Argumente in Lookup-Aufrufe einbauen
• WITH-Verarbeitung
Click to edit title
Click to edit Master text styles
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
Picture
• Multiple Insert: Die Information aus einer QuelleInsert
steht immer
zum
Click
to
edit
title
Beschreiben von Parent- und Child-Tabelle zur Verfügung.
to
edit Master
text styles
• Beide
sind synchronisierbar
BESTELLUNG
BESTELLPOSITION
BESTELLNR PK
ORTNR
KUNDENNR FK
BESTELLDATUM
ANZAHLPOS
BESTELLNR FK
POSITIONSNR PK
MENGE
ARTIKELNR FK
DEPOTSTELLE
RABATT
Here
Multiple Inserts verarbeiten
INSERT ALL
WHEN 1=1‚
THEN INTO BESTELLUNG
(KUNDENCODE,BESTELL_TOTAL,STATUS)
VALUES
(KUNDENCODE,BESTELL_TOTAL,STATUS)
Insert Picture
WHEN 1=1,
INTO BESTELLPOSITION
ClickTHEN
to (BESTELLMENGE,BESTELL_TOTAL,PRODUKT_NR)
edit
Master text styles
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
99
Here
Multiple Inserts verarbeiten
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
100
Insert Picture Here
Zusammenfassung der Techniken bezogen
auf das Schichten-Modell
Data Integration
Layer
CTAS
Enterprise Information
Layer
R: Referenztabellen
T
20% R
T
PEL
Click to edit title
CTAS
User View
Layer
R
Click to edit Master text styles
S
S
S
Insert
D
DPicture Here
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
F
B
T
D
D
B
T
PEL
A
PCT
PEL
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
Kennzahlensysteme mit
Materialized
Views
Fast Refresh mit
Partition Change
Tracking (PCT)
D: Dimensionen
F: Fakten
A: Aggregate
Partitionierte
Tabellen
Prüfungen
Themenübersicht 2/2
Datenbank-basierte ETL-Prozesse
• Zugriff auf Quellsystemen
• Umgang mit Deltadaten
• Kopiertechniken / SQL Loader / Data Pump / External Tables
/ Transportable Tablespace
Click to edit title
Insert Picture Here
Click to edit Master text styles
• Planung und Organisation des ETL Prozesses
• Schichten als Planungsgrundlage
• Umgang mit separaten ETL-Tools und Lade-Engines
• Weitere Datenbank-Techniken
•
•
•
•
Ersatz von Aggregat-Tabellen durch MAVs
Table Functions
Pivoting
Merge
• Zusammenfassung
102
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
• Zeitpunkt des Ladens kann kritisch sein
Click to edit title
Insert Picture Here
• Vollständigkeit der Daten
Click to
edit Master text styles
• Unterschiedliche GRANTs der User
• Zusätzlicher Netzwerkverkehr
• Meist ist nur das Delta der geänderten Daten gewünscht
• Formate (Datum, Zeichensätze)
104
Einlesetechniken Vorsysteme
EBCDIC
ASCII
SQL-Loader
External Table
Database Link
Transportable TS
textDatapump
styles
OracleClick to edit title
Click to edit Master
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
Datenbank-Trigger?
• Werden nur im Quellsystem angelegt
• Beeinflusst Performance des Quellsystems
• Eher als Notlösung anzusehen
Click
• Wenn es kein Änderungsdatum in der Quelltabelle
gibt Picture
Insert
Click
to
edit
title
• Zum Triggern Message-basierter oder Event-gesteuerter
to Ladeläufe
edit Master text styles
CREATE OR REPLACE TRIGGER Bestellung
BEFORE DELETE OR INSERT OR UPDATE ON Bestellung
FOR EACH ROW
WHEN (new.Bestellnr > 0)
DECLARE
sal_diff number;
BEGIN
INSERT INTO log_Bestellung
(Alte_Bestell_Nr,Neue_Bstell_Nr)
VALUES(old.Bestellnr,new.Bestellnr);
END;
/
106
Here
SQL*Loader
• Loader Modes
• Convential Path
• INSERT von Daten / UPDATE von Indizes / Auslösen von Triggern
• Auswertung von Constraints
• Direct
Path to
Click
edit title
Insert Picture Here
• Formatieren der Daten in Blöcken und direktes Einfügen in die
Click to edit
Master text styles
Datafiles
• Keine SGA-Operationen / kein INSERT auf SQL-Level
• Parallel Direct Path
• Parallele SQL*Loader-Aufrufe
107
SQL*Loader – Empfehlungen
• Direct Path Load nutzen
• Alle Integrity Constraints ausschalten
• NOT NULL, Unique und Primary Key Constraints
• Verhindern von Index-AktualisierungenInsert Picture Here
Click to edit
titlewählen
• UNRECOVERABLE
Option
Click
to edit Master text styles
• Partitionen nach und nach laden
• Andere Partitionen bleiben für andere Benutzer im Zugriff
• Parallel laden, wenn es möglich ist
• Nutzung paralleler Schreib-Threads
• Alternativ parallele Jobs starten
108
Beispiel - Control File
OPTIONS (SKIP=1, BINDSIZE=50000, ERRORS=50, ROWS=200, DIRECT=TRUE,
PARALLEL=TRUE, READSIZE=65536, RESUMABLE=TRUE,
RESUMABLE_TIMEOUT=7200)
UNRECOVERABLE LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'C:\orte.csv'
Insert Picture Here
BADFILE 'orte.bad'
DISCARDFILE
'orte.dis‚
Click
to edit Master
text styles
INTO TABLE dwh.tb_orte WHEN ort_id != BLANKS
APPEND
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
(orte_nr POSITION(1) INTEGER EXTERNAL ,
ort CHAR ,
plz CHAR ,
bundesland CHAR ,
region CHAR ,
nummernfeld INTEGER EXTERNAL )
Click to edit title
109
Aufruf des SQL*Loaders
sqlldr userid=DWH/DWH control=c:\orte.ctl log=C:\orte.log
orte.ctl
Click to edit title
orte.csvInsert
Picture Here
Click to edit Master text styles
Control
File
110
Daten
TB_ORTE
Data Pump Architektur
Click to edit title
Insert Picture Here
Click to edit Master text styles
Quelle: http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-05-26.3042156388
111
Vereinfachte Verfahrensdarstellung
DWH
OLTP
Click to edit title
Click to edit Master text styles
Export mit
Data Pump
(expdp)
112
FTP
Insert Picture Here
Import mit
Data Pump
(impdp)
Schema OLTP
Schema DWH
Besondere GRANTs
Delta-Load
Oracle Data Pump
• Höhere Performance als bei IMP / EXP oder anderen
Entlade-Verfahren
• Daten und / oder Metainformationen von DB Objekten
• Größere Steuerungsmöglichkeit, d.h. mehr Parameter und
Insert Picture Here
Kontrolle
derto
Datenextraktion
Click
edit title
Click
to edit Master
text styles
• Leichtere
Einbindung
der Datenflüsse über
Rechnergrenzen hinweg
• Parallelisierung in RAC-Umgebungen Instanz-übergreifend
• Kompression u. Verschlüsselung nach Bedarf
• Legacy Mode zur Weiterverwendung von Ex-/Import
Controls
• Wiederanlauffähig
113
Release 2
Export der Daten
• Optional Flashback zum Absichern des EntladeZeitpunktes nutzen
• Remote-Export möglich (per NETWORK_LINK)
• Wegfall von separatem FTP-Aufruf
Click to edit title
Insert Picture Here
• Einschränkung
durch
Query-Bedingung
Click
to edit Master text
styles
• Damit Zugriff z. B. auf „Last Update-Sätze“
• Default Export Location
• D:\o11\admin\o11\dpdump\EXPDAT.DMP
114
Ablauf des Exports
Click to edit title
expdp dwh/dwh@o11 parfile=Para_EX.txt
Click to edit Master text styles
impdp dwh2/dwh2@o11 DIRECTORY=DP_OUT
DUMPFILE=EXP1.DMP
LOGFILE=DP_OUT:imp_log
REMAP_SCHEMA=DWH:DWH2
115
Insert Picture Here
• Export über Parameter-Datei
• Export auch mit RemoteZugriff
• Einschränkung der
Datenmenge durch QUERY
• Bei dem Import: REMAP auf
das Schema
Parameterauswahl
SCHEMAS=HR DUMPFILE=expinclude.dmp
DIRECTORY=dpump_dir1
LOGFILE=expinclude.log
INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"
INCLUDE=PROCEDURE
Insert Picture
INCLUDE=INDEX:"LIKE 'EMP%'“
ClickNETWORK_LINK=source_database_link
to edit Master text styles
NOLOGFILE={y | n}
PARALLEL=integer
PARFILE=[directory_path]file_name
QUERY = [schema.][table_name:] query_clause
QUERY=employees:"WHERE department_id > 10 AND salary > 10000“
REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function
Click to edit title
Here
Interaktiver Modus von Data Pump
• CTRL-C zum Starten des interaktiven Modus
• ADD_FILE
•
Das Hinzufügen eines neuen Dump-Files ist möglich
• KILL_JOB
•
Prozess kann abgebrochen werden
Click to edit title
• STOP_JOB
•
Aktueller Job wird beendet
•
Einstellung des Parallelisierungsgrads
Insert Picture Here
Click
to edit Master text styles
• PARALLEL
• Eingabe von „continue_client“ führt zur normalen Monitor-Ausgabe
zurück
• FILESIZE
• HELP
• PARALLEL
• START_JOB
• STATUS
• STOP_JOB
119
Laufzeitenbeispiel
C:\Users\aschlauc>expdp dwh/dwh
"DWH"."SYS_EXPORT_SCHEMA_01": dwh/******** wird gestartet
SchStzung erfolgt mit Methode BLOCKS...
Objekttyp SCHEMA_EXPORT/TABLE/TABLE_DATA wird verarbeitet
Gesamte SchStzung mit BLOCKS Methode: 4.570 GB
Objekttyp SCHEMA_EXPORT/USER wird verarbeitet
Objekttyp SCHEMA_EXPORT/SYSTEM_GRANT wird verarbeitet
Objekttyp SCHEMA_EXPORT/ROLE_GRANT wird verarbeitet
Objekttyp SCHEMA_EXPORT/DEFAULT_ROLE wird verarbeitet
Objekttyp SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA wird verarbeitet
Objekttyp SCHEMA_EXPORT/TABLE/TABLE wird verarbeitet
Objekttyp SCHEMA_EXPORT/TABLE/COMMENT wird verarbeitet
Objekttyp SCHEMA_EXPORT/PROCEDURE/PROCEDURE wird verarbeitet
Objekttyp SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE wird verarbeitet
Objekttyp SCHEMA_EXPORT/TABLE/INDEX/INDEX wird verarbeitet
Objekttyp SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT wird verarbeitet
Objekttyp SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS wird verarbeitet
Click to edit title
Insert Picture Here
Click to edit Master text styles
Objekttyp SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS wird verarbeitet
. . "DWH"."F_UMSATZ"
1.807 GB 51200000 Zeilen exportiert
. . "DWH"."T10"
1.256 GB 8999991 Zeilen exportiert
. . "DWH"."LIEFERUNG"
421.5 MB 1999999 Zeilen exportiert
. . "DWH"."LIEFERUNG_COMP"
421.5 MB 1999999 Zeilen exportiert
. . "DWH"."LIEFERUNG_MITTEL_COMP"
70.45 MB 1999999 Zeilen exportiert
. . "DWH"."LIEFERUNG_MITTEL"
70.45 MB 1999999 Zeilen exportiert
. . "DWH"."D_REGION"
469.8 KB
7202 Zeilen exportiert
. . "DWH"."D_KUNDE"
209.9 KB
1029 Zeilen exportiert
. . "DWH"."BESTELLUNGEN"
5.859 KB
4 Zeilen exportiert
. . "DWH"."D_ARTIKEL"
16.40 KB
129 Zeilen exportiert
. . "DWH"."D_VERTRIEBSKANAL"
6.859 KB
7 Zeilen exportiert
. . "DWH"."KUNDE"
5.015 KB
2 Zeilen exportiert
. . "DWH"."LIEFERUNGEN"
5.859 KB
4 Zeilen exportiert
. . "DWH"."PRODUKT"
5.031 KB
3 Zeilen exportiert
. . "DWH"."D_ZEIT"
0 KB
0 Zeilen exportiert
Master-Tabelle "DWH"."SYS_EXPORT_SCHEMA_01" erfolgreich geladen/entladen
******************************************************************************
Testlaufzeiten
Gesamtlaufzeit für
4,5 GB
Schreiben auf SSD
4 Cores
Dumpfile
GBtitle
Click
to 4,5
edit
Click to edit Master text styles
Parallel 0
Parallel 2
Parallel 4
Insert Picture Here
1: 42 Minuten
0: 56 Minuten
0: 52 Minuten
Alternative mit klassischem EXP:
exp
3,5 Minuten
External Tables
• Tabelle, die eine Datei referenziert
• Datei wird als normale Tabelle behandelt
• Nur lesend zugreifbar
Insert Picture Here
• RMAN
sichert
Click
to nicht
edit die
titleDaten
• Bulk-Loading
Click
to edit MasterOperationen,
text styles wie insert... select
• Mehr Transformationsoptionen
als im SQL* Loader
• Parallelisierbares Lesen
• Alternative zum SQL*Loader
122
External Tables – Beispiel 1
CREATE DIRECTORY Exttab AS
'D:\Szenario\Exttab';
DROP TABLE Gemeinde_EX;
CREATE TABLE Gemeinde_EX (
Gemeinde_Nr
VARCHAR2(8),
Gemeinde
VARCHAR2(50) )
Click to edit title
ORGANIZATION
EXTERNALtext styles
Click
to edit Master
(TYPE oracle_loader
DEFAULT DIRECTORY Exttab
ACCESS PARAMETERS
(RECORDS DELIMITED BY newline
BADFILE 'Gemeinde.bad‚
DISCARDFILE 'Gemeinde.dis‚
LOGFILE 'Gemeinde.log‚
SKIP 20
FIELDS TERMINATED BY ";"
OPTIONALLY ENCLOSED BY '"‚
)
LOCATION ('Gemeinde_CSV.TXT')
)
123
GemeindeID;Gemeinde;KundenID;KreisID
01001000;Flensburg;;0;1001
01002000;Kiel;;0;1002
01003000;Luebeck;;0;1003
01004000;Neumuenster;;0;1004
01051001;Albersdorf;;0;1051
01051002;Arkebek;;0;1051
01051003;Averlak;;0;1051
01051004;Bargenstedt;;0;1051
01051005;Barkenholm;;0;1051
01051006;Barlt;;0;1051
01051008;Bergewoehrden;;0;1051
01051010;Brickeln;;0;1051
01051011;Brunsbuettel;;0;1051
Insert Picture Here
...........................
...........................
Modifikationsmöglichkeiten
create or replace directory
LC_TEXTE_2
AS 'D:\Szenario\Testdaten';
Click to edit title
Click to edit Master text styles
Insert Picture Here
alter table ex_orte default
directory LC_Texte_2;
alter table ex_orte
location ('ORTE_Y.CSV');
124
Konzept zum einspielen von Dateien
1. Änderungsprozedur zum
Click to edit title
ABC120109
ABC130109
ABC140109
ABC150109
Click to edit Master text styles
• Kopieren der Dateien
• Umbenennen
von
Insert Picture
Dateinamen
Here
2. Änderungsprozedur zum
• Ändern der Einträge in der
External Table
• Ändern des Pfades im
Directory-Objekt
Sich täglich ändernde Dateinamen
Datum im Dateinamen
Lieferantenname im Dateinamen
125
Preprocessing für External Tables
Release 2
CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER
...)
ORGANIZATION external (TYPE oracle_loader
DEFAULT DIRECTORY data_file_dir
Insert Picture Here
ACCESS
PARAMETERS
Click
to
edit
title
(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
Click toPREPROCESSOR
edit Master text
styles
exec_file_dir:'gunzip'
OPTIONS '-C'
BADFILE log_file_dir:'sh_sales.bad_xt'
LOGFILE log_file_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
( PROD_ID, CUST_ID, TIME_ID DATE(10) "YYYY-MM-DD",
CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
UNIT_COST, UNIT_PRICE))
location ('sh_sales.dat.gz'))
REJECT LIMIT UNLIMITED;
126
External Tables mit Data Pump
• Erstellen External Table in Quell-DB
• Verwendung von CREATE AS SELECT * FROM
<source_table>
• Das Ausführen des CREATE startet den Data Pump-Export
•
Insert Picture Here
Click
to
edit
title
Kopieren der Dump-Datei auf die Zielumgebung
Click to edit Master text styles
• In der Zielumgebung neue External Table-Definition
erstellen und aktivieren
• Durch Zugriff mit SELECT auf die External Table die
Daten lesen
127
External Tables mit Data Pump
DWH
OLTP
Click to edit title
EX_T
Click to edit Master
text styles
FTP
128
Insert Picture Here
EX_T
External Tables mit Data Pump
OLTP
DWH
select * from EX_Bestellung_2
Click to edit title
Click to edit Master text styles
129
Insert Picture Here
Testzenario
DWH
OLTP
DB_Link
Bestellung
Click to edit title
Insert
CTAS
Bestellung
Click to edit Master text styles
OLTP
DWH
EX_T
EX_T
Bestellung
Impliziter
FTP-Lauf
Picture Here
Bestellung
Vorteile der Kombination
• Leichte Handhabung
• Syntax der beiden Typen sehr ähnlich
• Hohe Performance
Click
• Data Pump-eigenes Format ist für schnellen Ex-/Import
Insert ausgelegt
Picture Here
Click
to
edit
title
• Parameter von Data Pump zusätzlich nutzen, um die zu
to extrahierende
edit MasterDatenmenge
text stylesauf das Wesentliche zu reduzieren
• Verbleiben innerhalb der SQL-Sprache
• Durch CREATE TABLE AS SELECT lassen sich sowohl WHEREFilter als auch Joins während des Extrahierens verarbeiten
131
Transportable Tablespaces
• Höchste Performance beim Austausch von Oracle
zu Oracle
• Daten werden als komplettes File oder File Set bewegt
• Austausch zwischen unterschiedlichenInsert Picture Here
Click
to
edit
title
Betriebssystemen möglich
Click to
edit Master text styles
• Konvertierung kann mit RMAN erfolgen, z.B. von BigEndian
nach LittleEndian
• Nützlich beim Bewegen der Daten zwischen
Quellsystem und Staging Area sowie zwischen den
anderen Schichten im Warehouse
132
Vorgehensweise
1. Anlegen des Tablespaces im Quellsystem
2. Zuweisung der zu kopierenden Daten zum Tablespace
• Alle Daten sind dem Tablespace zugeordnet (Indizes etc.)
Insert Picture Here
3. Ändern
des
Click
toTablespaces
edit title auf Read-Only
4. to
Export
der Metadaten
mit Data Pump (EXPDP)
Click
edit Master
text styles
5. Eventuell Konvertierung des Tablespace Datafiles
• Über die RMAN CONVERT Function
6. Kopieren des Tablespace Datafiles und der Metadaten
7. Import der Metadaten in der Zielumgebung
8. Ändern des Tablespaces auf Read-Write
133
Transportable Tablespaces
Buchhaltung
1
Produktion
Personal
CREATE TABLE temp_jan_umsatz
NOLOGGING
TABLESPACE ts_temp_umsatz
AS
SELECT * FROM ?????????
WHERE time_id BETWEEN '31-DEC1999' AND '01-FEB-2000';
P1 Z1 4
Click to edit
title
P2 Z2 4
Insert Picture Here
Lager
P3 Z3
9
Click
to edit Master text
styles
Index/Constraint free
Lieferanten
P4 Z4 8
Marketing
Service
2
ALTER TABLESPACE ts_temp_umsatz READ ONLY;
3
Kopieren des Tablespace zur Zielplattform
4
135
Parallel Direct Path Insert
Set Based
BS-Copy
EXP TRANSPORT_TABLESPACE=y
TABLESPACES=ts_temp_umsatz
FILE=jan_umsatz.dmp
Daten
Meta
daten
Transportable Tablespaces
5
IMP TRANSPORT_TABLESPACE=y DATAFILES='/db/tempjan.f'
TABLESPACES=ts_temp_umsatz
FILE=jan_umsatz.dmp
6
ALTER TABLESPACE ts_temp_umsatz READ WRITE;
Click to edit title
Click to edit
Master text styles
ALTER TABLE umsatz ADD PARTITION
7
Metadaten
Insert Picture Here
umsatz_00jan VALUES
LESS THAN (TO_DATE('01-feb-2000','dd-mon-yyyy'));
ALTER TABLE umsatz EXCHANGE PARTITION umsatz_00jan
WITH TABLE temp_umsatz_jan
INCLUDING INDEXES WITH VALIDATION;
Neuer Monat
012000
121999
111999
101999
091999
Fakttable Umsatz
136
Themenübersicht 2/2
Datenbank-basierte ETL-Prozesse
• Zugriff auf Quellsystemen
• Umgang mit Deltadaten
• Kopiertechniken / SQL Loader / Data Pump / External Tables
/ Transportable Tablespace
Click to edit title
Insert Picture Here
Click to edit Master text styles
• Planung und Organisation des ETL Prozesses
• Schichten als Planungsgrundlage
• Umgang mit separaten ETL-Tools und Lade-Engines
• Weitere Datenbank-Techniken
•
•
•
•
Ersatz von Aggregat-Tabellen durch MAVs
Table Functions
Pivoting
Merge
• Zusammenfassung
137
Frühzeitige ETL-Aktivitäten schaffen Synergien
Das Schichtenmodell hilft bei der Positionsfindung für
Transformationen und Aggregationen
Die frühest mögliche Stelle für Transformationen und Prüfungen finden
Data Integration Layer
Enterprise Information Layer
User View Layer
R: Referenztabellen
R
Click Tto edit Rtitle
T: Transfertabellen
S
T
Click to edit Master
text styles
S
InsertDPicture Here
ETL: Kosten D
B
B
F
B
B
ETL: Kosten
pro Kunde
D
B
138
D
D
B
ETL: Kosten
pro Kunde
Prüfungen
B: Bewgungsdaten
S
T
ETL: Kosten
pro Kunde
S: Stammdaten
pro Kunde
D: Dimensionen
F: Fakten
Transformation
F
D
F
D
D
Frühzeitige ETL-Aktivitäten schaffen Synergien
Das Schichtenmodell hilft bei der Positionsfindung für
Transformationen und Aggregationen
Die frühest mögliche Stelle für Transformationen und Prüfungen finden
Data Integration Layer
Enterprise Information Layer
User View Layer
R: Referenztabellen
Insert Picture Here
Click to edit title
S
T
T: Transfertabellen
R
R
T
S
S
Click to edit Master
text styles
T
B
D
F
139
Distincts
Joins
S: Stammdaten
B: Bewgungsdaten
D
F
Prüfungen
D
D
D
B
B
F
B
B
B
D
D: Dimensionen
F: Fakten
D
D
Effizientes Laden beginnt so früh wie
möglich
Data Integration
Layer
Data Integration
Layer
1:1
T
1:1
Click to edit title
Insert Picture Here
CTAS
T
Click to edit Master
text styles
T
1:1
1:1
T
T
Warum?
140
T
Logik so früh wie möglich
Verteilte Server zwingen oft zu unproduktiven
1:1 Ladevorgängen
Viele unnötige und versteckte Aufwände
User View Layer
Click to edit title
1:1
Insert
Picture Here
Data Integration Layer
Enterprise Information Layer
Vorsystem
Click
to edit Master
text styles
User View Layer
1:1
Process neutral / 3 NF
1:1
Vorsystem mit
Vorrechner
1:1
1:1
User View Layer
Eine Hardware (bzw. Cluster) / ein Ort
ermöglicht flexibles Handeln durch kurze
Wege
Freie
Wahlmöglichkeit
für Ort und Art des ETL
Data Integration Layer
Enterprise Information Layer
Click
to edit title
Click to edit Master text styles
Process neutral / 3 NF
User View Layer
Insert
Picture Here
Zu viele teuere Ladestrecken
• Redundante Wege
• Gefahr von 1:1 Kopien
• Schwache Netze
DWH Server
Data Integration Layer
Enterprise Information Layer
Click
to
edit
title
c
User View Layer
Insert
Picture Here
Click to edit Master text styles
OLTP
Process neutral / 3 NF
Separater ETL Server
Balance zwischen den beteiligten
Komponenten finden
DWH-Datenbank
Click to edit title
Insert Picture Here
Click to edit Master text styles
ETL-Engine
ETL-Server
DWH-Server
Balance zwischen den beteiligten
Komponenten finden
DWH-Datenbank
Click to edit title
Insert Picture Here
Click to edit Master text styles
ETL-Engine
ETL-Server
DWH-Server
Balance zwischen den beteiligten
Komponenten finden
Dokumentation
Steuerung
Benutzerfühung
Click to edit title
Rechen-Power
Ausnutzen von bestehenden
Ressource
DWH-Datenbank
Insert Picture Here
Click to edit Master text styles
ETL-Engine
ETL-Server
DWH-Server
Aufwendige Extraktionsverfahren
Insert Picture Here
Nur ein Ladeschritt !
Click to edit title
Oracle
OLTP
Click to edit Master text styles
Oracle
DWH
2. Ladeschritt
1. Ladeschritt
ETL
Tool
Server
Themenübersicht 2/2
Datenbank-basierte ETL-Prozesse
• Zugriff auf Quellsystemen
• Umgang mit Deltadaten
• Kopiertechniken / SQL Loader / Data Pump / External Tables
/ Transportable Tablespace
Click to edit title
Insert Picture Here
Click to edit Master text styles
• Planung und Organisation des ETL Prozesses
• Schichten als Planungsgrundlage
• Umgang mit separaten ETL-Tools und Lade-Engines
• Weitere Datenbank-Techniken
•
•
•
•
Ersatz von Aggregat-Tabellen durch MAVs
Table Functions
Pivoting
Merge
• Zusammenfassung
148
Aggregattabellen
• Die meisten Kennzahlen sind bekannt
• In der Datenbank als Aggregattabellen vorbereiten
• Nicht über das BI-Tool umsetzen (meist langsamer)
Insert Picture Here
• Keine eigenständigen Aggregat-Tabellen
Click to edit title
• Haben separate Namen -> Zwang zu Änderungen in BI-Tools
Click to edit
Master
text styles
• Fehlende
Transparenz
• Aktualität wird nicht automatisch festgestellt
• Aggregate-Tabellen liefern nur genau die Daten, die tatsächlich
enthalten sind. Ableitungen, z. B. zusätzliche Aggregationen sind nicht
möglich (Rewrite-Technik)
Materialized Views entlasten den
ETL-Prozess
Länder
AnalyticalFunctions
Zeit
Regionen
Click to edit title
Click to edit Master text stylesOrte
Star-Transformation
Bitmap-Index
Partitions
Produkt
FK_Ort
FK_Zeit
FK_Produkt
Level 3
Insert Definitionen
Picture Here
Attribute
Level 2
Definitionen
Attribute
Level 1
Definitionen
Attribute
Query
Rewrite
Materialized
View
Umsätze
Parallel+
Cluster
Dimension
Ort
Kunde
Materialized Views sparen Plattenplatz und
minimieren die Objektanzahl
Top/Alle_Artikel
Segement
Click to edit title
Summe pro Sparte
Click to edit Master text Artikelsparte
styles
Artikelgruppe
Summe pro Artikel
Artikel
Summe pro Charge
Artikelcharge
Menge
Umsatz
Insert Picture Here
Review
Summe pro Gruppe
Query
Summe pro Segement
Summe pro Charge
Nested Materialized Views nutzen bereits
ausgeführte IO-Leistung
Umsatz Prod. Gr B relativ zum Gesamtjahresumsatz Materialized View
Level 4
Summierung/Jahr
Click to edit title
Insert Picture Here
Materialized View
Level 3
Click to edit Master text styles
Umsatz Prod.Gr A
Umsatz Prod.Gr B
Summierung/Monat
Aufwendige Join-Operation
DIM_Zeit
FAKT_Umsatz
DIM_Produkte
Materialized View
Level 2
Materialized View
Level 1
Basistabellen
IO
152
Table Functions – Pipeline-Verfahren
Parallelisierung trotz aufwendiger Programmierlogik
tf1
Click to edit title
tf2 Insert Picture Here
Click to edit Master text styles
Quelle
Ziel
tf3
Stage_tabelle
INSERT INTO Ziel SELECT * FROM (tf2(SELECT * FROM (tf1(SELECT
INSERT INTO Ziel SELECT
153
* FROM Quelle))))
* FROM tf( SELECT * FROM (Stage_tabelle))
Begriffe im Bereich Table Functions
•
Table Function
•
Record Type
•
Nested
Tableto
Click
• Funktionen, die eine Gruppe von Sätzen (SET) gleichzeitig bearbeitet.
Table Functions wirken wie physische Tabellen. Entsprechend werden sie
auch in der FROM Klausel verwendet.
• Ein komplexer, aus mehreren Feldern zusammengesetzter Datentyp.
•
Insert Picture Here
edit
title
Eine Art virtuelle Tabelle (temporäre Tabelle im Speicher). Eine Table
einestyles
solche Tabelle komplett an das aufrufende
Click to edit Function
Masterkann
text
Kommando zurückgeben.
154
•
Ref Cursor
•
Parallel
•
Pipelined
• Eine Art Pointer auf ein Result – Set einer Abfrage. Man übergibt einen
Ref Cursor einer Table Function, damit diese die Sätze des Result – Sets
innerhalb der Function abarbeitet.
• Table Functions können eingehende Sätze parallel bearbeiten, wenn
diese als Ref Cursor übergeben werden.
• Eine Table Function reicht bereits fertige Sätze an das aufrufende
Kommando zur weiteren Verarbeitung weiter, während sie noch weitere
Sätze bearbeitet.
Mengenbasierte Verarbeitung
Trotz Programmierung
INSERT INTO Table
SELECT Feld1, Feld2
FROM Table_Function(
Click toFunktion
edit title
Click to edit Master text styles
Cursor
Fetch
Loop
Variante 1
Variante 2
155
If a = b...
Update...
Case...
pipe
row(record Type)
Return Table
)
• Schnelle
Verarbeitung
Insert
Picture Here
(Pipelined)
• Objekttechnik
• Parallelisierung
• Mehrere
Rückgabewerte
und Einzelrückgaben
• Cursor als Input
• Schachtelbar
Die Hilfstypen für Daten und Cursor
drop type Bestellung_X_t;
create type Bestellung_X_t as object (
BESTELLNR
NUMBER(10),
KUNDENCODE
NUMBER(10),
BESTELLDATUM
DATE,
LIEFERDATUM
DATE,
BESTELL_TOTAL
NUMBER(12,2),
Fehler_Datum
DATE);
Click to edit title
Click to edit Master text styles
drop type Bestellung_X_t_table;
create type Bestellung_X_t_table
as TABLE of Bestellung_X_t;
create or replace package cursor_pkg as
type Bestellung_t_rec IS RECORD (
BESTELLNR
NUMBER(10),
KUNDENCODE
NUMBER(10),
BESTELLDATUM
DATE,
LIEFERDATUM
DATE,
BESTELL_TOTAL
NUMBER(12,2));
END;
156
Definition
Record-Type
Insert Picture Here
Definition
Nested-Table auf
der Basis des
Rekord-Types
Definition
Cursor als Typ
des
Übergabeparameters
Die Table Function
create or replace function f_Bestellung_X(cur cursor_pkg.refcur_t)
RETURN Bestellung_X_t_table IS
BESTELLNR
NUMBER(10);
KUNDENCODE
NUMBER(10);
BESTELLDATUM
DATE;
LIEFERDATUM
DATE;
BESTELL_TOTAL
NUMBER(12,2);
Fehler_Datum
DATE;
ORDER_ID
NUMBER(10);
Click to edit title
Übernahme von Ausgangssätzen
als Cursor
Insert Picture Here
Definieren einer Nested-Table-Struktur
objset Bestellung_X_t_table := Bestellung_X_t_table();
für die spätere Rückgabe.
i number := 0;
begin
LOOP
Lesen aus Cursor
-- read from cursor variable
FETCH cur into BESTELLNR,KUNDENCODE, BESTELLDATUM,LIEFERDATUM,BESTELL_TOTAL,ORDER_ID;
-- ext when last row
EXIT WHEN cur%NOTFOUND;
i := i+1;
if substr(to_char(LIEFERDATUM,'YYYY.MM.YY'),1,4) >2002 then
Fehler_Datum := to_date('9999.12.12','YYYY.MM.DD');
Erweitern Nested-Table um einen
else Fehler_Datum := LIEFERDATUM;
Satz und Überführen eines Satzes in
End if;
die Nested-Table
objset.extend;
objset(i) :=
Bestellung_X_t(BESTELLNR,KUNDENCODE, BESTELLDATUM,LIEFERDATUM,BESTELL_TOTAL,Fehler_Datum);
END LOOP;
Rückgabe der kompletten Tabelle an
CLOSE cur;
das aufrufende Statement
Return objset;
(Alternative zu PIPE).
END;
Click to edit Master text styles
157
Beispielaufrufe
insert into bestellung_X
Insert Picture Here
select * from
Click
to
edit
title
TABLE(f_Bestellung_X(CURSOR(SELECT * from Bestellung)))
Click to edit Master text styles
select * from
TABLE(f_bestellung(CURSOR(SELECT * from Bestellung)))
select count(*) from
TABLE(f_bestellung(CURSOR(SELECT * from Bestellung))
158
Verhindern des mehrfachen Ladens
Prüfung 1
Bestellung
Insert into Ergebnis
select * from bestellung b, bestellposition p
where b.PK = p.FK
Prüfung 2
Click to edit
title
Insert into Ergebnis
Bestellposition
Insert Picture Here
select * from bestellung b, bestellposition p
Click to edit Master text styles
where b.PK = p.FK
Der Join wird
4 mal ausgeführt
Prüfung 3
Insert into Ergebnis
select * from bestellung b, bestellposition p
where b.PK = p.FK
Prüfung 4
Insert into Ergebnis
select * from bestellung b, bestellposition p
where b.PK = p.FK
Verhindern des mehrfachen Ladens
Bestellung
select * from
TABLE(f_bestellung(CURSOR(select *
from bestellung b, bestellposition p
where b.PK = p.FK)))
Click to edit title
Bestellposition
Click to edit Master text styles
Der Join wird
1 mal ausgeführt
Insert
f_bestellung
Picture Here
Einlesen Cursor
Prüfung 1
Prüfung 2
Prüfung 3
Prüfung 4
Pipe
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
SALESREP
Q1
Q2
Q3
Q4
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
Click to edit title
Click to edit Master text styles
161
SALESREP
---------100
100
100
100
101
101
101
101
102
102
102
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
260
Q4
300
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
Q2
280
Q3
265
Q4
310
Insert Picture Here
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
QUARTERLY_SALES
SALESREP
Q1
Q2
Q3
Q4
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
Click to edit title
Click to edit Master text styles
SALESREP
---------100
100
100
100
101
101
101
101
102
102
102
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
260
Q4
300
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
Q2
280
Q3
265
Q4
310
Insert Picture Here
select * from quarterly_sales
unpivot include nulls
(revenue for quarter in (q1,q2,q3,q4))
order by salesrep, quarter ;
162
Native Support für Pivot und Unpivot
Sinnvoller Einsatz im Rahmen
des ETL-Prozesses
SALESREP 'Q1' 'Q2' 'Q3' 'Q4'
---------- ----- ----- ----- ----100
230
240
260
300
101
200
220
250
260
102
260
280
265
310
Click to edit title
Click to edit Master text styles
SALES_BY_QUARTER
SALESREP
---------100
100
100
100
100
100
100
101
101
101
101
102
QU
REVENUE
-- ---------Q1
230
Q2
240
Q3
160
Q4
90
Q3
100
Q4
140
Q4
70
Q1
200
Q2
220
Q3
250
Q4
260
Q1
260
Insert Picture Here
select * from sales_by_quarter
pivot (sum(revenue)
for quarter in ('Q1','Q2','Q3','Q4'))
order by salesrep ;
163
MERGE INTO "Kunde_TGT" USING
(SELECT
"KUNDEN_STAMM"."KUNDENNR" "KUNDENNR",
"KUNDEN_STAMM"."VORNAME" "VORNAME",
"KUNDEN_STAMM"."NACHNAME" "NACHNAME",
"KUNDEN_STAMM"."STATUS" "STATUS",
"KUNDEN_STAMM"."STRASSE" "STRASSE",
"KUNDEN_STAMM"."TELEFON" "TELEFON",
"KUNDEN_STAMM"."TELEFAX" "TELEFAX„
FROM "KUNDEN_STAMM" "KUNDEN_STAMM") MERGE_SUBQUERY
ON ( "Kunde_TGT"."KUNDENNR" =
"MERGE_SUBQUERY"."KUNDENNR")
WHEN NOT MATCHED THEN
INSERT
("Kunde_TGT"."KUNDENNR",
"Kunde_TGT"."VORNAME",
"Kunde_TGT"."NACHNAME",
"Kunde_TGT"."STATUS",
"Kunde_TGT"."STRASSE",
"Kunde_TGT"."TELEFON",
"Kunde_TGT"."TELEFAX")
VALUES
("MERGE_SUBQUERY"."KUNDENNR",
"MERGE_SUBQUERY"."VORNAME",
"MERGE_SUBQUERY"."NACHNAME",
"MERGE_SUBQUERY"."STATUS",
"MERGE_SUBQUERY"."STRASSE",
"MERGE_SUBQUERY"."TELEFON",
"MERGE_SUBQUERY"."TELEFAX")
WHEN MATCHED THEN UPDATE
SET
"VORNAME" = "MERGE_SUBQUERY"."VORNAME",
"NACHNAME" = "MERGE_SUBQUERY"."NACHNAME",
"STATUS" = "MERGE_SUBQUERY"."STATUS",
"STRASSE" = "MERGE_SUBQUERY"."STRASSE",
"TELEFON" = "MERGE_SUBQUERY"."TELEFON",
"TELEFAX" = "MERGE_SUBQUERY"."TELEFAX";
MERGE-Funktion
• Funktion MERGE dient
dem gleichzeitigen
INSERT und UPDATE
• Basierend auf dem
Matching
des definierten
Click
to edit title
(ON-Klausel)
ClickSchlüssels
to edit Master
text styles
• Auch DELETEOperationen möglich
164
Insert Picture Here
Themenübersicht 2/2
Datenbank-basierte ETL-Prozesse
• Planung und Organisation des ETL Prozesses
Insert Picture Here
Schichten als Planungsgrundlage
Click to edit•• title
Umgang mit separaten ETL-Tools und Lade-Engines
Click to edit Master text styles
• Szenario zum Prüfen von Daten
• Szenario zum Prüfen von Daten
• Weitere Datenbank-Techniken
•
•
•
•
Ersatz von Aggregat-Tabellen durch MAVs
Table Functions
Pivoting
Multiple Inserts / Merge
• Zusammenfassung
165
Zusammenfassung der bevorzugten LadeVarianten
Data Integration
Layer
CTAS
Enterprise Information
Layer
User View
Layer
R: Referenztabellen
20% R
T
R
S
S
S
Click Tto edit PEL
title
S: Stammdaten
B: Bewgungsdaten
CTAS
Click to edit Master text styles
T: Transfertabellen
D
D
Insert
Picture Here
F
B
T
D
D
B
T
PEL
A
PEL
Mengenbasierte
Prüfungen
Vorbereitete
temporäre
Tabellen
Partition
Exchange
Konzentration aller Prüfungen
166
CTAS : Create Table As Select
F: Fakten
A: Aggregate
Partitionierte
Tabellen
Prüfungen
80%
Vorgelagerte
Prüfungen
D: Dimensionen
Partition
Exchange
Aggregatbildung
durch Materialized
Views
Denormalisierung (Joins)
und Aggregate
PEL : Partition Exchange and Load