OLAP T Kudra HTWK Leipzig Warum l Daten

OLAP

© T. Kudraß, HTWK Leipzig Warum? l Daten einer Firma verfügbar machen für Entscheidungsprozesse – l Umsetzung schwierig neue Konzepte notwendig zur analytischen Informationsverarbeitung – – – OLAP Data Warehousing Data Mining

© T. Kudraß, HTWK Leipzig OLAP Einleitung DSS: Decision Support System

© T. Kudraß, HTWK Leipzig Einführungsbeispiel Umsatz pro Zeit und Produkt

© T. Kudraß, HTWK Leipzig Einführungsbeispiel Umsatz pro Zeit, Produkt und Region

© T. Kudraß, HTWK Leipzig Einführungsbeispiel

© T. Kudraß, HTWK Leipzig OLAP l OLAP erleichtert die Analyse von Kennzahlen unter verschiedenen Gesichtspunkten (Dimensionen) – – – l 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

© T. Kudraß, HTWK Leipzig Analyse-Datenmodelle kategorisches (beschreibendes) Modell – – l exegetisches (erklärendes) Modell – l zur Erklärung der Ursachen für Zustand durch Nachvollziehen der Schritte, die ihn hervorgebracht haben (durch einfache Anfragen) kontemplatives (bedenkendes) Modell – l statisches Analysemodell zur Beschreibung des gegenwärtigen Zustands Vergleich von historischen mit aktuellen Daten 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 angestrebtes Ergebnis notwendig Komplexität l

OLAP Charakteristika

© T. Kudraß, HTWK Leipzig OLAP Charakteristika* Evaluierungsregeln nach E. F. Codd l l l l l 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

© T. Kudraß, HTWK Leipzig OLAP Charakteristika - FASMI = Fast Analysis of Shared Multidimensional Information l Fast: 1 -2 Sekunden als Antwortzeit bei einfachen Anfragen bis maximal 20 Sekunden für komplexe Datenanalysen l Analysis: Verfahren und Techniken zu einfachen mathematischen Berechnungen und Strukturuntersuchungen l Shared: Schutzmechanismen für den Zugriff im Mehrbenutzerbetrieb l Multidimensional: Multidimensionale konzeptionelle Sicht auf Informationsobjekte, d. h. freier Zugriff auf einen Datenwürfel und multiple Berichtshierarchien über die Dimensionen

© T. Kudraß, HTWK Leipzig OLAP Charakteristika CD DVD Sachsen-Anhalt Thüringen Sachsen Dez Nov Okt Sep Aug Jul Jun Mai Apr Mrz Feb Jan Kühlschrank Zeit Begriffe: Multidimensionalität, Hypercubes, Ausprägungen (Members), Zellen eg i on e Video n Fernseher R Produkte Daten werden über Dimensionen beschrieben.

© T. Kudraß, HTWK Leipzig OLAP Charakteristika Dimensionen können Hierarchien haben. Alle Produkte Lippenstift Deo Haarzeugs Elektro DVD Zeit 2000 Q 4 Dez Nov Okt Q 3 Sep Aug Jul Q 2 Jun Mai Apr Q 1 Mrz Feb CD Jan Produkte Kosmetik Alle Regionen Sachsen-A. Sachsen Thüringen Re on i g

© T. Kudraß, HTWK Leipzig Zu Hierarchien l Hierarchie – Hierarchische Aufteilung der Dimension Lipp. Kosmetik Deo Dimension Produkte Haar. . . DVD Elektro CD

Architekturkonzepte

© T. Kudraß, HTWK Leipzig OLAP Grobarchitektur

© T. Kudraß, HTWK Leipzig OLAP Architekturkonzepte l ROLAP = Relational OLAP – – – l MOLAP = Multidimensional OLAP – l 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) direkte Speicherung multidimensionaler Daten in multidimensionalen DBMS HOLAP = Hybrid OLAP – Kombiniert Vorteile von relationaler und multidimensionaler Realisierung

© T. Kudraß, HTWK Leipzig Architekturkonzept ROLAP l l l SQL zur Datentransformation Multidimensionale Datenmodelle werden in 2 dimensionalen Tabellen gespeichert Star-, Snowflake, Starflake-Schema Dim 4 Dim 1 Star-Schema Facts Dim 1 ID Dim 2 ID Dim 3 ID Dim 4 ID Dim 2 Dim 3 Beispiel

© T. Kudraß, HTWK Leipzig ROLAP - Star-Schema l l 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

© T. Kudraß, HTWK Leipzig Architekturkonzept MOLAP l Speicherung erfolgt in multidimensionalen Speicherarrays l Multidimensionale Speicherstrukturen werden komplett übernommen l Hypercube – l Datenbestand in einem Würfel Multicube – kleinere Würfel

© T. Kudraß, HTWK Leipzig Architekturkonzepte Hybrides OLAP HOLAP Relationales OLAP Daten werden relational gespeichert Komplexe Anfragen können hohe Antwortzeiten verursachen Grosse Datenmengen verarbeitbar Query and Calculation Engine OLAPFrontend MOLAP Multidimensionales OLAP Daten werden multidimensional gespeichert Schnelle Antwortzeiten auch auf komplexe Anfragen Verarbeitbare Datenmenge beschränkt

© T. Kudraß, HTWK Leipzig Unterschiede OLTP/OLAP Transaktionsorientierte Systeme Operative Systeme Auswertungsorientierte Systeme OLTP (Online Transaction Processing) OLAP (Online Analytical Processing) Häufige, einfache Anfragen Weniger häufige, komplexe Anfragen Kleine Datenmengen je Anfrage Grosse Datenmengen je Anfrage Operieren hauptsächlich auf aktuellen Daten Operieren auf aktuellen und historischen Daten Schneller Update wichtig Schnelle Kalkulation wichtig Datenbanksystem kann nicht gleichzeitig für OLTP- und für OLAPAnwendungen optimiert werden Paralleles Ausführung von OLAP-Anfragen auf operationalen Datenbeständen könnte Leistungsfähigkeit der OLTP-Anwendungen beeinträchtigen

OLAP Funktionalität

© T. Kudraß, HTWK Leipzig OLAP Funktionen Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden. Drilldown Rollup

© 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 Eine beliebige Kombination von Dimensionen und Ausprägungen kann angezeigt werden. Umsatz Gewinn Produkte Regionen Zeit

© T. Kudraß, HTWK Leipzig OLAP Funktionalität Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden. Dice Die Achsen können beliebig ausgetauscht werden.

© T. Kudraß, HTWK Leipzig OLAP Funktionalität l Drill Down – l Roll Up – l invers zu Drill Down Pivot – – l erhöhen des Detaillierungsgrades betrachten aus unterschiedlichen Perspektiven rotate Slice & Dice – ändern des Analyseblickwinkels

OLAP in SQL

© T. Kudraß, HTWK Leipzig Themen l Materialized Views l Merge von Tabellen l SQL für Drill down und Roll up (ROLAP) l CUBE-Operator

© T. Kudraß, HTWK Leipzig Tabellen für Beispiel (Star Schema)

© T. Kudraß, HTWK Leipzig OLAP Anfragebeispiel Erzeugen einer materialisierten Sicht: CREATE MATERIALIZE VIEW Verdichtung mittels GROUP BY Beispiel: „Ermittle die quartalsweises Verkaufszahlen pro Pizza-Marke und Region“ 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; Materialisierte Sichten l vielbenutzte Aggregate (Analysen) materialisieren l schnellerer Zugriff auf Daten l Ablegen der Daten in eigene Relationen

© T. Kudraß, HTWK Leipzig OLAP-Anfragebeispiel Verdichtung erhöhen durch Entfernung von Attributen aus der GROUP BY-Klausel. Beispiel: „Ermittle die Gesamt-Verkaufszahlen für alle Regionen“ 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 l l • 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 l l Kombination von Aktualisieren und Einfügen Beispiel: Liste neuer Produkte mit bestehender Tabelle Produkt mischen MERGE INTO Produkt P 1 USING (SELECT ID, Name FROM Produkt_Neu) P 2 ON (P 1. ID = P 2. ID) WHEN MATCHED THEN UPDATE SET P 1. Name = P 2. Name WHEN NOT MATCHED THEN INSERT (P 1. ID, P 1. Name) VALUES (P 2. ID, P 2. Name)

© T. Kudraß, HTWK Leipzig Komplexes Gruppieren SELECT Monat, Produkt, SUM(Anz) AS Anz FROM Ort_Produkt_Monat_Verkauf GROUP BY Monat, Produkt; Beispieldaten für Gruppierungsanfragen

© 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);

© T. Kudraß, HTWK Leipzig Hinweise zum Beispiel l ROLLUP – – – berücksichtigt auch Zwischen- und Endsummen Funktion GROUPING liefert 1 bei Summe DECODE kann Standard-Rückgabewert mit Text füllen l l – l DECODE vergleichbar mit bedingter Anweisung DECODE (ausdruck, if 1, then 1, if 2, then 2, …, else) ohne GROUPING/DECODE erscheinen Nullwerte CUBE-Operator – – – GROUP BY CUBE (Spalte 1, Spalte 2, Spalte 3, …) k Dimensionen: 2 k mögliche GROUP BY-Klauseln bietet Ansatz zur Optimierung

© T. Kudraß, HTWK Leipzig Zukünftige Entwicklung l Web OLAP mit Front End über Web l Verknüpfung von OLAP mit anderen Werkzeugen (z. B. Reporting) l Konzepte weiterentwickeln l Anwendungen auf horizontalen oder vertikalen Markt zuschneiden (bestimmte Branche oder bestimmte Unternehmensfunktion)