LUDWIGMAXIMILIANSUNIVERSITY MUNICH
DEPARTMENT INSTITUTE FOR INFORMATICS
DATABASE SYSTEMS GROUP
Skript zur Vorlesung:
Datenbanksysteme II Sommersemester 2014
Kapitel p 7
Grundlagen von Data Warehouses Vorlesung: PD Dr. Peer Kröger http://www.dbs.ifi.lmu.de/cms/Datenbanksysteme_II © Peer Kröger 2014 Dieses Skript basiert im Wesentlichen auf den Skripten zur Vorlesung Datenbanksysteme II an der LMU München von Prof. Dr. Christian Böhm (SoSe 2007), PD Dr. Peer Kröger (SoSe 2008, 2014) und PD Dr. Matthias Schubert ((SoSe 2009) 9)
DATABASE SYSTEMS GROUP
7. Grundlagen von DWHs 7.1 Einleitung g 7.2 Multidimensionale Datenmodellierung g
Datenbanksysteme II― 7. Grundlagen v. DWHs
2
DATABASE SYSTEMS GROUP
7. Grundlagen von DWHs 7.1 Einleitung g 7.2 Multidimensionale Datenmodellierung g
Datenbanksysteme II― 7. Grundlagen v. DWHs
3
DATABASE SYSTEMS GROUP
7.1 Einleitung
Zwei Arten von DB-Anwendungen g – Online Transaction Processing (OLTP) • Routinetransaktionsverarbeitung • Realisierung des operationalen Tagesgeschäfts wie “Buchen eines Flugs” Verarbeitung einer Bestellung” Bestellung “Verarbeitung “Ein- und Verkauf von Waren” …
• Charakteristik: – – – –
Arbeitet auf dem jüngsten, aktuellsten Zustand der Daten Änderungstransaktionen (kurze Lese Lese-/Schreibzugriffe) /Schreibzugriffe) Zugriff auf sehr begrenzte Datenmenge Sehr kurze Antwortzeiten erwünscht (ms-s)
• OLTP-Datenbanken optimieren typischerweise den logischen und physischen DB-Entwurf hinsichtlich dieser Charakteristik
Datenbanksysteme II― 7. Grundlagen v. DWHs
4
DATABASE SYSTEMS GROUP
7.1 Einleitung
Zwei Arten von DB-Anwendungen g ((cont.)) – Online Analytical Processing (OLAP) • Bilden Grundlage für strategische Unternehmensplanung (Decision Support) • Anwendungen wie „Entwicklung Entwicklung der Auslastung der Transatlantik-Flüge Transatlantik Flüge über die letzten 2 Jahre? Jahre?“ „Auswirkungen spezieller Marketingaktionen auf Verkaufszahlen der Produkte?“ „Voraussichtliche Verkaufszahl eines Produkts im nächsten Monat?“ …
• Charakteristik: – – – –
Arbeitet mit „historischen“ Daten (lange Lesetransaktionen) Zugriff auf sehr große Datenmengen Meist Integration, Konsolidierung und Aggregation der Daten Mittlere Antwortzeiten akzeptabel (s-min) (s min)
• OLAP-Datenbanken optimieren typischerweise den logischen und physischen DB-Entwurf hinsichtlich dieser Charakteristik Datenbanksysteme II― 7. Grundlagen v. DWHs
5
DATABASE SYSTEMS GROUP
7.1 Einleitung
Zwei Arten von DB-Anwendungen g ((cont.)) – OLTP- und OLAP-Anwendungen sollten nicht auf demselben Datenbstand ausgeführt werden • Unterschiedliche Optimierungsziele beim Entwurf • Komplexe OLAP-Anfragen könnten die Leistungsfähigkeit der OLTPAnwendungen beeinträchtigen
– Data Warehouse • Datenbanksystem, indem alle Daten für OLAP-Anwendungen OLAP Anwendungen in konsolidierter Form gesammelt werden • Integration von Daten aus operationalen DBs aber auch aus Dateien (E (Excel, l …), ) … • Daten werden dabei oft in aggregierter Form gehalten • Enthält historische Daten • Regelmäßige Updates (periodisch)
Datenbanksysteme II― 7. Grundlagen v. DWHs
6
DATABASE SYSTEMS GROUP
7.1 Einleitung
Operationales p DBS vs. Data Warehouse
Ziel Focus auf Versionen DB Größe DB-Größe DB-Operationen Zugriffe pro Op. Op Leistungsmaß
operationales ti l DBS Abwicklung des Geschäfts D t il D t Detail-Daten nur aktuelle Daten ~ 1 GB Updates und Anfragen ~ 10 Datensätze Durchsatz
Datenbanksysteme II― 7. Grundlagen v. DWHs
Data D t W Warehouse h Analyse des Geschäfts aggregierten i t D Daten t gesamte Historie der Daten ~ 1 TB nur Anfragen ~ 11.000.000 000 000 Datensätze Antwortzeit
7
DATABASE SYSTEMS GROUP
7.1 Einleitung
Begriff g A Data Warehouse is a subject-oriented, integrated, non-volatile, and time variant collection of data to support management decisions [W.H. Inmon, 1996]
– Fachorientierung (subject-oriented) (subject oriented) • System dient der Modellierung eines spezifischen Anwendungsziel (meist g g in Unternehmen)) Entscheidungsfindung • System enthält nur Daten, die für das Anwendungsziel nötig sind. Für das Anwendungsziel irrelevante Daten werden weggelassen
– Integrierte Datenbasis (integrated) • Verarbeitung der Daten aus unterschiedlichen Datenquellen
Datenbanksysteme II― 7. Grundlagen v. DWHs
8
DATABASE SYSTEMS GROUP
7.1 Einleitung
Begriff g A Data Warehouse is a subject-oriented, integrated, non-volatile, and time variant collection of data to support management decisions [W.H. Inmon, 1996]
– Nicht-flüchtige Nicht flüchtige Datenbasis (non (non-volatile) volatile) • Stabile, persistente Datenbasis • Daten im Data Warehouse werden nicht mehr entfernt oder geändert
– Historische Daten (time variant) • Vergleich der Daten über die Zeit möglich • Speicherung über längeren Zeitraum
Datenbanksysteme II― 7. Grundlagen v. DWHs
9
DATABASE SYSTEMS GROUP
7.1 Einleitung
Architektur eines Data Warehouse operationale DB
operationale DB flat files
operationale DB
Einzelne Abteilungen • OLTP
spread sheets
Integration Initiales Laden und periodische Auffrischung des Data Warehouse
Management • OLAP
Data Warehouse
• Decision Support Systems (DSS) • Executive Information Systems (EIS) • Data D t Mi Mining i
Datenbanksysteme II― 7. Grundlagen v. DWHs
10
DATABASE SYSTEMS GROUP
7.1 Einleitung
Data Warehouses und Data Marts – Data Mart: inhaltlich beschränkte Sicht auf das Data Warehouse • Gründe: Eigenständigkeit, Datenschutz, Lastenverteilung, … • Realisierung: Verteilung der DW-Datenbasis • Klassen: – Abhä Abhängige i D Data t Marts: M t Verteilung V t il eines i b bestehenden t h d DW DWs => Analysen auf DM konsistent zu Analysen auf gesamten DW – Unabhängige Data Marts: unabhängig voneinander entstandene „kleine“ DWs, nachträgliche Integration zum globalen DW => unterschiedliche Analysesichten Analyse des Kaufverhaltens im Internetshop Data Mart 1
Analyse des Kaufverhaltens in den Filialen
…
Data Mart n
DWH Datenbanksysteme II― 7. Grundlagen v. DWHs
11
DATABASE SYSTEMS GROUP
7. Grundlagen von DWHs 7.1 Einleitung g 7.2 Multidimensionale Datenmodellierung g
Datenbanksysteme II― 7. Grundlagen v. DWHs
12
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Datenmodellierung g – Datenmodell sollte bzgl. Analyseprozess optimiert werden – Datenanalyse im Entscheidungsprozess • Betriebswirtschaftliche Kennzahlen stehen im Mittelpunkte (z.B. Erlöse, Gewinne, Verluste, Umsätze, …) => Fakten • Betrachtung dieser Kennzahlen aus unterschiedlichen Perspektiven (z.B. zeitlich, regional, produktbezogen, …) > Dimensionen => • Unterteilung der Auswertungsdimensionen möglich (z.B. zeitlich: Jahr, Quartal, Monat; regional: Bundesländer, Bezirke, Städte/Gemeinden; …) => Hierarchien, Hierarchien Konsolidierungsebenen
Datenbanksysteme II― 7. Grundlagen v. DWHs
13
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Kennzahlen/Fakten – Kennzahlen/Fakten • Numerische Messgrößen • Beschreiben betriebswirtschaftliche Sachverhalte
– Beispiele: Umsatz, Gewinn, Verlust, … – Typen • Additiv: (additive) Berechnung zwischen sämtlichen Dimensionen möglich (z.B. Bestellmenge eines Artikels) • Semi-additiv: S i dditi (additive) ( dditi ) Berechnung B h möglich ö li h mitit A Ausnahme h ttemporaler l Dimension(z.B. Lagerbestand, Einwohnerzahl) • Nicht-Additiv: keine additive Berechnung möglich (z.B. Durchschnittswerte, prozentuale t l Werte, W t …))
Datenbanksysteme II― 7. Grundlagen v. DWHs
14
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Dimensionen – Dimension • Beschreibt mögliche Sicht auf die assoziierte Kennzahl • Endliche Menge von d 2 Dimensionselementen (Hierarchieobjekten), die eine semantische Beziehung aufweisen • Dient der orthogonalen Strukturierung des Datenraums
– Beispiele: Produkt, Geographie, Zeit
Datenbanksysteme II― 7. Grundlagen v. DWHs
15
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Hierarchien in Dimensionen – Dimensionselemente sind Knoten einer Klassifikationshierarchie – Klassifikationsstufe beschreibt Verdichtungsgrad g g – Darstellung von Hierarchien in Dimensionen über Klassifikationsschema Land – Formen • Einfache Hierarchien: höhere Ebene
Stadt
– enthält die aggregierten Werte genau gena – einer niedrigeren Hierarchiestufe
Filiale
• Parallele Hierarchien: innerhalb einer – Dimension sind mehrere verschiedene – Arten der Gruppierung möglich
Jahr Monat
Quartal
Woche
Tag
Datenbanksysteme II― 7. Grundlagen v. DWHs
16
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Data-Cubes – Grundlage der multidimensionalen Datenanalyse: Datenwürfel (Data-Cube) – Kanten des Cubes: Dimensionen – Zellen des Cubes: ein oder mehrere Kennzahlen (als Funktion der Di Dimension) i ) – Anzahl der Dimensionen: Dimensionalität des Cubes – Visualisierung Vi li i • 2 Dimensionen: Tabelle • 3 Dimensionen: Würfel • >3 Dimensionen: Multidimensionale Domänenstruktur
Datenbanksysteme II― 7. Grundlagen v. DWHs
17
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Artikeel
Kategorie
Proodukt
Beispiel: p 3D Data-Cube
Kennzahl: Umsatz Region Filiale Stadt Bundesland
Datenbanksysteme II― 7. Grundlagen v. DWHs
18
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Standardoperationen p zur Datenanalyse y – Pivotisierung/Rotation • Drehen des Data-Cube durch Vertauschen der Dimensionen • Datenanalyse aus verschiedenen Perspektiven Produkt • Beispiel: Zeitraum
Produkt
Region
Datenbanksysteme II― 7. Grundlagen v. DWHs
Zeitraum
Region 19
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Standardoperationen p zur Datenanalyse y ((cont.)) – Roll-Up • Erzeugen neuer Informationen durch Aggregierung der Daten entlang der Klassifikationshierarchie in einer Dimension (z.B. Tag => Monat => Quartal => Jahr) • Dimensionalität bleibt erhalten
– Drill-Down • Komplementär zu Roll Roll-Up Up • Navigation von aggregierten Daten zu Detail-Daten entlang der Klassifikationshierarchie
– Drill-Across • Wechsel von einem Cube zu einem anderen
Datenbanksysteme II― 7. Grundlagen v. DWHs
20
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Standardoperationen p zur Datenanalyse y ((cont.)) – Beispiel: Roll-Up, Drill-Down
Produkt
Produkt 1. Quartal 2. Quartal
Drill-Down 2006
3. Quartal
Roll-Upp 2007
4. Quartal
Summe
Summe
Zeitraum
Region
Datenbanksysteme II― 7. Grundlagen v. DWHs
Zeitraum
Region
21
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Standardoperationen p zur Datenanalyse y ((cont.)) – Slice und Dice • Erzeugen individueller Sichten • Slice: – Herausschneiden von „Scheiben“ aus dem Cube (z B alle Werte eines Quartals) (z.B. – Verringerung der Dimensionalität
• Dice: – Herausschneiden eines „Teil-Cubes“ (z.B. Werte bestimmter Produkte und Regionen) – Erhaltung g der Dimensionalität – Veränderung der Hierarchieobjekte
Datenbanksysteme II― 7. Grundlagen v. DWHs
22
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Standardoperationen p zur Datenanalyse y ((cont.)) – Beispiel: Slice Produkt
Produkt
Summe Elektro
Summe Elektro
Haushalt Kosmetik
Haushalt Kosmetik
Slice 2006
2006
2007
2007
Summe
Summe
Region
Datenbanksysteme II― 7. Grundlagen v. DWHs
Region
23
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Umsetzung g des multidimensionalen Modells – Interne Verwaltung der Daten durch • Relationale Strukturen (Tabellen) – Relationales OLAP (ROLAP) – Vorteile: Verfügbarkeit, Reife der Systeme
• Multidimensionale Strukturen (direkte Speicherung) – Multidimensionales OLAP (MOLAP) – Vorteil: Wegfall der Transformation
– Wichtige Designaspekte • Speicherung • Anfragebearbeitung
Datenbanksysteme II― 7. Grundlagen v. DWHs
24
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Anforderungen g – Vermeidung des Verlusts anwendungsbezogener Semantik aus dem multidimensionalen Modell (z.B. Klassifikationshierarchien) – Effiziente Übersetzung multidimensionaler Anfragen – Effiziente Verarbeitung der übersetzten Anfragen – Einfache Pflege der entstandenen Relationen (z.B. Laden neuer Daten) – Berücksichtigung B ü k i hti d der A Anfragecharakteristik f h kt i tik und dd des D Datenvolumens t l von Analyseanwendungen
Datenbanksysteme II― 7. Grundlagen v. DWHs
25
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Faktentabelle – Ausgangspunkt: Umsetzung des Data-Cubes ohne Klassifikationshierarchien • Dimensionen und Kennzahlen => Attribute der Relation • Zellen => Tupel der Relation Zeit
Kennzahl
Dimensionen
04.04.2008 05 04 2008 05.04.2008
Vanish Oxy Action
Filiale
Tag g
Verkäufe
Vanish Ox. A. Vanish Ox. A. Kukident K kid t Kukident Vanish Ox. A.
Horb Horb Horb R th Roth Roth
04.04.2008 05.04.2008 04.04.2008 04 04 2008 04.04.2008 05.04.2008
4 1 12 0 2
…
Kukident
Artikel
Ro oth
Ho orb
Produkt
Datenbanksysteme II― 7. Grundlagen v. DWHs
Region
26
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Snowflake Schema – Abbildung von Klassifikationen? – Eigene g Tabelle für jjede Klassifikationsstufe ((Artikel, Produktgruppe, g pp …) – Klassifikationstabellen enthalten • ID für entsprechenden Klassifikationsknoten • Beschreibende Attribute (Marke, Hersteller, Bezeichnung, …) • Fremdschlüssel der direkt übergeordneten Klassifikationsstufe
– Faktentabelle enthält • Kenngrößen • Fremdschlüssel der jeweils niedrigsten Klassifikationsstufe der einzelnen Dimensionen • Fremdschlüssel bilden zusammengesetzte Primärschlüssel der Faktentabelle
Datenbanksysteme II― 7. Grundlagen v. DWHs
27
7.2 Multidim. Datenmodellierung
DATABASE SYSTEMS GROUP
Snowflake Schema: Beispiel p Jahr 1
*
Tag
*
TagID Datum atu MonatID WocheID
JahrID Jahr
Monat *
MonatID Name JahrID
Artikel 1
1
1
ArtikelID Bezeichnung * G GruppeID ID
ProduktGruppe GruppeID Bezeichnung K KategorieID i ID
1
ProduktKategorie KategorieID Bezeichnung
*
1
Woche
*
1
WocheID Nummer
Verkauf ArtikelID TagID FilialID Verkaeufe Umsatz
*
* 1
Fili l Filiale FilialID Bezeichnung StadtID *
1
Stadt
*
StadtID Name LandID
Faktentabelle
Bundesland 1
LandID Name
Kl ifik ti t b ll Klassifikationstabelle Datenbanksysteme II― 7. Grundlagen v. DWHs
28
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Star Schema – Snowflake Schema ist normalisiert • Keine Update-Anomalien • ABER: Zusammenholen von Informationen erfordert Join über mehrere Tabellen
– Star Schema • Denormalisierung der zu einer Dimension gehörenden Tabellen • Für jede Dimension genau eine Dimensionstabelle • Redundanzen in der Dimensionstabelle für schnellere Anfragebearbeitung
Datenbanksysteme II― 7. Grundlagen v. DWHs
29
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Star Schema: Beispiel p Zeit ZeitID Tag Woche Monat Quartal Jahr
Produkt 1
1
Verkauf *
*
Geographie GeographieID Filiale Stadt Bundesland
ProduktID Artikelname Produktgruppe Produktkategorie
ProduktID ZeitID GeographieID Verkaeufe Umsatz
*
1
Datenbanksysteme II― 7. Grundlagen v. DWHs
30
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Mischformen – Idee: Abbildung einzelner Dimensionen anhand von Snowflake oder Star Schema • Kriterien – – – – –
Änderungshäufigkeit der Dimensionen Reduzierung des Pflegeaufwands => Snowflake Anzahl der Klassifikationsstufen einer Dimension Höhere Effizienz durch größere Redundanz => Star ...
– Galaxie (Multi-Cube, Hyper-Cube) • Mehrere Faktentabellen im Star Schema teilweise mit gleichen Dimensionstabellen verknüpft
– Fact Constellation • Speicherung vorberechneter Aggregate in Faktentabelle (z.B. Umsatz für Region) Datenbanksysteme II― 7. Grundlagen v. DWHs
31
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Relationale Umsetzung: g Probleme – Transformation multidimensionaler Anfragen in relationale Repräsentation nötig – Einsatz komplexer Anfragewerkzeuge nötig (OLAP-Werkzeuge) – Semantikverlust S tik l t • Unterscheidung zwischen Kennzahlen und Dimensionen in der Faktentabelle nicht gegeben • Unterscheidung zwischen beschreibenden Attributen und Attributen zum Hierarchie-Aufbau in Dimensionstabellen nicht gegeben
– Daher: direkte multidimensionale Speicherung besser ???
Datenbanksysteme II― 7. Grundlagen v. DWHs
32
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Multidimensionale Umsetzung g – Idee: • Verwende entsprechende Datenstrukturen für Data-Cube und Dimensionen • Speicherung des Data-Cube als Array • Ordnung der Dimensionen nötig nötig, damit Zellen des Data Data-Cube Cube addressiert werden können
– Bemerkung g • Häufig proprietäre Strukturen (und Systeme)
Datenbanksysteme II― 7. Grundlagen v. DWHs
33
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Multidimensionale Umsetzung g ((cont.)) – Datenstruktur für eine Dimension • Endliche geordnete Liste von Dimensionswerten (aller Klassifikationsstufen) • Dimensionswerte: einfache, atomare Datentypen (String Integer (String, Integer, Date Date, …))
– Datenstruktur für Cube • Für d Dimensionen: d-dimensionaler Raum • Bei m Werten in einer Dimension: Aufteilung des Würfels in m parallele Ebenen => endliche gleichgroße Liste von Ebenen je Dimension • Zelle Z ll eines i d d-dimensionalen di i l C Cubes b wird i d eindeutig i d ti üb über d Dimensionswerten identifiziert p Array y • Pro Kennzahl in Zelle ein entsprechendes
Datenbanksysteme II― 7. Grundlagen v. DWHs
34
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Multidimensionale Umsetzung g ((cont.)) – Speicherung des Data-Cube: • Linearisierung des d-dimensionalen Arrays in ein 1-dimensionales Array • Koordinaten der Würfelzellen (Dimensionen) „entsprechen“ Indizes des Arrays • Indexbrechnung für Zelle mit Koordinaten z = x1, …, xd
Index( z )
x1 ( x2 1) | D1 | ( x3 1) | D1 | | D2 |
D3 D1 D2
( xd 1) | D1 | | Dd 1 |
Datenbanksysteme II― 7. Grundlagen v. DWHs
35
DATABASE SYSTEMS GROUP
7.2 Multidim. Datenmodellierung
Multidimensionale Umsetzung g ((cont.)) – Vorteile • Direkte OLAP-Unterstützung • Analytische Mächtigkeit
– Grenzen • Hohe Zahl an Plattenzugriffen bei ungünstiger Linearisierungsreihenfolge • Durch die Ordnung der Dimensionswerte (für Array-Abbildung nötig) keine einfache Änderung an Dimensionen möglich • Kein Standard für multidimensionale DBMS
– Oft: Hybride y Speicherung p g HOLAP = MOLAP + ROLAP • Relationale Speicherung der Datenbasis • Multidimensionale Speicherung für häufig aggregierte Daten (z.B. angefragte f t (Teil-)Data (T il )D t Cubes) C b )
Datenbanksysteme II― 7. Grundlagen v. DWHs
36
