IDM - Visit

Last updated
Save as PDF

Contents

Dimension Tables
- dim_visit
- dim_visitor
- dim_visitor_type
- dim_visit_occurrence
Calculated Tables
- cal_visitor_type_location

Dimension Tables

dim_visit

Partition By: tenant_id

Column	Type	Mandatory	UDM mapping	Note
visit_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
location_id	string	True	location_id	dim_building.id
name	string	True	name
status	string	True	status	Enum: in_review denied expected
timezone	string	True	timezone
host_person_id	string	True	host_person_id	dim_platform_user.person_id
creator_person_id	string		creator_person_id	dim_platform_user.person_id
start_time	timestamp	True	start_time
end_time	timestamp	True	end_time
visitor_ids	string	True	visitor_ids	The visitor IDs are delivered as a JSON array stored in string format. Because Microsoft Fabric Lakehouse does not yet support JSON array types, users must query and process these values as raw JSON strings. To work with the individual array elements, users can use OUTER APPLY with OPENJSON to expand the JSON array into rows.
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_visitor

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
visitor_pk	long	True		surrogate key as primary key
id	string	True	id
tenant_id	string	True	tenant_id	dim_tenant.id
person_id	string	True	person_id	dim_platform_user.person_id When person_id is not NULL, the visitor is employee.
preferred_language_code	string	True	preferred_language_code
first_name	string	True	first_name
last_name	string	True	last_name
email	string		email
company_name	string		company_name
about	string		about
license_plate	string		license_plate
visit_type_id	string	True	visit_type_id	dim_visit_type.id
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_visitor_type


Column	Type	Mandatory	UDM mapping	Note
visitor_type_pk	long	True		surrogate key as primary key
id	string	True	id
tenant_id	string	True	tenant_id	dim_tenant.id
name	string	True	name
description	string		description
location_id	string	True	location_id	dim_building.id
is_default	string	True	is_default
is_active	string	True	is_active
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_visit_occurrence

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
visit_occurrence_pk	long	True		surrogate key as primary key
id	string	True	id
tenant_id	string	True	tenant_id	dim_tenant.id
status	string	True	status	Enum: in_review denied expected checked_in checked_out on_site
visit_id	string	True	visit_id	dim_visit.id
visitor_id	string	True	visitor_id	dim_visitor.id
host_person_id	string	True	host_person_id	dim_platform_user.person_id
occurrence_date	date	True	occurrence_date
expected_arrival_time	timestamp	True	expected_arrival_time
expected_departure_time	timestamp	True	expected_departure_time
pre_checked_in_time	timestamp		pre_checked_in_time
on_site_time	timestamp		on_site_time
checked_in_time	timestamp		checked_in_time
checked_out_time	timestamp		checked_out_time
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 visitor related data into one calculate table to facilitate Power BI data marts

cal_visitor_type_location

Description: Incrementally merge visitor, visitor_type, visit, visit_occurrence and location hierarchical data together to facilitate power bi data mart. The location hierarchy data from building up to region. All data is live (row_is_current is true). If previous data record is deleted, it would be removed from the table.

Partition By: tenant_id, start_date


Column	Type	Mandatory	Note
visitor_type_location_pk	long	True	surrogate key as primary key
tenant_id	string	True	dim_tenant.id
visitor_id	string	True	dim_visitor.id
visit_id	string	True	dim_visit.id
visitor_type_id	string	True	dim_visitor_type.id
visitor_type_name	string	True	dim_visitor_type.name
start_date	date	True	to_date(dim_visit.start_time)
end_date	date	True	to_date(dim_visit.end_time)
expected_arrival_time	timestamp	True	dim_visit_occurrence.expected_arrival_time
expected_departure_time	timestamp	True	dim_visit_occurrence.expected_departure_time
checked_in_time	timestamp		dim_visit_occurrence.checked_in_time
checked_out_time	timestamp		dim_visit_occurrence.checked_out_time
visitor_first_name	string	True	dim_visitor.first_name
visitor_last_name	string	True	dim_visitor.last_name
visit_name	string	True	dim_visit.name
visit_status	string	True	dim_visit_occurrence.status
person_id	String		dim_platform_user.person_id when it’s NULL, the visitor is employee
building_id	string	True	dim_building.id
building_name	string	True	dim_building.name
region_id	string		dim_region.id
region_name	string		dim_region.name
site_id	string		dim_site.id
site_name	string		dim_site.name
city	string		dim_address.city
state	string		dim_address.state
country	string		dim_address.country
address_id	string		dim_address.id
edw_start_time	timestamp	True	dim_visit_visitor.edw_start_time ETL internal usage