Power BI Microsoft Dynamics CRM 2013 – Part 1

Colin Maitland, 14 October 2014

In this series of blogs I will provide a simple overview of how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation and how to then use some of the Power BI tools in Microsoft Excel, such as Power Query, Power View, PowerPivot and Power Map, to create nice looking, interactive and informative reports. 

The following screenshots show examples of the types of reports and visualisations that can be created. 

This first screenshot shows an example of a Power View Report in Microsoft Excel. This report displays several tables and charts which work together interactively to provide an analysis of Opportunities in Microsoft Dynamics CRM Online.

 Interactive features of this report include the following: 

•  Clicking a column heading on a table sorts the table by the selected column.
•  Selecting a table or chart and using any of the filters on the Table or Chart area on the Filters pane filters that table or chart independently from all other tables and charts in the report.
•  Moving the mouse over a chart displays the Chart Command Bar which displays controls for sorting, filtering and expanding (popping the chart out to full screen de).
•  Clicking the Pop-Out and Pop-In control displayed when moving the mouse over the top right-hand corner of any table or chart displays the selected table or chart in full screen mode or returns you to the Power View report.
•  Clicking the <, > or X controls on the Filters pane expands, collapses or closes the Filters pane.
•  Clicking any category related bar, column, segment or area of chart automatically filters the related tables and charts in the report by the selected category. 

This next screenshot shows an example of a PowerPivot Report in Microsoft Excel. PowerPivot Reports may display the following table and chart combinations: 

In a Power View Report, the table and the chart(s) are related to each other by a single filter control. The filter control, table and chart in the Power View Report at interactive. 

This final screenshot shows an example of a Power Map Report in Microsoft Excel. This particular example shows three layers. Layer 1 shows Opportunities Estimated Revenue x Actual Revenue as a Bubbles, Layer 2 shows Revenue by Account as Columns and Layer 3 shows No of Employees as Heat Maps. Each layer is mapped to the City of the corresponding Account. The map is interactive and can be zoomed, rotated and time-lapsed.

In my next blog I will describe the requirements for Microsoft Excel the add-ins that are required and how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation.