Build Power BI Data Model for Dynamics 365 Field Service

In the previous posts, I discussed how to build PowerPivot data models for sales and financial analysis for Dynamics GP.

In this post, I will describe how we can build a Power BI data model for Dynamics 365 Field Service.

(Related: Dynamics 365 Field Service Data Model)

Note: As of this writing, Microsoft has not made a Power BI Content Pack for Dynamics 365 Field Service available.

Primary goal of the data model is to provide useful insights to field service managers of the company using Dynamics 365 Field Service. It should enable service managers to analyze historical data and identify potential opportunities for improvement in service operations.

Here’s the list of some of the key metrics that service managers can use.

1. Estimated versus Actual Time: Are technicians able to complete work within the estimated time? If technicians are taking longer than the original estimate, it may point to lack of training, skills, parts, tools or access to right information/resources or it may be because of incorrect estimates.
2. Estimated Amount versus Actual Amount: This will help service managers review pricing and estimating process.
3. Technician Utilization: What percentage of technicians’ available time was productive i.e., spent on work orders? You can also build Technician Leaderboard using this metric.
4. Billable Time: What percentage of time used was billable?
5. Billable Amount, Cost and Gross Profit: This can be analyzed by any desirable dimensions such as Technician, Work Order Type, Equipment/Asset Type, etc.
6. Travel Time and Travel Distance: Is technicians’ travel optimized based on their skills and location?
7. Ratio of time spent on preventive maintenance work orders to time spent on work orders from customer requests: It is more efficient and cost effective to service equipment proactively than to fix them when they break down.

This is a good list as a starting point for our data model. We can enhance it in the future to include additional metrics. Here are a few examples:

1. First time fix rates: Are technicians able to fix the problem on the same day/same visit?
2. SLA Compliance Rates
3. Warranty to Maintenance Contract Conversion Rates
4. Maintenance Contract Renewal Rates
5. Customer satisfaction ratings: We can include voice of the customer data in the model to measure this.

Below is the diagram of the star schema of the data model. Entities (tables) have been denormalized to create one fact table (Work Order Details) and various dimension tables related to it. This follows Ralph Kimball’s dimensional modeling guidelines. Fact table provides measurements (how much or how many) and dimension tables can be used to answer “who, what, where, when, why, and how” part of the query or question. Dimensional model using star schema is ideal for both performance and usability for Power BI queries and reports compared to relational model used for storing data for the business application such as Dynamics Field Service.

The data model includes the following tables.

Table	Source Entity or Entities	Comments
Work Order Details	Work Order Work Order Product Work Order Service	This is the fact table used for reporting estimated and actual cost, revenue, quantities and duration. Line Type indicates whether the record is for Product or Service. Services include Mileage (with UOM of Miles or Hours).
Service Account	Account	Billing and service customer information
Resource	Resource	Represents various resources including Technicians, Vehicles, Facilities, Tools, etc.
Customer Asset	Customer Asset	Information about Customer Asset and Equipment being serviced
Work Order Type	Work Order Type	Represents the types of work order company offers (ex. Maintenance, Service, Installation, etc.)
Product Service	Product	Master table storing products and services
Price List	Price List	Price List used to bill the Work Order
Territory	Territory	Sales and Service Territory
Incident Type	Incident Type	Defines various types of incidents (issues) that a customer could report, on which work orders are based.
Calendar	N/A	Calendar table for time intelligence with relationship to Work Order Details (fact table).

In the future post, I will discuss how this data model can be used to create Power BI reports useful to service managers.