PowerBI in Office 365 can be a really useful tool, however many users may have come up against an issue with using the MS Dynamics CRM Online OData Service to “Get Data”.
You can easily register to use Power BI for your Office 365 tenant and download PowerBI Desktop, however it doesn’t appear to be as easy to use the “Get Data” function or register CRM as a data source using the OData Service for CRM Online.
If you a looking for a walkthrough on the set up of PowerBI, then this blog post from Steve Ivie is a useful place to start; https://www.packtpub.com/books/content/getting-dynamics-crm-2015-data-power-bi
If like me you attempt to connect to CRM using the OData Service URL found in CRM Online then you might have used the following steps;
- Download and Open Power BI, from the pop-up menu select Get Data
- Select Dynamics CRM Online for the list and then click Connect
- You are then prompted for the OData Service URL in the following dialog
- Now you would expect to enter the URL for the OData Service from CRM Online right? The format should be something like this; https://<your org>.crm5.dynamics.com/XRMServices/2011/OrganizationData.svcPlease Note: the URL ‘crm5’ section will vary depending on your datacentre. To find the OData Service URL navigate to; Settings > Customizations > Developer Resources in CRM.
- Once you have entered the URL click OK, the organisations available data sets should begin to load
- In the Navigator menu select a data set or data sets from the CRM list and then click Load, (in the example AccountSet and ContactSet have been selected)
- This is where the frustration starts! The load process is triggered and I receive the following message; OLE DB or ODBC error: [DataSource NotFound] OData Request failed (404) Not Found.At this point you cannot apply any changes and you have not data to work with. Close the message box.
The Work Around*
Now, I researched the problem and couldn’t find a resolution for CRM 2015 Online, however if you have Microsoft Dynamics CRM 2016 Online your PowerBi dreams are but a URL away for fulfillment – *note I didn’t call this a “solution”, I will take answers on a postcard (or comment) from anyone who managed to get the OData Service working.
- In CRM Online 2016 navigate to; Settings > Customizations > Developer Resources
- Grab the Instance Web API from the field;
- Repeat steps 1-3 from the previous section
- Instead of the OData URL, enter the Instance Web API from CRM Online and click OK
- Once you have connected to CRM select the entity tables you want to work with in the Navigator menu and click Load
- The data sets should now load and the glorious world of CRM with PowerBI is yours to explore at last!
Hopefully this will save some people from trawling support threads, if there is a better way or a way you have managed to use the OData Service for CRM Online 2015 or 2016 please get in touch!
Happy CRM’ing for now. 🙂