DB_ETL_April_2017 - Oracle Data Warehouse Community Seite

Oracle Data Warehouse –
Datenbank basierte ETL-Prozesse
Reduzieren Sie Ihre Ladezeiten! Organisieren Sie Ihre ETL-Prozesse!
DATA WAREHOUSE
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
• Speichermanagement und Grundlagentechniken
• Blöcke, Extents, Segmente, Tablespace
• Direct Path Load, Mengenbasiertes Laden
• Optimierungsszenario
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
2
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Das große Klagen
Bzgl. System-Nutzen
• Lieferzeiten der Daten zu lange (Latenzen)
• Zu schwerfällig bei Änderungen
• Informationen mehrfach vorhanden
• Fehlende unternehmensweite Sichten
• 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
ETL ist mehr als nur Daten von A nach B kopieren
nicht Point-To-Point sondern Informationsschaffend
Quellsystem
DWH-System
n-tier
n-tier
Application Server
ETL?
Application Server
ETL?
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 4
Die strategische Rolle der Warehouse-Schicht
Service
Logistik
Einkauf
Vertrieb
Integration
Layer
Die
selben
Geschäftsobjekte
in unterschiedlichen
Prozessen
?
Enterprise Layer
Core - DWH / Info Pool
Strategische
Daten
Controlling
Marketing
Wirkungsweite des
Systems muss
festgelegt sein
User View
Layer
Wunsch
für
unterschiedliche
Sichten
auf die
selben
Geschäftsobjekte
?
Eindeutigkeit in
der Kernschicht
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Service
Logistik
Einkauf
Vertrieb
Controlling
Marketing
Was verdienen wir an gelben + bunten Fahrrädern?
Logistik
WARE
Waren_Nr
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Einkauf
ARTIKEL
Artikel_Nr
Einheit
Preis
Lieferant
Vertrieb
PRODUKT
Produkt_Nr
Einheit
Farbe
Preis
Integration
T_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
Lieferant
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Vert_Einheit
Farbe
Vert_Preis
Rabatte
LieferantenDiscounts
Enterprise Layer
LIEFERANT
PK_Lieferanten_ID
Lieferant_Name
Es geht um
Gesamtsichten:
Verpackungen
S_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
FK_Lieferanten_ID
Gebinde
Gewicht
PK_Verpackungs_ID
Vert_Einheit
FK_Farben_ID
Vert_Preis
FARBE
PK_Farben_ID
Farbe
Aufschlag
User View
„Breite“ der
Datenmodelle
Gelagerte
Artikel,
Menge +
Größe
Lager
Lieferanten
Gekaufte
Artikel,
Menge +
Preise
Zeit
VERPACKUNGSART
PK_Verpackungs_ID
Hoehe
Laenge
Breite
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Artikel
Verkaufte
Artikel
Menge +
Preise
Gewinn =
Verkaufspr. –
Einkaufspr. –
Lagerkosten
Was verdienen wir an gelben + bunten Fahrrädern?
Logistik
WARE
Waren_Nr
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Einkauf
ARTIKEL
Artikel_Nr
Einheit
Preis
Lieferant
Vertrieb
PRODUKT
Produkt_Nr
Einheit
Farbe
Preis
Integration
T_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
Lieferant
Gebinde
Gewicht
Hoehe
Laenge
Breite
Verpackung
Vert_Einheit
Farbe
Vert_Preis
User View
Enterprise Layer
LIEFERANT
PK_Lieferanten_ID
Lieferant_Name
Verpackungen
S_ARTIKEL
PK_Artikel_ID
Eink_Artikel_Nr
Log_Waren_Nr
Vert_Produkt_Nr
Eink_Einheit
Eink_Preis
FK_Lieferanten_ID
Gebinde
Gewicht
PK_Verpackungs_ID
Vert_Einheit
FK_Farben_ID
Vert_Preis
FARBE
PK_Farben_ID
Rabatte
Farbe
Lieferanten- Aufschlag
Discounts
VERPACKUNGSART
PK_Verpackungs_ID
Hoehe
Laenge
Breite
D_ARTIKEL_LAGER
PK_Artikel_ID
Log_Waren_Nr
Gebinde
Gewicht
Verpackung
D_ARTIKEL_EINK
PK_Artikel_ID
Eink_Artikel_Nr
Eink_Einheit
Eink_Preis
Lieferanten
Farben
D_ARTIKEL_VERT
PK_Artikel_ID
Vert_Produkt_Nr
Eink_Einheit
Vert_Einheit
Farben
Vert_Preis
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Lager
Gelagerte
Artikel,
Menge +
Größe
Lieferanten
Gekaufte
Artikel,
Menge +
Preise
Zeit
Verkaufte
Artikel
Menge +
Preise
Es geht
um
Gesamtsichten:
„Breite“
der
Datenmodelle
Was definieren wir als ETL-Prozesse?
Enterprise Information
Layer
User View
Layer
OLTP + Data Warehouse + BI
von
L a t e n z
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 8
BI-Tool Server + Caches
Data Integration
Layer
bis
Es gibt Data Warehouse-spezifische Rahmenbedingungen
OLTP
Data Warehouse
Interaktion
Lesen und Schreiben
Eher nur Lesen / konzentriertes
Mengen-Schreiben
Verteilung der
Aktionen
Oft auf viele Tabellen
verteilt
Oft zentrisches Arbeiten auf
wenigen Tabellen
Art von Lese-/SchreibAktionen
Verteilt und nur einzelne
Sätze
Konzentriert und viele Sätze
gleichzeitig
Art der physischen
Speicherung
Satzorientiert in kleinen
Blöcken
Spaltenorientiert in sortieren Blöcken
Anzahl Benutzer
Hoch
Weniger
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
9
Was bedeutet das für die eingesetzte Technologie?
OLTP
Data Warehouse
Interaktion
Lesen und Schreiben
Eher nur Lesen / konzentriertes
Mengen-Schreiben
Parallelisierung
Gering
Hoch
Hochverfügbarkeit
Hoch
Eher gering
Storagesystem
SAN
Dedicated
Backup
komplett
selektiv
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
10
11
Bei der Wahl von Mitteln immer berücksichtigen:
10 – 50 Tabellen
500 – 1000 Tabellen
Große Tabellen
Partitioniert
Namentlich bekannt
> 70 % des Datenvolumens
KleineTabellen
Nicht Partitioniert
Unkenntliche Masse
< 30 % des Datenvolumens
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Datenbank-nahes Laden – Grundlage für Flexibilität und
Performance
• Daten dort laden, wo sie liegen
• Möglichst wenig Datenbewegung
• Mengenbasiertes Laden!
• ETL-Tools sollten Datenbank – Features mitnehmen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted
12
Ziele und Aufgaben
• Bereitstellen von Daten in adäquater
Weise
– Zeitlich passend
– Richtige Form
– Passende Inhalte
• Daten so ablegen, dass man sie
wiederfindet
– Dokumentation
• Daten Ressourcen-ökonomisch
speichern
– Berücksichtigung von Plattenplatz
13
Was wird geladen
• Es sollte nur das geladen werden, was
wirklich gebraucht wird
• Gibt es einen Auftrag für das Laden
bestimmter Daten?
– Wer braucht die Daten?
– Welche Daten werden gebraucht?
• Sind die zu ladenden Daten in einem
brauchbaren Zustand?
– Welche Anforderungen sind an Quelldaten zu
stellen?
– Wer definiert die Anforderungen?
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Hilfsmittel in der Datenbank (Auflistung)
• Parallelisierung
• Partitioning / Partition Exchange Load (PEL)
• Direct Path Load
• Set-Based SQL
• Pipelined Table Functions
• Materialized Views
• External Tables / Loader
• Transportable Tablespace
• Data Pump
• Database Link
• Direkt FTP-Load
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Ziele eines effizienten ETL-Prozesses
• Ressource-schonend
– Rechenzeit, Storage
• Schnell änderbar und pflegbar
• Kurze Laufzeiten
• Erzeugen von stimmigen Abfrage-Ergebnissen
• Erleichterung für BI-Tools
....
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
Schichten reagieren
• Alle Schichten innerhalb derselben Datenbank
• 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Enterprise Information Layer
R
R
T
T
T
S
B
B
S
User View Layer
D
S
B
B
Strategische
Daten
D
B
F
B
D
D
D
F
D
F
D
D
Taktische
Daten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 17
Zusammenhängender
Abfragebereich
Operative
Daten
Data Integration Layer
aufbereiten
integrieren
Flexibilität und schnelles Bereitsstellen
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Die Organisation des ETL-Prozesses
Data Integration
Layer
Richtig
selektieren
Die Masse
aller
Prüfungen
Enterprise Information
Layer
StammReferenzdaten
aktualisieren
User View
Layer
Nur
denormalisierende
Joins
Repository
(Glossar, alle Objekte)
Für alle Aktionen den frühest möglichen Punkt finden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 18
Möglichst viele
Kennzahlen
in die
Datenbank
Angemessen in den Situationen agieren
Data Integration
Layer
Keine
Daten
Enterprise Information
Layer
Referenzdaten
Stammdaten
Bewegungsdaten
(granulare
Transaktionsdaten)
User View
Layer
Dimensionen
Fakten
vorberechnete
Kennzahlen
vorberechnete
Kennzahlen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 19
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Angemessen in den Situationen agieren
Data Integration
Layer
Temporäre
Daten
Enterprise Information
Layer
20% Volumen f. viele
Kleine Tabellen
User View
Layer
Wieder
herstellbare
Daten
80% Volumen f.
wenige
große Tabellen
=> partitioniert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 20
Lade-Aktivitäten an Schichtübergängen
Integration
Enterprise
Persistent
Flüchtige Daten
Clearing-Verfahren,
technisches, logisches,
semantisches Prüfen
Keine Constraints
Kopieren
Selektieren
Mengenbasiertes
Prüfen ohne
Constraints
Kopien / teilpersistent
dynamisch
Denormalisieren
z.T. Aggregieren
Normalisieren
(Granularisieren)
Historisieren
Generische
Datenstrukturen
(isolierte Tabellen,
teil-ausgeprägte Datentypen)
User View
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)
Lookups -> Dimensionsdaten
Joins - Denormalisieren
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 21
Verfahren für schnelles ETL in der Datenbank
Data Integration
Layer
Selektieren
Statt
kopieren
Direct Path
in temporäre
Tabellen
Contraintfreies
Prüfen mit
Mengenbasiertem
SQL
User View
Layer
Enterprise Information
Layer
Partition
Exchange
&LOAD
in partit.
Tabellen
(PEL)
Unveränderte
Bewegungsdaten liegen
lassen
Große
Fakten-Tab.
über PEL.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kennzahlen ausschließlich
über
MAVRefresh
Bekannte
Kennzahlen
in die DB
weniger
Koipien in
BI-Tools
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
• Speichermanagement und Grundlagentechniken
• Blöcke, Extents, Segmente, Tablespace
• Direct Path Load, Mengenbasiertes Laden
• Optimierungsszenario
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
23
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Zuordnung Datenobjekten und Speicher
DB-Objekte
Table
Partition
Index
Mview
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Speicherobjekte
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Die automatische Extent-Vergrößerung
• Automatische Allokierung von weiteren Segmenten
• Exponentielles Vergrößerungs-Mass
select
t.TABLE_NAME, t.blocks,t.EMPTY_BLOCKS,t.AVG_SPACE,t.AVG_ROW_LEN,t.NUM_ROWS,
t.pct_free, t.compression,s.EXTENTS,s.bytes seg_bytes,e.blocks ext_blks,e.bytes ext_bytes
from
user_segments 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Block- und Satzstruktur
Datenbank Block
Row header
Column length
Column value
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
– Sollten dennoch UPDATES gemacht werden müssen:
• 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
High Water Mark
Nach INSERTS:
0
1
2
0
1
2
Unused block
3
4
Free space
after delete
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Segment
Used block
4
High-water mark
Nach DELETES:
Extent ID
3
Segment
Extent ID
Wie wird die „High Water Mark“ bestimmt
TOTAL_BLOCKS
UNUSED_BLOCKS
0
1
2
3
High-water mark
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
4
Segment
Extent ID
Deallocate Space
0
1
2
3
ALTER TABLE tablename
DEALLOCATE UNUSED;
0
Used block
1
High-water mark
2
3
Unused block
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
4
Segment
Extent ID
After
deallocation
4
Segment
Extent ID
Before
deallocation
Free space
after delete
Truncate Table
TRUNCATE TABLE tablename
Extent ID
0
1
Segment
High-water mark
Free space
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Direct Path Load
INSERT /*+APPEND */ INTO DWH.F_UMSATZ
NOLOGGING
SELECT * FROM OLTP.BESTELLUNGEN;
F_UMSATZ
Server
process
Segment
Used block
Free space after delete
High-water mark
Blocks used by inserted rows
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Paralleler Direct Path Load
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+APPEND PARALLEL(F_UMSATZ,2) */
INTO DWH.F_UMSATZ NOLOGGING
SELECT * FROM OLTP.BESTELLUNGEN;
Slave
process
Slave
process
F_UMSATZ
Segment
Used block
Free space after delete
High-water mark
Temporary segments
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
„Convential“ und „Direct Path“ - Load
Instance
SGA
Shared pool
Array
insert
Extent
management
Conventional
Table
Data
save
Direct
path
High-water mark
Space used only by conventional load
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Direct Path / Convential Path
SQL Loader
External Table
Insert Append
CTAS
Convential Path
SQL Command Processing
Space Management
Get new extents
Adjust High
Water Mark
Find partial blocks
Fill partial blocks
Buffer Cache Management
- Manage queues
- Manage contention
Read Database
Blocks
Buffer
Cache
Write Database
Blocks
Database
Oracle Server
Direct
Path
Benutzer
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
51.100.000
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;
Stattdessen INSERTS mit
vielen Sätzen
Insert /*+ APPEND */ into
ziel_Tabelle
select ......
Direct Path Load nur mit
echten Mengen und nicht
bei Einzel-Inserts
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Beliebte Fehler – Direct Path Load
Batch-Lauf 1
Batch-Lauf 2
(Session 1)
(Session 2)
Insert /*+ APPEND */
into
F_UMSATZ
select ......
Insert /*+ APPEND */
into
F_UMSATZ
select ......
Tabelle
BESTELLUNG
Gegenseitiges Blockieren durch Direct Path Loads auf
In unterschiedlichen Sessions auf die gleiche Tabelle
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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.
Name Type
------------------F0 NUMBER
F1 NUMBER
F2 NUMBER
F3 VARCHAR2(50)
F4 DATE
F5 VARCHAR2(50)
F6 VARCHAR2(50)
F7 NUMBER
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
........................................................................................................................................................
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
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)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Der einfache INSERT
insert into t20 select * from t10;
• INSERT in leere Tabelle
Laufzeit: 01:46 (Minuten : Sekunden)
• INSERT in gefüllte Tabelle
Laufzeit: 01:58 (Minuten : Sekunden)
• INSERT in gefüllte Tabelle (Wiederholung)
Laufzeit: 01:58 (Minuten : Sekunden)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Umschalten der DB
shutdown immediate;
startup mount
alter database archivelog;
[alter database noarchivelog;]
alter database open;
Logging / Nologging
• INSERT im Archivelog-Modus
insert into t20 select * from t10;
Laufzeit: 02:56 (Minuten : Sekunden)
(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.)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Logging / Nologging
• Wird der Archivelog-Modus benötigt oder nicht?
• Relevant für
– Backup
– DataGuard / Golden Gate
– Flashback
• Wichtigster Punkt ist: BACKUP
– Abhängig vom Backup-Konzept
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Auswirkungen auf das Backup-Konzept
• Plattensicherung
– Oft einfach, weil eingespielte Verfahren
– Grosser Ressourcenverbrauch
• Alle (DWH-Bereiche) werden gesichtert -> großer Platzbedarf
• Teure Backup-Software
– Nicht immer sicher, weil 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
RMAN- Backup-Verfahren:
Was wird gesichert?
Data Integration Layer
T
T
T
Keine Sicherung
Enterprise Information Layer
R
R
S
User View Layer
D
S
S
B
B
F
D
D
Keine Sicherung,
Inkremental Backup
wenn Data Marts
nur für Referenz- und
komplett neu aufgebaut
Stammdaten
Werden
große
RMAN
Bewegungsdatentabellen
besten nach Abschluss des
(Incremental)am
ETL-Laufes sichern
RMAN (Incremental)
46
D
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
Direct Path Load
• Create Table As Select (CTAS)
Create Table t20 as select * from t10;
Laufzeit: 01:00 (Minuten : Sekunden)
• INSERT mit APPEND - Hint
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 01:00 (Minuten : Sekunden)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Arbeiten mit Buffer-Caches
• Wiederholtes Laden ohne zuvor die Buffer-Caches zu leeren
– Der SELECT-Teil läuft schneller
1. Verarbeitung
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)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
Data Integration
Layer
CTAS
CTAS
T
SSD
T
SSD
T
SSD
T
SSD
User View
Layer
Enterprise Information
Layer
20% R
PEL
SSD
R
S
SSD
S
B
80%
D
D
F
B
SSD
PEL
S
SSD
PEL
SSD SSD
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D
D
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
Create Table T20 as select * from t10;
Laufzeit: 00:10 (Minuten : Sekunden)
APPEND mit SSD
insert /*+ APPEND */ into t20 select * from t10;
Laufzeit: 00:10 (Minuten : Sekunden)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
• Speichermanagement und Grundlagentechniken
• Blöcke, Extents, Segmente, Tablespace
• Direct Path Load, Mengenbasiertes Laden
• Optimierungsszenario
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
51
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Verfahren für schnelles ETL in der Datenbank
Data Integration
Layer
Selektieren
Statt
kopieren
Direct Path
in temporäre
Tabellen
Contraintfreies
Prüfen mit
Mengenbasiertem
SQL
User View
Layer
Enterprise Information
Layer
Partition
Exchange
&LOAD
in partit.
Tabellen
(PEL)
Unveränderte
Bewegungsdaten liegen
lassen
Große
Fakten-Tab.
über PEL.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kennzahlen ausschließlich
über
MAVRefresh
Bekannte
Kennzahlen
in die DB
weniger
Koipien in
BI-Tools
Es gibt 6 Prüf-Kategorien
Tabellen-über
greifende Regeln
Attribut-bezogene Regeln
1.
2.
Not Null / Pflichtfelder
Formatangaben
A
3.
4.
9.
numeric
Alphanumerisch
Date
Masken
a)
b)
c)
d)
D
5.
C
Abhängigkeiten von Werten in anderen Attributen
desselben Satzes
E
Primary Key / Eindeutigkeit
Aggregat – Bedingungen
a)
b)
8.
Aggregat – Bedingungen
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Referenz-Zusammenhänge
–
Satz-übergreifende Regeln
6.
7.
10.
11.
Ober-/Untergrenzen / Wertelisten
Satz-bezogene Regeln
B
Child-Parent (Orphan)
Parent-Child
a)
b)
a)
b)
Div. Check Constraint
Wertbereiche
–
Foreign Key
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
F
Zeit-/ Zusammenhang-bezogene Regeln
12.
13.
14.
Zeitinvariante Inhalte (z. B. Anz. Bundesländer)
Zeitabhängige Veränderungen
Über die Zeit mit anderen Daten korrelierende Feldinhalte
Verteilungs-/Mengen-bezogene Regeln
15.
Verteilung
a)
b)
Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
Verweise auf Sätze einer anderen Tabelle (Relation)
16.
Arithmetische Mittel
Varianz / Standardabweichungen
Qualitätsmerkmale und Mengen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Mengen-basierte Prüfungen mit SQL
Attribut-bezogene Regeln
1.
2.
A
3.
4.
select
bestellnr,
Not Null / Pflichtfelder
case
Formatangaben
when -- wenn Feld BESTELLNR nicht numerisch
a)
numeric
REGEXP_LIKE(BESTELLNR, '[^[:digit:]]')
b)
Alphanumerisch
then 1
c)
Date
else 0
d)
Masken
End Num_Check_bestellnr
select
Div. Check Constraint
from bestellung;
CASE
Wertbereiche
WHEN (F1 = 3 and F2 = F3 + F4)
–
Ober-/Untergrenzen / Wertelisten
then 1
ELSE 0
end
from fx
Abhängigkeiten von Werten in anderen Attributen
Satz-bezogene Regeln
B
5.
desselben Satzes
insert /*+ APPEND */ into err_non_unique_bestellung
select bestellnr from
(select
count(bestellnr) n,
bestellnr
from bestellung
group by bestellnr)
where n > 1;
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)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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...
);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Prüfungen Kategorie A
Attribut-/Column-bezogene Regeln
1. Not Null / Pflichtfelder
2. Formatangaben
a)
b)
c)
d)
numeric
Alphanumerisch
Date
Masken
3. Div. Check Constraint
4. Wertbereiche
Ober-/Untergrenzen / Wertelisten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Prüfen mit oder Ohne Datenbank-Constraints
• Constraints verlangsamen den Massen-Insert des ETL-Prozesses
• => Ohne Constraints arbeiten
• => Prüfen mit SQL-Mitteln
• => Prüfen mit DML-Errorlogging
– Nur bei wenigen Daten sinnvoll
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Prüfkonzepte
• Fachliche Prüfungen kaum möglich
• Eventuell zusätzliche Prüfungen nötig
• Einfach implementierbar
• Bessere Performance
• Nur bei aktivierten Constraints
Stage-Tabelle
+ Geprüfte Daten
Kopieren
Statistik
Routine
Date
Number
Varchar2()
Bad
File
DML
Error Log
Check
Constraints
Fehlerhafte
Sätze
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 58
Statistiken
Error Logging
• Constraints
Kunde
INSERT INTO Kunde
VALUES (......)
LOG ERRORS INTO
kunde_err('load_20040
802')
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
– Unique Key / Primary Key
– Foreign Key
– NOT NULL
– Check Constraint
Kunde_err
KUNDENNR
VORNAME
NACHNAME
ORTNR
STRASSE
TELEFON
ORA_ERR_NUMBER$
ORA_ERR_MESG$
ORA_ERR_ROWID$
ORA_ERR_OPTYP$
ORA_ERR_TAG$
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 59
Testfall
Bestellung
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
Bestellung_Check
BESTELLNR
ORTNR
KUNDENNR
DATUM
ANZAHLPOS
1.100.000
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$
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
UNIQUECONSTRAINT
Testfall
begin
dbms_errlog.create_error_log(
dml_table_name
err_log_table_name
);
end;
=> 'BESTELLUNG_CHECK',
=> 'BESTELLUNG_CHECK_ERRORS'
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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;
Tabelle wurde erstellt.
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
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
63
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
Zeichenklassen
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
64
Wichtiges Hilfsmittel für Einzelfeldprüfungen: CASEAnweisung
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;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
65
Hilfsfunktion:
Date_Check
create or replace function IsDate (str varchar2)
return varchar2 is inDate varchar2(40);
FUNCTION dateCheck (inputDate varchar2,
inputMask varchar2) RETURN varchar2
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';
END;
66
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Abarbeitungslogik für Einzelfeldprüfung mit CASE
Gepruefte_Daten
Temp-Tabelle
Stage-Tabelle
Kopieren
Varchar2()
Feld1
Feld2
Feld3
INSERT INTO
temp_table
SELECT
CASE ....
FROM
Stage_Table
Varchar2()
Feld1
Feld2
Feld3
Feld1_is_null
Feld1_is_numeric
Feld2_is_numeric
INSERT ALL
WHEN
Feld_1_is_null =1
into
Error_Daten
WHEN
Feld_1_is_null=0
into
Gepruefte_Daten
Date
Number
Varchar2()
Error_Daten
Date
Number
Varchar2()
• Temporäre Tabelle ist optional
– Ist wesentlich übersichtlicher
– Erlaubt Kombination von unterschiedlichen Prüfkriterien
67
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Abarbeitungslogik mit CASE
OLTP_Kunden
Bestellnr
Menge
Summe
Name
Ort
BestDatum
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;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
OLTP_Kunden_tmp
Bestellnr
Menge
Summe
Name
Ort
BestDatum
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
• Lösung: Analog zu Kategorie A über CASE
F1
3
9
F2
7
5
F3
3
1
F4
4
4
1
0
select
CASE
WHEN (F1 = 3 and F2 = F3 +
F4)
then 1
ELSE 0
end
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Prüfungen Kategorie C
Satz-übergreifende Regeln
6. Primary Key / Eindeutigkeit
7. Aggregat – Bedingungen
a)
b)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
8. Rekursive Zusammenhänge
Verweise auf andere Sätze derselben Tabelle (Relation)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
6. Eindeutigkeit / PK
BESTELLUNG
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
BESTELLPOSITION
BESTELLNR
FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
• Lösung: Mengenbasiertes Sammeln doppelter Sätze in Fehlertabelle
insert /*+ APPEND */ into err_non_unique_bestellung
select bestellnr from
(select
count(bestellnr) n,
bestellnr
from bestellung
group by bestellnr)
where n > 1;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D_ARTIKEL
7. Aggregatbildung
•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
F_UMSATZ
FK
ARTIKEL_ID
FK
KUNDEN_ID
FK
FK
ZEIT_ID
REGION_ID FK
KANAL_ID
UMSATZ
MENGE
UMSATZ_GESAMT
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
ARTIKEL_NAME
GRUPPE_NR
GRUPPE_NAME
SPARTE_NAME PK
SPARTE_NR
ARTIKEL_ID
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
gruppiert
select distinct
A.F_key und summiert wird.
F_Key
3
9
12
15
4
17
23
Parent
28
Parent
Member Group
_Value
_Value
0
3
3
3
0
4
4
4
0
4
6
2
0
3
8
1
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;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
12
0
0
0
15
0
0
0
Prüfungen Kategorie D
Tabellen-übergreifende Regeln
9. Foreign Key
a)
b)
Child-Parent (Orphan)
Parent-Child
10. Aggregat – Bedingungen
a)
b)
Ober- Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
11. Referenz-Zusammenhänge
Verweise auf Sätze einer anderen Tabelle (Relation)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
BESTELLUNG
KUNDE
9. Foreign Keys
•Anforderung:
Zu jeder Bestellung muss es einen
Kunden geben.
•Lösung:
KUNDENNR PK
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
Sub-Select in Where-Klausel.
insert /*+ APPEND */ into err_orphan_Bestellung
select bestellnr
from bestellung
where
Kundennr not in (select Kundennr from kunde);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
10. Aggregatbedingungen
Anzahl Sätze pro Einheit
BESTELLUNG
•Anforderung:
Anzahl Positionen muss
einen bestimmten Wert haben.
•Lösung:
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
Sub-Select in FROM-Klausel.
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;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
BESTELLPOSITION
FK
BESTELLNR
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
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
•
•
•
•
•
•
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:
– 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.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Die Beispiel - Quellumgebung
ARTIKEL
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR
PK
ARTIKEL_GRUPPEN
GRUPPE_NR
PK
GRUPPE_NAME
FK
SPARTE_NR
BESTELLUNG
KUNDE
PK
KUNDENNR
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
ARTIKEL_NAME
PK
ARTIKEL_ID
FK
GRUPPE_NR
PREIS
BESTELLPOSITION
BESTELLNR
FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 78
Beispielprüfungen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Beispielprüfungen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
1. Schritt: Prüfungen von
Tabellen-übergreifenden Beziehungen
ARTIKEL
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR
PK
ARTIKEL_GRUPPEN
GRUPPE_NR
PK
GRUPPE_NAME
FK
SPARTE_NR
ARTIKEL_NAME
PK
ARTIKEL_ID
FK
GRUPPE_NR
PREIS
18
KUNDE
6
PK
KUNDENNR
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
BESTELLUNG
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
2
13
BESTELLPOSITION
BESTELLNR
FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
21
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 81
2. Schritt: Prüfungen und Berechnungen von
Satz-übergreifenden Abhängigkeiten
ARTIKEL
ARTIKEL_GRUPPEN
GRUPPE_NR
PK
GRUPPE_NAME
FK
SPARTE_NR
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR
PK
ARTIKEL_NAME
PK
ARTIKEL_ID
FK
GRUPPE_NR
PREIS
18
KUNDE
BESTELLUNG
6
PK
KUNDENNR
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
4
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
2
13 9
BESTELLPOSITION
BESTELLNR
FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
21
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
82
14
10
1
13
3. Schritt: Feldprüfungen
ARTIKEL
GRUPPE_NR
PK
GRUPPE_NAME
FK
SPARTE_NR
ARTIKEL_SPARTEN
SPARTE_NAME
SPARTE_NR
ARTIKEL_NAME
PK
ARTIKEL_ID
FK
GRUPPE_NR
PREIS
ARTIKEL_GRUPPEN
PK
18
KUNDE
6
PK
KUNDENNR
KUNDENNAME
BERUFSGRUPPE
SEGMENT
KUNDENTYP
5
4
21
2
BESTELLUNG
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
6
ANZAHLPOS
8
BESTELLPOSITION
11
13 9
12
BESTELLNR
FK
PK
POSITIONSNR
15 16
MENGE
FK 18
ARTIKELNR
DEPOTSTELLE
19
RABATT 20
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 83
14
17
10
1
13
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
– Prüfen einer Join-Tabelle
– 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Anordnung und Gruppierung der Ladeschritte des
Beispiel-Szenarios
KUNDE
err_anz_pos_Bestellposition
err_orphan_Bestellung
err_AVG_Pos_Wert
err_childless_Bestellung
err_orphan_PositionArtikel
BESTELLUNG
err_seq_pos_Bestellposition
BEST_POS
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
err_maske_depotstelle
ARTIKEL
err_not_null_Menge
err_not_Rabatt_Wert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Auflistung der Laufzeiten für die einzelnen
Prüfungen (unterschiedliche Massnamen)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Themenübersicht
Datenbank-basierte ETL-Prozesse
• Anforderungen an den ETL-Prozess im Data Warehouse
• Speichermanagement und Grundlagentechniken
• Blöcke, Extents, Segmente, Tablespace
• Direct Path Load, Mengenbasiertes Laden
• Optimierungsszenario
• Hilfsmittel für schnelles Laden
• Prüftechniken mit SQL
• Szenario zum Prüfen von Daten
• Weitere Techniken und Tools
91
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Verfahren für schnelles ETL in der Datenbank
Data Integration
Layer
Selektieren
Statt
kopieren
Direct Path
in temporäre
Tabellen
Constraintfreies
Prüfen mit
Mengenbasiertem
SQL
User View
Layer
Enterprise Information
Layer
Partition
Exchange
&LOAD
in partit.
Tabellen
(PEL)
Unveränderte
Bewegungsdaten liegen
lassen
Große
Fakten-Tab.
über PEL.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kennzahlen ausschließlich
über
MAVRefresh
Bekannte
Kennzahlen
in die DB
weniger
Kopien in
BI-Tools
Partition Exchange Loading (PEL)
Financial
Production
Human Res.
Store
Supplier
Marketing
Service
Temporäre
Tabelle
P1
P2
P3
P4
Z1
Z2
Z3
Z4
Neuer Monat
4
4
9
8
Zeit
Monat 13
Parallel Direct Path INSERT
(Set Based)
CREATE TABLE AS SELECT
(CTAS)
CREATE Indizes / Statistiken anlegen
EXCHANGE Tabelle
Monat 12
Monat 11
Monat 10
DROP
PARTITION
Region
Faktentabelle
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
93
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;
-- 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;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
94
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
• Zwang zum späteren Monats-Merge nutzen für
– Komprimierung
– Reorganisation
– ILM-Konzept
• Eine Partitionierungs-Ebene ist durch das PEL-Verfahren meist schon belegt
• Local-Indizierung (wenn überhaupt gebraucht) entstehen automatisch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Exchange Partition
Data Integration
Layer
Enterprise Information
Layer
Älteste
Checks
CTAS
Tmp_table
Direct Path
Archivieren
(drop partition)
Mai
Juni
Juli
August
September
Oktober
November
Alter table
add partition
Alter table exchange partition
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
User View
Layer
Aufbau Fakten-Tabellen
Data Integration
Layer
Archivieren
(drop partition)
Älteste
Checks
CTAS
Tmp_table
Direct Path
User View
Layer
Enterprise Information
Layer
Fact-Table
Juni
Juli
August
September
Mai
Juni
Juli
August
September
Oktober
November
Tmp_table
Oktober
November
CTAS
Tmp_table
Alter table
exchange partition
Alter table exchange partition
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Beispiele für Lösungen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Klassische PL/SQL Cursor – Verarbeitung
(Negativ – Beispiel)
Für alle
Bewegeungssätze z. B.
300.000)
8,3 Std*
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)
cursor
t_Quelle_Stage_2 (z. B. 5000)
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Insert
Faktentabelle
Mengenbasierte Alternative
(Performance – Optimierung bis zu Faktor 20 und mehr)
t_Ref_1
t_Ref_2
t_Ref_3
t_Ref_4
t_Ref_5
300.000
Outer
Join
Temp Table
Fakt Table
Cursor
loop
t_Ref_6
t_Ref_7
Satzweise
Prüfung
t_Ref_8
t_Ref_9
Protokoll
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Aufgabenstellung:
Lookup-Argumente über Funktionen gewinnen
• Mehrstufige Verarbeitung
1.
2.
Über Funktionen Argumente gewinnen
Argumente in Lookup-Aufrufe einbauen
• WITH-Verarbeitung
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
L.V_ProdukT_id
= P.produkt_id)
Beschreiben einer Master/Detail - Beziehung
• Ziel: Die Sätze in der Detail-Tabelle müssen immer einen Parent-Satz haben
• Lösung:
– Multiple Insert: Die Information aus einer Quelle steht immer zum Beschreiben von Parentund Child-Tabelle zur Verfügung.
– Beide sind synchronisierbar
BESTELLUNG
BESTELLNR
PK
ORTNR
FK
KUNDENNR
BESTELLDATUM
ANZAHLPOS
BESTELLPOSITION
BESTELLNR
FK
PK
POSITIONSNR
MENGE
FK
ARTIKELNR
DEPOTSTELLE
RABATT
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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 ;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
104
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*/);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
105
Deltadaten Extrahieren
OLTP
DWH
1
Table
Änderungsdatum
Table
2
Table
Trigger
Table
Queue
3
Table
Deltabildung
über MINUS
Table
4
LogFile
Logminer
Table
Queue
5
LogFile
Streams
Queue
Table
6
LogFile
Golden Gate
Queue
Table
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Herausforderungen beim Extrahieren
• Unterschiedliche Namen in Quell- und Zielsystemen
• Bewahrung der Konsistenz
– Zeitpunkt des Ladens kann kritisch sein
– Vollständigkeit der Daten
• Unterschiedliche GRANTs der User
• Zusätzlicher Netzwerkverkehr
• Meist ist nur das Delta der geänderten Daten gewünscht
• Formate (Datum, Zeichensätze)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
107
Einlesetechniken Vorsysteme
EBCDIC
ASCII
Database Link
Transportable TS
Datapump
Oracle
ODBC
JDBC
Gateway
Non
Oracle
AP I
Applikation
z. B.
SAP R/3
SQL-Loader
External Table
API CALL
Direct Save
SQL
SQL
BS Copy
BS Copy
SQL
SQL
SQL
SQL
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle
DWH
Die Quellen
 Nur die Daten laden, die gebraucht
Selektieren
statt
kopieren
werden
 Keine 1:1 Kopien in die
Integrations-Schicht
 Nach Möglichkeit vor Eintritt in die
Datenbank Prüfungen durchführen
 Bereits geprüfte Daten nicht mehr
prüfen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Nicht so ....sondern
Data Integration
Layer
Data Integration
Layer
1:1
T
1:1
1:1
1:1
CTAS
T
T
T
T
T
Warum?
Logik so früh wie möglich
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Techniken für Data Marts / User View Layer
Viele verstecke Aufwende
Verlagerung von IT in die
Fachabteilungen
Redundanzen
Ursache vieler nicht
abgestimmter Kennzahlen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Minimales Bewegen
Große unveränderte Tabellen
liegen lassen
Enterprise Information
Layer
D
Zugriff auf beide Schichten
Security mit Bordmittel anstatt
durch Kopieren lösen
User View
Layer
F
B
B
D
PEL
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
D
D
Kennzahlen
Kennzahlen nur als
Materialized Views
User View Layer
Automatisches Refresh anstatt
ETL
Standardisierte und stimmige
Kennzahlen
Wiederverwenden von bereits
aggregierten Daten
L1
L2
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
L3
L4
Der Weg in die BI-Tools
User View Layer
Millionen von Sätzen
So viel wie möglich in der DB
vorbereiten
Verhindert unnötiges Kopieren
Keine Verlagerung von
Pseudo-ETL in die BI-Tools
Standardisierte Kennzahlen
User View Layer
Oder so
WenigeSätze
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
BI-Tool Server + Caches
So
Weitere Einflussfaktoren und Techniken
• Parallelisierung -> abhängig von Hardware
-> direktes Steuern über Hints
• Aktuelle Statistiken
-> Source Tabellen
-> auch während des ETL-Laufes
• Ausnutzen des
Cache-Effektes
-> Organisieren der Abarbeitungsreihenfolge
-> Eventuell Query-Result-Cache nutzen
• Vermeiden von großen
Join-Tabellen
-> eventuell kleine Join-Tabelle
mit wenigen Spalten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
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
mit großen Tabellen
• 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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Keine unnötige Daten-Transporte ...
Vorsystem mit
Vorrechner
Vorsystem
Data
Integration
Layer
Enterprise
Information
Layer
1:1
User View
Layer
1:1
1:1
1:1
1:1
Externe ETL-Server
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
User View
Layer
User View
Layer
... sondern kurze Wege
Vorsystem
Data Integration Layer
Freie
Wahlmöglichkeit
für Ort und Art des ETL
Enterprise Information Layer
User View Layer
Process neutral / 3 NF
Ein-Datenbank-Server
Externe ETL-Server
(Hauptsächlich zu Dokumentationszwecken)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Zusammenfassung der wichtigsten Techniken
Eine zusammenhängende Datenbank
Data Integration
Layer
CTAS
T
CTAS
T
T
T
Enterprise Information
Layer
20% R R S S S
PEL
B
B
PEL
User View
Layer
D
D
F
D
D
PEL
Vorgelagerte
Prüfungen
SQL-Mengenbasierte
Prüfungen
Vorbereitete
temporäre
Tabellen
Konzentration aller Prüfungen
CTAS : Create Table As Select
Partition
Exchange
Partitionierte
Tabellen
A
80%
Partition
Exchange
Aggregatbildung
durch Materialized
Views
Denormalisierung (Joins)
und Aggregate
PEL : Partition
Exchange and Load
119
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
A: Aggregate
Prüfungen
Zusammenfassung der Techniken bezogen auf das
Schichten-Modell
Data Integration
Layer
CTAS
CTAS
T
T
T
T
User View
Layer
Enterprise Information
Layer
20% R
R
PEL
S
S
S
D
F
B
B
PEL
D
PEL
D
D
PCT
A
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
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Kennzahlensysteme mit
Materialized Views
Fast Refresh mit
Partition Change
Tracking (PCT)
R: Referenztabellen
T: Transfertabellen
S: Stammdaten
B: Bewgungsdaten
D: Dimensionen
F: Fakten
A: Aggregate
Partitionierte
Tabellen
Prüfungen
Lade-Transaktionssteuerung innerhalb der
Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
121
122
Aufgabenstellung der Lade-Transaktion
• Betrachten des kompletten Ladelaufs als eine zusammenhängende
Transaktion
– Entweder alle Sätze oder keine geladen
• Wie können abgebrochene Ladeläufe wieder rückgängig gemacht
werden?
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
123
Transaktionssteuerung / -rücksetzung
• Markieren von Sätzen eines Ladelaufs in zusätzlichen Feldern
3
– Ladelauf-Nummer, Ladelauf-Datum, ...
– Zurückrollen durch langsames Einzel-DELETE
• Arbeiten mit Partitioning
– Aufbau einer neuen Partition unabhängig von der Zieltabelle
– Schnelles DROP PARTITION im Fehlerfall
– Einfachste und schnellste Variante
2
• Flashback Database / Table / Query
– Transaktions-genaues Zurückrollen
– Flashback DB benötigt zusätzlichen Plattenplatz
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
1
Flashback
• Steuerung über
– SCN ( Sequence Change Number / Log Archiving)
– Zeit (Timestamp)
– Restore Point
ETL / ODI
Flashback Recovery Area
Zeit
Log
SCN
Restore Point
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
124
Flashback Technologie
in der Datenbank
• Flashback Table
• Flashback Drop
• Flashback Query
• Flashback Versions Query
• Flashback Transaction Query
• Flashback Database
• Flashback Data Archive
Flashback table x to scn 16552768;
SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE
first_name = 'John';
SELECT xid, operation, start_scn,
commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
Flashback Database to scn 16552768;
Flashback Database AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Flashback Database
• Flashback Database
–
–
–
–
–
Erstellen der Fast (Flash) Recovery Area
Restart Database ( mount exclusive, wenn DB <11.2)
SQL> ALTER DATASE FLASHBACK ON;
SQL> ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;
SQL> ALTER DATABASE OPEN;
• Restore Points (ab 11.2 im laufenden Betrieb)
– create restore point PRE_LOAD;
– create restore point PRE_LOAD guarantee flashback database;
(impliziert das Anlegen von Flashback Logs)
– drop restore point PRE_LOAD;
• Anwendung eines Restores nur im DB Mount-Status
– flashback database to restore point PRE_LOAD;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Monitoring in der Datenbank
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
127
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
128
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted
129
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
130
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted
131
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Restricted
132