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.

Build a custom XRM app for tablets in about an hour

I am currently working on a project implementing Dynamics CRM Field Service which is bundled with Resco Mobile client. I am impressed with the capabilities of Resco Mobile solution, especially its powerful Form Rules that go far beyond what Dynamics CRM Business Rules offer.

With the recent release of Dynamics 365, I wondered if I could create an application for tablets using out of the box customization tools that come with the product rather than having to purchase an add-on.

I recently had window shutters installed at my home and noticed that the salesperson had used a tablet (I mean a yellow paper kind) to prepare an estimate. How about building an application for him and other sales professionals at his company to track prospects and prepare an estimate more efficiently?

I started by making a list of requirements for this application.

1. Ability to track prospects, customers and related data such as email, phone number and address
2. Ability to view customer’s address on the map and get driving directions
3. Ability to create estimates and save them for further actions and follow up
4. Ability to create multiple lines within an estimate with one line for each window
5. Ability to enter dimensions (width and height) for each window, select the type of shutter for each window and price the line automatically
6. Ability to calculate the estimated total automatically
7. Ability to get customer’s signature on the tablet acknowledging that he/she has reviewed the estimate
8. Ability to print the estimate and email it to the customer
9. Ability to use the application online or offline
10. Initially the application would be rolled out to field salespersons using Windows 10 tablets. However, in the future, we need an ability to use the application on Android tablets and iPads.

Once I had the basic requirements, I went to work. Here are a few design considerations I used for the application.

1. Naturally, I decided to use the Contact entity to track prospects and the Opportunity entity for creating and storing estimates.
2. Customized the Opportunity form to suit application needs.
3. Used the standard Product Catalog to define types of shutters and created a standard price list to define the list price for each product to be used to calculate the estimated amount.
4. Created a Business Rule to calculate Quantity = Width * Height
5. Added the following custom fields to Opportunity Product entity and form.
1. Window Width
2. Window Height
3. Window location (text field)
6. Added a multiline text field to the Opportunity entity and form and added a Pen Control to capture customer’s signature on a tablet.
7. Created a Word Document Template to generate the Estimate document that can be emailed to the customer as PDF using Outlook.

Here are a few screenshots of the application on Windows 10 tablet.

Notice the suggested Product from the catalog as you start typing. You can even use the wildcard to see possible matches to choose from.

Although the Contact or Opportunity form does not display the map, you can click or tap on the address on the Contact form which launches Bing Maps.

On Windows 10 tablet, you can also generate the estimate document using Word Document Template which can then be emailed using the “Send as PDF” feature of Word. Word converts the document to PDF and attaches it to the email in Outlook.

In this article, we saw how out of the box tools in Dynamics 365 together with Microsoft Office can be used to build a functional application for tablets or phones quickly and easily.

Build Custom Search using Dynamics CRM System Views

Dynamics CRM users have many options to search CRM data. They include Quick Find, Global (multi-entity) search and (my favorite) Advanced Find.

Advanced Find provides CRM users with a powerful tool to search for information they are looking for using flexible ways to filter, view and sort data. They can also search data across multiple entities such as account and opportunity in the same search.

However, from my experience, some CRM users find Advanced Find confusing and difficult to use especially when they have to search across entities because it requires some knowledge of entity relationships.

System Admins and Customizers can solve this problem by creating System Views with filters using placeholders. CRM users can use these views as a starting point, fill in some or all of the placeholders with known values to find what they are looking for.

Consider the following scenario. Inside Sales department users often need to find a prospect with opportunity that was created recently. However, they may or may not remember the exact name of the account, the exact date when the opportunity was created and the exact estimated revenue. However, they typically know the approximate timeframe when the opportunity was created and the approximate amount of the opportunity. They usually remember the State/Province where the customer is located.

We created the System View with the following filter criteria for the users.

Note the placeholders for Account Name, State/Province, Opportunity Created On and Est. Revenue. Inside Sales users can use this view as a starting point, fill in the values they know or remember and find the Account they are looking for.

They will use the following steps to perform the search.

1. Navigate to Account entity
2. Click on Advanced Find button on the top ribbon
   
   
   
3. Select the appropriate system view created for searching
   

   
   
4. Fill in the values they know or remember and click Results. Note that they do not need to fill in all the fields but only the ones they know or remember.
   
5. CRM returns the following result based on filter criteria.

   

   
   

Depending on the search requirements of the users, you may have to create multiple system views for the same entity and name them appropriately so that users can select the correct view for searching. For example, some users may also need an ability to search for the customer based on a Quote created recently.

This approach makes searching easier and consistent for end users and eliminates the need for in-depth knowledge of Advanced Find and understanding of entity relationships.

Workaround for Email Template Limitations

In Dynamics CRM, you can create entity specific email templates only for the following entities: Account, Contact, Lead, Opportunity, Case, Order, Quote, Invoice, Contract and Service Activity. You can also create a generic global email template.

However, there are many use cases where users need an ability to create and use email templates for other standard entities such as Appointment as well as for custom entities.

Standard email templates also have other limitations. For example, when you are creating an email template for the Opportunity entity, you can only insert fields from the related Contact or Account. There are situations where you may want to pull information from other related entities including custom entities into an email template.

You can take advantage of standard CRM dialog as a workaround for these limitations. This solution essentially offers the same capabilities as standard email templates for any standard CRM entity as well as custom entities.

In this example, user wants to send an email to a customer for an existing Appointment. However, user wants to be able to edit the content of the email before sending it. Because you cannot create an email template for the Appointment entity, we will use the following solution which provides the same capability as the email template.

We will create a Dialog for the Appointment entity with the following steps.

1. Create an email.
2. Change the status of the email to “Draft”.
3. Display a hyperlink to the draft email as the last step. User can click on the link to open the draft email, edit it and then send it to the customer.

Here’s the screenshot of the sample dialog.

When a user runs this dialog on an existing Appointment record, the dialog creates a draft email and displays the link as follows:

A user can click on the link to open the draft email, edit it and click send.

You can use this technique for other standard CRM entities as well as custom entities in your solution.