There are some great new features available in Excel 2013 that can take your Dynamics CRM data visualization onto a whole new level without the need to invest in new server infrastructure or build traditional OLAP cubes on your SQL Server. With the recent announcement of the GeoFlow Preview for Excel 2013, I decided to see what I could make of this tool when combined with some data pulled from CRM.
Working with Cloud Data
Although the feature parity of CRM Online and Dynamics CRM on-premises is on quite a high level these days in terms of the application platform capabilities, one unfortunate limitation has been that you can’t easily tap into the CRM Online data with analytical applications like Excel PowerPivot. Naturally the direct SQL database table connection is unavailable, but also the OData feeds that would be such a neat way for performing some pivot magic have been off limits, as PowerPivot doesn’t support the claims based authentication of CRM Online. Oh, and as far as I know, this also applies to an IFD configured on-premises server (if anyone knows how to connect to the OData feed with AD authentication while using an IFD CRM, do leave a comment below).
With the new capabilities of Excel 2013, there is now a workaround available that allows you to access OData feeds from CRM that require Windows Live ID / Microsoft Account / Office 365 authentication. You’ll also need the CRM Outlook client on your workstation to facilitate the initial connection, although with another workaround for the sign-in prompt on the Excel sheet you might be able to do without one.
Here’s a great video from former Dynamics CRM Team member Ed Martinez that walks you through the steps needed in order to connect your Excel 2013 (note: Excel 2010 is not supported AFAIK) to a Dynamics CRM Online OData feed:
As a summary, what you need to do is:
- Copy the OData feed address from the developer resources menu of your CRM
- Export any set of data from CRM to a dynamic Excel sheet
- Open the file and refresh the data (thus establishing the authenticated connection), the save it in .xlsx format
- Add a new data source by clicking on “Get External Data, From OData Data Feed”
- Paste in the address you previously copied from CRM
That’s it. Now you can connect an Excel sheet to a CRM Online (or IFD) data source through OData and refresh the content to reflect the live data as your CRM source system gets updated.
Power View in Action
For my own lil’ “Big Data” scenario I decided to point my OData enabled Excel 2013 towards a Dynamics CRM instance where ClickDimensions is storing the website visitor data from this blog (sorry, you’re being tracked, but it’s all for a noble cause, trust me). I proceeded by following the instructions above, adding a new OData data source into a dynamic Excel sheet, selecting the entities (“sets”) that I wanted to use in my analysis. Then I waited… and waited… and waited some more.
A word of warning: OData data feeds can be slow. It’s nothing like using a direct database connection, and not even the type of performance you get with a dynamic Excel in an IFD environment that uses an Excel Web Query to “screen scrape” the data over http. My data set was retrieving around 100,000 records and I saw the counter ticking on the bottom right corner of the Excel sheet at a rate of roughly 200 rows per second, which translates into some 10 minutes of waiting before the download from CRM is complete. Oh well, it’s not like we’re in a rush to create a real life report for the boss who wants to have it in his inbox within 15 minutes. Let’s be grateful that we can at least retrieve more rows in one go than the default MaxRecordsForExportToExcel setting for this CRM organization would otherwise allow (which is 10,000). [Read more…]