OLAP-Operationen

Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Anfragen an multidimensonale Daten
Alexander Heidrich - BID8
09.06.2005
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Inhaltsübersicht
• Motivation
• OLAP-Operationen
• Umsetzung in Standards
– SQL
– MDX
• Anfragetypen und -verarbeitung
• Fragen? / Diskussion!
09.06.2005
Anfragen an multidimensionale Daten
2
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Motivation
• Daten sind im Data
Warehouse  Wie
kommt man nun an
seine Informationen?
• Theoretische Grundlagen
• Welche Erweiterungen
der vorhandenen
(Datenbank-)mittel gibt
es? Wie werden sie
benutzt?
09.06.2005
Anfragen an multidimensionale Daten
3
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Inhaltsübersicht
• Motivation
• OLAP-Operationen
• Umsetzung in Standards
– SQL
– MDX
• Anfragetypen und -verarbeitung
• Fragen? / Diskussion!
09.06.2005
Anfragen an multidimensionale Daten
4
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Online Analytic Processing
• Werkzeuge und Technologien, mit
denen ein zugrundeliegendes DW
benutzerfreundlich analysiert und
abgefragt werden kann
• eingesetzt zur Unterstützung von
Managemententscheidungen (decision
support)
09.06.2005
Anfragen an multidimensionale Daten
5
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen:
Ausgangssituation
09.06.2005
Anfragen an multidimensionale Daten
6
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Roll-Up – Wechsel
auf eine
übergeordnete
Betrachtungsebene
• Drill-Down –
Wechsel auf eine
untergeordnete
Betrachtungsbene
09.06.2005
Anfragen an multidimensionale Daten
7
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Drill-Across – Wechseln des betrachteten
Fakts unter Beibehaltung der
Betrachtungsebene
09.06.2005
Anfragen an multidimensionale Daten
8
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Slice – Zugriff auf
Daten einer
extrahierten Schicht
09.06.2005
Anfragen an multidimensionale Daten
9
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Dice – Zugriff auf Teilwürfel
09.06.2005
Anfragen an multidimensionale Daten
10
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
OLAP-Operationen
• Pivotierung –
Drehen des Würfels
durch Vertauschen
der Dimensionen
09.06.2005
Anfragen an multidimensionale Daten
11
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Inhaltsübersicht
• Motivation
• OLAP-Operationen
• Umsetzung in Standards
– SQL
– MDX
• Anfragetypen und -verarbeitung
• Fragen? / Diskussion!
09.06.2005
Anfragen an multidimensionale Daten
12
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Umsetzung in Standards - SQL
• SQL-OLAP-Erweiterungen (seit SQL:1999)
– (erweiterte) Gruppierungsfunktionalität
• GROUP BY
• GROUPINGSETS
• GROUPING()
• ROLLUP
• CUBE
– SQL-OLAP-Funktionen
• Aggregate, Partitioning, Windows, Ranking
• IBM und Oracle an Standardisierung beteiligt
 integriert in DB2 und Oracle (DBMS)
09.06.2005
Anfragen an multidimensionale Daten
13
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Beispieldaten
09.06.2005
Anfragen an multidimensionale Daten
14
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Aggregate
• Aggregatfunktionen COUNT(), SUM(),
MIN(), MAX(), AVG()
• Funktionen liefern in dieser Anwendung
nur einen einzelnen Wert
• Verwendung von GROUP BY
09.06.2005
Anfragen an multidimensionale Daten
15
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: GROUP BY – Beispiel
09.06.2005
Anfragen an multidimensionale Daten
16
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: GROUPING SETS
• GROUP BY immer noch umständlich bei
der Erzeugung von verschiedenen
Gruppierungen (z.B. (Jahr, Land),
(Jahr), (Jahr, Land, Verkäufe), etc.)
• Lösung: GROUPING SETS 
Gruppierungskombinationen
09.06.2005
Anfragen an multidimensionale Daten
17
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: GROUPING SETS – Beispiel
09.06.2005
Anfragen an multidimensionale Daten
18
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: ROLLUP-Operator
• Erweiterung der GROUP-BY-Syntax um ROLLUP-Operator
• erzeugt hierarchisch multidimensionale Gruppierung
• GROUPING()-Funktion zur Indentifizierung von NULLWerten
09.06.2005
Anfragen an multidimensionale Daten
19
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: CUBE-Operator
• Vorschlag von
Microsoft und IBM
• Erweiterung der
GROUP-BY-Syntax
um den CUBEOperator
• N-Dimensionale
Generalisierung der
einfachen
Aggregatfunktionen
09.06.2005
Anfragen an multidimensionale Daten
20
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
CUBE
entsprechend GROUPING SETS(
(),(Jahr),(Land),(Kategorie),
(Jahr,Land),(Jahr, Kategorie),(Land,Kategorie),
(Jahr,Land, Kategorie))
09.06.2005
Anfragen an multidimensionale Daten
21
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Cube
09.06.2005
Anfragen an multidimensionale Daten
22
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: OLAP-Funktionen
• mit SQL:1999 eingeführt
• umfassen:
– Aggregate
– Partionierung
– Window (Bildung dynamischer Fenster)
– Ranking
• Kernkonstrukt: Over-Klausel
09.06.2005
Anfragen an multidimensionale Daten
23
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Over-Klausel
09.06.2005
Anfragen an multidimensionale Daten
24
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Over() – Beispiel
• Over()  Aggregation
über alle Tupel
• Aggregatbildung, keine
weitere Verdichtung
09.06.2005
Anfragen an multidimensionale Daten
25
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Over() mit Partionierung - Beispiel
• Partitionierung ähnlich
Gruppierung
PARTITON BY <attribute list>
09.06.2005
Anfragen an multidimensionale Daten
26
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Over() Bildung dynamischer
Fenster
OVER(ORDER BY <attribute list> [<window-spec>])
09.06.2005
Anfragen an multidimensionale Daten
27
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Over() Bildung dynamischer
Fenster - Beispiel
09.06.2005
Anfragen an multidimensionale Daten
28
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Ranking
• Bestimmung der Postion eines Tupels in
der Ergebnismenge
• Duplikate erhalten den gleichen Rang
• RANK() (mit Lücken) DENSE_RANK()
(ohne Lücken)
09.06.2005
Anfragen an multidimensionale Daten
29
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Ranking - Beispiel
09.06.2005
Anfragen an multidimensionale Daten
30
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
SQL: Umsetzung in DB2 und Oracle
• Behauptung: DB2 und Oracle
hinsichtlich den in SQL:1999 definierten
OLAP-Erweiterungen/-funktionen gleich
• SQL:2003 bringt weitere analytische
Funktionen  bislang nur von/in Oracle
implementiert
09.06.2005
Anfragen an multidimensionale Daten
31
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX
• MDX = MultiDimensional eXpressions
• orientiert sich stark an SQL-Syntax und
MDDM
• Entwicklung/Vorschlag von Microsoft
09.06.2005
Anfragen an multidimensionale Daten
32
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX
• Dimensions (Select)
– max. 64
– on COLUMNS, ROWS, …
• Cube (From)
• Slicer (Where)
– Auswahl der darzustellenden Werte
09.06.2005
Anfragen an multidimensionale Daten
33
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX: Elemente
• Measures  Fakten
– als Dimension modelliert
• Dimensions  Dimensionen
– Level: Klassifikationsstufe (Jahr)
– Member: Klassifikationsknoten (2003)
• Syntax
– {} Sets
– [] Einschluß von Strings
09.06.2005
Anfragen an multidimensionale Daten
34
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX: Navigationsfunktionen
• Navigationsfunktionen
– Members: Knoten einer Klassifikationsstufe
– Children: Kinderknoten eines Klassifikationsknotens
– Parent: Elternknoten eines Klassifikationsknotens
SELECT {[Kategorie].MEMBERS}  Autos,
Computer
SELECT {[ATHLONXP].PARENT}  CPU
SELECT {[CPU].CHILDREN}  ATHLON64,
ATHLONXP, PENTIUM4
09.06.2005
Anfragen an multidimensionale Daten
35
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX: Beispiele
• Drill-Down
• Roll-Up
• Crossjoin
09.06.2005
Anfragen an multidimensionale Daten
36
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX: Beispiel
CROSSJOIN
Drill-Down
Roll-Up
09.06.2005
Anfragen an multidimensionale Daten
37
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
MDX: weitere Funktionen
• TOPCOUNT  Ergebnisbereich einer
Dimension einschränken
• FILTER  Einschränkung über beliebige
Bedingungen
• + viele weitere Funktionen  sehr
mächtige Sprache mit hoher
Komplexität
09.06.2005
Anfragen an multidimensionale Daten
38
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Inhaltsübersicht
• Motivation
• OLAP-Operationen
• Umsetzung in Standards
– SQL
– MDX
• Anfragetypen und -verarbeitung
• Fragen? / Diskussion!
09.06.2005
Anfragen an multidimensionale Daten
39
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Anfragetypen
09.06.2005
Anfragen an multidimensionale Daten
40
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Methoden der Abfrageverarbeitung – Star Join
• Star Schema als Grundlage
– sehr große Faktentabelle
– kleine, voneinander
unabhängige
Dimensionstabellen
• Verbund zwischen n
Dimensionstabellen und
der Faktentabelle,
Restriktionen über
Dimensionstabellen
• typisches Muster für DWAnfragen
09.06.2005
Anfragen an multidimensionale Daten
41
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Star Join: Aufbau
• SELECT-Klausel
– Kenngrößen
– Granularitäten
• FROM-Klausel
– Fakten- und Dimensionstabellen
• WHERE-Klausel
– Verbundbedingungen
– Restriktionen
09.06.2005
Anfragen an multidimensionale Daten
42
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Star Join: Beispiel
SELECT
Geographie.Region, Zeit.Monat, SUM(Verkaeufe)
FROM
Verkauf, Zeit, Produkte, Geographie
WHERE
Verkauf.Produkt_ID = Produkt.ProduktID AND
Verkauf.Zeit_ID = Zeit.ZeitID AND
Verkauf.Geographie_ID = Geographie.GeographieID AND
Produkt.Produktkategorie = 'Waschgeräte' AND
Geographie.Land = 'Deutschland' AND Zeit.Jahr = 2000
09.06.2005
Anfragen an multidimensionale Daten
43
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Star Join: Beispiel
09.06.2005
Anfragen an multidimensionale Daten
44
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Inhaltsübersicht
• Motivation
• OLAP-Operationen
• Umsetzung in Standards
– SQL
– MDX
• Anfragetypen und -verarbeitung
• Fragen? / Diskussion!
09.06.2005
Anfragen an multidimensionale Daten
45
Lehrstuhl für Datenbanken und
Blockseminar „Data Warehousing“ im SoSe 2005
Informationssysteme
Fragen? Diskussion!