docs / data / schema

Star Schema v2

Constellation schema — 6 fact tables, 9 dimensions. Every table traces back to a backlog issue acceptance criterion.

Updated based on feedback from Cosette Schumacher (March 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_model🔑 model_keyINT model_versionVARCHAR model_typeVARCHAR trained_atDATETIME descriptionVARCHARdim_horizon🔑 horizon_keyINT horizon_typeVARCHAR horizon_daysINTdim_target🔑 target_keyINT target_nameVARCHARfact_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_dayPBI🔗 date_keyINT🔗 site_keyINT🔗 horizon_keyINT temperature_c_avgFLOAT humidity_pct_avgFLOAT precipitation_mm_sumFLOAT radiation_whm2_sumFLOATfact_predictionBoth🔗 datetime_keyINT🔗 date_keyINT🔗 room_keyINT🔗 model_keyINT🔗 horizon_keyINT🔗 target_keyINT predicted_valueFLOAT predicted_classINT confidenceFLOATdimensions (left)fact tablesdimensions (right)

← click any table to inspect it

changelog — changes based on feedback

dim_weather → fact_weather_dayFeedback #1

Weather contains numeric, aggregatable measures (temperature, humidity, precipitation, radiation). It's a fact table, not a dimension. Linked to fact_energy via date_key.

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.

weather_id removed from fact_predictionFeedback #3

Weather is an input feature for ML training, not a dimension of predictions. We don't predict weather — we use it as an explanatory variable.

dim_target + dim_horizon addedNew

Avoids the anti-pattern of separate columns per prediction type. dim_target defines what we predict (energy, presence), dim_horizon defines the time horizon.

access control — issues #23 & #24

role

issue

accessible tables

filter

Owner

Issue #23

fact_energy_minutefact_presence_minutefact_environment_minutefact_prediction

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