IDM - Location
Contents
- Dimension Tables
- dim_region
- dim_site_type
- dim_address
- dim_site
- dim_site_operating_hours
- dim_subsite_type
- dim_subsite
- dim_building_type
- dim_building
- dim_building_operating_hours
- dim_floor
- dim_section
- dim_space_type_classification
- dim_space_type
- dim_space
- dim_location
- Calculated Tables (Not in DDC)
- cal_site_location
- cal_space_location
Dimension Tables
dim_region
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
region_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for joins. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
parent_id |
string |
parent_id |
dim_region.id |
The id of the parent region in the hierarchy |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
name |
string |
True |
name |
The name of the region |
North America |
|
|
latitude |
decimal(9,6) |
latitude |
The latitude coordinates of this region |
48.1667 |
||
|
longitude |
decimal(9,6) |
longitude |
The longitude coordinates of this region |
48.1667 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage PowerBI should never use it for any report |
Daily incremental data synchronization. Internal column and should be ignored |
N/A |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
|||
|
history_start_time |
timestamp |
True |
|
the timestamp from published UDM message mandatory header: timestamp ETL or PowerBI could use it to sort and order the data |
The date this region was first published |
2025-11-19 09:58:25.618228 |
|
history_end_time |
timestamp |
|
|
the timestamp from published UDM optional message header history_time if data is historical data, otherwise, it’s |
The date this region was archived or deleted |
2025-11-19 09:58:25.618228 |
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_site_type
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
site_type_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The site type name |
Regional |
|
|
sequence |
int |
sequence |
The sequence determines the order of display for sites based on type |
12 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this site type was first published. |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this site type was archived or deleted. |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_address
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
address_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
unit |
string |
unit |
The unit for the address (if applicable) |
Unit 4 |
||
|
street |
string |
street |
The street name for this address |
Grandview Grove |
||
|
city |
string |
city |
The city for this address |
Melbourne |
||
|
state |
string |
state |
The state for this address |
Texas |
||
|
state_code |
string |
state_code |
The state code for this address |
TX |
||
|
country |
string |
country |
The country name for this address |
United States of America |
||
|
postal_code |
string |
postal_code |
The postcode for this address |
12345-6789 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this address was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this address was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_site
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
site_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this site. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
region_id |
string |
region_id |
dim_region.id |
The region ID for this site |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
site_type_id |
string |
site_type_id |
dim_site_type.id |
The site type id for this site. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
name |
string |
True |
name |
The name of this site |
Belkor Campus |
|
|
address_id |
string |
True |
address_id |
dim_address.id |
The address ID for this site |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
latitude |
decimal(9,6) |
latitude |
The latitude coordinate of this site |
48.1667 |
||
|
longitude |
decimal(9,6) |
longitude |
The longitude coordinate of this site |
48.1667 |
||
|
timezone |
string |
True |
timezone |
The timezone for this site |
UTC |
|
|
is_campus |
boolean |
True |
is_campus |
True if this site is campus |
1 |
|
|
image_url |
string |
image_url |
URL for the image of this site |
|||
|
has_operating_hours |
boolean |
True |
has_operating_hours |
True if the site has defined operating hours |
1 |
|
|
measurement_system |
enum |
True |
measurement_system |
Enum: [“IMPERIAL”, “METRIC”] |
Enumerator of “IMPERIAL”, “METRIC” that defines which measurement system should be used |
IMPERIAL |
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this site was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this site was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_site_operating_hours
The site operating hours if dim_site.has_operating_hours=true.
Columns
|
Column |
Description |
Example |
|---|---|---|
|
site_operating_hours_pk (integer, mandatory) |
surrogate key (unique across table rows) |
100 |
|
tenant_id (UUID, mandatory) → dim_tenant.id |
Unique tenant identifier.c |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
site_id (UUID, mandatory) → dim_site.id |
Unique site identifier.c |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
day_of_week (string, mandatory) |
Week day name. |
The Enum: MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY |
|
start_time_only (timestamp, mandatory) |
start time. |
1970-12-21 04:00:00.000000 |
|
end_time_only (timestamp, mandatory) |
end time. |
1970-12-21 05:00:00.000000 |
|
is_business_day (boolean, mandatory) |
Is Business day? |
true |
|
edw_start_time (timestamp, mandatory) |
Internal Usage! |
- |
|
edw_row_is_last(bool, mandatory) |
Internal Usage! |
- |
|
history_start_time (timestamp, mandatory) |
SCD: start time |
2025-11-19 09:58:25.618228 |
|
history_end_time (timestamp) |
SCD: end time |
2025-11-19 09:58:25.618228 |
|
row_is_current (bool, mandatory) |
SCD: is current |
true |
dim_subsite_type
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
subsite_type_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The name of this subsite type |
Campus |
|
|
sequence |
int |
sequence |
The sequence determines the order of display for subsites based on type |
12 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this subsite type was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this subsite type was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_subsite
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
subsite_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this subsite. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
site_id |
string |
True |
site_id |
dim_site.id |
The region ID for this subsite |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
subsite_type_id |
string |
subsite_type_id |
dim_subsite_type.id |
The subsite type ID for this subsite |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
name |
string |
True |
name |
The name of this subsite |
Phoenix Wing |
|
|
latitude |
decimal(9,6) |
latitude |
The latitude coordinate of this subsite |
48.1667 |
||
|
longitude |
decimal(9,6) |
longitude |
The longitude coordinate of this subsite |
48.1667 |
||
|
sequence |
int |
sequence |
The sequence determines the order of display for subsites based on their own sequence |
12 |
||
|
area_sq_m |
double |
area_sq_m |
The gross total area of this subsite |
843544.8 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this subsite was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this subsite was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_building_type
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
building_type_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The building type name |
Office |
|
|
sequence |
int |
sequence |
The sequence determines the order of display for buildings based on type |
12 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this building type was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this building type was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_building
Partition By: tenant_id
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
building_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this building. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
site_id |
string |
True |
site_id |
dim_site.id |
The site ID for this building |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The building name |
London HQ |
|
|
code |
code |
The code of the building |
LHQ |
|||
|
building_type_id |
string |
building_type_id |
dim_building_type.id |
The ID of the building type |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
address_id |
string |
True |
address_id |
dim_address.id |
The Address ID for this building |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
latitude |
decimal(9,6) |
True |
latitude |
mandatory if longitude is set |
The latitude coordinates of this building |
48.1667 |
|
longitude |
decimal(9,6) |
True |
longitude |
mandatory if latitude is set |
The longitude coordinates of this building |
48.1667 |
|
sequence |
int |
sequence |
The sequence determines the order of display for buildings based on their own sequence |
12 |
||
|
timezone |
string |
timezone |
The timezone of the building |
UTC |
||
|
has_operating_hours |
boolean |
True |
has_operating_hours |
True if the building has defined operating hours |
1 |
|
|
status |
enum |
status |
Enum: [“planned”, “active”, “archived”] |
Enumerator determine the status of the building. Options: “planned”, “active”, “archived” |
active |
|
|
building_cost |
double |
building_cost |
The total annual cost for the building |
1284772 |
||
|
target_seating_ratio |
double |
target_seating_ratio |
The target people to seat (desk) ratio |
1.22 |
||
|
target_meeting_ratio |
double |
target_meeting_ratio |
The target people to meeting room ratio |
12.3 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this building was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this building was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_building_operating_hours
The building operating hours if dim_building.has_operating_hours=true.
Columns
|
Column |
Description |
Example |
|---|---|---|
|
building_operating_hours_pk (integer, mandatory) |
surrogate key (unique across table rows) |
100 |
|
tenant_id (UUID, mandatory) → dim_tenant.id |
Unique tenant identifier.c |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
building_id (UUID, mandatory) → dim_building.id |
Unique building identifier.c |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
day_of_week (string, mandatory) |
Week day name. |
The Enum: MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY |
|
start_time_only (timestamp, mandatory) |
start time. |
1970-12-21 04:00:00.000000 |
|
end_time_only (timestamp, mandatory) |
end time. |
1970-12-21 05:00:00.000000 |
|
is_business_day (boolean, mandatory) |
Is Business day? |
true |
|
edw_start_time (timestamp, mandatory) |
Internal Usage! |
- |
|
edw_row_is_last(bool, mandatory) |
Internal Usage! |
- |
|
history_start_time (timestamp, mandatory) |
SCD: start time |
2025-11-19 09:58:25.618228 |
|
history_end_time (timestamp) |
SCD: end time |
2025-11-19 09:58:25.618228 |
|
row_is_current (bool, mandatory) |
SCD: is current |
true |
dim_floor
Partition By: tenant_id
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
floor_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this floor. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
building_id |
string |
True |
building_id |
dim_building.id |
The building id for this floor. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The name of this floor |
12F |
|
|
sequence |
int |
sequence |
The sequence determines the order of display for floors based on their own sequence |
12 |
||
|
map_id |
string |
map_id |
dim_map.id |
The ID for the floormap of this floor |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
is_ground_floor |
boolean |
True |
is_ground_floor |
True if this floor is the ground floor for the building |
1 |
|
|
lease_rentable_area_sq_m |
double |
lease_rentable_area_sq_m |
The rentable area on the lease agreement in square meters |
778003.2 |
||
|
interior_gross_area_sq_m |
double |
interior_gross_area_sq_m |
The interior gross area in square meters |
778003.2 |
||
|
exterior_gross_area_sq_m |
double |
exterior_gross_area_sq_m |
The exterior gross area in square meters |
778003.2 |
||
|
floor_number |
integer |
floor_number |
The number of the floor |
12 |
||
|
target_seating_ratio |
double |
target_seating_ratio |
The target ratio between people and seating |
1.22 |
||
|
target_meeting_ratio |
double |
target_meeting_ratio |
The target ratio between people and meeting rooms |
2.3 |
||
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
- |
- |
|
|
history_end_time |
timestamp |
|
|
The date this floor was first published |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_section
Partition By: tenant_id
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
section_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this section. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
floor_id |
string |
True |
floor_id |
dim_floor.id |
The floor id for this section. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The name of this section |
12-A |
|
|
sequence |
int |
sequence |
The sequence determines the order of display for sections based on their own sequence |
12 |
||
|
code |
string |
code |
Code for this section |
12A |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this section was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this section was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_space_type_classification
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
space_type_classification_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The space type classification name |
Rentable Exclusion |
|
|
code |
string |
code |
The space type classification code |
RE |
||
|
parent_id |
string |
parent_id |
dim_space_type_classification.id |
The ID of the parent space type classification |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this space type classification was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this space type classification was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_space_type
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
space_type_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
parent_id |
string |
parent_id |
dim_space_type.id |
The parent space type id |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
name |
string |
True |
name |
The space type name |
Sit-Stand Desk |
|
|
short_name |
string |
short_name |
The shortened name for this space type |
SSD |
||
|
code |
string |
code |
The code for this space type |
SSD |
||
|
sequence |
int |
sequence |
The sequence determines the order of display for spaces based on space type |
12 |
||
|
color |
string |
color |
The color that represents this space type on floorplans |
#0000FF |
||
|
usage |
string |
True |
usage |
Enum: one of ME, WE, COMMON or NONE ME: desk space WE: room space |
Enumerator representing how this space type is used. One of ME, WE, COMMON or NONE |
ME |
|
is_assignable |
boolean |
True |
is_assignable |
If true then a person may be assigned to permanently occupy the associated space |
True if this space can be assigned permanent people. Default value |
1 |
|
is_bookable |
boolean |
True |
is_bookable |
If true then the space can be booked for a temporary period through hoteling and/or calendar systems |
True if this space will be bookable in hoteling/calendar systems. Default value |
1 |
|
is_open |
boolean |
True |
is_open |
If true, space is not assignable or bookable and anyone can use this space for individual work |
True if the space type can be used for individual work, but is not bookable and is not assigned to specific individuals. Default value |
1 |
|
is_flexi |
boolean |
True |
is_flexi |
Is only available when Usage Type = Me |
True if the space type is flexible and used for individual work. Only available if space type usage is ME. Default value |
1 |
|
capacity |
int |
capacity |
The default number of people that can be present in the space, whether those are seats in a meeting room or workpoints at a desk. This is optional, and should not be set if the space type does not generally correlate with a specific capacity |
The default number of people that can be associated with this space. This represents seats for a meeting room, or workpoints for a desk. |
2 |
|
|
classification_id |
string |
classification_id |
dim_space_type_classification.id |
The ID for the space type classification for this space type |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
subclassification_id |
string |
subclassification_id |
dim_space_type_classification.id |
The ID for the sub space type classification for this space type. Joins to Space Type Classification |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this space type was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this space type was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_space
Partition By: tenant_id
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
space_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
id |
string |
True |
id |
uuid |
ID (Used for joins) |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
tenant_id |
string |
True |
tenant_id |
dim_tenant.id |
ID for the tenant. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
location_id |
string |
True |
location_id |
dim_location.id |
The location ID for this section. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
section_id |
string |
True |
section_id |
dim_section.id |
The section id for this space. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
space_type_id |
string |
True |
space_type_id |
dim_space_type.id |
The space type ID for this space |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
name |
string |
True |
name |
The name of this space |
12.128 D |
|
|
code |
string |
code |
The code that represents this space |
12.128 |
||
|
common_name |
string |
common_name |
The common name for this space |
12.128 Desk |
||
|
phone_number |
string |
phone_number |
The phone number for this space |
89943001 |
||
|
extension |
string |
extension |
The extension for the phone number for this space |
+61 |
||
|
calendar_email |
string |
calendar_email |
The calendar email for this space, used by booking systems |
|||
|
video_conference_email |
string |
video_conference_email |
The video conference email for this space, for hybrid digital and physical meetings |
|||
|
is_assignable |
boolean |
is_assignable |
If set this overrides the is_assignable value inherited from the space type |
True if this space can be assigned to people. Overrides the rule from space type |
1 |
|
|
is_bookable |
boolean |
is_bookable |
If set this overrides the is_bookable value inherited from the space type |
True if this space is bookable in calendar systems. Overrides the rule from space type |
1 |
|
|
is_open |
boolean |
is_open |
If set this overrides the is_open value inherited from the space type |
True if this space is considered open (usable but not bookable or assignable). Overrides the rule from space type |
1 |
|
|
is_retained |
boolean |
is_retained |
if true, then a person cannot be assigned to the space. |
If set to true, may not be assigned a person. Only applicable to is_assignable is true spaces |
1 |
|
|
is_shared |
boolean |
True |
is_shared |
If true, then multiple assignments to this space will not affect occupancy numbers |
If set to true and this space has multiple assignments, the additional people will not affect occupancy numbers |
1 |
|
is_occupiable |
boolean |
True |
is_occupiable |
True if the space is assignable, bookable, open, shared or retained |
True if the space is one of the following: assignable, bookable, open, shared or retained |
1 |
|
is_flexi |
boolean |
True |
is_flexi |
Is only available when Usage Type = Me |
True if the space is flexible and used for individual work. Only available if space type usage is ME |
1 |
|
capacity |
int |
True |
capacity |
The number of people that can be present in the space, whether those are seats in a meeting room or workpoints at a desk |
The number of people that can be associated with this space. This represents seats for a meeting room, or workpoints for a desk. Overrides capacity value set via space type |
2 |
|
planned_capacity |
int |
planned_capacity |
The number of people that are planned to be sharing a space if said space has been designated as Shared. |
The number of people that are planned to be sharing a space if said space has been designated as Shared. |
2 |
|
|
retained_until |
date |
retained_until |
The date/time at which the retained status of a space expires. |
The date in which the retention status set on the space expires. |
2025-11-19 09:58:25.618228 |
|
|
retention_notes |
string |
retention_notes |
Notes associated with the retention status on this space |
Space is needed for a new hire starting December |
||
|
retention_type |
string |
retention_type |
Enum: One of NEW_HIRE, CONTRACTOR, INTERN, OTHER [default: NEW_HIRE] |
Enumerator representing the type of retention status on this space. One of NEW_HIRE (default), CONTRACTOR, INTERN and OTHER. |
NEW_HIRE |
|
|
area_sq_m |
double |
area_sq_m |
The square meterage of this space |
8.92 |
||
|
image_url |
string |
image_url |
The url for an image representing this space |
|||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this space type was first published |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this space type was archived or deleted |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active |
1 |
dim_location
Partition By: tenant_id
Description: The location provides a data pointer, allow external systems to reference locations without needing to know the details of the exact location type. This allows them to reference the properties of a location that they care about without needing to understand the exact hierarchy. The location view expands all the foreign keys in that hierarchy for each location. Its location_type value is one of region, site, sub_site, building, floor, section and space.
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
Business Description |
Example |
|---|---|---|---|---|---|---|
|
location_pk |
long |
True |
surrogate key as primary key |
- |
- |
|
|
location_type |
enum |
True |
Enum: [“region”, “site”, “subsite”, “building”, “floor”, “section”, “space”] |
Enumerator indicating the category of this location row. One of: region, site, sub_site, building, floor, section or space. |
floor |
|
|
id |
string |
True |
corresponding table’s id column |
ID for this location row |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
tenant_id |
string |
True |
corresponding table’s tenant_id column |
- |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
|
|
region_id |
string |
dim_region.id |
The region id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
site_id |
string |
dim_site.id |
The site id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
sub_site_id |
string |
dim_sub_site.id |
The sub site id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
building_id |
string |
dim_building.id |
The building id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
floor_id |
string |
dim_floor.id |
The floor id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
section_id |
string |
dim_section.id |
The section id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
section_name |
string |
dim_section.name |
The name of the section for this location. Null if there is no section |
12-A |
||
|
space_id |
string |
dim_space.id |
The space id for this location. |
62a5f3c8-d031-4e26-b386-66d4416e5b5b |
||
|
latitude |
decimal(9,6) |
corresponding the latitude from region, site, sub_site, building |
The latitude for this location, based on the lowest level available latitude between region, site, sub_site and building. |
48.1667 |
||
|
longitude |
decimal(9,6) |
corresponding the longitude from region, site, sub_site, building |
The longitude for this location, based on the lowest level available latitude between region, site, sub_site and building. |
48.1667 |
||
|
edw_start_time |
timestamp |
True |
|
ETL internal usage |
- |
- |
|
edw_row_is_last |
boolean |
ETL internal usage |
- |
- |
||
|
history_start_time |
timestamp |
True |
|
The date this location was first published. |
2025-11-19 09:58:25.618228 |
|
|
history_end_time |
timestamp |
|
|
The date this location was archived or deleted. |
2025-11-19 09:58:25.618228 |
|
|
row_is_current |
boolean |
True |
|
the row data is current or not. |
True if the row is still active. |
1 |
Calculated Tables (Not in DDC)
[1]Denormalize the location hierarchical data into two calculated tables to facilitate Power BI data marts
cal_site_location
Description: merge site related hierarchies with address and region together to facilitate BI reports. It’s implemented in incremental mode to boost its calculation performance. All data is live (row_is_current is true). If previous data is deleted, the data would be removed from the table. E.g. it could be used with work_order.site_id to get its country, state, city, site and region names.
Partition By: tenant_id, history_start_date
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
|---|---|---|---|---|
|
site_location_pk |
long |
True |
surrogate key as primary key |
|
|
location_type |
string |
True |
Enum: One of region, site, subsite |
|
|
id |
string |
True |
The id of corresponding table of location_type value |
|
|
site_id |
string |
True |
dim_site.id |
|
|
tenant_id |
string |
True |
dim_space.tenant_id |
|
|
address_id |
string |
dim_address.id |
||
|
address_name |
string |
dim_address.name |
||
|
region_id |
string |
dim_region.id |
||
|
region_name |
string |
dim_region.name |
||
|
site_name |
string |
dim_site.name |
||
|
state |
string |
dim_address.state |
||
|
street |
string |
dim_address.street |
||
|
address_unit |
string |
dim_address.unit |
||
|
city |
string |
dim_address.city |
||
|
country |
string |
dim_address.country |
||
|
subsite_id |
string |
dim_subsite.id |
||
|
subsite_name |
string |
dim_subsite.name |
||
|
subsite_area_sq_m |
double |
dim_subsite.area_sq_m |
||
|
latitude |
decimal(9,6) |
corresponding the latitude from region, site, sub_site |
||
|
longitude |
decimal(9,6) |
corresponding the longitude from region, site, sub_site |
||
|
history_start_date |
date |
True |
to_date(dim_site.history_start_time) |
|
|
edw_start_time |
timestamp |
True |
dim_site.edw_start_time – ETL INTERNAL USAGE |
cal_space_location
Description: merge space related hierarchies with space type, section, floor, building, site, address and region together to facilitate BI reports. It’s implemented in incremental mode to boost its calculation performance. All data is live (row_is_current is true). If previous data is deleted, the data would be removed from the table.
Partition By: tenant_id, history_start_date
|
Column |
Type |
Mandatory |
UDM mapping |
Note |
|---|---|---|---|---|
|
space_location_pk |
long |
True |
surrogate key as primary key |
|
|
location_type |
string |
True |
Enum: One of region, site, subsite, building, floor, space |
|
|
id |
string |
True |
The id of corresponding table of location_type value |
|
|
space_id |
string |
True |
dim_space.id |
|
|
space_name |
string |
dim_space.name |
||
|
tenant_id |
string |
True |
dim_space.tenant_id |
|
|
region_id |
string |
dim_region.id |
||
|
region_name |
string |
dim_region.name |
||
|
site_id |
string |
dim_site.id |
||
|
site_name |
string |
dim_site.name |
||
|
building_id |
string |
dim_building.id |
||
|
building_name |
string |
dim_building.name |
||
|
floor_id |
string |
dim_floor.id |
||
|
floor_name |
string |
dim_floor.name |
||
|
space_type_id |
string |
dim_space_type.id |
||
|
space_type_name |
string |
dim_space_type.name |
||
|
space_capacity |
integer |
dim_space.capacity |
||
|
space_type_usage |
string |
dim_space_type.usage |
||
|
address_id |
string |
dim_address.id |
||
|
address_name |
string |
dim_address.name |
||
|
address_unit |
string |
dim_address.unit |
||
|
street |
string |
dim_address.street |
||
|
state |
string |
dim_address.state |
||
|
city |
string |
dim_address.city |
||
|
country |
string |
dim_address.country |
||
|
latitude |
decimal(9,6) |
corresponding the latitude from region, site, sub_site, building |
||
|
longitude |
decimal(9,6) |
corresponding the longitude from region, site, sub_site, building |
||
|
subsite_area_sq_m |
double |
|||
|
building_cost |
double |
dim_building.building_cost |
||
|
floor_lease_rentable_area_sq_m |
double |
dim_floor.lease_rentable_area_sq_m |
||
|
floor_interior_gross_area_sq_m |
double |
dim_floor.interior_gross_area_sq_m |
||
|
floor_exterior_gross_area_sq_m |
double |
dim_floor.exterior_gross_area_sq_m |
||
|
space_area_sq_m |
double |
dim_space.area_sq_m |
||
|
history_start_date |
date |
True |
to_date(history_start_time), the history_start_time of the corresponding table of location_type |
|
|
edw_start_time |
timestamp |
True |
dim_space.edw_start_time |