docs / workflows
Gold → BI / ML
Business intelligence dashboards and machine learning models consuming the Gold star schema. Role-based access control per apartment.
BI tools connect directly to the PostgreSQL Gold schema via the native PostgreSQL connector. The star schema is optimized for OLAP queries — dimension joins, time-series aggregation, filtering by apartment/room/device.
Connection —PostgreSQL connector → gold schema → direct query on fact + dim tables
dashboard
tool
fact table
key metrics
Energy
Power BI
fact_energy_minute
Power (W), energy (kWh), cost (CHF), per device per room
Environment
Power BI
fact_environment_minute
Temperature, humidity, CO2, noise, pressure, door/window status
Presence
SAP Analytics Cloud
fact_presence_minute
Motion count, presence flag, door activity per room
Sensor Health
Power BI
fact_device_health_day
Battery levels, uptime, error counts per device
Each apartment owner only sees their own data. Implemented via Power BI RLS roles filtering on apartment_key.
role
filter
access
role_jimmy
apartment_key = (jimmy)
Own apartment only — energy, environment, presence
role_jeremie
apartment_key = (jeremie)
Own apartment only — energy, environment, presence
role_admin
none
All apartments, all dashboards, sensor health
Energy consumption prediction
#27in progressForecast energy usage per apartment, considering weather data and historical patterns. Built with KNIME workflows.
Room presence prediction
#26in progressPredict room occupancy from motion/door sensor patterns, output to presence_prob. Built with KNIME workflows.
Stack —KNIME Analytics Platform. Models will output predictions into gold.fact_ml_predictions (future table).