indexierungsstrategie im data warehouse - zwischen albtraum und optimaler performance
DESCRIPTION
Dani Schnider, Principal Consultant bei der Trivadis AG, an der DOAG Konferenz 2011 in NürnbergTRANSCRIPT
2014 © Trivadis
BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN
2014 © Trivadis
INDEXIERUNGS-STRATEGIE IM DATA WAREHOUSEZWISCHEN ALBTRAUM UND OPTIMALER PERFORMANCE
DOAG Konferenz 2011
Dani SchniderTrivadis AG
Nürnberg, 15. November 2011
15. November 2011Indexierungsstrategie im Data Warehouse
1
2014 © Trivadis
Indexierungsstrategie im Data Warehouse2
Dani Schnider
Principal Consultant und DWH/BI Lead Architectbei Trivadis in Zürich
Kursleiter für Trivadis-Kurse über Data Warehousing, SQL Optimierung und Oracle Warehouse Builder
Co-Autor des Buches «Data Warehousing mit Oracle»
15. November 2011
2014 © Trivadis
11 Trivadis Niederlassungen mitüber 600 Mitarbeitenden
200 Service Level Agreements
Mehr als 4'000 Trainingsteilnehmer
Forschungs- und Entwicklungs-budget: CHF 5.0 / EUR 4 Mio.
Finanziell unabhängig undnachhaltig profitabel
Erfahrung aus mehr als 1'900 Projekten pro Jahr bei über 800 Kunden
Stand 12/2012
Hamburg
Düsseldorf
Frankfurt
FreiburgMünchen
Wien
Basel
ZürichBernLausanne
3
Stuttgart
Mit über 600 IT- und Fachexperten bei Ihnen vor Ort
15. November 2011Indexierungsstrategie im Data Warehouse
3
2014 © Trivadis
Trivadis ist führend bei der IT-Beratung, der Systemintegration, dem solution based Software- und Product-Engineering und der Erbringungvon IT-Services mit Fokussierung auf und Technologien im D-A-CH-Raum.
Unsere Leistungen erbringen wir aus den strategischen Geschäftsfeldern:
Durch unser Trainingsangebot stellen wir den Know-how-Transfer sicher.
Kurzvorstellung Trivadis
15. November 2011Indexierungsstrategie im Data Warehouse
4
2014 © Trivadis
Wie soll ich mein Data Warehouse indexieren?
15. November 2011Indexierungsstrategie im Data Warehouse
5
2014 © Trivadis
Es gibt verschiedene Meinungen...
Ein DWH-System hat mehr Indizes als ein OLTP-System
Indizes machen die Abfragen schneller
Full Table Scans sind langsam
Ein Data Warehouse sollte keine Indizes haben
Indizes verlangsamen die ETL-Prozesse
Jede Tabelle muss einen Primary Key haben
Indizes sind für ein effizientes Laden der Daten notwendig
Eine OLTP-Tabelle hat 2-3 Indizes, eine DWH-Tabelle 5-6
Bitmap Indizes sind für Attribute mit wenigen Werten sinnvoll
15. November 2011Indexierungsstrategie im Data Warehouse
6
2014 © Trivadis
DWH ≠OLTP
«It depends...»
15. November 2011Indexierungsstrategie im Data Warehouse
7
2014 © Trivadis
Data Warehouse Architektur
15. November 2011Indexierungsstrategie im Data Warehouse
8
Wie sollen die verschiedenen Schichten im DWH indexiert werden?
2014 © Trivadis
Staging Area / Cleansing Area
Staging Area
Keine Indizes
Cleansing Area
Primary Key / Unique Key Constraints für Datenvalidierung Oracle erstellt automatisch einen Unique Index pro PK/UK Eventuell Index erst nach dem Laden berechnen
(DISABLE / ENABLE CONSTRAINT)
Keine weitere Indizes
15. November 2011Indexierungsstrategie im Data Warehouse
9
2014 © Trivadis
Selektivität und Zugriffsmethode
15. November 2011Indexierungsstrategie im Data Warehouse
10
Selektivität = Prozentualer Anteil der Datensätze aus einer Tabelle
Indexzugriffe sind effizient für selektive Abfragen (< 1-2% der Daten) Gilt sowohl für B-Tree Index als auch Bitmap
Index
Full Table Scans sind für nicht-selektive Abfragen effizienter In Staging Area und Cleansing Area werden
immer 100% der Daten gelesen
2014 © Trivadis
Core
Zweck von Core Datenintegration von verschiedenen Quellsystemen Historisierung und Versionierung Datenbasis für Data Marts
Indexierung abhängig von verschiedenen Faktoren Logischem Datenmodell (dimensional / relational) Physischem Design (z.B. Partitionierung) Ladefrequenz der Data Marts
15. November 2011Indexierungsstrategie im Data Warehouse
11
2014 © Trivadis
Indexierung von Core
Dimensionales Core
Primary Key Constraints auf Dimensionstabellen Oracle erstellt automatisch einen Unique Index pro PK/UK Alternativer Ansatz: PK/UK disablen, um Index zu vermeiden
Keine Indizes auf Faktentabellen
Relationales Core
Primary Key / Unique Key Constraints auf Stammdaten Oracle erstellt automatisch einen Unique Index pro PK/UK Alternativer Ansatz: PK/UK disablen, um Index zu vermeiden
Möglichst wenige oder keine Indizes auf Bewegungsdaten Indexierung von Foreign Keys nicht notwendig
15. November 2011Indexierungsstrategie im Data Warehouse
12
2014 © Trivadis
Zeitliche Extraktion aus Core
Core dient als Basis zum Laden der Data Marts
Initiales Laden von Data Marts
Keine Datumeinschränkung
Eventuell Filter auf aktuelle Version der Stammdaten Bitmap Index auf VALID_TO
Inkrementelles Laden der Data Marts
Filter auf Gültigkeitsintervall der Stammdaten/Dimensionen Bitmap Index auf VALID_FROM
Filter auf Ereignisdatum der Bewegungsdaten/Fakten Bitmap Index auf Ereignisdatum
15. November 2011Indexierungsstrategie im Data Warehouse
13
2014 © Trivadis
Zeitliche Extraktion aus Core: Beispiel
Core enthält Daten der letzten 3 Jahre
Data Mart wird monatlich geladen Selektivität = 1/12/3 ≈ 2.8% Full Table Scan
Data Mart wird täglich geladen Selektivität = 1/365/3 ≈ 0.09% Index Scan
Besserer Ansatz: Core-Tabelle partitioniert pro Monat Selektivitätmonatlich = 1 = 100% Full Partition Scan
Selektivitättäglich= 1/31 ≈ 3.2% Full Partition Scan
15. November 2011Indexierungsstrategie im Data Warehouse
14
2014 © Trivadis
Benutzerzugriff auf Core
Endbenutzer haben keinen Lesezugriff auf Core Ausnahme: „Virtueller Data Mart“
Virtueller Data Mart
View Layer für Benutzerzugriff auf Core
Zusätzliche Bitmap Indizes auf Filterkriterien notwendig
15. November 2011Indexierungsstrategie im Data Warehouse
15
2014 © Trivadis
B-Tree Index oder Bitmap Index?
15. November 2011Indexierungsstrategie im Data Warehouse
16
2014 © Trivadis
B-Tree Index oder Bitmap Index?
Platzbedarf von Bitmap Index ist meistens kleiner Auch für Attribute mit vielen verschiedenen Werten
15. November 2011Indexierungsstrategie im Data Warehouse
17
10 100 1000 10000 1000000
50
100
150
200
250
300
350
Bitmap (scattered) Bitmap (sorted) B-tree index
2014 © Trivadis
In Data Warehouse generell Bitmap Indizes verwenden
B-Tree Indizes werden nur für Primary / Unique Keys verwendet
15. November 2011Indexierungsstrategie im Data Warehouse
18
2014 © Trivadis
Data Marts
Dimensionstabellen
Primary Key (Unique Index)
Bitmap Indizes auf zusätzlichen Filter-Spalten (optional)
Faktentabellen
Bitmap Indizes auf Dimensionsattributen (Foreign Keys)
Bitmap Join Indizes auf oft verwendete Filter-Spalten (optional)
Typischerweise kein Primary Key
15. November 2011Indexierungsstrategie im Data Warehouse
19
2014 © Trivadis
Abfrageoptimierung auf Star Schemas
Typische Abfragen: Filterkriterien auf (mehreren)
Dimensionstabellen Fakten werden durch Join mit
Dimensionen selektiert
Problem: Tabellen mit Restriktionen sollten
zuerst gelesen werden Es können jeweils nur zwei Tabellen
gejoined werden Keine Beziehungen zwischen
Dimensionstabellen
15. November 2011Indexierungsstrategie im Data Warehouse
20
1
2
3
3
2
1
2014 © Trivadis
Star Transformation
15. November 2011Indexierungsstrategie im Data Warehouse
21
2014 © Trivadis
Bitmap Join Index
Bitmap Index definiert auf Faktentabelle
Indexiertes Attribut gehört zu Dimensionstabelle
Möglich für Star Schemas und Snowflake Schemas
15. November 2011Indexierungsstrategie im Data Warehouse
22
2014 © Trivadis
Fazit
Staging Area, Cleansing Area, Core: Weniger ist mehr!
Data Marts: Einheitliche Indexierungsstrategie
Generell Bitmap Indexes verwenden, B-Tree Indexes nur für Primary/Unique Keys
Es gibt immer begründbare Ausnahmen
15. November 2011Indexierungsstrategie im Data Warehouse
23
2014 © Trivadis
BASEL BERN BRUGG LAUSANNE ZÜRICH DÜSSELDORF FRANKFURT A.M. FREIBURG I.BR. HAMBURG MÜNCHEN STUTTGART WIEN
2014 © Trivadis
Vielen Dank.Trivadis AG
Dani Schnider
Europa-Strasse 5CH-8152 Glattbrugg/ZürichSchweiz
Tel. +41 44 808 70 20Fax +41 44 808 70 21
15. November 2011Indexierungsstrategie im Data Warehouse
24