Explorative Analyse (DWH + OLAP, ohne KI)

Für die explorative Untersuchung des gemäßigten, mitteleuropäischen Klimas werde ich drei Städte als Referenz heranziehen. Die Daten werden nicht separat betrachtet, sondern aggregiert in einem Data Warehouse (DWH) zusammengeführt. Wetterbezogene Muster, Trends und Zusammenhänge zwischen den drei Städten werden mit OLAP-Cubes untersucht und visualisiert. Das Ganze bleibt rein explorativ, ohne Vorhersage oder Einsatz von KI:

Stadt	Land	Höhe	Klima-Typ	Besonderheiten
Traunstein	Deutschland	591m	(1) Feucht-gemäßigt	Mehr Niederschlag, Föhneinflüsse, alpennahe Lage
Sibiu	Rumänien	415m	(2) Kontinentales Übergangsklima	Beeinflusst durch die Karpaten, ausgeglichene Jahreszeiten, gemäßigte Niederschläge
Laufamholz	Deutschland	309m	(3) Kontinental-gemäßigt	Mäßige Sommer, kalte Winter, eher trocken

Habe mal die Anzahl der Zeilen aus den drei Städten überschlagen:

Laufamholz (403991 Zeilen, früher gekauft)
Sibiu (404265 Zeilen, am gleichen Tag gekauft)
Traunstein (404255 Zeilen, am gleichen Tag gekauft)

Die Einträge sehen u.a. so aus:

dt	dt_iso	timezone	city_name	lat	lon
283996800	1979-01-01 00:00:00+00	3600	Laufamholz	49.466267	11.162624

dt	dt_iso	timezone	city_name	lat	lon
283996800	1979-01-01 00:00:00+00	7200	Sibiu	45.803479	24.145

dt	dt_iso	timezone	city_name	lat	lon
283996800	1979-01-01 00:00:00+00	3600	Traunstein	47.867757	12.638404

Wenn man ein Data Warehouse (DWH) baut dann möchte man die Daten aus den drei Tabellen zu einer einzigen Tabelle zusammenführen. Das erreicht man über eine eigene fact_weather – Tabelle. Die sieht dann so aus:

dt	dt_iso	timezone	city_id	lat	lon
283996800	1979-01-01 00:00:00+00	3600	1	49.466267	11.162624
11.162624283996800	1979-01-01 00:00:00+00	7200	2	45.803479	24.145
283996800	1979-01-01 00:00:00+00	3600	3	47.867757	12.638404

Die Städte werden als eigene Dimension dim_city modelliert und über die city_id mit der Faktentabelle fact_weather verknüpft:

CREATE TABLE dim_city (
    city_id SERIAL PRIMARY KEY,     -- Eindeutiger Schlüssel
    city_name TEXT NOT NULL,        -- Name der Stadt
    country TEXT NOT NULL,          -- Land
    lat DOUBLE PRECISION,           -- Breitengrad
    lon DOUBLE PRECISION            -- Längengrad
);

Gleiches gilt auch für die Zeit. Hier wird die Granularität hour, day, week, month in eine eigene Dimensionstabelle modelliert und über die Spalte time_key mit der Faktentabelle fact_weather verknüpft. Eine Besonderheit ist hierbei, dass man den Zeitstempel zu einem INT konvertiert:

ALTER TABLE weather_data ADD COLUMN time_key INT;

UPDATE weather_data
SET time_key = EXTRACT(YEAR FROM dt_iso) * 1000000 + 
               EXTRACT(MONTH FROM dt_iso) * 10000 + 
               EXTRACT(DAY FROM dt_iso) * 100 + 
               EXTRACT(HOUR FROM dt_iso);

Zusammengefasst kann man sagen, dass das neue DWH aus einer Faktentabelle (fact_weather) mit 1212501 Zeilen (entsprechend der drei Städten) und zwei Dimensionstabellen (dim_time, dim_city) besteht. Ich habe es extra einfach gehalten, kompliziert geht noch immer.

Eine simple Abfrage über die Durchschnittstemperatur der drei Städte seit 1979 ergibt ein hübsches Balkendiagramm:

SELECT 
    dt.time_desc AS "Monat",
    ROUND(AVG(f.temp)::NUMERIC, 2) AS "Durchschnittstemperatur (°C)"
FROM fact_weather f
JOIN dim_time dt ON (f.time_key_int / 10000) = dt.time_key_int -- Umwandlung auf Monatsebene
WHERE dt.time_type = 'month'
GROUP BY dt.time_desc
ORDER BY dt.time_desc;

Als interaktives Dashboard ist das Ganze hier erreichbar:
https://metabase.roth-it-solutions.de/public/dashboard/dee33d7c-1b1f-4f05-98b4-04ac9063587b

Die Darstellung der Langzeit-Temperaturentwicklung wird über eine materialisierte Sicht als OLAP-Cube modelliert:

DROP MATERIALIZED VIEW IF EXISTS weather_olap_cube_yearly;

CREATE MATERIALIZED VIEW weather_olap_cube_yearly AS
SELECT 
    EXTRACT(YEAR FROM TO_DATE(dt.time_desc, 'YYYY-MM')) AS jahr,
    c.city_name,
    ROUND(AVG(f.temp)::NUMERIC, 2) AS avg_temp
FROM fact_weather f
JOIN dim_time dt ON (f.time_key_int / 10000) = dt.time_key_int  -- Gruppierung auf Jahrsebene
JOIN dim_city c ON f.city_id = c.city_id
WHERE dt.time_type = 'month'
GROUP BY jahr, c.city_name
ORDER BY jahr, c.city_name;

Hier ein paar mehr Dimensionen, zusätzlich zu dim_time und dim_city:

dim_daytime
dim_wind_condition
dim_weather_condition

Das schöne an Business Intelligence ist, dass man noch immer mit SQL-Abfragen auskommt und nicht mit fancy pandas aus numpy in Python arbeiten muss. Das bedeutet weniger Code und mehr DB-Performance durch den Query-Optimizer, den man selbst auch noch tunen kann.

Posted in Mustererkennung, Wetter by Eduard RothTags: Ohne KI