{ "cells": [ { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "# Data Warehousing: Aufgaben\n" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "## Aufgabe 1\n", "\n", "Gegeben sind die unten stehenden Anwendungsszenarien; kategorisieren Sie diese als OLAP- oder OLTP-Anfragen?\n", "\n", "1. Kassenverwaltung im Supermarkt.\n", "1. Auswirkung von Werbekampagnen auf Verkaufszahlen bestimmen.\n", "1. Ticketwebseite für Konzerte.\n", "1. Überwachung des Flugraums (Fluglotsen).\n", "1. Identifizieren der wichtigsten Kunden.\n", "1. „Wird oft zusammen gekauft“ (z.B. bei Amazon)." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "## Aufgabe 2\n", "\n", "Gegeben sei der folgende OLAP Würfel\n", "\n", "\n", "\n", "Der Würfel beschreibt die Clickstream-Datenbank. Diese ist eine einfache Datenbank, welche eine Aufzeichnung der von einem Benutzer auf einer Website getätigten Klicks speichert.\n", "\n", "Eine solche Datenbank kann zum Beispiel benutzt werden für:\n", "- Marketing \n", "- Click Fraud Detection\n", "- Verbesserung des Websitedesigns\n", "\n", "\n", "\n", "Für die Modellierungen gelten die folgenden Eigenschaften:\n", "\n", "\n", "* Dimensionen: Page, User, Session\n", "* Eine Zelle beschreibt eine Zusammenfassung der Klicks, die ein bestimmter Nutzer in einer bestimmten Session auf einer bestimmten Page getätigt hat.\n", "* Beachte: Logische Repräsentation, die meisten Zellen sind leer!\n", "\n", "User haben die folgenden Attribute:\n", "- Name, Email, Alter, Adresse, Kreditkartennummer, Kreditkarteninhaber, Kreditkartenablaufdatum, \n", "\n", "Session hat die folgenden Attribute:\n", "- Sessionstart, Sessionende, IP, Browsertyp, Browserversion, Betriebssystem\n", "\n", "Page hat die folgenden Attribute:\n", "- Seitenname, Seitendomain, Seitensubdomain" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 2.1 Modellierung\n", "\n", "Im Folgenden modellieren Sie die Klicks mittels aller drei Schemata, welche Sie in diesem Modul zu Data Warehousing kennengelernt haben. \n", "\n", "#### Aufgabe 2.1.1: Sternschema\n", "\n", "Modellieren Sie die Klicks in einem Sternschema. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.1.2: Schneeflockenschema\n", "\n", "Modellieren Sie die Klicks in einem Schneeflockenschema. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 2.1.2: Fullfact-Schema\n", "\n", "Modellieren Sie die Klicks in einem Fullfact-Schema. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 2.2 Vergleich der Schemata\n", "\n", "Wie viele Joins benötigen wir in jedem Schema, um für alle Nutzer aus Berlin, die am 21.06.21 auf der Seite dima.tu-berlin.de waren, die Daten auszuwerten?" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 2.3\n", "\n", "Was sind die Vorteile und Nachteile der jeweiligen Schemata?" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "## Aufgabe 3\n", "\n", "Gegeben sei das folgende **Sternschema** zum Modellieren einer Verkaufsdatenbank aus dem Star-Schema-Benchmark.\n", "\n", "\n", "Dieser OLAP-Würfel hat 5 Dimensionen, die als Dimensionstabellen modelliert sind: Customer, Supplier, Part sowie Orderdate und Commitdate.\n", "\n", "Außerdem gibt es noch weitere Dimensionen, die direkt in der Faktentabelle modelliert sind, z.B. orderpriority und shippriority.\n", "\n", "Als Erstes laden wir die Beispieldatenbank:" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "import duckdb\n", "\n", "con = duckdb.connect(database=\"resources/08_data_warehousing/ssb.duckdb\", read_only=False)" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "Zuerst testen wir die Datenbankverbindung, indem wir die Anzahl der Einträge in der Faktentabelle zählen." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT count(*)\n", "FROM lineorder\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 3.1: Drill down mittels Dicing und Slicing\n", "\n", "Die Faktentabelle enthält ca. 60000 Zeilen. Jeder Eintrag ist ein einzelner Bestellposten. \n", "\n", "(Ein Bestellposten ist Teil einer Bestellung und besteht unter anderem aus einem Produkt und der Anzahl des Produkts. Stellen Sie sich eine Rechnung eines Gemüsehändlers vor, mit 10 Äpfeln und 20 Birnen. Dann sind z.B. die 10 Äpfel ein Bestellposten, d.h., 1 Eintrag in der Faktentabelle.)\n", "\n", "Die Datenbank enthält nur Bestellungen aus dem Januar 1992.\n", "\n", "Im Folgenden führen wir ein Drill down mittels Dicing und Slicing durch.\n", "\n", "Zuerst führen wir ein Dicing durch. Wir erstellen einen OLAP-Würfel, der den Umsatz (revenue) gruppiert nach Jahr des Bestelldatums und der die Herkunftsregion der Käufer beinhaltet." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT year, region, sum(revenue)\n", "FROM lineorder l \n", "JOIN date d ON (l.orderdate = d.datekey) \n", "JOIN customer c ON (l.custkey = c.custkey)\n", "GROUP BY year, region\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "Der OLAP-Würfel hat 2 **Dimensionen**: Bestelldatum und Käufer. Die Granularität der Bestelldatum-Dimension ist Jahr und die Granularität der Käufer-Dimension ist Region.\n", "\n", "**Granularität**: Granularität bezieht sich auf das Detailniveau, auf dem Daten analysiert oder gespeichert werden, und bestimmt das Maß an Spezifität oder Aggregationsgrad der Daten." ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1.1: Dicing\n", "\n", "Erstellen Sie einen OLAP-Würel, der den Umsatz der Bestellungen gruppiert nach Jahr, Monat und Wochentag (dayofweek) und nach dem Land der Käufer gruppiert ist.\n", "\n", "- Wie viele Dimensionen hat dieser OLAP-Würel?\n", "- Was ist die Granularität der Dimensionen?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1.2: Slicing\n", "\n", "Verfeinern Sie den bestehenden OLAP-Würfel, in dem sie nur die Umsatzsummen an Dienstagen und in Europa ausgeben.\n", "\n", "- Wie viele Dimensionen hat dieser OLAP-Würfel?\n", "- Was ist die Granularität der Dimensionen?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1.3: Slicing und Dicing\n", "\n", "Verfeinern Sie den vorherigen OLAP-Würfel, in dem sie nur Einträge in Deutschland auswählen und gleichzeitig nach den Städten der Käufer gruppieren. Zählen Sie außerdem die Anzahl der Einträge in jeder Gruppe.\n", "\n", "- Wie viele Dimensionen hat dieser OLAP-Würfel?\n", "- Was ist die Granularität der Dimensionen?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT ...\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1.4: Ausgabe der Werte für eine Stadt\n", "\n", "Geben Sie die Bestellposten aus, die an einem Dienstag in Deutschland in der Stadt 'GERMANY 1' (zwei Leerzeichen zwischen Germany und 1) verkauft wurden.\n", "\n", "Es sollten ausgegeben werden:\n", "\n", "- Das Datum der Bestellung (date.date).\n", "- Der Name des Käufers.\n", "- Der Name des Produkts.\n", "- Der Umsatz (revenue)." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.1.5: Auswirkungen von Verfeinerungen auf Ergebniskardinalitäten\n", "\n", "Betrachten Sie nun die Slicing- und Dicing-Operationen, die wir in den vorherigen Aufgaben verwendet haben. Welche Auswirkungen haben Verfeinerungen auf die Kardinalität unserer Ergebnisrelation?" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "### Aufgabe 3.2: CUBE und ROLLUP \n", "\n", "In den folgenden Teilaufgaben wenden Sie die CUBE- und ROLLUP-Operatoren an. " ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.2.1: CUBE-Operator\n", "\n", "Bestimmen Sie die Summe des Umsatzes, gruppiert nach Wochentag und Region der Kunden. Benutzen Sie den CUBE-Operator, um über mehrere Gruppensets zu gruppieren. Sortieren Sie die Anfrage nach Wochentag und Region.\n", "\n", "- Welche Gruppensets gibt es?\n", "- Wie viele Zeilen enthält die Ausgabe, wenn es 7 Wochentage und 5 Regionen gibt?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.2.2: CUBE-Operator nachbauen \n", "\n", "Wie kann das gleiche Ergebnis ohne CUBE-Operator erzeugt werden?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.2.3: ROLLUP-Operator \n", "\n", "Ermitteln Sie die Summe des Umsatz gruppiert nach Region und Land. Benutzen Sie dafür den ROLLUP-Operator.\n", "\n", "Wie viele Einträge hat das Ergebnis, wenn es 24 Länder und 5 Regionen gibt?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "#### Aufgabe 3.2.4: ROLLUP-Operator nachbauen \n", "\n", "Wie kann die Gleiche Anfrage ohne ROLLUP-Operator erzeugt werden?" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", "\"\"\"\n", "con.execute(query).fetchdf()" ] }, { "cell_type": "markdown", "metadata": { "tags": [] }, "source": [ "Der folgende Befehl schließt die Datenbank." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [], "source": [ "# Einkommentieren, falls Sie die Verbindung nicht schließen wollen\n", "con.close()" ] } ], "metadata": {}, "nbformat": 4, "nbformat_minor": 4 }