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".
Dimension 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.
Fact 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 |
dim_area_v1 |
dim_building_v1 |
dim_desk_v1 |
dim_facility_v1 |
dim_floor_v1 |
dim_meeting_room_v1 |
dim_room_booking_v1 |
dim_room_calendar_v1 |
dim_workday_booking_v1 |
dim_workspace_booking_v1 |
fact_desk_occupancy_per_hour_v1 |
fact_meeting_room_occupancy_v1 |
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! 😉