Related sites:

Newsletter: Perspectives on Power Platform

Company: Niiranen Advisory Oy

Connecting to CRM Online OData feed with Excel 2013 Power Query

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.

Tags

Related Posts

35 Comments

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

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

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

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

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

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

  8. 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!

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

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

  15. Jukka – after some trial and error and also reading how Hugo got his to work, I have the connection working (yay!) but not all entities seem to be coming over. For example, I see OrderClose but not Order. And our couple of custom entities don’t seem to either. It may just be I’ve got to work out how best to work with this but I feel like I’ve taken the top off a box (after some effort) and not quite sure what I’m looking at.

    • RSA, are you sure you are searching for the entities based on their schema names and not display names? For example, “Order” is “salesorder” in the CRM database…

  16. Thank you Jukka for the quick response – that was it!! For example, custom entities schema names start with “new”. Now it’s a case of exploring this cool new world! Previously I was using the Outlook Connector to bring the CRM data into Excel via exporting queries in CRM as dynamic worksheets but this method is going to be MUCH better once I totally work out what it’s doing.

  17. I am having issues establishing a data connection in Excel – for the ultimate goal of publishing a Pivot Graph on the Power BI home page. I have successfully pulled up the Navigator for the entire portal, but even when I attempt to select the OMS data from the sublist (Rather than the specific URL itself), I receive the error again. WHAT am I missing!!

    I keep receiving the following error message-
    DataFormat.Error: OData: The given URL neither points to an OData service or a feed: “https://concierge1.sharepoint.com/sites/salesportal/OMS/Lists?OMS?All Shaded.aspx’.

    • Caitlin, is your OData feed pointing to a CRM Online instance? From what I know, these type of data sources can’t currently be used in the Power BI portal. I haven’t experimented with any other types of OData feeds yet, so not sure whether your scenario should be supported or not, but I’d be very careful in reading through the announcements in the Power BI team’s blog to check what should be working today and what may still be on the roadmap.

  18. I am receiving an error, “The given URL neither points to an OData service or feed; any troubleshooting suggestions?

  19. Hi Jukka,

    Following up on the IFD issue submitted by Fabrizio and Pekka (because now it’s my issue too 😉 )

    Have you had a chance to check if Power Query connection to a On Premise / IFD CRM 2013 requires something more recent than ADFS 2.0? I’m stuck with ADFS 2.0 on 2008 R2 and it would be quite a business case to get IT to upgrade the server based on a hunch. If you have more definitive info on the requirements to get Power Query to work On Prem that would be great!

    • L-A, from what I’ve seen, I don’t think Power Query yet supports the type of authentication needed for connecting to an on-premises Dynamics CRM OData feed. Organizational account authentication only works with Office 365, the other available options in Power Query won’t allow you to access CRM OData feeds. The only workaround I know is the old trick of using CRM Outlook client for providing the necessary authentication.

      Since on-prem support would need enhancements to the Power Query features, I recommend you to go to the Power BI Support site and vote on the item “On premise CRM Dynamics”. Perhaps even adding comments on what new scenarios this on-premises OData feed authentication support could unlock.

  20. Thanks Jukka, I just added 3 votes to the item and will get my team to pile on more votes 🙂

  21. Jukka,

    For some reason I didn’t get an email letting me know about your response to me there, sorry about that. However I just thought I’d let you know MS did get involved and resolved the problem. It ended up being a syncing issue on the server end for me.

    Thanks!

  22. Alicia, was your CRM an On Premise/IFD installation or a CRM Online instance?

  23. I implemented Power Query 2013 into CRM Online 2015 without a hitch last weekend. Working like a dream and SO much better than exporting as a Dynamic Excel Sheet/Pivot Table.

  24. For On-Premises CRM, there is an update to Power Query:
    https://support.office.com/en-ie/article/Whats-new-in-Power-Query-936b2fca-4168-41ad-bbbd-7b83856b6776

    “Support for Custom ADFS Authentication Services – with this update you can use custom ADFS authentication endpoints through our Organizational Account credential type, enabling access to data sources that require ADFS authentication, such as some on-premise instances of Dynamics CRM.”

    However, you need the appropriate admin rights to set the approuved AFDS authentication services.

  26. Has anyone seen the message “The service returned a state value that does not match the value that was sent. This might indicate cross-site request forgery”.

    This is CRM Online 2015
    Connecting from Excel 2013 – 64-Bit
    I have tried signing in in PowerQuery and not signed in. If not signed in – I am not prompted for credentials. If I am signed into PowerQuery, I am using the Office365 credentials to sign in.

    I have also signed into Excel 2013 as the Office365 account.

    I have even tried to setup a new machine account with the Office365 credentials (userid) to see if it will mimic.

    All with this same message – nothing on a search for the message.

    Thanks!

    Peter

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.