Home > Dynamics CRM > Visualize Live CRM Data Using Power View for Microsoft Excel 2013

Visualize Live CRM Data Using Power View for Microsoft Excel 2013

The ability to efficiently access and break down data allows businesses to more effectively strategize and implement successful courses of action. Open Data Protocol, also known as OData, is a web protocol for querying and updating data, applying web technologies to provide access to information from a variety of different data sources.

Microsoft Excel 2013 offers a new data source called OData Data Feed. This enables you to import live data feeds from various data sources, including free and subscription-based data sources available on Windows Azure Marketplace. You can then use these live data feeds in Excel for analysis and reporting.

Microsoft Excel 2013 allows users to import live data feeds from different sources into the application for analysis and reporting purposes

Microsoft Excel 2013 allows users to import live data feeds from different sources into the application for analysis and reporting purposes.

Interestingly, Microsoft Dynamics CRM 2011 also offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol. You can find the service endpoint for OData service by navigating to Settings > Customizations > Developer Resources.

Microsoft Dynamics CRM offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol

Microsoft Dynamics CRM offers an OData data feed that can be used to retrieve live CRM data into any application using OData protocol.

Using a combination of these features, you can now create a live connection to your Dynamics CRM data and generate interesting reports and dashboards using Excel.

For connecting to Dynamics CRM OData service, follow these steps.

In Excel 2013, click Data > From Other Sources > From OData Data Feed. Enter the service endpoint link above and click “Next”.

The Data Connection Wizard presents the list of data sets available in Dynamics CRM

The Data Connection Wizard presents the list of data sets available in Dynamics CRM. For the purpose of this article, we will select AccountSet (Accounts) and OpportunitySet (Opportunities).

Excel allows you to use this data to populate a Table, PivotTable Report, PivotChart or Power View Report

Excel allows you to use this data to populate a Table, PivotTable Report, PivotChart or Power View Report. We will select Power View.

When you click OK, Excel  retrieves the data from Dynamics CRM and populates the data in PowerPivot Data  Model

When you click OK, Excel retrieves the data from Dynamics CRM and populates the data in PowerPivot Data Model. This can take some time depending on the volume of data being retrieved. There are tools available to limit the data by using the OData query instead of retrieving the full data set. You can view the PowerPivot Data Model by clicking on PowerPivot > Manage Data Model.

The following screenshot shows the data model in the diagram view. Excel does not automatically create the relationship between the tables. However, you can do this manually using the PowerPivot features. This is important when you want to use data from multiple tables in the PivotTable or Power View report.

Once the data model is  populated, the data is available for reporting in Excel

Once the data model is populated, the data is available for reporting in Excel.

The first Power View report shows the Open Opportunities by Account Chart and the Opportunities table. Clicking on the specific bar in the chart automatically filters the Opportunities table based on the relationship we defined earlier.

The second Power View report shows the Opportunities on the map

The second Power View report shows the Opportunities on the map. Maps in Power View display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. Clicking on a dot on the map automatically filters the opportunities in the table below. The size of the dot represents the estimated value of the opportunity.

Because the reports use the data feed from Dynamics CRM, they can be refreshed any time to see the current view of the data

Because the reports use the data feed from Dynamics CRM, they can be refreshed any time to see the current view of the data.

OData helps businesses access valuable information in various applications and thus put that data to good use. In this article, we reviewed how we can take advantage of OData data source and Power View to better analyze live Dynamics CRM data in Microsoft Excel 2013. 

Abi Shende is a Team Lead in Microsoft Dynamics CRM at Ignify. Ignify is a technology provider of ERP, CRM, and eCommerce software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011.

  1. No comments yet.
  1. No trackbacks yet.