Connection Options for getting Dynamics 365 Sales Data into Power BI

Microsoft Dynamics 365 Sales is a model-driven app that enables users to build strong relationships with customers, discover insights to drive action and decision-making, and close sales faster. It can be used to keep track of accounts and contacts and to nurture sales from lead to order. Additionally, users can create marketing campaigns, and follow cases of service that are related to specific accounts and/or opportunities.

Power BI serves as an interactive data & analytics tool for business intelligence. It can be used to pull data from many data sources including Dynamics 365 Sales to create dashboards, report, and drill in to ask questions about key business metrics.

As of 2019, roughly 4.4 Million users utilize Dynamics 365 for their business. It is also known that Power BI has over 5 million subscribers. With these being such prevalent business solutions that Microsoft offers, it is very important to know how we can create and utilize the relationship between the two systems.

There are a few ways to connect the Dynamics 365 data source to Power BI Desktop including:

• Dynamics 365 (OData)
• Microsoft Dataverse Connection
• Common Data Service (CDS)

Dynamics 365 (OData)

Connection instructions

This option for connecting will be under the Online Services category. This Odata connection can be done by selecting the “Get Data” dropdown and selecting More….

Then, find and select the Dynamics 365 (online) option under “Online Services”

To connect to Dynamics 365 (online), you will need the web API URL, which can be retrieved from Dynamics 365 in developer resources.

• First, go to Advanced Settings in the online Dynamics 365 Application.
• Then, click Settings > Customizations

• Within the Customizations tab, click Developer Resources.

Here you will find the Web API URL that is needed for the Power BI connection.

• Copy and paste this URL into the Dynamics 365 (online) pop-up window.

• Then, sign in with your organizational account to grant Power BI data access.

The Navigator window will provide a full list of accessible entities from Dynamics to choose from. Now it is time to choose the entities you need and load them into Power BI.

After your table is imported, you can begin building reports with that loaded data.

Microsoft Dataverse Connection

The Microsoft Dataverse is a key piece of the Business Applications Platform. It provides secure cloud storage for an application’s data and for integrating with Dynamics 365. The Microsoft Dataverse connection enables us to extend the Dynamics 365 application to import the business data of need. This connector is the most recent version and uses the tabular data stream (TDS) protocol.

All Dynamics 365 applications are built on top of the Microsoft Dataverse (previously named Common Data Service (CDS)), which is the secure backbone that powers Dynamics 365 and Power Platform. For more information on what Microsoft Dataverse is and the naming changes from Common Data Service, click here: Microsoft Dataverse

Connection instructions

• First, select the “Get Data” dropdown and select More…. Next, select the Power Platform and select Dataverse.

Connecting to Microsoft Dataverse can be done by using the dynamics URL.

• When signed into the Dynamics 365 application, copy and paste the URL from that webpage into the Environment Domain section. Make sure you disclude “https://” and the “/” at the end of the URL when you enter it in.

When entering this domain, you are given the option to select between Import & DirectQuery.

• Microsoft recommends using Import when at all possible. With the Import connectivity mode, data is cached in the Power BI service and can be refreshed at regular intervals or ad hoc.
• With DirectQuery mode, the connection is done directly to the Dataverse. This mode is meant to be used when real-time data retrieval is needed. This mode is also said to more strictly enforce the Dataverse security model.

Since I want to bring the data in to be cached and refreshed at regular intervals, I will choose Import.

After selecting OK on the previous dialog box, you may be prompted to sign in with your organization’s credentials. If so, do this and select Connect.

After successfully signing in, the Navigator will present all tables available for your environment. Just as done in the Dynamics 365 (Online) connection, select the tables you need, and select Load

After your table is imported, you can begin building reports with that loaded data.

Common Data Service (Legacy) Connection

This connection is the earlier version of the Microsoft Dataverse connection.

Benefits to using the Legacy connection over the Dataverse connection:

• This connection should be used for datasets larger than 80 MB
• Supports paging of query results
• Can be used for building reports that use the image data type

Connection instructions

• Within Get Data, navigate to Power Platform
• Next, you can enter the Server URL in the format seen in the figure below. This will include https:// and the trailing /. Make sure to keep this URL handy so you can easily paste it when needing to import more data.

After successfully signing in, the Navigator should present two options showing the list of entities in “Entities”, and “System”.

When using the CDS connector, you will likely be querying through the “Entities” option, as this area has the new features. Using “System” is identical to querying via the Dynamics 365 Online connector.

• Once you choose the entities you want to load into the report, click “Load” and the chosen entity will be imported into Power BI.
• After your table is imported, you can begin building reports with that loaded data.

Report Sharing

Once reports are built, users with the right licensing can view these reports in the Power BI service. Alternatively, the reports and visualizations could be embedded into the Dynamics 365 Sales environment.

Common Obstacles & their solutions in working with Dynamics 365 data in Power BI

“Cannot find the Entity I need”

Oftentimes, as a Power BI report developer, you may be exposed to the Dynamics 365 UI, but not know what the “back-end” field names are that you’d like to bring in. In order to make sure you find the exact fields you are looking for, take the following steps:

1. From your app’s home page, navigate to the settings icon on the ribbon and select Advanced Settings.
2. Next, select the Settings dropdown menu and click Customizations à Customize the System
3. You will then have a new window pop up. Within the window, you will be able to search through entities. Within the entity you are interested in, select Fields, and you will be able to explore through the tables with those “back-end” naming conventions.

“Unable to Connect via Microsoft Dataverse Connection”

 A common obstacle in connecting via Microsoft Dataverse involves an issue with the SQL Server Connection:

If you experience an error like the one above, try the following two steps to overcome this:

1. The environment ID used may have been incorrectly formatted. If this is the case, make sure that you remove https:// from the beginning of the URL and remove the “/” from the end of the URL.
2. If this does not solve the issue, it could be that the TDS endpoint needs to be configured to allow remote connections. To enable the TDS endpoint, navigate to the Power Platform Admin Center for the Dynamics Application. Next, click settings in the ribbon. Within the Product section, click Features to see the page below:

Make sure the TDS endpoint is turned on, then see if the Power BI connection now works.

“Data will not refresh on Power BI Service”

 If failing to refresh data within the Power BI Service, you may see the following error message:

If so, you may need to update the data source credentials. To do this, use the following steps:

1. On the dataset you are interested in, select the ellipses icon (more options) à Settings to get to the following page:

2. Once here, select “Edit credentials” to ensure that you are signed in under the correct authentication for your organization.

Connecting to Dynamics 365 Sales using the connectors set up in Power BI is simple no matter which way you choose to connect.

If using the Dynamics 365 (Online) connector, there are two main issues that you may run into:

1. You cannot directly get option set labels and lookup values without applying an additional query or lookup table
2. You may encounter obstacles while trying to connect

The simplest (least number of steps) way to connect is to use the Common Data Service (Legacy) connector.

If you have any questions about this process, Power BI, or data analytics in general, please visit our website, call 800-274-3978, or contact us.

Evan Hopper