Oracle Data WarehouseImplementierung dimensionaler Modelle
”Star - Modell”
Alfred Schlaucher, Oracle Deutschland
2
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
3
SchnellerMehr
FlexiblerGenauer
Operativer
Umfassende Sichten auf Kunden
Einheitliche Informationen standardisierte und Daten
Flexible und reichhaltige Auswertestrukturen
Immer kürzere Auswertezyklen
Was Anwender bewegt
4
Alle Kundenkontaktpunkte
Alle Geschäftsbeziehungen zu dem Kunden
Alle historischen Daten
Alle öffentlich verfügbaren Kundendaten
Plus soziographische Informationen (Referenzdaten)
Ergeben ein vollständiges Bild
+ neue Geschäftsoptionen
Potentielle Informationsschätze
360°
5
Informationsdrehscheibe für alle (!) Sachgebiete
User View Layer
Enterprise Information
Layer
Data Integration
LayerReferenzdatenStammdaten
Bewegungsdaten
Standardisierte Berichte
AdvancedAnalytics
Ad-hoc Query & Reporting
ModelleSimulation
D a t a W a r e h o u s e
6
Informationsdrehscheibe für alle (!) Sachgebiete
User View Layer
Enterprise Information
Layer
Data Integration
Layer
Einheitliches Verständnis über
Sachverhalte
Einheitliche verbundene Stammdaten
Standardisierte Kennzahlen
Harmonisieren
Neutrale Sicht aufalle Unternehmens-
Bereiche
Flexibel für alleEndebenutzer
zugänglich
StammdatenReferenzdaten
Bewegungsdaten
ReferenzdatenStammdaten
Bewegungsdaten
Standardisierte Berichte
AdvancedAnalytics
Ad-hoc Query & Reporting
ModelleSimulation
7
Alle Modellformen in einem System
Umfangreichste R-Unterstützung
Integriertes Data Mining
Multidimensionale Speicherung als Add On zum Star
Star Schema und 3 NF
Analyseverfahren schon im Data Warehouse vorbereiten
User View Layer
Standardisierte Berichte
AdvancedAnalytics
Ad-hoc Query & Reporting
ModelleSimulation
Relational
Würfel
Mining-Struktur
Oracle R EnterpriseOracle Data Miner
Oracle OLAP
Any SQL
8
1. Informations-bedarfsanalyse
Informationsbedürfnisse der Endanwender stehen am Anfang
Auflistung aller benötigten Kennzahlen
Wachsende DWH-Informationslandschaft
R R
BS S
S
D
D
DD
Enterprise Information Layer User View LayerData Integration Layer
T
T
T
T: TransfertabellenR: Referenztabellen
S: StammdatenB: BewgungsdatenD: DimensionenF: Fakten
B
F
F
F
D
D
D
DB
B
B
B
Strategische Daten Taktische Daten
Sachgebiets-/Aspekt-/Teilprozess-Bezogene Vorgehens-weise
WachsendesInformations-modell
9
2. Analyse- / Geschäftsobjekt- /Konzeptionelles Modell
Eine zusammenhängende Auswerteschicht wird entworfen
Daraus leiten sich alle Informationsobjekte in dem gesamten DWH ab.
Wachsende DWH-Informationslandschaft
R R
BS S
S
D
D
DD
Enterprise Information Layer User View LayerData Integration Layer
T
T
T
T: TransfertabellenR: Referenztabellen
S: StammdatenB: BewgungsdatenD: DimensionenF: Fakten
B
F
F
F
D
D
D
DB
B
B
B
Strategische Daten Taktische Daten
Sachgebiets-/Aspekt-/Teilprozess-Bezogene Vorgehens-weise
WachsendesInformations-modell
10
3. Das DWH wächst
Nach und nach entsteht ein unternehmensweit reichendes zusammenhängendes Informationmodell
(Enterprise Layer)
Wachsende DWH-Informationslandschaft
R R
BS S
S
D
D
DD
Enterprise Information Layer User View LayerData Integration Layer
T
T
T
T: TransfertabellenR: Referenztabellen
S: StammdatenB: BewgungsdatenD: DimensionenF: Fakten
B
F
F
F
D
D
D
DB
B
B
B
Strategische Daten Taktische Daten
Sachgebiets-/Aspekt-/Teilprozess-Bezogene Vorgehens-weise
WachsendesInformations-modell
11
Exemplarische Fragestellungen der Anwender
Umsatz_Pro_Produkt_Segment Top 3 Produkte pro Segment Verhältnis von Handelsware zu
Vermittlungsdiensten Personalaufwand / Investition pro
Segement
Kennzahlen aus dem Vertrieb
Umsatz_Verhältnis: Privat-/Firmenkunde Verhältnis von Umsatz mit und ohne
Kundenkarte Umsatz pro spezifischem
Kundensegment Berufsgruppe Altersgruppe Gehaltsgruppe
Umsatz pro Produkt- und Kundensegment
Kennzahlen aus dem Marketing
Abgleich zwischen Einkaufs- und Verkaufszahlen
Kennzahlen aus dem Controlling
Abgleich zwischen Bestell- und Liefervorgängen
Kennzahlen aus der Buchhaltung
12
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
13
Datenarten
•Referenzdaten
• Stammdaten
• Bewegungsdaten
• Fakten
• Dimensionen
Das physische Modell - Betrachtungshorizont
Enterprise Information Layer
User View Layer
14
Durchgängige Schichten
Verbundmodelle
Zusätzliche Referenzdaten
Schnelligkeit bei derAufbereitung der Daten
Flexibles Informationsangebot + Schnelligkeit
R R S S S
Enterprise Information Layer
User View Layer
D
D
DD
F
F
D
D
D
D F
F
F
B
B
B B
B
D
R R S S S
Zu
sa
mm
en
hän
ge
nd
er A
bfra
ge
be
reic
h
S c h i c h t e n - ü b e r g r e i f e n d e r A b f r a g e b e r e i c h
D
Selbstpflegendes Kennzahlensystem
MJ
A
C
AQ
L
D
B
s c h a f f t S c h n e l l i g k e i t
sc
ha
ff
t
F
le
xi
bi
li
tä
t
A
A
A
AA
A
15
Namensvergaben hilft bei der Orientierung
Prefixe für die unterschiedlichen Datenarten
– Fakten->
F_
– Dimensionen-> D_
– Referenzdaten ->R_
– Stammdaten-> S_
– Temporäre Daten ->tmp_
– Bewegungsdaten ->B_
Suffixe für die unterschiedlichen Feld-Arten
– PK_/Schlüsselfelder ->_ID
– FK- Felder->
_ID (gleiche Namen wie PK-Fleder)
– Allgemeine Nummerierungen / Zählfelder
->_NR
– Datumsfelder-> _Dat /
_Datum
Ziel: Die Wartbarkeit des Modells
16
Kunden_Wohnart_Nr
Informationzu einem Kunden wird beschrieben
Die Art und Weise, wie ein Kundewohnt wirdbeschrieben
unter-schiedlicheWohnungs-arten sind durch-nummeriert
Hauptwort Eigenschafts-benennung
Basistyp
Bezugsobjekt BeschreibendeInformation
Charakter desAttributes
Wortstammanalyse hilft bei der Klassifizierung von Column-Namen
17
Basistypgruppe
Feldyp und Art des Wertes
Rolle in Ab-hängigkeits-be-ziehung
Sind NULLserlaubt
Muss Eindeutigkeitvorliegen
Identifikatoren und bezeichnende Begriffe
meist numerisch LHS nein ja
Beschreibungen, Erzählungen, Texte
meist Text , beliebige Zeichen
RHS ja nein
Klassifikatoren alphanumerisch,in Bezug setzende Begriffe,oft wenige Werte
RHS eher nicht, eine Klassifizierung sollte für alle Sätze gelten
nein
Zustände meist Text , beliebige Zeichen
RHS eher nicht, denn Zustände sollten für alle Sätze gelten,
nein
Zeiten Date / Time RHS ja nein
Sequenzen, Aufzählungen Zählwerte)
meist numerisch,oft versteckte Schlüsselkandidaten
LHS nein
ja
18
Mengen meist numerisch,einfache Zahlenwerte ohne weitere Angaben
RHS nein,wenn etwas gezählt wird, sollte es immer gezählt warden
ja
Operatoren und abgeleitete Größen
meist Text , beliebige Zeichen
RHS nein ja
Werte
(brauchen i. d. R. eine relativierende Bezugsgröße z. B. Preis -> Währung)
meist numerisch,einfache Zahlenwerte ohne weitere Angaben(brauchen i. d. R. eine relativierende Bezugsgröße z. B. Preis -> Währung)
RHS nein ja
Maße, Bezugsgrößen, Einheiten
meist Text , beliebige Zeichen
RHS nein ja
19
selectsubstr(table_name,1,20)
Tab,substr(column_name,1,20) Col,
substr(data_type,1,8) Typ, substr(data_length,1,3)LenFrom dba_tab_columnsWHEREowner = 'SV' and(table_name like 'F_%' ortable_name like 'D_%')order by col/
Über alle Tabellen
Alphabetisch sortiert nach Spaltennamen
Hilft beim Erkennen von Homonymen und Synonymen
Hilft bei der Bewertung der Tauglichkeit von Spaltennamen
Erlaubt Vorahnungen von Schlüsselkandidaten
Hilfsmittel Feldliste
20
Die Feldliste(Beispiel)
TAB COL TYP LEN-------------------- -------------------- -------------------------------- ------------D_FILIALEN ALLE_FILIALEN VARCHAR2 20D_KUNDE ALLE_KUNDEN VARCHAR2 20D_LAGER ALLE_LAGER VARCHAR2 40D_MITARBEITER ALLE_MA VARCHAR2 10D_REGION ALLE_ORTE CHAR 9D_PRODUKT ALLE_PRODUKTE VARCHAR2 50D_ZEIT ALLE_ZEITEN CHAR 11D_KUNDE ANREDE VARCHAR2 10D_SPEDITEUR ANZ_FAHRZEUGE NUMBER 22D_KUNDE ANZ_KINDER VARCHAR2 5D_ARTIKEL ARTIKEL_ID NUMBER 22D_ARTIKEL ARTIKEL_NAME VARCHAR2 50D_KUNDE BERUFSGRUPPE VARCHAR2 30D_KUNDE BERUFSGRUPPEN_NR NUMBER 22F_ZAHLUNGEN BEZAHLT VARCHAR2 5F_ZAHLUNGEN_PARTITIO BEZAHLT VARCHAR2 5D_KUNDE BILDUNG VARCHAR2 30D_KUNDE BILDUNGS_NR NUMBER 22D_KUNDE BRANCHE VARCHAR2 30D_REGION BUNDESLAND VARCHAR2 255D_LAGER BUNDESLAND VARCHAR2 50D_LIEFERANTEN BUNDESLAND VARCHAR2 255D_LAGER BUNDESLAND_NR NUMBER 22D_LIEFERANTEN BUNDESLAND_NR NUMBER 22D_REGION BUNDESLAND_NR NUMBER 22F_POSITION DISCOUNT_PROZENT NUMBER 22
21
SERVICE GmbH
Die Modellfirma: SERVICE GmbH
Ursprungsgeschäft– Baumärkte
Erweiterungen– Vermittlung von Handwerker-Service-
Leistungen
– Vermittlung von Finanzdienstleitungen rund um das Bauen
– Direktes Endkundengeschäft über Internet
– Lieferservice direkt ins Haus
Unterscheidung– Privat- / Firmenkunden
22
SERVICE GmbH
Erwartungen aus dem Unternehmen
Vertrieb
ControllingManagement
Marketing Buchhaltung
Vertrieb: wünscht leichtere Auswertungen Was sind wichtige Produkte? Was sind rentable Sparten? Hat sich der Servicebereich
gelohnt?
Marketing: Absatzzahlen sind nicht aussagefähig Wie viel Kunden gibt es? Lohnt die Kundenkarte? Welche Segmentierung gibt es?
Buchhaltung: Es fehlen Daten Warum sind die Spediteursrechnungen so hoch? Sind alle Bestellungen korrekt bezahlt worden? Wie hoch sind die Versandkosten pro Lieferung? Was wurde storniert?
Controlling: Vergleichbarkeit fehlt Was sind rentable Sparten? Wie rentabel sind einzelne
Produkte? Was kosten Produkte im
Einkauf? Wie teuer wurden Produkte
verkauft?Management: Kennzahlen fehlen Wie hoch sind die
liquiden Mittel? Wie hoch sind die
Außenstände?
22
23
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
24
Referenzdaten
Referenzdaten
25
Stammdaten
26
Das Auswerte-Schema
27
67.840.000
67.840.00015.104.000
65.920.001
220.000
100024
16461
1999
5479
67.840.000
305
34 6782745
100
2996370
37
10
Das Szenario und die Mengen
28
Master Detail – Schlüssel: Numerische Felder
Zwischen Zeit-Tabelle und Fakten-Tabelle DATE-Format
FK-Constraint nicht nötig aber für den Optimizer bei Abfragen sinnvoll
I.d.R. Keine PKs auf den Faktentabellen
Schlüssel im Star
28
Artikelgruppe
Sparte
Dim_Artikel
Artikel_Langtext
Artikelsparte
Artikel
Artikel_Schlüssel
Artikelgruppe_Langtext
Artikelsparte_Langname
Parent
Parent
Fakten-FKsFakten (Umsatz)
Levelschlüssel
Levelschlüssel/Objektname
Levelschlüssel
Business Key
Künstlicher Dimension Key
Dim_Schlüssel
Aggregation
Aggregation
Hi
er
ar
ch
ie
Ko
nso
lidieru
ng
slevel B
etrachtu
ng
slevel
29
PK
PK
PKPK
PK
PK
FKs
30
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
31
Design-Prinzip - Ziel: Leichte AuswertbarkeitVerteilung der Daten in den Schichten
?R R S S S
Enterprise Information Layer
User View Layer
D
D
DD
F
F
D
D
D
D F
F
F
B
B
B B
B
D
R R S S S
Zu
sa
mm
en
hän
ge
nd
er A
bfra
ge
be
reic
h
S c h i c h t e n - ü b e r g r e i f e n d e r A b f r a g e b e r e i c h
D
D
B
s c h a f f t S c h n e l l i g k e i t
sc
ha
ff
t
F
le
xi
bi
li
tä
t
32
Design-Prinzip - Ziel: Leichte AuswertbarkeitVerteilung der Daten in den Schichten
Gleiche Daten an mehreren Stellen
In unterschiedlichen Dimensionen
Redundante (konvergente) Fakten-Daten
Synchronisierung über Zentrale Stamm- und
Referenzdaten Standardisierten
ETL-Prozess
R R S S S
Enterprise Information Layer
User View Layer
D
D
DD
F
F
D
D
D
D F
F
F
B
B
B B
B
D
R R S S S
Zu
sa
mm
en
hän
ge
nd
er A
bfra
ge
be
reic
h
S c h i c h t e n - ü b e r g r e i f e n d e r A b f r a g e b e r e i c h
D
D
B
s c h a f f t S c h n e l l i g k e i t
sc
ha
ff
t
F
le
xi
bi
li
tä
t
33
Sich überschneidende DimensionenEinzelne Attribute sind gleich
Umsatz
RegionBundesland
KreisWohnort
NameKunden_NR
KundeRegion
BundeslandKreisOrt
Filial_KategorieFilial_NR
Filiale
Artikel_NRMenge
Kunden_NRPreis
Filial_Nr
33
34
35
36
Standardisieren über einheitliche Referenzdaten
Enterprise Information Layer User View Layer
Referenzdaten
37
Master Detail – Fakten Tabellen
38
Die Wechselwirkung zwischen Master Detail Fakten-Tabellen
39
Konvergente Fakten-Felder
40
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
41
Wo und wie wird im DWH indiziert
Enterprise Information Layer User View LayerData Integration Layer
Process neutral / 3 NF
Keine Indexe B*tree für Eindeutigkeit und als Primary KeyBitmaps
L a d e - A k t i v i t ä t e n
L e s e - A k t i v i t ä t e n
Bitmaps
B*tree für Primary KeysIn den DimensionenTabellen
42
Bitmap-Indexe auf alle Fremdschlüssel-Felder der gößeren Fakten-Tabellen(Ausnahme KAUF_ID)
Bei partitionierten Fakten-Tabellen:LOCAL Index
Komprimierung erfolgt automatisch
Regelmäßiges Löschen und Neuanlegen der Indexe
Star Query-Transformation
Bitmap-Index-Definitionen in Fakten-Tabellen
CREATE bitmap index idx_BM_KAUF_ZEIT_ID on F_KAUF_Partition(ZEIT_ID) local; CREATE bitmap index idx_BM_KAUF_PRODUKT_ID on F_KAUF_Partition(PRODUKT_ID) local; CREATE bitmap index idx_BM_KAUF_ORT_ID on F_KAUF_Partition(ORT_ID) local; CREATE bitmap index idx_BM_KAUF_KUNDEN_ID on F_KAUF_Partition(KUNDEN_ID) local; CREATE bitmap index idx_BM_KAUF_FILIAL_ID on F_KAUF_Partition(FILIAL_ID) local; CREATE bitmap index idx_BM_KAUF_MITARBEITER_ID on F_KAUF_Partition(MITARBEITER_ID) local; CREATE bitmap index idx_BM_KAUF_MANAGER_ID on F_KAUF_Partition(MANAGER_ID) local; CREATE bitmap index idx_BM_KAUF_FILIALLEITER_ID on F_KAUF_Partition(FILIALLEITER_ID) local;
43
Feststellen, für welche Spalten Indexe nötig sind
Selektivität der Werte in den betroffenen Spalten Prüfen
Platzverbrauch im Blick haben
Regelmäßig neu machen
Umgang mit Bitmap-Indexe
Rowid Name Abschluss Rating
AAAHfVAAJAAAKOKAAA Meier Klasse_10 5
AAAHfVAAJAAAKOKAAB Schubert Abitur 5
AAAHfVAAJAAAKOKAAC Klaus-Gustav Abitur 5
AAAHfVAAJAAAKOKAAD Schmidt Diplom 5
AAAHfVAAJAAAKOKAAE Langbein Doktor 5
AAAHfVAAJAAAKOKAAF Hund Klasse_10 5
AAAHfVAAJAAAKOKAAG Vogel Abitur 5
AAAHfVAAJAAAKOKAAH Messner Abitur 5
AAAHfVAAJAAAKOKAAA
AAAHfVAAJAAAKOKAAB
AAAHfVAAJAAAKOKAAC
AAAHfVAAJAAAKOKAAD
AAAHfVAAJAAAKOKAAE
AAAHfVAAJAAAKOKAAF
AAAHfVAAJAAAKOKAAG
AAAHfVAAJAAAKOKAAH
Abschluss=Klasse_10
Abschluss=Abitur
Abschluss=Diplom
Abschluss=Doktor
1
0
0
0
0
1
0
0
0
1
1
0
0
0
1
1
0
0
0
1
0
0
0
0
0
0
0
0
1
0
0
0SELECT NameFROM KD_TableWHERE Abschluss=‘Diplom‘;
44
Platzverbrauch Bitmap-Indexe
SQL> SELECT index_name,index_type blevel, (leaf_blocks*8/1000) MB, NUM_ROWS, distinct_keys FROM user_indexes;
INDEX_NAME BLEVEL MB NUM_ROWS DISTINCT_KEYS------------------------------ --------------------------- ---------- ---------- -------------IDX_BM_LIEFERUNG_KAUF_ZEIT_ID BITMAP 99.656 76530 76447IDX_BM_ZAHLUNGEN_ZEIT_ID BITMAP 117.672 83538 83349IDX_BM_ZAHLUNGEN_PRODUKT_ID BITMAP 218.72 54851 210IDX_BM_ZAHLUNGEN_KUNDEN_ID BITMAP 116 114030 3687IDX_BM_ZAHLUNGEN_FILIAL_ID BITMAP 70.624 17662 31IDX_BM_POSITION_ZEIT_ID BITMAP 104.104 83375 83349IDX_BM_POSITION_PRODUKT_ID BITMAP 186.584 46854 210IDX_BM_POSITION_KUNDEN_ID BITMAP 105.104 113930 3687IDX_BM_POSITION_FILIAL_ID BITMAP 61.064 15271 31IDX_BM_KAUF_ZEIT_ID BITMAP 65.848 83352 83349IDX_BM_KAUF_PRODUKT_ID BITMAP 8.472 2211 23IDX_BM_KAUF_ORT_ID BITMAP 29.28 7327 31IDX_BM_KAUF_KUNDEN_ID BITMAP 70.8 113758 3687IDX_BM_KAUF_FILIAL_ID BITMAP 29.264 7325 31IDX_BM_KAUF_MITARBEITER_ID BITMAP 61.352 52979 1682IDX_BM_KAUF_MANAGER_ID BITMAP 17.736 4424 20IDX_BM_KAUF_FILIALLEITER_ID BITMAP 9.016 2241 4
1.373392 GB
45
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 106 | 15048 (1)| 00:00:01| 1 | HASH GROUP BY | | 1 | 106 | 15048 (1)| 00:00:01|* 2 | HASH JOIN | | 1 | 106 | 15047 (1)| 00:00:01|* 3 | HASH JOIN | | 1 | 87 | 14975 (1)| 00:00:01| 4 | NESTED LOOPS | | | | || 5 | NESTED LOOPS | | 1 | 65 | 14962 (1)| 00:00:01| 6 | TABLE ACCESS FULL | D_PRODUKT | 305 | 5185 | 6 (0)| 00:00:01| 7 | PARTITION RANGE ALL | | | | || 8 | BITMAP CONVERSION TO ROWIDS | | | | ||* 9 | BITMAP INDEX SINGLE VALUE | IDX_BM_KAUF_PRODUKT_ID | | | || 10 | TABLE ACCESS BY LOCAL INDEX ROWID| F_KAUF_PARTITION | 1 | 48 | 14962 (1)| 00:00:01| 11 | TABLE ACCESS FULL | D_ZEIT | 5479 | 117K| 12 (0)| 00:00:01| 12 | TABLE ACCESS FULL | D_REGION | 16461 | 305K| 71 (0)| 00:00:01----------------------------------------------------------------------------------------------------------------
select sum(k.gesamt_wert), p.segment, r.bundesland, z.Kalender_jahr_codefrom F_Kauf_PARTITION K, d_Zeit Z, d_region R, d_produkt Pwhere to_date(K.zeit_id,'DD-MON-YY') = to_date(Z.zeit_id,'DD-MON-YY') and K.ort_id = R.ort_id and K.produkt_id = p.produkt_idgroup by p.segment, r.bundesland, z.Kalender_jahr_code
46
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
47
Partitioning senkt Verwaltungs-/Betriebskosten
Partition-bezogene Sicht
Kollektive Sicht(alle Tabellendaten)
November
ja
nein
Ba
ck
up
Sto
rag
e
günstig
sehrgünstig
High end
Ko
mp
rim
ieru
ng
Ak
tua
lis
ieru
ng
S
tati
sti
ke
n
Ak
tua
lis
ieru
ng
In
de
x
Mai
Juni
Juli
August
September
Oktober
ja
nein
ja
nein
In M
em
ory
Arc
hiv
ieru
ng
nein
ja
24/7 Online
ETL-ProzessNovember
48
Partitionierung
Große Tabellen (ab mehrere Millionen Sätze) Meist die Fakten-Tabellen Meist RANGE auf Zeit-Spalte Effekte bei Sub-partitionierung Range -> 70%- 80% Performance-Optimierung
Welche Tabellen werden wie partitioniert
49
Partitioniert Partitioniert
Partitioniert
Partitioniert
50
Beispiel F_KAUF_PARTITIONcreate table f_kauf_partition (zeit_id date,Kauf_id number,Produkt_id number,ort_id number,Kunden_id number,Gesamt_Wert number,Gesamt_Discount_Prozent number,Filial_id number,Mitarbeiter_ID number,Manager_ID number,Filialleiter_ID number)PARTITION BY RANGE (ZEIT_ID) ( PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')),
PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01','SYYYY-MM-DD')),PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')),PARTITION apr10 VALUES LESS THAN (TO_DATE('2010-05-01','SYYYY-MM-DD')),PARTITION mai10 VALUES LESS THAN (TO_DATE('2010-06-01','SYYYY-MM-DD')),PARTITION jun10 VALUES LESS THAN (TO_DATE('2010-07-01','SYYYY-MM-DD')),PARTITION jul10 VALUES LESS THAN (TO_DATE('2010-08-01','SYYYY-MM-DD')),
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PARTITION nov13 VALUES LESS THAN (TO_DATE('2013-12-01','SYYYY-MM-DD')),PARTITION dec13 VALUES LESS THAN (TO_DATE('2014-01-01','SYYYY-MM-DD')),
PARTITION next_month VALUES LESS THAN (MAXVALUE));;
51
Beispielabfrageselect sum(k.gesamt_wert) Wert, p.segment segment, r.bundesland land, z.Kalender_jahr_code Jahrfrom F_Kauf_PARTITION K, d_Zeit Z, d_region R, d_produkt Pwhere to_date(K.zeit_id,'DD-MON-YY') = to_date(Z.zeit_id,'DD-MON-YY') and K.ort_id = R.ort_id and K.produkt_id = p.produkt_id and Z.monat_des_Jahres = 1 and Z.KALENDER_JAHR_CODE = 2012group by p.segment, r.bundesland, z.Kalender_jahr_code
[F_Kauf K,]
52
Mit Partitioning
Elapsed: 00:00:08.90
Execution Plan----------------------------------------------------------Plan hash value: 217629528
--------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 119 | 15048 (1)| 00:00:01 | | || 1 | HASH GROUP BY | | 1 | 119 | 15048 (1)| 00:00:01 | | ||* 2 | HASH JOIN | | 1 | 119 | 15047 (1)| 00:00:01 | | ||* 3 | HASH JOIN | | 1 | 100 | 14975 (1)| 00:00:01 | | || 4 | NESTED LOOPS | | | | | | | || 5 | NESTED LOOPS | | 1 | 65 | 14962 (1)| 00:00:01 | | || 6 | TABLE ACCESS FULL | D_PRODUKT | 305 | 5185 | 6 (0)| 00:00:01 | | || 7 | PARTITION RANGE ALL | | | | | | 1 | 49 || 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | ||* 9 | BITMAP INDEX SINGLE VALUE | IDX_BM_KAUF_PRODUKT_ID | | | | | 1 | 49 || 10 | TABLE ACCESS BY LOCAL INDEX ROWID| F_KAUF_PARTITION | 1 | 48 | 14962 (1)| 00:00:01 | 1 | 1 ||* 11 | TABLE ACCESS FULL | D_ZEIT | 31 | 1085 | 12 (0)| 00:00:01 | | || 12 | TABLE ACCESS FULL | D_REGION | 16461 | 305K| 71 (0)| 00:00:01 | | |--------------------------------------------------------------------------------------------------------------------------------
53
Ohne Partitioning
Elapsed: 00:00:30.10
Execution Plan----------------------------------------------------------Plan hash value: 2124788479
-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 80 | 4960 | 29304 (2)| 00:00:01 || 1 | HASH GROUP BY | | 80 | 4960 | 29304 (2)| 00:00:01 ||* 2 | HASH JOIN | | 154 | 9548 | 29303 (2)| 00:00:01 || 3 | VIEW | VW_GBC_13 | 154 | 6622 | 29231 (2)| 00:00:01 || 4 | HASH GROUP BY | | 154 | 11088 | 29231 (2)| 00:00:01 ||* 5 | HASH JOIN | | 4680K| 321M| 29003 (2)| 00:00:01 || 6 | TABLE ACCESS FULL | D_PRODUKT | 305 | 5185 | 6 (0)| 00:00:01 ||* 7 | HASH JOIN | | 4680K| 245M| 28974 (2)| 00:00:01 ||* 8 | TABLE ACCESS FULL| D_ZEIT | 31 | 1085 | 12 (0)| 00:00:01 || 9 | TABLE ACCESS FULL| F_KAUF | 15M| 287M| 28888 (1)| 00:00:01 || 10 | TABLE ACCESS FULL | D_REGION | 16461 | 305K| 71 (0)| 00:00:01 |-------------------------------------------------------------------------------------
54
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
55
Aktualität der Statistiken
Skripte mit allen Partitionen
Statistiken sammeln- auch für Partitioen
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SV','F_Kauf_partition', estimate_percent=>20, GRANULARITY => 'PARTITION');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('SV','F_KAUF',estimate_percent=>20);
56
Storage-Bedarf Regelmäßige Kontrolle der verbrauchten Ressourcen
select substr(segment_name,1,25) Segment, round((bytes/1000000000),2) GB,bytes, blocks,extents from user_segments where segment_type = 'TABLE‘ and segment like ‘F%’;
SEGMENT GB BYTES BLOCKS EXTENTS------------------------------ ---------- ---------- ---------- ----------F_KAUF .63 627048448 76544 146F_LIEFERUNG 2.58 2578448384 314752 222F_POSITION 1.95 1946157056 237568 212F_ZAHLUNGEN 4.1 4097835008 500224 245F_CHARGE 1.74 1744830464 212992 209F_STORNO .25 251658240 30720 101F_EINKAEUFE .01 12582912 1536 27F_RETOUREN .1 100663296 12288 83
57
Platzverbrauch von partitionierten Tabellen
TABLE_NAME SUM(NUM_ROWS) SUM(BLOCKS) GB------------------------------ ------------- ----------- ----------F_KAUF_PARTITION 15103875 126816 1.04F_LIEFERUNG_PARTITION 65924495 477444 3.91F_POSITION_PARTITION 67822775 420463 3.44F_ZAHLUNGEN_PARTITION 67832265 802824 6.58
SELECT table_name, sum(num_rows), sum(blocks), round(sum(((blocks*8192)/1000000000)),2) GB
FROM user_tab_partitionsgroup by table_name order by table_name
58
Einfache Tabellen
Partitionierte Tabellen
Komprimierung
alter table TMP_KAUF_POSITION move compress;
ALTER TABLE F_KAUF_PARTITION MOVE PARTITION APR10 COMPRESS FOR ALL OPERATIONS NOLOGGING;
59
Datenmengen ohne Partitionierung
Vorher Nachher Komp-Zeit Anz. Zeilen
F_ZAHLUNGEN 6.44 4.1 2:23.47 67.840.000
TMP_KAUF_POSITION 2,55 1.48 1:45.19 67.840.000
F_POSITION 3.36 1.95 1:18.92 67.840.000
F_LIEFERUNG 3.78 2.58 1:31.57 65.920.001
F_KAUF 0.87 0.63 0:17.95 15.104.000
60
Datenmengen mit Partitionierung
Mit Part.
OhnePart
Nachher Komp-Zeit Anz. Zeilen
F_ZAHLUNGEN 6.57 6.44 6.57 3 67.840.000
TMP_KAUF_POSITION 2,55 67.840.000
F_POSITION 3.44 3.36 3.44 4 67.840.000
F_LIEFERUNG 3.91 3.78 3.91 3 65.920.001
F_KAUF 1,03 0.87 1.03 4:17.95 15.104.000
61
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views
62
Qu
ery
Rew
rite
Transparanz: Abfragen nur auf die Original-Tabellen
Flexibel: Alle Felder einer Dimensionstabelle sind abfragbar
Stale: Stellt selbst fehlende Aktualität fest
ETL-Effizient: Aktualisiert sich selbst
Automatisches Summenmanagement Materialized Views
Kreise
Regionen
Bundesland
FaktenTabelle
Level 1
MaterializedView
Regionen_ID
Bundesland_ID
OrteOrte_ID
Kreise_ID
PLZ
Level 2
Level 3
Level 4
Re g
i on
e n D
i me n
s i on
63
Top/Alle_Artikel
Segement
Artikelsparte
Artikelgruppe
Artikel
Artikelcharge
Materialized Views sparen Plattenplatz und minimieren die Objektanzahl
MengeUmsatz
Summe pro Charge
Summe pro Artikel
Summe pro Gruppe
Summe pro Sparte
Summe pro Segement
Summe pro ChargeQ
ue
ry
R
ev
ie
w
64
Nested Materialized Views nutzen bereits ausgeführte IO-Leistung
Aufwendige Join-Operation
DIM_Zeit FAKT_Umsatz DIM_Produkte
Umsatz Prod.Gr A
Umsatz Prod. Gr B relativ zum Gesamtjahresumsatz
Summierung/Monat
Summierung/Jahr
Umsatz Prod.Gr B
Basistabellen
Materialized ViewLevel 1
Materialized ViewLevel 2
Materialized ViewLevel 3
Materialized ViewLevel 4
IO
65
Anwendung: Auswertungen über Ein-/Verkäufe
Level 1: Getrennte MAVs mit Monats-Aggregat pro Produkt1. auf Einkaufs-Fakten2. auf Verkaufs-Fakten
Level 2: Zusammenführen der Ein-/Verkaufs-Aggregation mit
Mengenvergleiche und BerechnungenLevel 3: Basierend auf den Mengen von Level 2 werden
Finanzberechnungen gemacht Level 3: Zusätzliche Berechnungen
3. Aggregation pro Produktgruppe / Segment usw
66
F_EINKAEUFE F_POSITION F_KAUF
Mv_EA_Finanz_Kum_Monat
Mav_Produkt_Monat_einkaeufe Mav_Produkt_Monat_Verkaeufe
Mav_Einkauf_Verkauf_Diff_Jahr Mv_EA_Menge_Kum_Monat
LFD_Bestands_Wert / Produkt / MonatLFD_Saldo / Produkt / MonatKumulierter EK / ProduktKumulierter VK / ProduktKumuliertes Saldo
LFD_Bestands_Menge / Produkt / MonatVK_Menge / Produkt / MonatEK_Menge / Produkt / MonatKumulierte EK Menge / ProduktKumulierte VK Menge / Produkt
Finanz-Sicht / Berechnungen
Bestands-/Lager-Sicht / Berechnungen
Jahres-Sicht
Mv_EA_Finanz_Kum_Gruppe_Monat
EA: Einkauf/VerkaufKum: kumuliert
Produktgruppen-Sicht
Level 1
Level 2
Level 3
Level 4
67
Das Materialized View Konzept
Optionen beim Anlegen Option BUILD DEFERRED Option REFRESH FORCE ON DEMAND Option ENABLE QUIRY REWRITE
Sprechende Namen wählen Definition nur auf dem untersten Level der
verbundenen Dimensionen Immer COUNT() in die SELECT-Definition dbms_mview.pmarker(u.rowid) – Funktion mit
einbauen Dimension-Table Definitionen einbauen
68
Materialized View Beispiel
create MATERIALIZED VIEW Mav_Zeit_Region_Produkt_kaufBUILD DEFERRED REFRESH FORCE ON DEMAND ENABLE QUERY REWRITEasselect sum(k.gesamt_wert), p.Produkt_ID, r.ort_id, z.zeit_id, COUNT(*)from F_Kauf K, d_Zeit Z, d_region R, d_produkt Pwhere K.zeit_id = Z.zeit_id and K.ort_id = R.ort_id and K.produkt_id = p.produkt_idgroup by p.Produkt_ID, r.ort_id, z.zeit_id
69
Dimension (Beispiel)
CREATE DIMENSION d_produkt LEVEL Produkt IS d_produkt.PRODUKT_ID LEVEL Produktgruppe IS d_produkt.PRODUKTGRP_ID LEVEL Segment is d_produkt.SEGMENT_ID LEVEL Vermittlungs_Art IS d_produkt.VERMITTL_ART_ID LEVEL Produktart IS d_produkt.PRODUKTART_ID LEVEL Kanal IS d_produkt.KANAL_ID LEVEL Alle_Produkte IS d_produkt.ALLE_PRODUKTEHIERARCHY H_Produkt_SEGment (Produkt CHILD OF Produktgruppe CHILD OF Segment CHILD OF Produktart CHILD OF Kanal CHILD OF Alle_Produkte )HIERARCHY H_Produkt_Vermittlungs_art (Produkt CHILD OF Produktgruppe CHILD OF Vermittlungs_Art CHILD OF Produktart CHILD OF Kanal CHILD OF Alle_Produkte )ATTRIBUTE att_Produkt LEVEL Produkt DETERMINES
(d_produkt.PRODUKT, d_produkt.VK_PREIS,d_produkt.PREF_LIEFER_ID)ATTRIBUTE att_Produktgruppe LEVEL Produktgruppe DETERMINES d_produkt.PRODUKTGRUPPEATTRIBUTE att_Segment LEVEL Segment DETERMINES d_produkt.SEGMENTATTRIBUTE att_Vermittlungs_Art LEVEL Vermittlungs_Art DETERMINES d_produkt.VERMITTL_ARTATTRIBUTE att_Vermittlungs_Art LEVEL Produktart DETERMINES d_produkt.PRODUKTARTATTRIBUTE att_KANAL LEVEL Kanal DETERMINES d_produkt.KANAL;
70
Themenübersicht
• Ausgangssituation• Architekturüberlegungen• Startpunkt Anwender• Namensregelungen• Das physische Modell• Verteilung auf Schichten• Schlüssel und Indizierung• Compression
Data Warehouse Technik im Fokus• Partitionierung• Statistiken• Storage-Bedarf• Materialized Views• Die Abfragen und die
erreichten Kennzahlen
71
Selectsum(kp.vk_wert)
Umsatz, p.segment from f_position kp, d_produkt p where
p.produkt_id = kp.produkt_id group by p.segment;
Umsatz pro Segment
UMSATZ SEGMENT---------- ---------------------- 25358040 Erstellungsleistung 203098265 Baumarktware 4365920 Buergschaft 5354648 Finanzgeschaeft80138814.9 IT-Ware 1331202 Darlehensvermittlung 44640800 Planungsleistung
72
select * from (select rank() over (partition by p.segment order by sum(kp.vk_wert) desc ) as rangfolge, p.produkt, p.segment, round(sum(kp.vk_wert),0) as umsatzfrom f_position_partition kp, d_produkt pwhere p.produkt_id = kp.produkt_id group by p.segment,p.produkt order by p.segment)where rangfolge < 4
Top 3 Produkte pro Segment
RANGFOLGE PRODUKT SEGMENT UMSATZ---------- --------------------------- ------------------------------- ---------- 1 Universal_Wagenheber Baumarktware 17361703 2 Bohrmaschine 800 Watt Baumarktware 8568086 3 Duschbecken 80 Baumarktware 7655290 1 Absicherungsbuergschaft Buergschaft 4365920 1 Kapitalvermittlung Darlehensvermittlung 1331202 1 Bauleitung Erstellungsleistung 22695550 2 Elektorarbeiten Erstellungsleistung 891822 3 Maurerarbeiten Erstellungsleistung 890588 1 Investitionsdarlehen Finanzgeschaeft 2646342 2 Hyothekendarlehen Finanzgeschaeft 2259600 3 Kleinkredit Finanzgeschaeft 448706 1 GRX_GRUMOR IT-Ware 25661311 2 SUN_AZOR_BIG IT-Ware 22529704 3 XT_MM IT-Ware 9559710 1 Architektenplan Planungsleistung 22605050 2 Statikplan Planungsleistung 22035750
73
Selectsum(kp.vk_wert) Umsatz_pro_Prod_Art, p.produktart
from f_position kp,
d_produkt pwhere
p.produkt_id = kp.produkt_id group by p.produktart
Verhältnis Handelsware / Vermittlungsleistung
UMSATZ_PRO_PROD_ART PRODUKTART--------------------------------------- 81050610 Vermittlung 283237080 Handelsware
74
Weitere Kennzahlen (Ausschnitt)(Marketing)
Kunden-Mengenverhältnis Privat-Kunden Firmen-Kunden
Umsatz-Verhältnis Privat-Kunden Firmen-Kunden
Verhältnis Umsatz mit und ohne Kundenkarte
select count(status), status from d_kunde group by status;
select sum(ka.GESAMT_WERT) Umsatz, kd.kundenkarte * from f_kauf ka, d_kunde kd where ka.kunden_id = kd.kunden_id group by kd.kundenkarte
select round(sum(ka.VK_WERT),0) Umsatz, p.segment from f_position ka, d_Produkt P where ka.produkt_id = p.produkt_id group by p.segment * order by umsatz
75
Bestell- und Liefervorgänge(Buchhaltung)
SQL> select * from(select count(kauf_id) Anz_Lieferungen from f_lieferung),(select sum(Gesamt_wert) Lieferwert from f_lieferung),(select sum(Lieferkosten) Kosten_Lieferungen from f_lieferung),(select count(kauf_id) Anz_Positionen from f_position),(select count(kauf_id) Anz_Kaeufe from f_kauf),(select count(kauf_id) Anz_Lieferfreie_Kaeufe
from f_kauf where produkt_id != 0),(select count(*) Anz_Stornos
from (select distinct Kauf_ID, Positions_ID from f_storno)),
(select sum(p.VK_Wert) Wert_Stornos from f_position p,f_storno s
where p.kauf_id = s.kauf_id and p.position_id = s.positions_id)
ANZ_LIEFERUNGEN LIEFERWERT KOSTEN_LIEFERUNGEN ANZ_POSITIONEN ANZ_KAEUFE ANZ_LIEFERFREIE_KAEUFE ANZ_STORNOS WERT_STORNOS--------------- ---------- ------------------ -------------- ---------- ---------------------- ----------- ------------ 5025000 1262158240 63107912 5135000 1115000 110000 2980 155510.87
76
Umsatz, Einkauf, Gewinn pro ProduktControllingselect a.Produkt,a.Umsatz_pro_Prod,b.einkauf_pro_Prod, (a.Umsatz_pro_Prod-b.einkauf_pro_Prod) Gewinn_pro_Prod from (select round(sum(kp.vk_wert),0) Umsatz_pro_Prod, p.produkt Produkt from f_position kp, d_produkt p where p.produkt_id = kp.produkt_id group by p.produkt) a, (select round(sum(e.menge*e.ek_preis),0) einkauf_pro_Prod, p.produkt Produkt from f_einkaeufe e, d_produkt p where p.produkt_id = e.produkt_id group by p.produkt) b where a.produkt = b.produkt order by a.Umsatz_pro_Prod
PRODUKT UMSATZ_PRO_PROD EINKAUF_PRO_PROD GEWINN_PRO_PROD-------------------------------------------------- --------------- ---------------- ---------------Luesterklemmen 1,5 23281 7132 16149Muffe 15mm 46744 12387 34357Reinigungstuecher 46821 12802 34019Eimer_10l 69715 19609 50106Sitzauflage 69738 20788 48950Schrauben_M12 69924 20750 49174Schrauben_M9 70052 22337 47715Bindeseil 70415 18577 51838
Top Related