Warum nicht eine flache Tabelle
In der Rohdatei steht in jeder der 119.390 Zeilen der Text „Online TA“ oder „Resort Hotel“ noch einmal. Das funktioniert – bis jemand fragt, wie „Online TA“ auf Deutsch heißt, welches Land zu „PRT“ gehört oder in welchem Quartal der 14. März 2016 liegt. Solche Antworten gehören zu dem Ding, das die Buchung beschreibt: zum Segment, zum Land, zum Datum. Ein Sternschema trennt beides: In der Mitte steht die Faktentabelle (fact table) mit einer Zeile je Ereignis und den Kennzahlen; außen stehen die Dimensionstabellen (dimension tables) mit den Beschreibungen, nach denen man filtert und gruppiert.
Flache Tabelle
- eine Tabelle, 32 Spalten, Texte in jeder Zeile wiederholt
- ein Attribut ergänzen (deutscher Klarname) heißt 119.390 Zeilen anfassen
- Datum in vier Spalten, Quartal und Wochentag fehlen
- gut für ein Notebook, schlecht für ein Modell mit Beziehungen
Sternschema
- eine Faktentabelle, acht Dimensionen, Texte genau einmal
- ein Attribut ergänzen heißt eine Zeile je Dimensionswert
- Datumsdimension mit Jahr, Quartal, Monat, Woche, Wochentag, Wochenende
- Power BI erkennt die Beziehungen selbst; jede Frage ist ein Join plus GROUP BY
Die Regel, nach der sortiert wird
Alles, was man summiert oder mittelt, ist ein Fakt und bleibt in der Faktentabelle: Nächte, Preis, Umsatz, Vorlaufzeit, Flags. Alles, wonach man filtert oder gruppiert und was einen Namen hat, wird eine Dimension: Hotel, Marktsegment, Kanal, Kundentyp, Verpflegung, Kaution, Land, Datum. Das ist die Rolle aus Lab 01, jetzt als Tabellenentscheidung.
Der Stern: eine Faktentabelle, acht Dimensionen
| Tabelle | Rolle | Zeilen | Schlüssel | Inhalt |
|---|---|---|---|---|
fact_bookings | Fakten | 119.390 | booking_id + neun Fremdschlüssel | is_canceled, lead_time, Nächte, Gäste, adr, revenue, Sonderwünsche … |
dim_hotel | Dimension | 2 | hotel_id | City Hotel, Resort Hotel |
dim_date | Dimension, zweimal angebunden | 1.064 | date_key | ein Tag je Zeile: Jahr, Quartal, Monat, Monatsname, Woche, Tag, Wochentag, Wochenende, Datum |
dim_market_segment | Dimension | 8 | market_segment_id | Online TA, Offline TA/TO, Groups, Direct, … |
dim_distribution_channel | Dimension | 5 | distribution_channel_id | TA/TO, Direct, Corporate, GDS, Undefined |
dim_customer_type | Dimension | 4 | customer_type_id | Transient, Transient-Party, Contract, Group |
dim_meal | Dimension | 5 | meal_id | BB, HB, FB, SC, Undefined |
dim_deposit_type | Dimension | 3 | deposit_type_id | No Deposit, Non Refund, Refundable |
dim_country | Dimension | 178 | country_id | ISO-3-Code, plus UNK |
Gezeichnet ergibt das den Stern: die Faktentabelle in der Mitte, die Dimensionen außen, jede über genau eine
Beziehung verbunden – dim_date über zwei. Power BI zeigt in Lab 07 genau dieses Bild in seiner
Modellansicht.
Die Faktentabelle hat auch Spalten, die weder Kennzahl noch Schlüssel sind: reserved_room_type,
reservation_status. Das sind degenerierte Dimensionen – Merkmale mit so wenig Inhalt,
dass eine eigene Tabelle nur Aufwand wäre. Kimball, der das Sternschema in den 1990er-Jahren geprägt hat,
erlaubt sie ausdrücklich.
Schlüssel
Die Verbindung zwischen Fakt und Dimension läuft über Zahlen. Jede Dimension hat einen
Primärschlüssel (primary key): eine laufende Nummer, die in der Tabelle genau einmal
vorkommt. Die Faktentabelle trägt diese Nummer als Fremdschlüssel (foreign key). Aus „Resort
Hotel“ in der Buchung wird hotel_id = 2; wer den Namen will, geht über den Schlüssel in die
Dimension.
Was ein Fremdschlüssel garantiert
Die Klausel REFERENCES hotel_bi.dim_hotel (hotel_id) im Schema ist ein Zwang: PostgreSQL weist jede Buchung ab, deren hotel_id in dim_hotel nicht existiert.
Deshalb muss das Ladeskript die Dimensionen vor den Fakten laden – und deshalb kann es in diesem Modell keine
Buchung geben, die zu keinem Hotel gehört.
Die Nummern sind künstlich (surrogate keys): Sie bedeuten nichts und werden vom Notebook alphabetisch vergeben. Wer sich merkt, dass 1 das City Hotel ist, hat einen Zufall gelernt. Im Bericht erscheint immer der Name aus der Dimension, nie die Nummer.
Die Datumsdimension in zwei Rollen
Eine Buchung hat zwei Daten: die Anreise und den Tag, an dem der letzte Status gesetzt wurde – bei stornierten
Buchungen der Tag der Stornierung. Beide zeigen auf dieselbe Tabelle dim_date, über zwei
Fremdschlüssel: arrival_date_key und reservation_status_date_key. Eine Dimension, die
mehrfach angebunden ist, heißt Role-Playing Dimension.
| Rolle | Fremdschlüssel | Frage, die sie beantwortet | In Power BI |
|---|---|---|---|
| Anreise | arrival_date_key | Wie viel Umsatz fällt im August 2017 an? (Anreisemonat) | aktive Beziehung |
| Status | reservation_status_date_key | Wie viel gebuchter Umsatz wurde im März 2016 storniert? (Stornodatum) | inaktive Beziehung, per USERELATIONSHIP eingeschaltet |
Alle Kennzahlen des Projekts beziehen sich auf den Anreisemonat. Das ist eine Entscheidung, keine Selbstverständlichkeit: Ein Controller könnte genauso nach Buchungsmonat fragen – der ist im Datensatz aber nur als Anreise minus Vorlaufzeit rekonstruierbar.
Das Schema in SQL
Die Datei sql/01_schema.sql legt das Schema hotel_bi und die neun Tabellen an. Sie ist
für Erstsemester kommentiert und läuft unverändert in PostgreSQL – auf dem Server wie in Ihrem Browser. Der Ausschnitt unten lässt die Kommentare der Datei weg.
Auf den Fremdschlüsseln der Faktentabelle liegen Indizes. Ohne sie müsste PostgreSQL bei „alle Buchungen aus Portugal“ alle 119.390 Zeilen lesen; mit Index springt es direkt zu den passenden. Bei dieser Größe merkt man den Unterschied kaum, bei Millionen Zeilen entscheidet er, ob ein Dashboard antwortet.
Abfragen gegen den Stern
Jede Frage an ein Sternschema hat dieselbe Form: Faktentabelle, ein oder mehrere Joins auf Dimensionen, eine
Aggregation, ein GROUP BY nach dem Attribut der Dimension. Wer das Muster einmal kann, kann alle Fragen. Die
Datenbank unten ist dieselbe wie auf dem Server – PostgreSQL in Ihrem Browser, Schema hotel_bi im
Suchpfad, deshalb reicht fact_bookings ohne Präfix.
USING (hotel_id) geht, weil die Spalte in beiden Tabellen gleich heißt. Bei
dim_date heißt sie date_key in der Dimension und arrival_date_key in den
Fakten – dort braucht es ON d.date_key = f.arrival_date_key. Genau daran erkennt man die Rolle.
Übungen
Fünf Übungen: die Zuordnung von Spalten zu Fakt oder Dimension, Verständnisfragen zu Schlüsseln und Rollen und drei SQL-Abfragen gegen das Sternschema – eine ohne Join, eine mit Join, eine über die Datumsdimension.
Zusammenfassung
- Ein Sternschema trennt Fakten (was gezählt wird) von Dimensionen (wonach gefiltert und gruppiert wird).
hotel_bi: eine Faktentabelle mit 119.390 Buchungen, acht Dimensionen,dim_datezweimal angebunden.- Primärschlüssel in der Dimension, Fremdschlüssel in den Fakten; PostgreSQL erzwingt, dass jeder Fremdschlüssel existiert.
- Künstliche Schlüssel bedeuten nichts; im Bericht steht immer der Name aus der Dimension.
- Zwei Rollen der Datumsdimension: Anreise (aktiv) und Status (inaktiv). Alle Kennzahlen des Projekts laufen über die Anreise.
- Jede Frage ist Join plus Aggregation plus GROUP BY – das Muster gilt für SQL, DAX und das Dashboard gleichermaßen.