Data Warehousing: Eine Einführung#
Während wir in der vergangenen Woche gelernt haben, wie wir unsere Datenbank normalisieren können, ist dies im Data Warehousing, dem Thema dieser Woche, nur in sehr geringem Maße erwünscht.
Einführung#
In den vergangenen Wochen haben wir uns immer mit atomaren Anfragen auseinandergesetzt, welche auf gereinigten Datensätzen basierten, in denen wir auch nur einzelne einfache Ergebnisse aus der Datenbank abgefragt haben. Solch einzelne Anfragen, aber auch andere INSERT-, UPDATE- und DELETE-Befehle können in vielen SQL Dialekten auch zu sogenannten Transaktionen zusammengefasst werden. Dies dient dem Zweck, dass wenn z.B. ein Kunde ein Produkt in einem Online-Shop bestellt, dann auch der Bestand des Produkts angepasst werden muss. Transaktionen sind nicht prüfungsrelevant, dieser kleine Einschub dient aber der Namensgebung dieser Art von Datenbankverarbeitung, welche im Englischen unter dem Begriff Online Transactional Processing oder kurz OLTP bekannt ist.
OLAP#
Data Warehousing hingegen beschäftigt sich mit großen Mengen an historischen Daten (Anfragen brauchen Minuten oder Stunden, um ihr Ergebnis zu bestimmen) und analysiert diese. Da mittels Selektionen, Gruppierungen und Aggregationen Daten analysiert werden, wird diese Art der Datenbankverarbeitung auch als Online Analytical Processing oder kurz OLAP bezeichnet. Da man in diesem Bereich mit solch großen Mengen an Daten arbeitet, meidet man rechenintensive Operationen wie z.B. Joins, welche wir jedoch je mehr haben, desto mehr wir normalisieren.
Data Warehousing Schemas#
Im Kontext von Data Warehousing und OLAP Anfragen arbeiten wir dann bewusst auf Relationen, welche gar nicht oder nur sehr wenig normalisiert sind, damit wir eben möglichst wenig Joins haben, welche für sehr große Datenmengen (Terabyte oder mehr) sehr teuer werden können. Stattdessen werden die Daten in einem Data Warehouse als ein mehrdimensionaler Würfel betrachtet, welcher durch unterschiedliche Schemata dargestellt werden kann. In diesem Modul behandeln wir die folgenden Schemata:
Sternschema#
Im Sternschema existiert genau eine Tabelle pro Dimension, plus die Faktentabelle, welche die unterschiedlichen Dimensionen miteinander verbindet.
Schneeflockenschema#
Das Schneeflockenschema ist eine Erweiterung des Sternschemas, da die Dimensionen normalisiert sind. Das Schneeflockenschema hat somit mindestens genau so viele Relationen wie das Sternschema, häufig aber deutlich mehr.
Fullfact-Schema#
Das Fullfact-Schema versucht dann eine Balance zwischen den beiden zu finden, indem die Dimensionsrelationen normalisiert werden, deren Schlüssel aber auch einen Eintrag in der Faktentabelle erhalten.
Hinweis: Was für Implikationen die unterschiedlichen Schemata auf den Speicherbedarf und die Laufzeit einer OLAP-Anfrage haben, diskutieren Sie in Aufgabe 2.3 des Tutoriums.#
Slicing und Dicing#
Wenn wir dann einmal Daten zum Verarbeiten haben, dann können wir auf diesen wie gehabt SQL-Anfragen stellen. Dabei haben wir schon zuvor erwähnt, dass diese Anfragen einen analytischen Fokus haben. So werden Selektionen (WHERE) genutzt, um einzelne Abschnitte (Slices) aus dem Würfel zu schneiden und Gruppierungen (GROUP BY) um den Würfel oder einen Teil davon in mehrere kleinere Würfel zu zerlegen (Dices).
ROLLUP und CUBE#
Wie sich das Thema des Data Warehousing weiterentwickelt hat, so haben sich typische Anfragen ergeben, welche sehr beliebt sind, jedoch verhältnismäßig schwer in SQL zu schreiben sind, weshalb man für diese Anfragen unterschiedliche eingeführt hat.
Als Beispiel werden wir für beide Operationen Durchschnittsnoten nehmen. Der Einfachheit nehmen wir an, dass die Daten passend für uns aufbereitet sind.
CUBE#
Der CUBE-Operator erzeugt eine Ergebnismenge, in der alle Gruppierungssets des Kreuzprodukts der Gruppierungsattribute miteinander vereinigt werden. Der CUBE-Operator bildet somit das Kreuzprodukt aus den ihm übergebenen Attributen und vereinigt dann die Ergebnisse aus \(2^n\) Teilanfragen. Diese Funktionalität ist äußerst nützlich in OLAP-Szenarien, in denen Benutzer multidimensionale Analysen durchführen möchten.
Bsp.: Die Durchschnittsnote aller Studierenden, die Durchschnittsnoten aller Studierenden pro Fakultät, die Durchschnittsnote pro Studiengang und die Durchschnittsnoten aller Studierenden pro Fakultät und Studiengang.
SELECT Studiengang, Fakultät, AVG(Note)
FROM Student
GROUP BY CUBE(Studiengang, Fakultät)
Die Anfrage ist somit äquivalent zu folgender Anfrage.
SELECT Studiengang, Fakultät, AVG(Note)
FROM Student
GROUP BY Studiengang, Fakultät
UNION
SELECT Studiengang, NULL AS Fakultät, AVG(Note)
FROM Student
GROUP BY Studiengang
UNION
SELECT NULL AS Studiengang, Fakultät, AVG(Note)
FROM Student
GROUP BY Fakultät
UNION
SELECT NULL AS Studiengang, NULL AS Fakultät, AVG(Note)
FROM Student
ROLLUP#
Der ROLLUP-Operator erzeugt eine Ergebnismenge mit hierarchischen Aggregationsstufen; dies bedeutet, dass der ROLLUP-Operator \(n+1\) unterschiedliche Anfragen im Hintergrund bildet, dessen Zwischenergebnisse vereinigt werden. Dabei wird die Gruppierung sukzessiv gröber. Diese Eigenschaft ist entscheidend für die Durchführung von Drill-Down- und Roll-Up-Analysen in OLAP. Durch Verwendung des ROLLUP-Operators können Benutzer Daten auf verschiedenen Ebenen der Hierarchie zusammenfassen oder aufteilen, um unterschiedliche Analyseebenen zu betrachten.
Bsp.: Die Durchschnittsnote aller Studierenden, die Durchschnittsnoten aller Studierenden pro Studiengang und die Durchschnittsnoten aller Studierenden pro Fakultät und Studiengang.
SELECT Studiengang, Fakultät, AVG(Note)
FROM Student
GROUP BY ROLLUP(Studiengang, Fakultät)
Die Anfrage ist somit äquivalent zu folgender Anfrage.
SELECT Studiengang, Fakultät, AVG(Note)
FROM Student
GROUP BY Studiengang, Fakultät
UNION
SELECT Studiengang, NULL AS Fakultät, AVG(Note)
FROM Student
GROUP BY Studiengang
UNION
SELECT NULL AS Studiengang, NULL AS Fakultät, AVG(Note)
FROM Student