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.

Upgrade PowerPivot Data Model to SSAS Tabular

In the previous articles, I discussed the steps for building PowerPivot data models for Dynamics GP Sales and Financial (GL) data using PowerPivot for Excel. This is a great way for power users to build powerful business intelligence solutions without expensive hardware and difficult to use development tools. It is also a great way for professional BI developers to prototype a complete solution quickly.

However, there will be situations where PowerPivot based solutions may not meet your business needs. In this case, there are two upgrade paths available to migrate your PowerPivot data model. These are PowerPivot for SharePoint and SSAS Tabular. In this article, I will discuss SSAS Tabular.

Here are some of the reasons why you may want to upgrade from PowerPivot to SSAS Tabular.

Security

Tabular model databases can use row-level security, using role-based permissions in Analysis Services. For example, you can configure security to enable Sales Manager to view all sales data while Salesperson to view only his or her sales data.

Power Pivot workbooks are secured at the file level. A user who has access to the file can view all the information contained within the workbook.

Scalability

PowerPivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in Power Pivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a Power Pivot workbook to a tabular solution on a standalone Analysis Services instance is to get around the file size limitation.

Partitioning

Partitions, in tabular models, divide a table into logical partition objects. Each partition can then be processed independent of other partitions. For example, a table may include certain row sets that contain data that rarely changes (historical sales data, for example), but other row sets have data that changes often (current year’s sales data).

Query and Reporting

Excel and PowerView are your only options if you use PowerPivot. With SSAS Tabular, you can use many other reporting tools including SQL Server Reporting Services (SSRS).

You will find detailed comparison of SSAS Tabular and PowerPivot here.

Before you upgrade your PowerPivot data model to SSAS Tabular, you will need the following:

1. SQL Server Analysis Services instance running in Tabular mode (I used SQL Server 2014)
2. SQL Server Data Tools (SSDT) (This is required if you use Visual Studio to import your data model to SSAS Tabular. Please see below for details)

There are two ways you can import your PowerPivot data model to SSAS Tabular.

Option 1:

SQL Server Management Studio option: Import directly to SQL Server using Restore from PowerPivot option as shown in the following screenshot

Option 2:

Visual Studio (SSDT) option: Import using Visual Studio (SSDT) as shown in the following screenshot

Once completed, you will be able to connect to the SSAS Tabular data model on the server and create a report in Excel and other reporting tools such as SSRS.

Here’s the PivotTable created using Excel. You will notice that the presentation of PivotTable fields is slightly different from what you see when you create a PivotTable from PowerPivot data model. For example, KPIs have their own folder. However, the end result is the same.

Because the data is no longer stored within the Workbook but is stored on the server, the file size is very small as shown in the following screenshot.

After you have migrated the PowerPivot data model to SSAS Tabular, you do not need the original Excel workbook any more. You will need to use Visual Studio to make changes to the Tabular data model and implement new features such as security and partitioning.

If you would like to learn more about SSAS Tabular, you will find step by step tutorials here.