Understanding the data tables
When configuring your data connection, you have the option to specify what data tables you want to include. Mapiq is giving access to quite some data tables, and some understanding of the information about these tables can help out a lot in leveraging the data in your own analytics and reporting.
Table types
Within the available data tables two different types can be identified:
Dimension table: identified by the prefix "dim".
Fact table: identified by the prefix "fact".
Fact table
Provide the context and background information for the measures recorded in the fact table. One of the main differences between fact tables and dimension tables is that dimension tables contain the attributes that the measures in the fact table are based on.
Dimension table
Contains records that combine attributes from different dimension tables. These records allow users to analyze different aspects of their business, which can aid in decision-making and improving the business, for example the desk and meeting room
The available data tables
The following tables are available through data connections (at time of writing).
Table name | Description |
Dim_Building | The building hierarchy, describes physical building attributes, capacity, opening hours and time zone. |
Dim_Floor | Describes the floor-level dimension within the building hierarchy. |
Dim_Area | Area-level dimension zones within a floor with booking policies and amenities. |
Dim_Desk | Individual desk dimension, location, availability and island grouping. |
Dim_Desk_Island | Desk island grouping dimension and clusters of desks sharing amenities. |
Dim_Meeting_Room | Meeting room dimension — capacity, equipment, activities and calendar link. |
Dim_Workday_Booking | Workday booking — tracks when users plan to be in the office or work remotely. |
Dim_Workspace_Booking | Workspace booking — specific desk or area reservations tied to a workday. |
Dim_Facility | Facility dimension — named spaces within a building with type and layout. |
Dim_Room_Calendar | Room calendar — links meeting rooms to their calendar resource (O365/Google). |
Dim_Room_Booking | Room booking — full history of meeting room reservations including recurrence and invitees. |
Dim_Desk_Occupancy | Desk occupancy — real-time and historical sensor events for desk usage. |
Fact_Desk_Occupancy_per_hour | Desk occupancy — real-time and historical sensor events for desk usage. |
Dim_Meeting_Room_Occupancy | Meeting room occupancy — sensor-based people count and occupancy status events. |
Fact_Meeting_Room_Occupancy_per_hour | Meeting room occupancy — sensor-based people count and occupancy status events. |
Dim_Wifi_Location | Wifi location mapping — links wifi access points to building/floor locations. |
Fact_Wifi_Visit | Wifi visit events — device-based presence detection with entry/exit timestamps. |
Dim_Checkin | Check-in events — manual and automatic user check-ins with source type. |
Dim_User | User dimension — non-sensitive profile attributes (excludes PII like email/name). |
Dim_Quota_Profile_Transaction | Unified SCD2 history of workspace access profiles and parking quota profiles. Use quota_profile_discriminator to filter by category (Workspace |
Dim_Location_Profile_Transaction | Full SCD2 workspace location profile transaction history. Currently Workspace only — Parking rows will be added when parking location profile events become available. Array columns flattened to comma-separated strings for Power BI. |
Dim_Parking_Lot | Parking lot dimension — capacity, time zone, opening days and associated buildings. |
Dim_Parking_loc_transaction | Parking location transaction — combined lot and zone SCD record with effective dates. |
Dim_Parking_Zone | Parking zone dimension — zone configuration within a parking lot. |
Fact_Parking_Reservation | Parking reservation fact — reservation status, user, lot/zone and effective timestamps. |
Note: Mapiq strongly recommends using the latest version of the data tables because older variants have the chance of being deprecated in the near future.
Using the data tables
The tables are designed in such a way that Mapiq will be able to track all the historical changes along with the latest change or updates. This is technically referred to as Slowly Changing Dimension (SCD) Type 2. To explain with an example, imagine you have changes in your building capacity, all the changes can be tracked on:
Exactly when the change happened.
Within what period the old capacity was valid.
At what point in time the new capacity is added/valid from including what is the latest and current state of the building.
The transactions or changes mentioned at the beginning of this section can be seen in all the dimension tables. Lets consider the table first version of the building dimension, named dim_building_v1. This table has a column called is_current (type: Boolean). When this column is true, that row contains the latest valid information for that building and all the other columns are the previous/historical.
This table dim_building_v1 also has a column called transaction_hk which represents all unique transactions which has happened to the building. Additionally, each unique transaction has an effective_from and effective_to columns, basically the period in which that transaction is valid.
The other office layer dimension tables, like the floors, areas, meeting rooms and desks for instance, have a slightly different structure to obtain the valid transaction across any time period. All these tables have a columns called building_transaction_hk, which should be linked to the transaction_hk from the dim_building_v1 table to obtain the validity. These office layer tables have a hierarchy, all the desks are a part of an area, and all the areas belongs to a floor and the floors belongs to a specific building. So, the deepest level desk in this case contains a desk node ID, but also all the IDs from the other hierarchical tables it belongs to. That is, the dim_desk_v1 table also contains area ID, floor ID and the corresponding building ID it belongs to.
💬 Need More Help?
If you’d like extra assistance, reach out via the Messenger (question mark in the corner) and chat with our support team, or email us at [email protected].
We’re always ready to help! 😉