I was recently tasked with creating a Power BI dashboard based directly off Dynamics 365 data. We were in a bit of a rush and even though the performance isn’t great we only needed a few charts and it was only a POC. Using Power BI Desktop you can add a datasource and select Dynamics 365 form the list.
Then it prompts you for the OData service (notice the format it is asking for)
So you fill in your org as prompted, connect up as a user and away you go. You can build you dashboards/reports/datasets and refresh them to your hearts content.
Then when publish your report to the online Power BI service, it allows you to do it no problem. However, if you then try and refresh your dataset, you get an error,
Your data source can’t be refreshed because the credentials are invalid. Please update your credentials and try again.
OK you might say, I’ll re-enter my credentials. So you change the Authentication Method from the default Anonymous to OAuth2 Some times it will work, but you still get the error in sometimes.
After much searching I came across this TechNet article where it tells you that you must use the new web api in the format https://foo.api.crm.dynamics.com/api/data/v8.1 to connect to your org in PowerBI Desktop. Even though it prompts you for the older SOAP endpoints in the format https://bcrm4.dynamics.com/XRMServices/2011/OrganizationData.svc.
Using the new web api allows you to get at all your data and publish the reports out to PowerBI online and then you can successfully connect and refresh your data.
