The latest version of Excel contains a Get External Data menu option for linking the workbook to an OData data feed. Knowing that Dynamics CRM 2011 and 2013 both provide an OData endpoint, this would seem like a great option for retrieving data from a CRM Online based system for reporting purposes, since direct database connections aren’t available in the cloud. Unfortunately, if you try to enter the CRM Online OData URL as a feed link, you’ll get this error message:
“We can’t use the data from this feed.” Bummer. The reason behind the error messages is that Excel and PowerPivot have not been able to support the authentication mechanism required for accessing the CRM Online OData feed. There is a workaround that you could apply if you’ve got the CRM Outlook client installed on the same machine, as described in this video by Ed Martinez, but in general using PowerPivot for CRM Online reporting has been quite restricted.
Power BI December 2013 Update
Last summer Microsoft announced the preview program for Power BI, their cloud based business intelligence suite, containing tools for publishing reports in Office 365. The Power BI umbrella also covers client side components, including Power Pivot (now spelled as two separate words apparently) and Power Query, that bring new capabilities to Excel. To get an understanding of the features included or under development, I encourage you to watch this Power BI demo from WPC 2013, which definitely deserves the Coolest Tech Demo of 2013 trophy if you ask me.
The latest December 2013 Update for Power BI announcement mentions the following enhancements:
“Power Query can now connect to more data sources:
- Sybase IQ
- Exchange
- Dynamics CRM Online“
Hey, that last part sounds great! So, what does it mean in practice for us CRM people? Well, the Power BI Preview subscription still doesn’t provide the option to provision a CRM Online instance for the same Office 365 instance, so there’s not that much new things visible on the cloud side yet if you spin up a preview org. On the client side, however, there is now an updated version of Power Query Preview for Excel 2013 that gives us the possibility to finally connect to a CRM Online OData feed directly. Let’s explore that in more detail.
Connecting to CRM Online from Excel
First you’ll need to download the Power Query component and install it as an add-on to Excel 2013. Then you’ll need to acquire the OData endpoint URL for your CRM Online organization. You can grab this from the Settings – Customizations – Developer Resources menu as shown below.
Next we’ll open a new Excel workbook and navigate to the Power Query tab on the Ribbon. Under Get External Data, choose the From Other Sources – From OData Feed option.
Now we’ll paste in the OData URL that we copied from the Developer Resources page in CRM Online earlier.
If we haven’t authenticated against the Office 365 AD yet, we’ll be requested for credentials to gain access to the OData feed. Choose the Organizational account option, enter your username & password in the dialog and click OK. (Sorry, Windows Live ID based CRM Online organizations still on the CTP platform are not supported as far as I can see, so you’ll need to wait for your organization’s transition process to take place before accessing this feature.)
The moment of truth is upon us: can Excel now retrieve the CRM Online data through the OData feed with the authentication info we’ve provided? The answer is: yes, it can!
You should now see a list of your entities as data sets in the Navigator window. Hovering over an entity like account will show you a preview window of the records delivered through the OData feed. Once you select your entities, you’ll then be able to further edit them in the Workbook Queries section, to map relationships between the entities, insert custom columns and perform all sorts of magic on the data. Then you can of course use this data for building pivot tables, charts and any other visualization methods that the modern Excel 2013 client and all its Power BI add-ons provide.
Once Power BI becomes generally available sometime in the future, the reports created in Excel with data acquired through the CRM OData feeds can then be published into the intranet sites running on Office 365 SharePoint Online for end users to consume. This should provide a great new alternative for developing advanced reporting for a pure cloud based environment. For example, you could leverage the Power Map Excel add-on (previously known as GeoFlow) to create a map of your website visitor analytics data, collected via ClickDimensions, stored into CRM Online database and then published as an interactive report through SharePoint Online. See my previous blog post where I did just that (apart from the publishing part).
To learn more about Power BI, read the team’s blog post on the latest new features introduced during the December 2013 Update.
Leave a Reply