Storing analytics data in IBSS

Written By Sam Walton (Super Administrator)

Updated at August 29th, 2024

Applies to version 2024.02 onwards.

 

The IBSS platform ingests data from a number of different sources. Some of this data is updated very infrequently, whilst others especially occupancy and environmental data can be updated every minute. To manage the data storage requirements, the IBSS platform summarises the ingested data into aggregates that can be used for analytics. This data is then kept for a configurable period of time before being removed to make way for the latest data. This is controlled by Archive data tables listed below.

Each table has two settings:

  • RemoveActiveDataAfter - the number of days after which a record is being removed from the Active table.
  • RemoveHistoricalDataAfter - the number of days after which a record is being removed from the Historical table.

Values for these settings must be:

  • The default and maximum value is 3650 (~10 years).
  • The minimum value is 1, meaning removal after 1 day.
  • Any value outside 1-3650 will be set as default 3650.

Archive data tables

  • Notifications
  • Tasks
  • Bookings
  • BookingParties
  • TasksDailySummary
  • ResolversDailySummary
  • BookingsDailySummary
  • EnvironmentalZoneSummary
  • EnvironmentalZoneDailySummary
  • EnvironmentalZoneData
  • UserNotifications
  • SpacesSummary
  • SpacesDailySummary
  • SpacesWeeklySummary
  • SpacesMonthlySummary
  • EnvironmentalFloorData
  • Visits
  • Visitors
  • SpaceUtilisationSummary
  • DevicesStatus
  • BookingSpaces
  • VisitsDailySummary
  • CateringOrders

SpacesSummary data

SpacesSummary data contains the 15m aggregated data about a space. 96 records will be created for each space per day. This data is used to look at intra-day information about the usage of a space. 

SpacesDailySummary data

SpacesDailySummary data contains the daily aggregated data about a space. 5 records will be created for each space per day with the information from the 15m summary data.

  • All Day
  • Working Hours
  • Non working hours
  • Office Hours
  • Non Office Hours

This data is used to look at daily/weekly trends about the usage of a space.

SpaceUtilisationSummary data

SpaceUtilisationSummary data takes that daily summary data for each space and then rolls it up as follows:

  • Zone:
    • Groups all spaces by their Space Class and Space Type per zone (across multiple floors or across a single floor) set to the local time of the building
    • Provides aggregate totals for each Space Class per zone (across multiple floors or across a single floor) set to the local time of the building
  • Floor:
    • Groups all spaces by their Space Class and Space Type per floor set to the local time of the building
    • Provides aggregate totals for each Space Class per floor set to the local time of the building
  • Building:
    • Groups all spaces by their Space Class and Space Type per building set to the local time of the building
    • Provides aggregate totals for each Space Class per building set to the local time of the building
  • Region:
    • Groups all spaces by their Space Class and Space Type per region set to UTC
    • Provides aggregate totals for each Space Class per region set to UTC

📌 Applies to version 2023.02

The IBSS platform ingests data from a number of different sources. Some of this data is updated very infrequently, whilst others especially occupancy and environmental data can be updated every minute. To manage the data storage requirements, the IBSS platform summarises the ingested data into aggregates that can be used for analytics. This data is then kept for a period of time before being removed to make way for the latest data. This is controlled by Archive parameters listed below.

Archive Parameters

Parameter
Name
Description
Default Value in days
Not_Rem_Days
Notification Remove Days
How many days to keep notification records before deleting.
5
Space_Raw_Rem_Days
Raw Space Remove Days
How many days to keep raw spaces data before deleting.
7
Space_15m_Rem_Days
15m Space Remove Days
How many days to keep 15 minute spaces data before deleting.
30
Space_1d_Rem_Days
1d Space Remove Days
How many days to keep daily spaces data before deleting.
120
Space_Util_Rem_Days
Utilisation Remove Days
How many days to keep utilisation data before deleting.
120
Env_Sensor_Rem_Days
Environmental Remove Days
How many days to keep environmental sensor data before deleting.
7
Bookings_Rem_Days_Archive Historical booking data All bookings older than the Bookings_Rem_Days_Archive parameter setting are removed from the History (Cosmos) table along with all booking parties belonging to those bookings. 90
Bookings_Rem_Days_Active Active booking data All bookings older than the Bookings_Rem_Days_Active parameter setting are removed from the Active (Cosmos) and Recent (Cosmos & Redis) tables. 90

SpacesSummary Data

SpacesSummary data contains the 15m aggregated data about a space. 96 records will be created for each space per day. This data is used to look at intra-day information about the usage of a space. 

SpacesDailySummary Data

SpacesDailySummary data contains the daily aggregated data about a space. 5 records will be created for each space per day with the information from the 15m summary data.

  • All Day
  • Working Hours
  • Non working hours
  • Office Hours
  • Non Office Hours

This data is used to look at daily/weekly trends about the usage of a space.

SpaceUtilisationSummary Data

SpaceUtilisationSummary data takes that daily summary data for each space and then rolls it up as follows:

  • Zone:
    • Groups all spaces by their Space Class and Space Type per zone (across multiple floors or across a single floor) set to the local time of the building
    • Provides aggregate totals for each Space Class per zone (across multiple floors or across a single floor) set to the local time of the building
  • Floor:
    • Groups all spaces by their Space Class and Space Type per floor set to the local time of the building
    • Provides aggregate totals for each Space Class per floor set to the local time of the building
  • Building:
    • Groups all spaces by their Space Class and Space Type per building set to the local time of the building
    • Provides aggregate totals for each Space Class per building set to the local time of the building
  • Region:
    • Groups all spaces by their Space Class and Space Type per region set to UTC
    • Provides aggregate totals for each Space Class per region set to UTC

Bookings_Rem_Days_Active

Every day, all bookings older than the date set for the Bookings_Rem_Days_Active parameter are removed from the Active (Cosmos) and Recent (Cosmos & Redis) tables. For users, this means the following:

  • They won't see their past bookings in the list on the My Schedule > Bookings page when selecting a custom date range and when the included dates are older than the Bookings_Rem_Days_Active parameter setting.
  • All bookings parties belonging to those bookings are also removed from the Active (Cosmos) and Recent (Cosmos & Redis) tables.

Value for the Bookings_Rem_Days_Active parameter must be an integer positive number between 1 and 90:

  • If the parameter Bookings_Rem_Days_Active cannot be found or does not contain a valid numeric value, it uses the default value of 30 days.
  • If the parameter Bookings_Rem_Days_Active is a valid numeric value, but the value is less than 1, it uses 1.
  • If the parameter Bookings_Rem_Days_Active is a valid numeric value, but the value is greater than 90, it uses 90.

Bookings_Rem_Days_Archive

Value for the Bookings_Rem_Days_Archive parameter must be an integer positive number between 90 and 400:

  • If the parameter Bookings_Rem_Days_Archive cannot be found, it uses the default value of 90 days.
  • If the parameter Bookings_Rem_Days_Archive is there, but does not contain a valid numeric value, it uses the default value of 90 days.
  • If the parameter Bookings_Rem_Days_Archive is a valid numeric value, but the value is less than 1, it uses 1.
  • If the parameter Bookings_Rem_Days_Archive is a valid numeric value, but the value is greater than 400, it uses 400 (loosely one year + one month).

⚠️ At all times, if Bookings_Rem_Days_Archive is less than Bookings_Rem_Days_Active, it should use the Bookings_Rem_Days_Active value.

Data source in the API

  • Data in the future and up to Bookings_Rem_Days_Active in the past will be picked up from the Active table.
  • Data in past beyond Bookings_Rem_Days_Active will be picked up from the History table.