docs / data / schema

Star Schema v3

Constellation schema — 7 fact tables, 7 dimensions, 1 materialised view. As-built reflects the gold layer that create_gold.py creates and KNIME writes into.

Updated May 2026 — see changelog below.

🔑 primary key  ·  🔗 foreign key  ·  PBI = Power BI  ·  SAC = SAP Analytics Cloud

Power BI
SAP SAC
Both
dim_datetime🔑 datetime_keyINT timestamp_utcDATETIME date_keyINT hourINT minuteINT day_of_weekVARCHAR weekINT monthINT yearINT is_weekendBOOLEAN is_holidayBOOLEANdim_date🔑 date_keyINT dateDATE day_of_weekVARCHAR weekINT monthINT yearINT is_weekendBOOLEAN is_holidayBOOLEANdim_apartment🔑 apartment_keyINT apartment_idINT nameVARCHAR owner_user_idVARCHAR building_idINT building_nameVARCHAR floorINTdim_room🔑 room_keyINT room_idINT apartment_keyINT room_nameVARCHAR room_typeVARCHARdim_device🔑 device_keyINT device_idVARCHAR room_keyINT device_nameVARCHAR device_typeVARCHAR sensor_typeVARCHAR is_activeBOOLEANdim_weather_site🔑 site_keyINT site_codeVARCHAR nameVARCHAR latFLOAT lonFLOATdim_tariff🔑 tariff_keySERIAL providerVARCHAR yearSMALLINT chf_per_kwhNUMERICfact_energy_minutePBI🔗 datetime_keyINT🔗 date_keyINT🔗 device_keyINT🔗 room_keyINT🔗 apartment_keyINT power_wFLOAT energy_whFLOAT energy_kwhFLOAT cost_chfFLOAT counter_totalFLOAT is_validBOOLEANfact_environment_minutePBI🔗 datetime_keyINT🔗 date_keyINT🔗 room_keyINT🔗 apartment_keyINT temperature_cFLOAT humidity_pctFLOAT co2_ppmINT noise_dbINT pressure_hpaFLOAT window_open_flagBOOLEAN door_open_flagBOOLEAN is_anomalyBOOLEANfact_presence_minuteSAC🔗 datetime_keyINT🔗 date_keyINT🔗 room_keyINT🔗 apartment_keyINT motion_countINT door_open_flagBOOLEAN presence_flagBOOLEAN presence_probFLOATfact_device_health_dayPBI🔗 date_keyINT🔗 device_keyINT🔗 room_keyINT🔗 apartment_keyINT error_countINT missing_readingsINT uptime_pctFLOAT battery_min_pctFLOAT battery_avg_pctFLOATfact_weather_hourPBI🔗 datetime_keyBIGINT🔗 date_keyINT🔗 site_keyINT prediction_dateDATE temperature_cFLOAT humidity_pctFLOAT precipitation_mmFLOAT radiation_wm2FLOAT n_model_runsINTfact_prediction_motionPBI🔗 datetime_keyBIGINT🔗 date_keyINT apartmentVARCHAR roomVARCHAR timestamp_roundedTIMESTAMP predicted_occupiedBOOLEAN actual_occupiedBOOLEAN model_nameVARCHAR targetVARCHARfact_prediction_consumptionPBI🔗 datetime_keyBIGINT🔗 date_keyINT apartmentVARCHAR roomVARCHAR timestamp_roundedTIMESTAMP predicted_power_wFLOAT actual_power_wFLOAT model_nameVARCHAR targetVARCHARdimensions (left)fact tablesdimensions (right)

← click any table to inspect it

changelog — changes based on feedback

dim_weather → fact_weather_hourFeedback #1

Weather is numeric and aggregatable, so it's a fact, not a dimension. Stored at hourly grain with prediction_date in the PK to keep multiple forecast revisions.

dim_time → dim_datetime + dim_dateFeedback #4

Separated date and time: dim_datetime at minute grain for sensor facts, dim_date at day grain for weather and device health.

dim_sensor merged into dim_deviceFeedback #5

dim_sensor and dim_device had a redundant hierarchy. In our data, the sensor is an attribute of the device (sensor_type). One dimension is enough.

fact_device_health → linked to dim_roomFeedback #6

fact_sensor_reliability was not linked to dim_room. Fixed: fact_device_health_day includes room_key for spatial reliability analysis.

fact_prediction split into _motion + _consumptionSimplification

KNIME's DB Writer creates each table from its own workflow output, with apartment/room/model_name/target as denormalised string columns. Two facts were simpler than one fact + four extra dims (model/target/horizon).

dim_tariff added (Oiken Sion 2023-2025)New

Energy tariff joined to fact_energy_minute via the materialised view mv_energy_with_cost — derives cost_chf without polluting the fact table.

access control — issues #23 & #24

role

issue

accessible tables

filter

Owner

Issue #23

fact_energy_minutefact_presence_minutefact_environment_minutefact_prediction_motionfact_prediction_consumption

Row-level: own apartment only (dim_apartment.owner_user_id)

Renter

Issue #23

fact_environment_minute

Row-level: own apartment only

all tables

facts

dimensions