Power BI Microsoft Dynamics CRM 2013 – Part 2

Colin Maitland, 15 October 2014

In my previous blog I showed some samples of Power View, PowerPivot and Power Map reports that displayed Account and Opportunity related information retrieved from a Microsoft Dynamics CRM Online Organisation. In this blog I will provide a list of the supported versions of Microsoft Excel, the Power BI related add-ins that are required to be installed and enabled in Microsoft Excel, and how to create a Power Query connection to a Microsoft Dynamics CRM Online Organisation. 

Supported Microsoft Excel Versions

The preferred version of Microsoft Excel is Microsoft Excel 2013 Professional Plus or Microsoft Excel 365 ProPlus as these versions provide the richest suite of Power BI features. It is important not to confuse the Professional with the Professional Plus version. Supported versions of Microsoft Excel are: 

•  Microsoft Office 2010 Professional Plus with Software Assurance
•  Microsoft Office 2013 Professional Plus, Office, 365 ProPlus or Excel 2013 Standalone.

Install and Configure Power BI Related Add-Ins

The following add-ins need to be downloaded, installed and enabled for Microsoft Excel 2013 Professional Plus 2013: 

•  Power Query for Microsoft Excel:  Can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=39379. You need to ensure that you download and install either the 32-bit version or the 64-bit version based on
your installed version of Microsoft Excel, either 32-bit or 64-bit. 

 •  Power Map Preview for Microsoft Excel 2013: Can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=38395. Note: Power Map is already included with Microsoft Office 365  

Once installed and enabled, the following POWER QUERY and POWERPIVOT tabs should be visible in Microsoft Excel:

 

Power View and Map should be displayed in the Reports and Power Map areas of the INSERT tab in Microsoft Excel: 

If any of these items are not visible then ensure that the following COM Add-ins are enabled in Microsoft Excel by completing these steps: 

1.  Navigate to File, Options.
2.  Select Add-Ins from the list of items on the left-hand pane of the Excel Options window.
3.  Select COM Add-Ins in the Mange field located at the bottom of the View and Manage Microsoft Office Add-Ins window and then click Go… to view the list of
enabled COM Add-Ins.
4.  If required, enable the Microsoft Office PowerPivot for Excel 2013, Microsoft Power Map, Microsoft Power Query for Excel, and Power View add-ins
5.  Click OK.  




Create a Power Query Connection to a Microsoft Dynamics CRM Online Organisation

Assuming that you have the correct version of Microsoft Excel with Power Query installed and enabled, and a Microsoft Dynamics CRM Online Organisation to connect to, complete the following steps to create a Power Query connection to your Microsoft Dynamics CRM Organisation: 

1.  Logon to your Microsoft Dynamics CRM Organisation.
2.  Navigate to SETTINGS, Customizations, Developer Resources.
3.  Locate and take note of, of copy, the Organisation Data Service URL located in the Service End Points section on the Developer Resources page. This is the OData (REST) protocol end-point.  You will need this URL for creating your Power Query Connection to your Microsoft Dynamics CRM Online Organisation. In my example the URL is:  
4.  Open Microsoft Excel.
5.  Select the POWER QUERY tab.
6.  Click From Other Sources and then select From OData Feed from the list of available External Data sources. 



7.  When prompted, enter the Organisation Data Service URL, previously located in your Microsoft Dynamics CRM Organisation, into the URL field and then click OK: 



8.  Select Organizational Account from the list of authentication methods displayed on the left-hand pane of the Access an OData Feed screen. 



9.  Click the Sign-In. 



10.  When prompted, sign-in to your Microsoft Dynamics CRM Organisation using your Microsoft Dynamics CRM Online User Name and Password and importantly ensure the Keep Me Signed In option is selected. 



11.  Click Save on the Access an OData Feed screen. 



12.  Power Query will not connect to your Microsoft Dynamics CRM Online Organisation and download a list of available Data Sets which will then be displayed in a Navigator pane in Microsoft Excel. 



The Data Source Setting which has been created is now available to any of your Microsoft Excel workbooks.

At any time you can use the Data Source Settings command on the POWER QUERY tab to view and manage your Data Source Settings.





At any time, you can re-access the Navigator Pane, which displays the available Data Sets for the selected Data Source, by clicking Recent Sources on the POWER QUERY tab then selecting your Microsoft Dynamics CRM Organisation’s Data Source from the list of Recent Data Sources.



In my next blog I will describe how to configure some Power Query Options, how to select the Data Sets (such as Account, Contact and Opportunity) you want to work with and how to create/configure the Relationships between the selected Data Sets.