IDM - Booking

Last updated
Save as PDF

Contents

Dimension Tables
- dim_booking
- dim_booking_spaces
- dim_booking_spaces_attendees
Calculated Tables
- cal_booking_spaces

Dimension Tables

dim_booking

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
booking_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
title	string		title
start_time	datetime	True	start_time
end_time	datetime	True	end_time
booking_resource	enum	True	booking_resource	Enum: [“API“, “Calendar”, “Mobile”, “Web”]
is_private	boolean		is_private
request_time	datetime		request_time
vc_id	string		vc_id
recurrence_id	int		recurrence_id
edw_start_time	timestamp	True		ETL internal usage Azure eventhub system generated timestamp when a new UDM message is published and captured it’s used by ETL to incrementally transform UDM into IDM PowerBI should never use it for any report
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
history_end_time	timestamp			the timestamp from published UDM optional message header history_time if data is historical data, otherwise, it’s 9999-12-31 00:00:00 similar as NULL
row_is_current	boolean	True		the row data is current or not.

dim_booking_spaces

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
booking_spaces_pk	long	True		surrogate key as primary key
booking_id	string	True	id	dim_booking.id
space_id	string	True	space_id	dim_space.id
tenant_id	string	True	tenant_id	dim_booking.tenant_id
space_booked_for	enum		space_booked_for	Enum: ["Internal", "External"]
booking_status	enum		booking_status	Enum: ["booked", "inProgress", "end", "bumped", "deleted"]
pending	boolean		pending
check_in_time	datetime		check_in_time
check_out_time	datetime		check_out_time
bump_time	datetime		bump_time
deletion_time	datetime		deletion_time
is_all_day	boolean		is_all_day
actual_start_time	datetime		actual_start_time
actual_end_time	datetime		actual_end_time
booking_type	enum		booking_type	Enum: ["Internal", "External", "Client_meeting", "VIP"]
attendee_count	int		attendee_count
cost	double		cost
country_code	string		country_code
currency_code	string		currency_code
clean_down_period	int		clean_down_period
setup_period	int		setup_period
edw_start_time	timestamp	True		ETL internal usage
edw_row_is_last	boolean			ETL internal usage
history_start_time	timestamp	True
history_end_time	timestamp
row_is_current	boolean	True		the row data is current or not.

dim_booking_spaces_attendees

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
booking_spaces_attendees_pk	long	True		surrogate key as primary key
booking_id	string	True		dim_booking_spaces.booking_id
space_id	string	True	space_id	dim_booking_spaces.space_id
tenant_id	string	True	tenant_id	dim_booking_spaces.tenant_id
person_id	string		person_id	dim_platform_user.id mandatory when space type is ME and space_booked_for is Internal
first_name	string		first_name
last_name	string		last_name
email	string	True	email
edw_start_time	timestamp	True		ETL internal usage
edw_row_is_last	boolean			ETL internal usage
history_start_time	timestamp	True
history_end_time	timestamp
row_is_current	boolean	True		the row data is current or not.

Calculated Tables

[1]Denormalize booking related data into one calculate table to facilitate Power BI data marts

cal_booking_spaces

Description: merge booking spaces with related hierarchy tables such as space, 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). It includes deleted bookings identified by dim_booking_spaces.booking_status, and booking deletion data is required by booking dashboards.

Partition By: tenant_id, start_date


Column	Type	Mandatory	Note
booking_spaces_pk	long	True	surrogate key as primary key
booking_id	string	True	dim_booking_spaces.booking_id
tenant_id	string	True	dim_booking_spaces.tenant_id
space_id	string	True	dim_booking_spaces.space_id
space_name	String		dim_space.name
booking_cost	double		dim_booking_spaces.cost
is_pending	boolean		dim_booking_spaces.pending
check_in_time	timestamp		dim_booking_spaces.check_in_time
check_out_time	timestamp		dim_booking_spaces.check_out_time
actual_start_time	timestamp		dim_booking_spaces.actual_start_time
actual_end_time	timestamp		dim_booking_spaces.actual_end_time
start_date	date	True	to_date(dim_booking_spaces.actual_start_time)
end_date	date		to_date(dim_booking_spaces.actual_end_time)
attendee_count	integer		dim_booking_spaces.attendee_count
space_booked_for	string		dim_booking_spaces.space_booked_for
space_capacity	integer		dim_space.capacity
space_type_id	string		dim_space_type.id
space_type_name	string		dim_space_type.name
space_type_usage	string		dim_space_type.usage
is_desk_booking	boolean		when dim_space_type.usage=ME
is_room_booking	boolean		when dim_space_type.usage=WE
booking_category			Enum: Bookings, Cancellations when dim_booking_spaces.booking_status=Bumped or Deleted → Cancellations, otherwise, Bookings
site_id	string		dim_site.id
site_name	string		dim_site.name
building_address	string		Concat(building name, city, state and country)
building_id	string		dim_building.id
building_name	string		dim_building.name
floor_id	string		dim_floor.id
floor_name	string		dim_floor.name
address_id	string		dim_address.id
street	string		dim_address.street
city	string		dim_address.city
state	string		dim_address.state
country	string		dim_address.country
region_id	string		dim_region.id
region_name	string		dim_region.name
edw_start_time	timestamp	True	dim_space.edw_start_time