Indexierungsstrategie im Data Warehouse

INDEXIERUNGSSTRATEGIE IM
DATA WAREHOUSE
ZWISCHEN ALBTRAUM UND
OPTIMALER PERFORMANCE
BASEL
1
BERN
LAUSANNE
ZÜRICH
DÜSSELDORF
FRANKFURT A.M.
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
DOAG Konferenz 2011
Dani Schnider
Trivadis AG
Nürnberg, 15. November 2011
FREIBURG I.BR.
HAMBURG
MÜNCHEN
STUTTGART
WIEN
Dani Schnider
  Principal Consultant und
DWH/BI Lead Architect
bei 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»
2
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Trivadis Facts & Figures
11 Trivadis Niederlassungen mit über 550
Mitarbeitern
Hamburg
Finanziell unabhängig und nachhaltig
profitabel
Kennzahlen 2010
  Umsatz CHF 101 / EUR 73 Mio.
~180 MA
Düsseldorf
  Dienstleistungen für über 700
Kunden in mehr als 1‘800 Projekten
Frankfurt
  Über 170 Service Level Agreements
Stuttgart
Wien
Freiburg
Basel
Bern
Lausanne
3
Zürich
München
~20 MA
~350 MA
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
  Mehr als 5'000 Trainingsteilnehmer
  Forschungs- und Entwicklungsbudget: CHF 5.0 / EUR 3.6 Mio.
Das Besondere
Kundenindividuelle
Lösungskompetenz und
Herstellerunabhängigkeit
Technologiekompetenz
  bietet fundierte Methodenkenntnisse und eigenentwickelte
Vorgehensweisen   garantiert wiederholbare Qualität und Realisierungssicherheit   hat über 17 Jahre Expertise in Oracle und Microsoft   verfügt über ein eigenes Technology Center und setzt auf
technologische Exzellenz Lösungs- und IntegrationsKnow-how
  hat eine breite, branchenübergreifende Kundenbasis und jährlich über
1800 Projekte   verbindet technologisches Spezialistenwissen mit dem Verständnis für
die Business-Spezifika des Kunden Begleitung über den
gesamten IT-ProjektLifecycle
4
  begleitet den gesamten IT-Projekt-Lifecycle mit einem modularen
Dienstleistungsportfolio   bietet für jeden „Reifegrad“ die passende Dienstleistungs- und
Lösungskombination
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Wie soll ich mein
Data Warehouse
indexieren?
5
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Es gibt verschiedene Meinungen...
  Ein DWH-System hat mehr Indizes als ein OLTP-System
  Indizes machen die Abfragen schneller
  Fall 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
6
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
DWH ≠OLTP
«It depends...»
7
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Data Warehouse Architektur
Data Warehouse
Quellsysteme
Staging Area
Cleansing Area
ETL
Core
Marts
BI Plattform
Metadaten
  Wie sollen die verschiedenen Schichten im DWH indexiert werden?
8
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
9
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Selektivität und Zugriffsmethode
  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
10
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
11
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
12
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
13
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
14
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
B-Tree Index oder Bitmap Index?
B-Tree Index
(✗)
Geeignet für selektive Abfragen
✗
Bitmap Index
Mehrere Indizes können kombiniert
werden
Geeignet für selektive Abfragen
Geeignet für Kombinationen von
unselektiven Filterkriterien
=, <, <=, >=, BETWEEN, LIKE, IN
=, <, <=, >=, BETWEEN, LIKE, IN
(IS NULL)
!=, NOT IN, IS NULL, (IS NOT NULL)
Unique Index möglich
✗
Row Locking
✗
Standard Edition, Enterprise Edition
16
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Nur Enterprise Edition
B-Tree Index oder Bitmap Index?
  Platzbedarf von Bitmap Index ist meistens kleiner
  Auch für Attribute mit vielen verschiedenen Werten
350
300
250
200
150
100
50
0
10
100
Bitmap (scattered)
17
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
1000
Bitmap (sorted)
10000
B-tree index
100000
In Data Warehouse
generell Bitmap
Indizes verwenden
  B-Tree Indizes werden nur für Primary / Unique Keys verwendet
18
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
19
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Abfrageoptimierung auf Star Schemas
  Typische Abfragen:
1
  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
20
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
2
3
3
2
1
Star Transformation
Bitmap Index
SALES_PROD_BIX
Products
AND
Customers
Bitmap Index
SALES_CUST_BIX
21
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
Sales
(Fact Table)
Bitmap Join Index
  Bitmap Index definiert auf
Faktentabelle
  Indexiertes Attribut gehört zu
Dimensionstabelle
  Möglich für Star Schemas und
Snowflake Schemas
22
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
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
23
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
VIELEN DANK.
Trivadis AG
Dani Schnider
Europa-Strasse 5
CH-8152 Glattbrugg/Zürich
Schweiz
Tel. +
41 44 808 70 20
Fax +
41 44 808 70 21
[email protected]
www.trivadis.com
BASEL
24
BERN
LAUSANNE
ZÜRICH
DÜSSELDORF
FRANKFURT A.M.
2011 © Trivadis
Indexierungsstrategie im Data Warehouse
15. November 2011
FREIBURG I.BR.
HAMBURG
MÜNCHEN
STUTTGART
WIEN