This chapter contains some best practices for data analysis.
Always specify a date range
All the timeslotand raw data tables are partitioned by calendar date.
Consequently, all queries must include a mandatory date range based on column “calendar_date”.
Example:
SELECT * FROM timeslots_1h_occupancy_data_v1
WHERE calendar_date >= “2025-10-10”
By design, all the occupancy timeslots are available from 2024-01-01 and later.
New rows are appended every night after 12pm, representing values of the day before.
Existing rows are not changed or recalculated.
Consequently, Spacewell recommends that data engineers use incremental loading of timeslot values in their own BI infrastructure. This practice reduces costs and delivers better response times and efficiency.
Observing data during business hours
The 15-minute occupancy timeslots are recorded continuously, which means that per location, 24 x 4 = 96 rows are available per calendar day. The same is valid for the 1-hour timeslots, where 24 rows are available per calendar day and per location.
This allows reporting on a continuous timeline.
However, some data analysts may want to observe only business hours. Column “hour_of_day” is an easy way to filter on.
Example:
SELECT * FROM timeslots_1h_occupancy_data_v1
WHERE
calendar_date = “2025-10-10” AND hour_of_day >= 8 AND hour_of_day < 17
returns hourly timeslots between 8am and 17pm
The daily timeslots do not have a “hour_of_day” column. The metric values are already calculated taking the business hours (configured in Studio) into account. These business hour values are also recorded in columns “business_day_start_hour” and “business_dat_end_hour” for historical purposes.
Specify location_class
Spacewell uses a fixed hierarchy of “location classes”:
BUILDING -> FLOOR -> ROOM -> WORKPLACE
Complementary to aggregations on the time line (15m / 1h / 1d), Spacewell also aggregates values on FLOOR level.
It’s essential to understand that these aggregations are in the same table. To avoid duplication of values, data analysts should create reports on floor level OR on individual space level, by filtering on column “location_class”. The next chapters illustrate how to do this.
Make joins with the locations table
The timeslot tables contain only minimal information about the locations:
- Location_id unique identifier of a location
- Location_class enumeration (FLOOR / ROOM / WORKPLACE/PARKING)
- Parent_location_id unique identifier of a location
- Parent_location_class enumeration (BUILDING / FLOOR / ROOM)
Other location details like name, code/reference, area in sqm, etc are not available in the timeslot tables. They are maintained in the locations table (“locations_v1”), which contains all the locations that are currently active, regardless of being monitored by occupancy sensors or not.
Data engineers should always make joins with the locations table for analysis purposes.
The can be two different joins depending on business needs:
- An INNER JOIN will return occupancy data for all the current, active locations. This is the most popular use case.
- A LEFT JOIN will return all occupancy data, even for locations that are no longer active, or where sensors were removed. In this case, the result set may contain timeslots where location_id cannot be found in the locations table anymore.
Active vs historical spaces
The timeslot data set contains information about spaces that are linked to sensors.
Spaces that are not linked to sensors, will not appear in the timeslots.
In real life, customers often make changes to their sensor infrastructure:
- Installing more sensors to spaces
- Removing sensors from spaces
- Moving sensors from one space to another
- etc
So what happens in the curated timeslots?
Use case | Effect | Memo |
|---|---|---|
Installing more sensors | Spacewell Data Platform will generate more time slots for the affected spaces | |
Removing or disabling sensors | Spacewell Data Platform will no longer generate new timeslots for the affected spaces | Existing timeslots remain available |
Moving sensors from space A to space B | From the move date, Spacewell Data Platform will
| Existing timeslots for A remain available |
Putting active sensors in a storage box for later use | Spacewell Data Platform continues to register timeslots, assuming that the sensors are still attached to spaces. | This may sound silly but it’s a real-world use case. |
Offline sensors | Spacewell Data Platform continues to register timeslots for the linked spaces. | Values are NULL (blanks) |
As a result, the timeslot data represents historical information.
Some of it may still represent the current spaces, some of it may refer to spaces that are no longer monitored, or that were disabled/deleted/whatever the case may be.