Salesforce Field Service Analytics data model
Kimball warehouse for Salesforce Field Service (WorkOrder, WorkOrderLineItem, ServiceAppointment, ServiceResource, ServiceTerritory, WorkType, Asset). Accumulating-snapshot work-order and appointment facts with dispatch milestones, a parts/labour line fact, and a daily resource-utilization snapshot for field-operations performance.
Salesforce Field Service (FSL) migrated to a cloud warehouse: work orders, service appointments, parts/labour lines, and technician utilization over conformed SCD2 Account, Service Resource, and Asset dimensions plus service-territory and work-type dimensions for first-time-fix, SLA attainment, travel-vs-wrench time, and resource utilization analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 119
- columns
- 14
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Work Order and Service Appointment as separate accumulating snapshots
FSL splits the 'what' (WorkOrder: scope, parts, cost) from the 'when/who' (ServiceAppointment: scheduling, dispatch, arrival); each has its own milestone sequence, so each gets its own accumulating snapshot.
Dispatch milestones for travel-vs-wrench time
Scheduled → dispatched → arrived → completed timestamps on the appointment fact separate travel time from on-site wrench time and measure arrival-window punctuality, the core FSL efficiency metrics.
Daily resource-utilization snapshot
Utilization, idle time, and capacity are per-resource-per-day measures used for scheduling optimisation and headcount planning, independent of any single appointment, so they live in a periodic snapshot.
Asset as a conformed SCD2 dimension
Repeat service against the same installed Asset drives reliability and warranty analytics; versioning the Asset dimension preserves its state (status, warranty) as of each job.
Tables in the Salesforce 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 WorkOrder (accumulating snapshot across the job lifecycle)
Accumulating-snapshot work-order fact for SLA attainment, first-time-fix, and cost-to-serve.
| Column | Type | Keys |
|---|---|---|
| work_order_key | bigint | PK |
| account_key | bigint | |
| asset_key | bigint | |
| service_territory_key | bigint | |
| work_type_key | bigint | |
| created_date_key | bigint | |
| scheduled_date_key | bigint | |
| completed_date_key | bigint | |
| closed_date_key | bigint | |
| work_order_id | varchar | |
| work_order_number | varchar | |
| case_id | varchar | |
| status | varchar | |
| priority | varchar | |
| sla_due_date_key | bigint | |
| total_price | decimal | |
| total_cost | decimal | |
| duration_minutes | integer | |
| is_sla_met | boolean | |
| is_first_time_fix | boolean | |
| is_closed | boolean | |
| work_order_count | smallint |
fct_service_appointment
Grain: one row per ServiceAppointment (accumulating snapshot across dispatch)
Accumulating-snapshot appointment fact for dispatch performance, punctuality, and travel-vs-wrench time.
| Column | Type | Keys |
|---|---|---|
| service_appointment_key | bigint | PK |
| account_key | bigint | |
| service_resource_key | bigint | |
| service_territory_key | bigint | |
| work_type_key | bigint | |
| asset_key | bigint | |
| scheduled_date_key | bigint | |
| dispatched_date_key | bigint | |
| arrived_date_key | bigint | |
| completed_date_key | bigint | |
| appointment_id | varchar | |
| work_order_id | varchar | |
| status | varchar | |
| scheduled_duration_minutes | integer | |
| actual_duration_minutes | integer | |
| travel_minutes | integer | |
| arrival_window_minutes | integer | |
| reschedule_count | smallint | |
| is_within_arrival_window | boolean | |
| is_completed | boolean | |
| appointment_count | smallint |
fct_work_order_line
Grain: one row per WorkOrderLineItem (part or labour line)
Transaction-grain work-order lines for parts consumption, labour, and margin per job.
| Column | Type | Keys |
|---|---|---|
| work_order_line_key | bigint | PK |
| product_key | bigint | |
| date_key | bigint | |
| work_order_id | varchar | |
| work_order_line_id | varchar | |
| line_type | varchar | |
| quantity | decimal | |
| unit_price | decimal | |
| total_price | decimal | |
| total_cost | decimal | |
| is_billable | boolean | |
| line_count | smallint |
fct_resource_utilization_snapshot
Grain: one row per service resource per day (periodic snapshot)
Daily resource-utilization snapshot for scheduling efficiency and capacity planning.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| service_resource_key | bigint | |
| service_territory_key | bigint | |
| scheduled_minutes | integer | |
| worked_minutes | integer | |
| travel_minutes | integer | |
| idle_minutes | integer | |
| capacity_minutes | integer | |
| utilization_pct | decimal | |
| appointments_assigned | smallint | |
| appointments_completed | smallint |
Dimension tables (4)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| day_of_week | smallint | |
| is_weekend | boolean | |
| is_business_day | boolean |
dim_account
Grain: one row per Salesforce Account (customer) per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| salesforce_id | varchar | BK |
| account_name | varchar | |
| service_tier | varchar | |
| billing_country | varchar | |
| service_region | varchar | |
| is_under_contract | boolean |
dim_service_resource
Grain: one row per ServiceResource (technician) per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| service_resource_key | bigint | PK |
| salesforce_id | varchar | BK |
| resource_name | varchar | PII |
| resource_type | varchar | |
| primary_territory | varchar | |
| skill_set | varchar | |
| is_capacity_based | boolean | |
| hourly_cost | decimal | |
| is_active | boolean |
dim_asset
Grain: one row per serviced Asset per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| asset_key | bigint | PK |
| salesforce_id | varchar | BK |
| asset_name | varchar | |
| product_family | varchar | |
| serial_number | varchar | |
| install_date | date | |
| warranty_end_date | date | |
| status | varchar |
Lookup tables (3)
dim_service_territory
Grain: one row per ServiceTerritory
| Column | Type | Keys |
|---|---|---|
| service_territory_key | bigint | PK |
| salesforce_id | varchar | BK |
| territory_name | varchar | |
| territory_type | varchar | |
| region | varchar | |
| country | varchar | |
| operating_hours_name | varchar | |
| timezone | varchar |
dim_work_type
Grain: one row per WorkType (standard job definition)
| Column | Type | Keys |
|---|---|---|
| work_type_key | bigint | PK |
| salesforce_id | varchar | BK |
| work_type_name | varchar | |
| category | varchar | |
| estimated_duration_minutes | integer | |
| requires_parts | boolean |
dim_product
Grain: one row per Product2 (parts catalogue)
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| salesforce_id | varchar | BK |
| product_code | varchar | |
| product_name | varchar | |
| product_family | varchar | |
| is_serialized | boolean | |
| list_price | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_work_order.account_key → dim_account (many-to-one)
- fct_work_order.asset_key → dim_asset (many-to-one)
- fct_work_order.service_territory_key → dim_service_territory (many-to-one)
- fct_work_order.work_type_key → dim_work_type (many-to-one)
- fct_service_appointment.account_key → dim_account (many-to-one)
- fct_service_appointment.service_resource_key → dim_service_resource (many-to-one)
- fct_service_appointment.service_territory_key → dim_service_territory (many-to-one)
- fct_service_appointment.work_type_key → dim_work_type (many-to-one)
- fct_service_appointment.asset_key → dim_asset (many-to-one)
- fct_work_order_line.product_key → dim_product (many-to-one)
- fct_work_order_line.date_key → dim_date (many-to-one)
- fct_resource_utilization_snapshot.date_key → dim_date (many-to-one)
- fct_resource_utilization_snapshot.service_resource_key → dim_service_resource (many-to-one)
- fct_resource_utilization_snapshot.service_territory_key → dim_service_territory (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.