Field Service Analytics data model
Kimball warehouse for field-service management: an accumulating work-order snapshot tracking created->dispatched->completed milestones, a truck-roll fact for dispatch efficiency, and a parts-consumption fact over conformed technician/customer/asset/service-type/region dimensions. For Salesforce Field Service (ServiceAppointment, ServiceTerritory, WorkType), use the Salesforce Field Service Analytics template.
Vendor-neutral field-service-management analytics (utilities, telecom, equipment maintenance, HVAC) covering the work-order lifecycle, technician truck rolls, and parts consumption — a Kimball star schema with conformed technician (SCD2), customer, asset, service-type, and region dimensions for first-time-fix, SLA attainment, and dispatch-efficiency reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 132
- columns
- 23
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for the work order
A work order progresses through created, dispatched, and completed milestones with measurable lags; an accumulating snapshot supports cycle-time, SLA, and first-time-fix analysis on one updated row.
Separate truck-roll fact at the visit grain
First-time-fix and routing efficiency require a record per technician visit (with repeat-visit flags) that a work-order-level fact cannot express when multiple visits resolve one order.
Parts consumption at its own transaction grain
Material cost and warranty recovery need a row per part used; modeling it separately keeps quantities and costs additive and independent of labor measures on the work order.
Customer and service address classified as PII
Residential service addresses and contacts are personal data subject to privacy controls, so they are flagged PII and masked while remaining usable for regional analytics.
Tables in the Field Service Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_work_order
Grain: one row per work order (accumulating snapshot created->dispatched->completed)
Accumulating snapshot of the work-order lifecycle with milestone lags.
| Column | Type | Keys |
|---|---|---|
| work_order_key | bigint | PK |
| created_date_key | bigint | |
| technician_key | bigint | |
| customer_key | bigint | |
| asset_key | bigint | |
| service_type_key | bigint | |
| region_key | bigint | |
| priority_key | bigint | |
| channel_key | bigint | |
| resolution_code_key | bigint | |
| dispatched_date_key | bigint | |
| completed_date_key | bigint | |
| work_order_number | varchar | |
| create_to_dispatch_hours | decimal | |
| dispatch_to_arrival_hours | decimal | |
| onsite_duration_hours | decimal | |
| create_to_complete_hours | decimal | |
| labor_cost | decimal | |
| parts_cost | decimal | |
| total_billed_amount | decimal | |
| is_first_time_fix | boolean | |
| is_sla_met | boolean | |
| customer_satisfaction_score | decimal |
fct_truck_roll
Grain: one row per technician visit (truck roll)
Transaction-grain truck-roll fact for dispatch and routing efficiency.
| Column | Type | Keys |
|---|---|---|
| truck_roll_key | bigint | PK |
| date_key | bigint | |
| technician_key | bigint | |
| customer_key | bigint | |
| region_key | bigint | |
| truck_roll_id | varchar | |
| work_order_number | varchar | |
| travel_distance_km | decimal | |
| travel_time_minutes | decimal | |
| onsite_minutes | decimal | |
| travel_cost | decimal | |
| visit_sequence | smallint | |
| is_repeat_visit | boolean | |
| is_resolved | boolean |
fct_parts_consumption
Grain: one row per part consumed on a work order
Transaction-grain parts-consumption fact for service material cost.
| Column | Type | Keys |
|---|---|---|
| parts_consumption_key | bigint | PK |
| date_key | bigint | |
| technician_key | bigint | |
| asset_key | bigint | |
| service_type_key | bigint | |
| work_order_number | varchar | |
| part_number | varchar | |
| part_description | varchar | |
| quantity_used | integer | |
| unit_cost | decimal | |
| extended_cost | decimal | |
| billed_amount | decimal | |
| is_warranty_covered | boolean | |
| is_from_truck_stock | boolean |
fct_appointment
Grain: one row per scheduled appointment slot
Transaction-grain appointment fact for scheduling and arrival-window KPIs.
| Column | Type | Keys |
|---|---|---|
| appointment_key | bigint | PK |
| date_key | bigint | |
| technician_key | bigint | |
| customer_key | bigint | |
| service_type_key | bigint | |
| region_key | bigint | |
| channel_key | bigint | |
| appointment_id | varchar | |
| scheduled_window_start_minute | integer | |
| scheduled_window_end_minute | integer | |
| actual_arrival_minute | integer | |
| window_adherence_minutes | decimal | |
| lead_time_days | decimal | |
| is_within_window | boolean | |
| is_no_show | boolean | |
| is_rescheduled | boolean | |
| is_cancelled | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean | |
| is_holiday | boolean |
dim_technician
Grain: one row per technician per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| technician_key | bigint | PK |
| technician_id | varchar | BK |
| technician_name | varchar | PII |
| employee_number | varchar | PII |
| home_region | varchar | |
| skill_level | varchar | |
| specialty | varchar | |
| certification | varchar | |
| hire_date | date | |
| is_active | boolean |
dim_customer
Grain: one row per service customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| customer_name | varchar | PII |
| customer_type | varchar | |
| service_address | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| contact_phone | varchar | PII |
| contract_tier | varchar |
dim_asset
Grain: one row per serviced asset per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| asset_key | bigint | PK |
| asset_id | varchar | BK |
| asset_name | varchar | |
| asset_type | varchar | |
| manufacturer | varchar | |
| model_number | varchar | |
| serial_number | varchar | PII |
| install_date | date | |
| warranty_expiry_date | date | |
| is_under_contract | boolean |
dim_region
Grain: one row per service region
| Column | Type | Keys |
|---|---|---|
| region_key | bigint | PK |
| region_id | varchar | BK |
| region_name | varchar | |
| district | varchar | |
| territory | varchar | |
| country | varchar | |
| dispatch_center | varchar |
Lookup tables (4)
dim_service_type
Grain: one row per service type
| Column | Type | Keys |
|---|---|---|
| service_type_key | bigint | PK |
| service_type_code | varchar | BK |
| service_type_name | varchar | |
| service_category | varchar | |
| default_sla_hours | decimal | |
| is_billable | boolean |
dim_priority
Grain: one row per priority level
| Column | Type | Keys |
|---|---|---|
| priority_key | bigint | PK |
| priority_code | varchar | BK |
| priority_name | varchar | |
| target_response_hours | decimal |
dim_channel
Grain: one row per intake channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar | |
| is_self_service | boolean |
dim_resolution_code
Grain: one row per resolution code
| Column | Type | Keys |
|---|---|---|
| resolution_code_key | bigint | PK |
| resolution_code | varchar | BK |
| resolution_name | varchar | |
| resolution_category | varchar | |
| is_successful | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_work_order.created_date_key → dim_date (many-to-one)
- fct_work_order.technician_key → dim_technician (many-to-one)
- fct_work_order.customer_key → dim_customer (many-to-one)
- fct_work_order.asset_key → dim_asset (many-to-one)
- fct_work_order.service_type_key → dim_service_type (many-to-one)
- fct_work_order.region_key → dim_region (many-to-one)
- fct_work_order.priority_key → dim_priority (many-to-one)
- fct_work_order.channel_key → dim_channel (many-to-one)
- fct_work_order.resolution_code_key → dim_resolution_code (many-to-one)
- fct_truck_roll.date_key → dim_date (many-to-one)
- fct_truck_roll.technician_key → dim_technician (many-to-one)
- fct_truck_roll.customer_key → dim_customer (many-to-one)
- fct_truck_roll.region_key → dim_region (many-to-one)
- fct_parts_consumption.date_key → dim_date (many-to-one)
- fct_parts_consumption.technician_key → dim_technician (many-to-one)
- fct_parts_consumption.asset_key → dim_asset (many-to-one)
- fct_parts_consumption.service_type_key → dim_service_type (many-to-one)
- fct_appointment.date_key → dim_date (many-to-one)
- fct_appointment.technician_key → dim_technician (many-to-one)
- fct_appointment.customer_key → dim_customer (many-to-one)
- fct_appointment.service_type_key → dim_service_type (many-to-one)
- fct_appointment.region_key → dim_region (many-to-one)
- fct_appointment.channel_key → dim_channel (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.