Use Power BI with Microsoft Dynamics 365

 

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

Note

The information provided here is for versions of Dynamics 365 prior to Dynamics 365 (online), version 9.0. For the latest documentation, see Use Power BI.

The Power BI for Office 365 cloud service works with Microsoft Dynamics 365 to provide a self-service analytics solution. Power BI automatically refreshes the Microsoft Dynamics 365 (online) data displayed. With Power BI Desktop or Microsoft Office Excel Power Query for authoring reports and Power BI for sharing dashboards and refreshing data from Microsoft Dynamics 365 (online), sales, marketing, and service personnel in your organization have a powerful new way to work with Dynamics 365 data.

In this topic

Get started using Microsoft Power BI with Microsoft Dynamics 365 (online)

Embed Power BI visualizations on personal dashboards

Use Power BI Desktop to connect directly to your Microsoft Dynamics 365 (online) instance

Use Power BI with Microsoft Dynamics 365 (on-premises)

Get started using Microsoft Power BI with Microsoft Dynamics 365 (online)

The Microsoft Dynamics 365 content packs for Power BI cloud service allow you to easily access and analyze your sales, service, or marketing data.

To create a Power BI dashboard using a content pack, follow these instructions.

1. If you haven't already done so, register with Microsoft Power BI.

2. After you have signed in to Power BI, in the Datasets area click Get Data, under Services click Get, and then select from the following content packs.

   • Microsoft Dynamics 365 (online) Sales Manager

   • Microsoft Dynamics 365 (online) Service Manager

   • Microsoft Dynamics Marketing

3. For the Sales Manager and Service Manager content packs, enter the URL of your Microsoft Dynamics 365 (online) instance, such as https://OrganizationName.crm.dynamics.com, where OrganizationName is the organization name of your instance of Microsoft Dynamics 365 (online), and click Next.

   Note

   If your data center is outside of North America the crm.dynamics.com domain name may be different, such as crm2.dynamics.com, crm3.dynamics.com, crm4.dynamics.com, etc. To find the domain name, in the Dynamics 365 web app go to Settings > Customizations > Developer Resources. The URLs listed will indicate the correct domain name.

   For the Marketing content pack, enter the URL as https://OrganizationName.marketing.dynamics.com/analytics, where OrganizationName is the organization name of your instance of Microsoft Dynamics 365 (online), and click Next

4. Under Authentication method, select oAuth2.

5. Your Microsoft Dynamics 365 (online) organization data is imported and several visualizations become available.

Tip

If the content pack you select does not open in your web browser, in the left pane of your Power BI workspace click the content pack under Dashboards.

Content packs available for download.

The Microsoft Dynamics 365 content packs support the default out-of-box entities. However, you can customize the following content packs by downloading the .PBIX file and then using Power BI Desktop to customize the content pack before uploading it to the Power BI service.

• Download the Microsoft Dynamics CRM Online Sales Manager .PBIX

• Download the Microsoft Dynamics 365 (online) Service Manager .PBIX

The Power BI Report Template for Connected Field Service for Microsoft Dynamics 365 enables users to publish a Power BI report that displays the live heart beat of connected devices.

• Download the Power BI Report Template for Connected Field Service for Microsoft Dynamics 365

For information about how to customize the content packs, see Customize Microsoft Dynamics 365 Power BI content packs.

Embed Power BI visualizations on personal dashboards

Before users can embed Power BI visualizations on personal dashboards, the organization-wide setting must be enabled.

Note

This feature was first introduced in CRM Online 2016 Update 1.

By default, Power BI visualization embedding is disabled and must be enabled before users can embed them in personal dashboards.

Enable Power BI visualizations in the organization

1. Sign-in to Microsoft Dynamics 365 as a user with the system administrator security role.

2. Go to Settings > Administration > System Settings.

3. On the Reporting tab in the Allow Power BI visualization embedding option, select Yes to enable or No to disable.

4. Click OK.

To learn more about how to add Power BI tiles to personal dashboards in Microsoft Dynamics 365, see Embed Power BI tiles on your personal dashboard.

To learn more about how to add Power BI dashboards to personal dashboards in Microsoft Dynamics 365, see Add a Power BI dashboard on your personal dashboard.

Use Power BI Desktop to connect directly to your Microsoft Dynamics 365 (online) instance

You can connect to Microsoft Dynamics 365 (online) with Power BI Desktop to create custom Dynamics 365 reports and dashboards for use with the Power BI service.

Requirements

• Power BI service registration

• Power BI Desktop.

• Microsoft Dynamics 365 (online) instance

Connect to Dynamics 365 (online)

1. Start Power BI Desktop.

2. From the Home tab, click Get Data, and then click More.

3. In the Get Data list, select Dynamics 365 Online.

4. Enter the Dynamics 365 (online) OData endpoint URL. It should look similar to this URL, where OrganizationName is the name of your Dynamics 365 (online) organization, and v8.1 is the version. Click OK.

   https://OrganizationName.api.crm.dynamics.com/api/data/v8.1

   Tip

   You can find your OData endpoint URL in the Microsoft Dynamics 365 web client. Go to Settings > Customizations > Developer Resources and locate the URL under Instance Web API.

5. In the Access an OData feed dialog click Organizational account, and then click Connect.

   Note

   If you aren't signed in to your Microsoft Dynamics 365 (online) instance, click Sign-in on the Access OData feed dialog before you click Connect.

6. The organization database entity tables appear in the Power BI Desktop Navigator window. You can select both default and custom entities. For more information about creating reports with Power BI Desktop, see Power BI Support: Report View in Power BI Desktop.

   

   Tip

   You can use similar steps to connect to Microsoft Dynamics 365 (online) using Microsoft Office Excel Power Query by selecting From Other Sources on the Power Query tab in Excel.

Use Power BI with Microsoft Dynamics 365 (on-premises)

You can use Power BI with Dynamics 365 (on-premises) to create and publish rich visualizations that can be published on powerbi.microsoft.com and accessed from anywhere.

Requirements

• Windows Server 2012 R2 with AD FS 3.0 (required for Internet-facing deployment (IFD).

• For IFD, OAuth must be enabled on the Microsoft Dynamics 365 Server. This step is described in the following section.

• Microsoft Power BI account.

Note

• Embedded Power BI tiles and dashboards are not supported with on-premises versions of Microsoft Dynamics 365.

• Scheduled refresh of reports isn’t supported with Dynamics 365 (on-premises) datasets that are published to the Power BI service. You can refresh reports using in Microsoft Power BI Desktop or Microsoft Office Excel and then upload the reports to the Power BI service.

Configure Microsoft Dynamics 365 (on-premises) for Power BI

1. If you're connecting to the Dynamics 365 (on-premises) deployment internally, (not using IFD), skip to step 4.

   If the deployment is configured for IFD, enable OAuth on the Microsoft Dynamics 365 Server. To do this, open a Windows PowerShell window and run the following PowerShell commands.

   Add-PSSnapin Microsoft.Crm.PowerShell
   $fedurl = Get-CrmSetting -SettingType ClaimsSettings
   $fedurl.FederationProviderType = 1
   Set-CrmSetting $fedurl
   

2. After you run the previous commands you need to restart IIS. In a command window, type IISReset, and then press ENTER.

3. Register the Power BI Desktop OAuth 2.0 client with ADFS. To do this, open a Windows PowerShell window and run the following PowerShell command on the PC where you are running Power BI Desktop that will be used to publish your reports to the Power BI service.

   Add-AdfsClient -ClientId "a672d62c-fc7b-4e81-a576-e60dc46e951d" -Name "Microsoft Power BI" -RedirectUri @("https://de-users-preview.sqlazurelabs.com/account/reply/", "https://preview.powerbi.com/views/oauthredirect.html") -Description "ADFS OAuth 2.0 client for Microsoft Power BI"
   

4. In Power BI Desktop sign in to Power BI, and then use the standard OData Feed connector to connect to your Dynamics 365 (on-premises) system by using Windows or OAuth authentication. To do this…

   1. Connect to a data source in Power BI Desktop or Excel using the OData Feed connector.

   2. • For internal (non-IFD) deployments enter the URL for the OData feed similar to https://server/crmorganization/api/data/vn.0 where server/crmorganization is the web app path for the Microsoft Dynamics 365 organization and vn.0 is the version of Microsoft Dynamics 365, such as v8.0 for Microsoft Dynamics 365. For example, https://contoso1/contosocrm/api/data/v8.0.

        When you are prompted to sign-in to access the OData feed, select Windows from the list of authentication types, and then enter your Dynamics 365 (on-premises) credentials.

      • For IFD deployments, enter the URL for the OData feed similar to https://[organization_URI]/api/data/vn.0, where organization_URI is your Internet accessible full domain name and vn.0 is the version of Microsoft Dynamics 365, such as v8.0 for Microsoft Dynamics 365. For example, the Contoso organization has this URL: https://crmweb.contoso.com/api/data/v8.0.

        When you are prompted to sign-in to access the OData feed, select Organizational account from the list of authentication types, and then enter your Dynamics 365 (on-premises) credentials.

5. The organization database tables are loaded from which you can build reports.

6. Publish your reports to Power BI and build dashboards.

7. Refresh your reports by publishing them again with Power BI Desktop or Excel on a frequent basis.

See Also

Extend Dynamics 365 with integration and solutions

© 2017 Microsoft. All rights reserved. Copyright1