IDM - Work Order

Last updated
Save as PDF

Contents

Dimension Tables:
- dim_work_order
- dim_work_type
- dim_work_status
- dim_work_status_type
Calculated Tables
- cal_work_order_type_location

Dimension Tables

dim_work_order

Partition By: tenant_id


Column	Type	Mandatory	UDM mapping	Note
work_order_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
type_id	string	True	type_id	dim_work_type.id
priority_id	string	True	priority_id
status_id	string	True	status_id	dim_work_status.id
site_id	string	True	site_id	dim_site.id
asset_id	string		asset_id	dim_asset.id
budget_id	string		budget_id
customer_id	string		customer_id
schedule_id	string		schedule_id
vendor_id	string		vendor_id
failure_id	string		failure_id
request_id	string		request_id
inspection_task_id	string		inspection_task_id
number	int	True	number
purpose	string	True	purpose
created	timestamp	True	created
due	timestamp		due
completed	timestamp		completed
vendor_access_expiration	timestamp		vendor_access_expiration
scheduled	timestamp		scheduled
description	string		description
was_processed	boolean		was_processed
duration_estimated	double		duration_estimated
duration_actual	double		duration_actual
vendor_access_key	string		vendor_access_key	uuid
vendor_status	string		vendor_status	Enum: work_order_accepted work_order_submitted etc.
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_work_type


Column	Type	Mandatory	UDM mapping	Note
work_type_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
name	string	True	name
failure_mode	string	True	failure_mode	Enum: not_applicable optional required_at_creation required_at_completion
workflow_id	string		workflow_id	UUID
is_invoiceable	boolean		is_invoiceable
is_customer_required	boolean		is_customer_required
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_work_status


Column	Type	Mandatory	UDM mapping	Note
work_status_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
user_id	string	True	user_id	dim_platform_user.id
status_type_id	string	True	status_type_id	dim_work_status_type.id
name	string	True	name
failure_mode	string	True	failure_mode	Enum: not_applicable optional required_at_creation required_at_completion
is_invoiceable	boolean		is_invoiceable
is_customer_required	boolean		is_customer_required
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_work_status_type


Column	Type	Mandatory	UDM mapping	Note
work_status_type_pk	long	True		surrogate key as primary key
id	string	True	id	uuid
tenant_id	string	True	tenant_id	dim_tenant.id
status_flow_id	string	True	status_flow_id
name	string	True	name
determines_initial_status	boolean		determines_initial_status
is_active	boolean		is_active
process_work_order	boolean		process_work_order
is_secured	boolean		is_secured
require_log_entries	boolean		require_log_entries
require_labor	boolean		require_labor
require_parts	boolean		require_parts
require_services	boolean		require_services
require_digital_signature	boolean		require_digital_signature
require_electronics_signature	boolean		require_electronics_signature
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

Denormalize work order related data into one calculate table to facilitate Power BI data marts

cal_work_order_type_location

Description: Incrementally merge work_order, work_type, asset, asset_type, and location hierarchical data together to facilitate power bi data mart. The building or lower location hierarchy data is linked by work order’s asset location if it’s available. 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, created_date


Column	Type	Mandatory	Note
work_order_type_location_pk	long	True	surrogate key as primary key
tenant_id	string	True	dim_tenant.id
work_order_id	string	True	dim_work_order.id
work_order_type_id	string	True	dim_work_type.id
work_order_type_name	string	True	dim_work_type.name
asset_id	string		dim_work_order.asset_id
asset_type_id	string		dim_asset_type.id
asset_type_name	string		dim_asset_type.name
created_date	date	True	dim_work_order.created
due_date	date		dim_work_order.due
scheduled_date	date		dim_work_order.scheduled
completed_date	date		dim_work_order.completed
site_id	string		dim_work_order.site_id
address_id	string		dim_address.id
region_id	string		dim_region.id
building_id	string		dim_building.is
floor_id	string		dim_floor.id
space_id	string		dim_space.id
site_name	string		dim_site.name
region_name	string		dim_region.name
building_name	string		dim_building.name
floor_name	string		dim_floor.name
space_name	string		dim_space.name
city	string		dim_address.city
state	string		dim_address.state
country	string		dim_address.country
edw_start_time	timestamp	True	dim_work_order.edw_start_time – ETL INTERNAL USAGE