The number of business scenarios I see these days involving Dynamics 365 Customer Engagement (D365CE) and Power BI is snowballing. It is pretty easy to understand why. Power BI is an incredibly engaging and intuitive reporting tool that, in some ways, surpasses the options available to us natively within D365CE. However, getting started can be easier said than done, thanks in part to the multiple avenues possible to retrieve data from the application. At the time of writing this post, these primarily include four options:
- The Common Data Service Connector
- The Dynamics 365 (online) Connector
- The Web Connector, i.e., directly querying the applications Web API endpoint
- Using the Power Query (M) Builder community tool
Each option has its own distinct set of advantages and disadvantages. In this week’s blog post, I wanted to evaluate each one in a little more detail, to hopefully assist you in determining the most suitable one for your particular situation.
Common Data Service Connector
The Common Data Service Connector is the newest option available to us, having only just recently coming out of public preview. Insofar as your potential reporting requirements are relatively simple, this connector is most certainly the “beginners choice” for bringing data in from D365CE. This is because it supports the latest version of the applications Web API and has several useful options to help with modelling any returned data. The connector can be accessed via the Get Data option on Power BI Desktop, as illustrated below:
Once selected and, as indicated below, you must then specify the following options:
- Server URL: The full server URL for your Dynamics 365 Customer Engagement instance. For tenants located within the United Kingdom, this will typically be in the format https://.crm11.dynamics.com.
- Reorder columns: If set to true, the connector will return all entity data columns in alphabetical order.
- Add display column: If set to true, Power BI will include an additional column for specific data types to assist with readability. For example, Option Set fields will return the Option Set Display Label. I recommended that the Reorder columns and Add display column options are always set to true.
By pressing OK and, after logging in with an Organizational Account with sufficient privileges to access the instance, you will see a list of data (entities) that can be selected:
The Entities folder will display a list of all distinct entities from Dynamics 365 Customer Engagement, formatted using the Entity logical name. Any selected entity data will then load into the Power Query model after pressing the Load button. You can also click the Edit button to automatically load the Power Query Editor, thereby allowing you to carry out additional transformations to your data.
Use the Common Data Service Connector when:
- You have been asked to set up a new report using Power BI.
- No previous work has been carried out in building out D365CE system views, reports, etc. that contain snapshots of the data that you need.
- You have minimal experience using the Power Query Editor to shape data.
Dynamics 365 (online) Connector
Before the previous connector was generally released, the Dynamics 365 (online) Connector was the only available D365CE connector for Power BI Desktop. It is accessed in much the same way, from within the Online Sources tab on the Get Data dialog window:
Once selected, the options available for selection are spread across the Basic and Advanced radio buttons:
- Within the Basic tab, you must specify a single option – the full Web API URL from the application. This value can be obtained by navigating to the Developer Resources area within Dynamics 365 Customer Engagement and locating the Instance Web API URL. For instances located in the United Kingdom, this will typically be in the format of https://.crm11.dynamics.com/api/data/v9.1/. You also can specify different versions of the API to use, ranging from the following options:
- v8.0
- v8.1
- v8.2
- v9.0
- The Advanced tab allows you to specify additional query parts URL parts as part of the given Web API URL. For example, the screenshot below shows an example of how to use the URL parts to return data from the Accounts entity only:
If the Basic options are utilised, then data is returned in the same manner as the Common Data Service Connector. This then allows you to select and preview data from multiple entities before loading it into your model.
Use the Dynamics 365 (online) Connector when:
Actually, unless you are already using this connector as part of an existing report, I really would not recommend you use this at all. Because the connector excludes options to sort columns by alphabetic order or to return option set labels, working with any returned data becomes that much more difficult. What’s more, now that the Common Data Service Connector has been released, I suspect that the Dynamics 365 (online) Connector will eventually go the way of the Dodo.
Web
The Web connector utilises the Web API OData Feed, similar to the Dynamics 365 (online) Connector, but with scope to fully leverage custom Web API OData queries. For example, let’s assume you have the following Web API URL to return filtered Contact entity data:
https://.crm11.dynamics.com/api/data/v9.1/contacts?$select=emailaddress1,fullname&$filter=contains(firstname, ‘Joe’)&$orderby=fullname asc
This URL can then be entered into the From Web connector dialog box to return the results of that specific query:
Data will then load into the Power Query Editor as a JSON object. You must then click the List hyperlink to expand out and return a list of all records within a tabular format:
To understand the types of things that you can do with the Web API, I recommend that you take a look through the Microsoft Docs article that is dedicated to this subject.
Use the Web Connector when:
- You have previously authored OData or FetchXML queries and wish to re-use them within Power BI Desktop.
- You are comfortable working with web API’s and the Power Query Editor. Similar to the Dynamics 365 (online) Connector, you should anticipate some work to ensure that, for example, option set values are displayed correctly.
- You require granular control over the various D365CE Web API options.
Power Query (M) Builder
The Power Query (M) Builder application is a community plugin, available as part of the freely distributed XrmToolBox. The XrmToolBox brings together several useful tools for Dynamics 365 Customer Engagement administrators, developers, and customisers. Once you have downloaded the XrmToolBox, the Power Query (M) Builder application can be installed by navigating to the Plugins Store within the application:
Once installed, you can then use the tool to:
- Select the entity data that you wish to query from Power BI. This is achieved by defining the fields that you want to return, based on an existing Entity view, or by specifying the list of fields to return from directly within the tool.
- Bring in any existing FetchXML queries and convert them into Power Query M code.
- Generate M queries for returning entity data and any related Option Set information.
The plugin has an intuitive interface that allows you to build your queries by selecting the entity/fields you would like to work with. In the example below, the Accounts I Follow view has been chosen to generate an M query code snippet:
Code generated within the tool via the GenerateOData and GenerateOptionSets buttons can then be straightforwardly copied across into Power BI by using the Blank Query data source and using the Advanced Editor option to paste in any relevant code:
Use the Power Query (M) Builder when:
- You are already using the XrmToolBox daily and are familiar with its core components.
- You have existing custom views or FetchXML queries that you want to re-use within Power BI Desktop, and you are looking for an easy way to migrate these across.
- You have a basic awareness of using the Power Query Editor and are looking for a solution that involves you writing a minimal amount of code.
I hope this post has been useful in demonstrating the options available to work with your D365CE data from within Power BI Desktop. Please leave a comment below if you have any questions