Inhaltsübersicht
Der technische Weg von der Datenerhebung bis zur fertigen Kennzahl
Routinemäßige Analyse der Verspätungen
Unpünktliche Flüge am größten Flughafen der Gesellschaft, dem Los Angeles International Airport, sollen routinemäßig untersucht werden. Um über verschiedene Abteilungen hinweg die gleiche Diskussionsgrundlage zu haben, soll ein für alle zugänglicher Bericht erstellt werden.
- Nur Flüge von oder nach LAX
- Zeitraum 2015–2017
- Keine gestrichenen oder umgeleiteten Flüge
- Übersicht Kennzahlen
- Top 3 der unpünktlichen Airlines mit Unpünktlichkeitsrate
- Zeiträume mit gehäuft starker Unpünktlichkeit
- Informationen getrennt nach Abflug und Ankunft
- ≥ 5 Minuten Abweichung von der geplanten Zeit
- Gilt für zu früh UND zu spät
Datenerhebung
SQL Native Query — Gefiltert an der Quelle, nicht clientseitig nachgezogen
Datenbereinigung
Power Query (M) — Zeit-Parsing als Funktion, die ≥5-Minuten-Regel an genau einer Stelle
Datenmodell
Star Schema — Eine Faktentabelle, zwei Zeit-Dimensionen, Airline-Lookup und eine Measures-Tabelle
Datenanalyse
DAX Measures — DIVIDE-sicher, Filterkontext bewusst genutzt
SELECT fl_date, EXTRACT(WEEK FROM fl_date) AS fl_week,
CASE WHEN dest = 'LAX' THEN 'arrival'
WHEN origin = 'LAX' THEN 'departure' END AS fl_direction,
origin AS fl_origin, dest AS fl_dest,
crs_dep_time AS raw_dep_crs, crs_arr_time AS raw_arr_crs,
dep_time AS raw_dep, arr_time AS raw_arr,
ABS(dep_delay) AS delay_dep, ABS(arr_delay) AS delay_arr,
op_carrier
FROM flights
WHERE EXTRACT(YEAR FROM fl_date) BETWEEN 2015 AND 2017
AND (origin = 'LAX' OR dest = 'LAX')
AND cancelled = FALSE AND diverted = FALSE
- Query Folding delegiert alle Filterungen und Berechnungen direkt an PostgreSQL.
- Filterung an Quelle grenzt Zeitraum, LAX-Bezug und reguläre Flüge direkt beim Laden ein.
EXTRACT(WEEK...)berechnet die Kalenderwoche performant auf dem Datenbankserver.fl_directionentsteht perCASEzur Unterscheidung von Ankunft und Abflug.- Rohdaten-Zeitstempel werden für spätere Typkonvertierungen in Power Query unverändert durchgereicht.
ABS()macht Verspätungen vorzeichenlos, wodurch Verfrühung und Verspätung gleichermaßen zählen.op_carrierdient als Schlüssel für den späteren Tabellen-Join in Power Query.
// fnParseFlightTime — hhmm → time, einmal statt 4× kopiert
(raw) => let
_n = Text.PadStart(Text.From(raw), 4, "0") & "00"
in try #time(Number.FromText(Text.Start(_n, 2)),
Number.FromText(Text.Middle(_n, 2, 2)), 0)
otherwise null
// ≥5-Minuten-Regel — genau eine Quelle der Wahrheit
fl_delay_status =
let d = if [fl_direction] = "arrival" then [delay_arr]
else [delay_dep]
in if d >= 5 then [fl_direction] & " delay" else "on time"
delayed = [fl_delay_status] <> "on time" // abgeleitet
- Zentrale Parse-Funktion verarbeitet vier Roh-Uhrzeiten effizient in einem Schritt statt über doppelte Copy-Paste-Blöcke.
- Einheitliche Schwellenlogik steuert die 5-Minuten-Regel zentral in
fl_delay_status, damit Definitionen nicht auseinanderdriften. - Abgeleitete Felder wie
delayednutzen die Ergebnisse vonfl_delay_status, statt die Logik neu zu berechnen.
Ein Star Schema: Die Trennung von Datum und Uhrzeit trägt die Zeitreihen.
Report_Flights_Data: Faktentabelle (1 Zeile = 1 Flug)_Calendar: Datumsebene (Jahr → Quartal → Monat → Woche → Tag)_Time: Tagesuhrzeit (Stunde) für die TagesansichtOrigin_Unique_Carrieres: Airline-Code → Klarname_Measures: eigene Tabelle für alle Kennzahlen
-- Ø-Verspätung: Summe / Anzahl der VERSPÄTETEN Flüge
avg_delay_value_total = DIVIDE([sum_delays_total], [cnt_total_delays])
otp_rate = DIVIDE([cnt_total_ontimes], [cnt_total]) -- 29,85 %
delay_rate = DIVIDE([cnt_total_delays], [cnt_total]) -- 70 %
delay_index = [delay_rate] * [avg_delay_value_total] -- 18,54
-- Kernzahl 3,51 %: eine Rangquelle für Farbe UND Anteil
top3_avg_delay_value_in_pct =
DIVIDE(
SUMX(FILTER(VALUES(op_carrier_name), [top3_avg_ranking] <= 3),
[cnt_total_delays]),
[cnt_total_delays])
- Durchgehend
DIVIDEgarantiert eine fehlerfreie Division und fängt Berechnungsfehler durch Nullwerte automatisch ab. - Spezifischer Durchschnitt teilt bewusst nur durch verspätete Flüge, damit Pünktliche den Schnitt nicht senken.
- Kombinierte Rang-Logik nutzt
RANKXüberSUMXzuDIVIDEfür die exakte Berechnung der 3,51-%-Kernzahl. - Einheitliche Formatierung steuert über dieselbe Rangdefinition sowohl die Chart-Farbe als auch den finalen Prozentanteil.
Alle acht Werte sind berechnete DAX-Measures auf der Faktentabelle, keine roh übernommenen Spaltenwerte: von einfachen Zählern (cnt_total, cnt_op_carrier) bis zu abgeleiteten Quoten (otp_rate, delay_index).
Measure avg_delay_value_total für die Anzeige des Diagramms und vier weitere top3_avg_*-Measures für die Top-3-Anzeige und -Darstellung.
Vom Rohdatenimport bis zur Kennzahl
SQL, Power Query (M), Datenmodell und DAX greifen bewusst ineinander: Jede Filterung, jede Bereinigungsregel und jede Kennzahl ist an genau einer Stelle definiert, nicht mehrfach kopiert.
Dieser Aufbau macht die Analyse reproduzierbar und flexibel: Neue Zeiträume oder zusätzliche Airlines lassen sich ergänzen, ohne die bestehende Logik anzufassen.