UC2 · Apartments Domotic
End-to-end architecture · Data range: Aug 18 – Oct 27, 2023 (simulated real-time)
64-61 Data Cycle Project
HES-SO Valais · Feb – May 2026
Final presentation: May 8, 2026
Fast flow
every 1 min
Slow flow
daily ~7am
Sources
External
MySQL DB (pidb)
Buildings · Rooms
Sensors · Devices · DIErrors
static · on-premise
JSON Sensors ×2
Jimmy & Jérémie apartments
Plugs · doors · motions · meteos
humidity · consumption
Raspberry Pi · local network
Weather CSV
sFTP folder Meteo2
Temp · humidity · rain
global radiation
PRED_* fields · external
recup.py · cron ·
must complete < 1 min
independent cron · daily download
Acquisition
recup.py
recup.py
Polls sensor endpoints
via local network
Queries MySQL static tables
Python · Raspberry Pi · iso copy only
Weather download
Separate independent script
Downloads CSV via sFTP
independent · no transform
write raw JSON · timestamped path
write raw CSV · daily path
Bronze
Raw storage
Raw JSON · Jimmy
Immutable copy
/bronze/jimmy/YYYY/MM/DD/HH/
Raw JSON · Jérémie
Immutable copy
/bronze/jeremie/YYYY/MM/DD/HH/
MySQL Snapshot
Periodic DB dump
/bronze/mysql/YYYY/MM/DD/
Raw Weather CSVs
Immutable daily files
/bronze/weather/YYYY/MM/DD/
ETL — flatten · clean · join metadata · deduplicate · normalize timestamps
Silver
Clean data
Sensor Events
Flattened rows · room + type
value + timestamp · outliers flagged
DB table
Apartment Metadata
Buildings · Rooms · Devices
joined · tz-aligned
addresses masked (GDPR)
DB table
Weather (clean)
Standardized units
location & time aligned
DB table
DIErrors (clean)
Parsed · linked to sensor
severity tagged
DB table
Aggregation — hourly & daily rollups · OLAP star schema · KPI computation
Gold
OLAP / DWH
Energy KPIs
kWh + CHF equivalent
per room/apt · hourly/daily
Fact table
Occupation Facts
Motion + door → presence logic
minutes occupied per room
Fact table
Environmental Facts
Temp · humidity · CO2
windows/doors · anomaly flags
Fact table
Sensor Reliability
Error counts · uptime %
missing data timeline
Fact table
BI reads enriched Gold directly
ML predictions written back to Gold
ML / Data Science
Reads Gold · trains models · writes
predictions back as new fact tables
Energy Forecast
Occupation Forecast
Anomaly Detection
Python · KNIME — #25 #26 #27 #28
direct DB connection
ETL export · load into SAC data model · #21
BI / Viz
Dashboards
Power BI
Microsoft
Energy Dashboard
kWh + CHF over time
per room / apt · equipment
weather overlay · forecast
#19 · #29 · filter by room, date
Environment Dashboard
Temp · humidity · CO2
windows/doors status by room
#20 · filter by room, time
SAP Analytics Cloud
SAP
Room Presence Dashboard
Occupation by room over time
usage patterns · filter by
period & room
#22 · FR / ES / RO / PT (#30)
Access control
Owner vs Renter roles
Row-level security per apt
#23 · #24