OLAP. © T. Kudraß, HTWK Leipzig Warum? Daten einer Firma verfügbar machen für...

© 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


Einführungsbeispiel

Umsatz pro Zeit und Produkt

UmsatzJan Feb Mrz Q1 Apr ... 2000

Haarzeugs 33 55 56 144 18 ... 760Lippenstift 72 136 117 325 74 ... 1338Deo 85 128 99 312 92 ... 1662Kosmetik 190 319 272 781 184 ... 3760DVD 55 69 99 223 84 ... 1051CD 22 17 47 86 39 ... 493Elektro 77 86 146 309 123 ... 1544Alle Produkte 267 405 418 1090 307 ... 5304



Alle RegionenJan Feb Mrz Q1 Apr ... 2000


Umsatz ThüringenJan Feb Mrz Q1 Apr ... 2000


Umsatz pro Zeit, Produkt und Region

Umsatz Sachsen AnhaltJan Feb Mrz Q1 Apr ... 2000


Umsatz, SachsenJan Feb Mrz Q1 Apr ... 2000




Umsatz, Sachsen Anhalt, TelefonJan Feb Mrz Q1 Apr ... 2000

Haar... 11 26 22 59 4 ... 299Lippenstift 16 54 49 119 18 ... 480Deo 29 34 35 98 18 ... 402Kosmetik 56 114 106 276 40 ... 1181DVD 19 18 53 90 27 ... 482CD 6 5 12 23 15 ... 202Elektronik 25 23 65 113 42 ... 684Alle Produkte 81 137 171 389 82 ... 1865

Umsatz, S-A, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz,Sachsen Anahlt , FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz,S-A, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz Sachsen, FAXJan Feb Mrz Q1 Apr ... 2000


Umsatz, Sachsen, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, Telefon,HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz, Alle Regionen, Telefon, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000


Umsatz, Thüringen, TelefonJan Feb Mrz Q1 Apr ... 2000


Umsatz, Th, HomepageJan Feb Mrz Q1 Apr ... 2000


Umsatz,Thüringen , FaxJan Feb Mrz Q1 Apr ... 2000


Umsatz, Th, Alle DistributionskanäleJan Feb Mrz Q1 Apr ... 2000



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


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 Dimensionoder über mehrere Dimensionen hinweg

formelbasiertes Modell– gibt Lösungswege vor: ermittelt für vorgegebene

Anfangs- und Endzustände, welche Veränderung fürwelche Kenngröße bzgl. welcher Kenngröße für ange-strebtes Ergebnis notwendig

Kom

plex

itä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


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



Daten werden über Dimensionen beschrieben.

Begriffe: Multidimensionalität, Hypercubes, Ausprägungen (Members), Zellen

ZeitReg

ionen

Pro

dukt

e

Ja

n

Fe

b

Mrz

Ap

r

Ma

i

Ju

n

Ju

l

Au

g

Se

p

Ok

t

No

v

De

z

CD

DVD

Fernseher

Video

KühlschrankSachsen-Anhalt

ThüringenSachsen


Dimensionen können Hierarchien haben.

Pro

dukt

e

Zeit

Sachsen-A.Sachsen

Thüringen

Lippenstift

Deo

Haarzeugs

DVD

CD

Ja

n

Fe

b

Mrz

Ap

r

Ma

i

Ju

n

Ju

l

Au

g

Se

p

Ok

t

No

v

De

z

Elektro

Kosmetik

Alle Produkte

Alle Regionen

Q1

Q2

Q3

Q4

20

00 Region



Zu Hierarchien

Hierarchie– Hierarchische Aufteilung der Dimension

DVD

KosmetikLipp.

Deo

CD

Elektro

Produkte Haar...DimensionProdukte

Architekturkonzepte


OLAP Grobarchitektur


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

Verkaufszahl

FilialeTagProduktAnzahl

1

N

N

1

N

1

ProduktProduktMarkeHerstellerProduktgruppe

OrtFilialeStadtRegionLand

Zeit

TagWocheMonatQuartalJahr

Dim2

Dim3

Dim4

Beispiel


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


Architekturkonzepte

Query and Calculation

Engine

OLAP-Fronten

d

MOLAP Multidimen-sionales OLAP

Daten werdenmultidimensionalgespeichert

ROLAPRelationales OLAP

Daten werden relational gespeichert

HO

LA

P

Hyb

rid

es O

LA

P

Komplexe Anfragen können hohe Antwortzeiten verursachen

Grosse Datenmengen verarbeitbar

Schnelle Antwortzeiten auch auf komplexe Anfragen

Verarbeitbare Datenmenge beschränkt


Unterschiede OLTP/OLAP

Transaktionsorientierte SystemeOperative Systeme

Auswertungsorientierte Systeme

Weniger häufige, komplexe Anfragen

Grosse Datenmengen je Anfrage

Häufige, einfache Anfragen

Kleine Datenmengen je Anfrage

Schnelle Kalkulation wichtigSchneller 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


Umsatz, JunCH D A Alle Regionen

Kosmetik 200 100 50 350DVD 100 75 20 180CD 20 35 10 80Elektro 120 110 30 260Alle Produkte 320 210 80 610

OLAP Funktionen

Die multidimensionalen Daten können am Bildschirm flexibel präsentiert werden.

Umsatz, JunSA Th S Alle Regionen

Kosmetik 200 100 50 350Elektro 120 110 30 260Alle Produkte 320 210 80 610

Drill-down

Roll-up




SliceKennzahlenUmsatzGewinn

ProdukteRegionenZeit

KennzahlenUmsatzGewinn

ProdukteRegionenZeit

UmsatzAlle Regionen

Qtr1 900Qtr2 1300Qtr3 1200Qtr4 17002000 5100

UmsatzAlle Regionen

Kosmetik 3200Elektro 1900Alle Produkte 5100

Eine beliebige Kombination von Dimensionen und Aus-prägungen kann angezeigt werden.




DiceUmsatzSA Th S Alle R.

KosmetikQtr1 200 100 50 350Elektro 120 110 30 260Alle P. 320 210 80 610KosmetikQtr2 180 90 50 320

... ... ... ... ...Alle P. 270 130 80 480Kos... 2000 910 390 180 1480E... 370 310 190 870Alle P. 1280 700 370 2350

Die Achsen können beliebig ausgetauscht werden.

UmsatzKos... Elektro Alle P.

Qtr1 SA 200 120 320Th 100 110 210SA 50 30 80Alle R. 350 260 610

Qtr2 SA 180 100 280Th 90 50 140

... ... ... ...2000 SA 910 370 1280

Th 390 310 700SA 180 190 370Alle R. 1480 870 2350


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

OLAP in SQL


Themen Materialized Views

Merge von Tabellen

SQL für Drill down und Roll up (ROLAP)

CUBE-Operator


Tabellen für Beispiel (Star Schema)OrtFiliale Stadt Region LandHamburg Hamburg Nord DLeipzig Leipzig Ost DStuttgart Stuttgart Süd DBremen-Nord Bremen Nord DBremen-Süd Bremen Nord DMünchen München Süd D

ZeitTag Woche Monat Quartal Jahr05.01.2006 2006-1 2006-1 2006-Q1 200612.01.2006 2006-2 2006-1 2006-Q1 200613.02.2006 2006-7 2006-2 2006-Q2 200623.02.2006 2006-8 2006-2 2006-Q1 200604.03.2006 2006-9 2006-3 2006-Q1 200607.04.2006 2006-14 2006-4 2006-Q2 200625.04.2006 2006-17 2006-4 2006-Q2 2006

ProduktProdukt Marke Hersteller ProduktgruppePizza Funghi Gourmet-Pizza Frost GmbH TiefkühlkostPizza Hawaii Gourmet-Pizza Frost GmbH TiefkühlkostPizza Napoli Pizza TK-Pizza AG TiefkühlkostPizza Vegetale Good&Cheap Frost GmbH TiefkühlkostPizza Calzione Pizza TK-Pizza AG Tiefkühlkost

VerkaufszahlFiliale Produkt Tag AnzHamburg Pizza Funghi 05.01.2006 78Hamburg Pizza Funghi 12.01.2006 67Leipzig Pizza Hawaii 12.01.2006 42München Pizza Calzione13.02.2006 53Stuttgart Pizza Napoli 23.02.2006 23Bremen-Nord Pizza Funghi 04.03.2006 69Bremen-Süd Pizza Vegetale07.04.2006 45Stuttgart Pizza Hawaii 25.04.2006 92


OLAP Anfragebeispiel

CREATE MATERIALIZED VIEW Region_Marke_Quartal ASSELECT O.Region, P.Marke, Z.Quartal, SUM(V.Anz) AS AnzahlFROM (((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 VIEWVerdichtung mittels GROUP BY

Marialized View: Region_Marke_QuartalRegion Marke Quartal AnzahlNord Gourmet-Pizza 2006-Q1 214Nord Good&Cheap 2006-Q2 45Ost Gourmet-Pizza 2006-Q1 42Süd Pizza 2006-Q1 76Süd Gourmet-Pizza 2006-Q1 92

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 AnzahlFROM (((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 ausder GROUP BY-Klausel.

Region AnzahlNord 249Ost 42Süd 168

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


SQL-Erweiterungen zum Einfügen Kombination von Aktualisieren und Einfügen Beispiel:

Liste neuer Produkte mit bestehender Tabelle Produkt mischen

ProduktID Name4711 Pizza Funghi4712 Pizza Quattro Stagione4713 Pizza Vegetale

Produkt_NeuID Name4711 Pilz-Pizza4712 Pizza Quattro Stagione4713 Pizza Vegetale4714 Pizza Hawaii

MERGE INTO Produkt P1USING (SELECT ID, Name FROM Produkt_Neu) P2ON (P1.ID = P2.ID)WHEN MATCHED THEN UPDATE SET P1.Name = P2.NameWHEN NOT MATCHED THEN INSERT (P1.ID, P1.Name) VALUES (P2.ID, P2.Name)

ProduktID Name4711 Pilz-Pizza4712 Pizza Quattro Stagione4713 Pizza Vegetale4714 Pizza Hawaii


Komplexes Gruppieren

Beispieldaten für Gruppierungsanfragen

Ort_Produkt_Monat_VerkaufOrt Produkt Monat AnzStuttgart Pizza Funghi 2006-1 155Stuttgart Pizza Vegatale 2006-1 133Stuttgart Pizza Hawaii 2006-1 89Stuttgart Pizza Funghi 2006-2 141Stuttgart Pizza Vegetale 2006-2 112Stuttgart Pizza Hawaii 2006-2 95Frankfurt Pizza Funghi 2006-1 77Frankfurt Pizza Vegatale 2006-1 93Frankfurt Pizza Hawaii 2006-1 102Frankfurt Pizza Funghi 2006-2 144Frankfurt Pizza Vegetale 2006-2 178Frankfurt Pizza Hawaii 2006-2 177

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 272

SELECT Monat, Produkt,SUM(Anz) AS Anz

FROM Ort_Produkt_Monat_VerkaufGROUP BY Monat, Produkt;


Komplexes Gruppieren

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-1 Alle Produkte 6492006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 2722006-2 Alle Produkte 847Alle Monate Alle Produkte 1496

SELECT DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat, DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS AnzFROM Ort_Produkt_Monat_VerkaufGROUP BY ROLLUP (Monat, Produkt);

Monat Produkt Anz2006-1 Pizza Funghi 2322006-1 Pizza Vegatale 2262006-1 Pizza Hawaii 1912006-1 Alle Produkte 6492006-2 Pizza Funghi 2852006-2 Pizza Vegetale 2902006-2 Pizza Hawaii 2722006-2 Alle Produkte 847Alle Monate Pizza Funghi 517Alle Monate Pizza Vegetale 516Alle Monate Pizza Hawaii 463Alle Monate Alle Produkte 1496

SELECT DECODE (GROUPING (Monat),1,‘Alle Monate‘, Monat) AS Monat, DECODE (GROUPING(Produkt),1,‘Alle Produkte‘, Produkt) AS Produkt, SUM(Anz) AS AnzFROM Ort_Produkt_Monat_VerkaufGROUP 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– bietet Ansatz zur Optimierung


Zukünftige Entwicklung Web OLAP mit Front End über Web

Verknüpfung von OLAP mit anderen Werkzeugen (z.B. Reporting)

Konzepte weiterentwickeln

Anwendungen auf horizontalen oder vertikalen Markt zuschneiden (bestimmte Branche oder bestimmte Unternehmensfunktion)

OLAP. © T. Kudraß, HTWK Leipzig Warum? Daten einer Firma verfügbar machen für...

Documents

Transcript of OLAP. © T. Kudraß, HTWK Leipzig Warum? Daten einer Firma verfügbar machen für...