© T. Kudraß, HTWK Leipzig
Warum?
Daten einer Firma verfügbar machen für Entscheidungsprozesse
– Umsetzung schwierig
neue Konzepte notwendig zur analytischen Informationsverarbeitung
– OLAP
– Data Warehousing
– Data Mining
OLAP Einleitung
DSS: Decision Support System
© T. Kudraß, HTWK Leipzig
Einführungsbeispiel
Umsatz pro Zeit und Produkt
Einführungsbeispiel
Umsatz pro Zeit, Produkt und Region
© T. Kudraß, HTWK Leipzig
Einführungsbeispiel
OLAP
OLAP erleichtert die Analyse von Kennzahlen unter verschiedenen Gesichtspunkten (Dimensionen)
– z.B. Produktmanager, Bereichsleiterin
– Kennzahlen
– graphische Darstellung (Diagramme)
Dynamische, multidimensionale Geschäftsanalyse mit Simulationskomponente
© T. Kudraß, HTWK Leipzig
Was ist OLAP?
OLAP ist ...
... ein Überbegriff für Technologien, Methoden und Tools zur Ad-hoc-Analyse
multidimensionaler Informationen ... bietet verschiedene Sichtweisen ... eine Komponente der
entscheidungsorientierten
Informationsverarbeitung
Analyse-Datenmodelle
kategorisches (beschreibendes) Modell
– statisches Analysemodell zur Beschreibung des gegenwärtigen Zustands
– Vergleich von historischen mit aktuellen Daten
exegetisches (erklärendes) Modell
– zur Erklärung der Ursachen für Zustand durch Nach- vollziehen der Schritte, die ihn hervorgebracht haben (durch einfache Anfragen)
kontemplatives (bedenkendes) Modell
– Simulation von „What If“Szenarios für vorgegebene Werte oder Abweichungen innerhalb einer Dimension oder über mehrere Dimensionen hinweg
formelbasiertes Modell
– gibt Lösungswege vor: ermittelt für vorgegebene Anfangs- und Endzustände, welche Veränderung für welche Kenngröße bzgl. welcher Kenngröße für ange- strebtes Ergebnis notwendig
Komplexität
OLAP Charakteristika
OLAP Charakteristika*
Multidimensionale konzeptionelle Sichten
funktionale Transparenz
unbeschränkter Zugriff auf operative und/oder externe Datenquellen
konsistente Berichtsgenerierung
Client-/Server Architektur
gleichgestellte Dimensionen
dynamische Behandlung dünn besetzter Datenwürfel
mehrere Anwender
unbeschränkte, dimensionsübergreifende Operationen Evaluierungsregeln nach E. F. Codd
© T. Kudraß, HTWK Leipzig
OLAP Charakteristika - FASMI
FASMI = Fast Analysis of Shared Multidimensional Information
Fast: 1-2 Sekunden als Antwortzeit bei einfachen Anfragen bis maximal 20 Sekunden für komplexe Datenanalysen
Analysis: Verfahren und Techniken zu einfachen mathematischen Berechnungen und
Strukturuntersuchungen
Shared: Schutzmechanismen für den Zugriff im Mehrbenutzerbetrieb
Multidimensional: Multidimensionale konzeptionelle
Sicht auf Informationsobjekte, d.h. freier Zugriff auf einen Datenwürfel und multiple Berichtshierarchien über die
Dimensionen
OLAP Charakteristika
Daten werden über Dimensionen beschrieben.
Begriffe: Multidimensionalität, Hypercubes, Ausprägungen (Members), Zellen
Zeit
Regionen
Produkte Jan Feb Mrz Apr Mai Jun Jul Aug Sep Okt Nov Dez
CD DVD Fernseher Video
Kühlschrank Sachsen-Anhalt
Thüringen Sachsen
© T. Kudraß, HTWK Leipzig
Dimensionen können Hierarchien haben.
Produkte
Zeit
Sachsen-A.
Sachsen Thüringen Lippenstift
Deo Haarzeugs
DVD CD
Jan Feb Mrz Apr Mai Jun Jul Aug Sep Okt Nov Dez
Elektro Kosmetik Alle Produkte
Alle Regionen
Q1 Q2 Q3 Q4 2000 Region
OLAP Charakteristika
Zu Hierarchien
Hierarchie
– Hierarchische Aufteilung der Dimension
DVD Kosmetik
Lipp.
Deo
CD Elektro
Produkte Haar...
Dimension Produkte
Architekturkonzepte
OLAP Grobarchitektur
© T. Kudraß, HTWK Leipzig
OLAP Architekturkonzepte
ROLAP = Relational OLAP
– bei Abbildung in Relationen: möglichst wenig Verlust von Semantik, die im multidimensionalen Modell
enthalten
– Effiziente Übersetzung und Abarbeitung von multidimensionalen Anfragen
– Einfache Wartung (z.B. Laden neuer Daten)
MOLAP = Multidimensional OLAP
– direkte Speicherung multidimensionaler Daten in multidimensionalen DBMS
HOLAP = Hybrid OLAP
– Kombiniert Vorteile von relationaler und multidimensionaler Realisierung
Architekturkonzept ROLAP
SQL zur Datentransformation
Multidimensionale Datenmodelle werden in 2- dimensionalen Tabellen gespeichert
Star-, Snowflake, Starflake-Schema
Facts Dim1ID Dim2ID Dim3ID Dim4ID Dim1
Star-Schema Dim2
Dim3 Dim4
Beispiel
© T. Kudraß, HTWK Leipzig
ROLAP - Star-Schema
erstellen von Fakten- und Dimensionstabellen
Faktentabelle mit Schlüsseln für Dimensionstabellen
in Dimensionstabellen stehen relevante Daten
Redundanz
– Alternative wäre Snowflake-Schema
– Dimensionsdaten relativ stabil
Architekturkonzept MOLAP
Speicherung erfolgt in multidimensionalen Speicherarrays
Multidimensionale Speicherstrukturen werden komplett übernommen
Hypercube
– Datenbestand in einem Würfel
Multicube
– kleinere Würfel
© T. Kudraß, HTWK Leipzig
Architekturkonzepte
Query and Calculation
Engine
OLAP- Fronten
d
MOLAP
Multidimen- sionales OLAP Daten werden multidimensional
ROLAP
Relationales OLAP
Daten werden relational
gespeichert
HOLAP Hybrides OLAP
Komplexe Anfragen können hohe Antwortzeiten verursachen
Grosse Datenmengen verarbeitbar
Schnelle Antwortzeiten auch auf komplexe Anfragen
Verarbeitbare Datenmenge beschränkt
Unterschiede OLTP/OLAP
Transaktionsorientierte Systeme Operative Systeme
Auswertungsorientierte Systeme
Weniger häufige, komplexe Anfragen Grosse Datenmengen je Anfrage Häufige, einfache Anfragen
Kleine Datenmengen je Anfrage
Schnelle Kalkulation wichtig Schneller Update wichtig
Paralleles Ausführung von OLAP-Anfragen auf operationalen Datenbe- ständen könnte Leistungsfähigkeit der OLTP-Anwendungen beeinträchtigen
Operieren auf aktuellen und historischen Daten
Operieren hauptsächlich auf aktuellen Daten
Datenbanksystem kann nicht gleichzeitig für OLTP- und für OLAP- Anwendungen optimiert werden
OLTP
(Online Transaction Processing)
OLAP
(Online Analytical Processing)
OLAP Funktionalität
OLAP Funktionen
Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden.
Drill- down
Roll- up
© T. Kudraß, HTWK Leipzig
OLAP Funktionalität
Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden.
Slice
Kennzahlen
Umsatz Gewinn
Produkte Regionen Zeit
Kennzahlen
Umsatz Gewinn
Produkte Regionen Zeit
Eine beliebige Kombination von Dimensionen und Aus- prägungen kann angezeigt werden.
OLAP Funktionalität
Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden.
Dice
Die Achsen können beliebig ausgetauscht
© T. Kudraß, HTWK Leipzig
OLAP Funktionalität
Drill Down
– erhöhen des Detaillierungsgrades
Roll Up
– invers zu Drill Down
Pivot
– betrachten aus unterschiedlichen Perspektiven
– rotate
Slice & Dice
– ändern des Analyseblickwinkels
© T. Kudraß, HTWK Leipzig
Themen
Materialized Views
Merge von Tabellen
SQL für Drill down und Roll up (ROLAP)
CUBE-Operator
Tabellen für Beispiel (Star Schema)
© T. Kudraß, HTWK Leipzig
OLAP Anfragebeispiel
CREATE MATERIALIZED VIEW Region_Marke_Quartal AS SELECT O.Region, P.Marke, Z.Quartal, SUM(V.Anz) AS Anzahl FROM (((Verkaufszahl V JOIN Ort O ON (V.Filiale=O.Filiale)) JOIN Zeit Z ON (V.Tag=Z.Tag))
JOIN Produkt P ON (V.Produkt=P.Produkt)) GROUP BY O.Region, P.Marke, Z.Quartal;
Erzeugen einer materialisierten Sicht:
CREATE MATERIALIZE VIEW Verdichtung mittels GROUP BY
Beispiel: „Ermittle die quartalsweises Verkaufszahlen pro Pizza-Marke und Region“
Materialisierte Sichten
vielbenutzte Aggregate (Analysen) materialisieren
schnellerer Zugriff auf Daten
Ablegen der Daten in eigene Relationen
OLAP-Anfragebeispiel
SELECT O.Region, SUM(V.Anz) AS Anzahl
FROM (((Verkaufszahl V JOIN Ort O ON (V.Filiale=O.Filiale)) JOIN Zeit Z ON (V.Tag=Z.Tag))
JOIN Produkt P ON (V.Produkt=P.Produkt)) GROUP BY O.Region
Verdichtung erhöhen durch Entfernung von Attributen aus der GROUP BY-Klausel.
Beispiel: „Ermittle die Gesamt-Verkaufszahlen für alle Regionen“
•weitere Verdichtungen möglich
weitere Verdichtungen möglich
automatisches Umschreiben der Anfrage durch Datenbank-Optimierer (query rewrite):
Verwenden der materialisierten Sicht zur weiteren Verdichtung
© T. Kudraß, HTWK Leipzig
SQL-Erweiterungen zum Einfügen
Kombination von Aktualisieren und Einfügen
Beispiel:
Liste neuer Produkte mit bestehender Tabelle Produkt mischen
MERGE INTO Produkt P1 USING (SELECT ID, Name
FROM Produkt_Neu) P2 ON (P1.ID = P2.ID)
WHEN MATCHED THEN
UPDATE SET P1.Name = P2.Name WHEN NOT MATCHED THEN
Komplexes Gruppieren
Beispieldaten für Gruppierungsanfragen
SELECT Monat, Produkt, SUM(Anz) AS Anz
FROM Ort_Produkt_Monat_Verkauf GROUP BY Monat, Produkt;
© T. Kudraß, HTWK Leipzig
Komplexes Gruppieren
SELECT
DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat,
DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS Anz
FROM Ort_Produkt_Monat_Verkauf GROUP BY ROLLUP (Monat, Produkt);
SELECT
DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat,
DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS Anz
FROM Ort_Produkt_Monat_Verkauf GROUP BY CUBE (Monat, Produkt);
Hinweise zum Beispiel
ROLLUP
– berücksichtigt auch Zwischen- und Endsummen
– Funktion GROUPING liefert 1 bei Summe
– DECODE kann Standard-Rückgabewert mit Text füllen
DECODE vergleichbar mit bedingter Anweisung
DECODE (ausdruck, if1, then1, if2, then2, …, else)
– ohne GROUPING/DECODE erscheinen Nullwerte
CUBE-Operator
– GROUP BY CUBE (Spalte1,Spalte2,Spalte3,…)
– k Dimensionen: 2k mögliche GROUP BY-Klauseln
© T. Kudraß, HTWK Leipzig
Zukünftige Entwicklung
Web OLAP mit Front End über Web
Verknüpfung von OLAP mit anderen Werkzeugen (z.B. Reporting)
Konzepte weiterentwickeln