Playing With CRM Data In Excel

In terms of Microsoft software, my second passion after CRM is Excel. I have used Excel to solve Maxwell’s equations, valuing the entire New York and Australian stock market for bargains and I use it to manage the household budget. Here are some graphs from the spreadsheet solving Maxwell’s equations.

Excel has come a long way since I put this spreadsheet together and the new BI tools make Excel ideal for analysing CRM data.

Giving Credit Where Credit Is Due

Firstly, I owe a big thanks to Jukka Niiranen for two posts in particular. They are here and here and are the essentials for connecting Excel to CRM. Thanks to Jukka, I now get Excel to talk to any Dynamics CRM implementation, regardless of deployment mode. What is annoying it I did not read these sooner, given he wrote them over six months ago. I will summarise the main takeaways of Jukka’s blogs below in regards to connecting Excel and Dynamics CRM and update them.

Closing the Loop

You may remember, a while ago I showed a way of getting Power View into CRM.  I tried replicating this for this blog post and I struggled. It seems the newer versions of SharePoint online do not like displaying files in an iframe which trips up my old trick. The dream of creating an Excel spreadsheet of CRM data, which can be refreshed, and have it displayed in a dashboard, is not quite possible for CRM Online but maybe soon.

***STOP PRESS*** Speaking to Mark Rettig of Microsoft, he reminded me of the trick to get the spreadsheet to work. Change the end of the URL to say “…=embedview”.

Connecting CRM to Excel

The obvious way to connect CRM data to Excel is via the CRM export. On practically any list of data in CRM you can export the data to Excel.

There are two main options for exporting data: static and dynamic. Static copies the data and dumps it into Excel. It works for any deployment method but to refresh the data, you have to export again, which is annoying. This is where the dynamic export comes in.

A dynamic export does not copy the data but rather creates a FETCH query in the Excel spreadsheet which retrieves the data when the Excel file is opened. The main drawback with this method is it needs the Outlook client to broker the connection. So what do you do if you do not have the Outlook client?

OData Connections

The next best option is the Organization Data Service. This provides an OData (REST) connection to the data in CRM and is one way developers chat to CRM. For those of us who cannot code, we access this via the web address at Settings-Customizations-Developer Resources.

You can stick this into a browser and it will return a list of CRM tables. You can even add a table to the end of the URL, say AccountSet, and you will get an RSS feed of the Accounts in CRM.

Fortunately, Excel understands OData, so all we have to do give Excel this address, a login and password and we have a dynamic connection.

Out of the Box Excel OData Connections

For Excel 2013, in the Data tab of the Ribbon, in the drop-down for ‘From Other Sources’ is an OData option.

Selecting the OData option asks for the URL, login and password, as expected.

This works for on-premise IFD implementations but consistently fails for CRM Online.

Apparently, Excel’s standard OData connection does not support passing through the Live ID or Office 365 credentials. Until Jukka’s articles, this had me stumped.

Excel’s Power Query

There are a whole bunch of new and exciting add-ons for Excel. One of these is Power Query, which can be downloaded here.

Power Query provides a whole range of new data query options like Online Search (which lets you search online data sources), MyData database source and, of course, OData in the ‘From Other Sources’ option.

Unlike the out-of-the-box OData connection, this one plays nicely with CRM Online.

What To Do With the Data?

Out of the Box, Excel 2013 comes with Power View, allowing you to display and manipulate your data in new ways (image care of Jukka).

Another add-on from Microsoft, currently in ‘Preview’ is Power Map, downloadable here (previously called Geo Flow). With Power Map, you can take data with address fields and Excel will use Bing Maps to plot your data. In this image, I take a few thousand account records from CRM, using the OData connection, plot their location and color-code them based on their industry.

The tool also allows you to create video fly-overs of your data for presentations and add soundtracks and commentary. There is also a layering option so you can overlay different kinds of data and compare them.

Conclusions

Microsoft is investing a lot of money into the Excel BI tools so, if you have a CRM system, and want to gain insights from the collected data, Excel is a good place to start. In the past it was difficult to get Excel to work with CRM Online but, this seems to be a thing of the past and, with tools like Power Map, it is possible to present and explore data in new ways. I strongly recommend upgrading to Excel 2013 and checking out what is available.