Connecting to CRM Online OData feed with Excel 2013 Power Query

16 comments

Posted on 15th December 2013 by Jukka Niiranen in Tips

, , , , , , , , ,

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:

CRM_OData_feed_Excel_error

“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.

PowerBI

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.

CRM_OData_feed_address

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.

CRM_OData_feed_Excel_Power_Query_1

Now we’ll paste in the OData URL that we copied from the Developer Resources page in CRM Online earlier.

CRM_OData_feed_Excel_Power_Query_2

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.)

CRM_OData_feed_Excel_Power_Query_3

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!

CRM_OData_feed_Excel_Power_Query_4

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.

CRM_OData_feed_Excel_Power_Query_5

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.

16 Comments
  1. Christophe says:

    As usual, a very useful post. And exactly what I have to work this week :)
    Thank you Jukka !

    16th December 2013 at 11:52

  2. fabrizio dal passo says:

    Hello!
    Is it supposed to work also with onpremise thru IFD?

    I can’t get it working

    18th December 2013 at 12:14

  3. Jukka Niiranen says:

    That’s a good question. None of the available documentation speaks of anything else than Power BI / Power Query supporting CRM Online specifically. There might be some new requirements on the version of ADFS used in the IFD setup, for example. Have you tried it with the latest Windows Server R2 & ADFS 2.2? I assume the CRM Online platform would be running the latest released version whereas many on-premises environments probably are still on ADFS 2.1.

    19th December 2013 at 18:57

  4. fabrizio dal passo says:

    Thank you Jukka, I’m using win 2k8 r2 + ADFS 2.0, so you’re probably right. It’s quite strange that there are no documentation about the requirements, though

    19th December 2013 at 19:05

  5. Pat Beautz says:

    Power BI is going to be a great add-on to Office 365 including CRM Online. Unfortunately I wasn’t able to get your steps to work. After trying to sign into my organizational account, I get an error message, “unable to connect either the credential type is not supported for this resource or you are not authorized”. I understand that the server side of Power BI is not available but I would have thought that the odata connection to my CRM Online would have worked without a hitch. I’ve even tried two different PC’s to rule hardware out of the equation.

    Are there any other settings or permissions to get this to work?

    Thanks!

    Pat

    11th January 2014 at 21:47

  6. Jukka Niiranen says:

    Pat, have you downloaded the Microsoft Power Query Preview for Excel? It’s very important to have the Preview version rather than the normal Power Query version, because only the Preview contains the necessary components for connecting to CRM Online OData feed (see this tweet).

    11th January 2014 at 22:38

  7. Pat Beautz says:

    Jukka,

    Yes I’m using the December release. Funny enough, when I use an odata connection URL from another CRM article (http://blogs.msdn.com/b/powerbi/archive/2013/12/19/dynamics-crm-online-in-power-query.aspx ), it works and presents me with the organizational login page. This article uses an odata connection domain of “Microsoft665″.

    For some reason my CRM Online domain doesn’t (domain = bipartisan). I’m checking with Office 365 support as well.

    Let me know what happens when you try creating an odata connection to my domain. Are you able to get to the organizational login page or do you error out before that?

    Thanks,

    Pat

    13th January 2014 at 01:52

  8. HExcelligent.fi says:

    […] Jukka Niiranen kirjoitti joulukuussa CRM Online datan tuonnista Power Queryllä: Connecting to CRM Online OData feed with Excel 2013 Power Query […]

    21st January 2014 at 10:34

  9. Hugo says:

    Hi Jukka, Excellent article! Thanks!
    My problem is that I cannot find the PoweBI december update in the link you posted (apparently was removed), so I downloaded the last update (31 jan 2014 – version: 2.10.3547.461), but I get this error: “DataFormat.Error: OData: The given URL neither points to an OData service or a feed”.

    I tried three different CRM Online instances with no success.
    Any suggestion?

    Appreciate and thanks, Hugo.

    11th February 2014 at 13:18

  10. Jukka Niiranen says:

    Hugo, I tested the 2.10.3547.461 version of Power Query on a fresh new CRM Online trial subscription that had also a Power BI trial included in it. I was able to access the CRM Online OData feed from it, although initially there appeared to be a login issue that kept on prompting me for credentials. After I signed out of the Organization account from Power Query’s ribbon, then accessed the OData feed with the same credentials, the data is flowing from CRM Online to Excel.

    Are the CRM Online instances upgraded to CRM 2013 level and running with Office 365 authentication instead of Windows Live ID? I believe both are requirements for accessing the OData feed from Power Query, although neither limitation appears to be explicitly stated in Microsoft’s materials.

    11th February 2014 at 18:26

  11. Hugo says:

    Hello Jukka! You are right. Apparently there was a (temporal?) login issue. I deleted cookies in IE and data sources in Excel’s Power Query and now all works like a charm!
    Thanks!

    11th February 2014 at 18:42

  12. Pat Beautz says:

    Jukka,

    I was able to resolve my issue with Microsoft support. It turns out Power Query only works with CRM Online 2013. When I was originally testing, we were still on CRM Online 2011.

    Also, even after being on CRM Online 2013, I hit the same problem that Hugo experienced as well. After a quick deletion of my IE cookies, I was able to get in.

    Although Power BI should be a fun tool to play around with, hopefully they will release Power BI for Office 365 soon as well.

    Thanks again for the article and support!

    Pat

    12th February 2014 at 16:34

  13. Dynamics CRM OData Feeds and Power Query: What’s the [Record]? - Surviving CRM - Microsoft Dynamics CRM - Microsoft Dynamics Community says:

    […] subscription for it and get familiar with the tools that it offers. My previous experiment with connecting to CRM Online OData feed with Excel 2013 Power Query seemed to be one of the very few blog posts that come up when you search for information on the […]

    16th February 2014 at 22:20

  14. Working with Price List Items in Dynamics CRM | Surviving CRM says:

    […] has recently been expanded to support connecting to CRM Online OData feeds. The method outlined in this blog post would therefore allow you to replicate the information of the aforementioned pivot table via an […]

    31st March 2014 at 15:03

  15. Pekka Sahlsten says:

    Has anybody been able to get PowerBI/Query working in an IFD configuration? Does fro instance updating ADFS to 2.2 help?

    15th April 2014 at 16:35

  16. Enable Dynamics CRM oData Query Filter for PowerQuery Performance | Work and Study book - Dynamics CRM Blog says:

    […] and Excel self-service BI for Dynamics CRM, which has been discussed by CRM MVP Jukka Niiranen on: http://niiranen.eu/crm/2013/12/connecting-crm-online-odata-feed-excel-2013-power-query/ and PowerBI’s blog: […]

    11th July 2014 at 10:11

Leave a comment