Connecting to Dynamics CRM with Power BI
If you are like us and use Microsoft Dynamics CRM to manage your customer data, you are also probably like us and want to analyse that data by connecting to Dynamics CRM with Power BI. For a first play there is a Dynamics CRM Power BI content pack. However content packs are limited in function (by design) and locked down so you can’t edit them. If you want your own custom metrics or access to your own custom fields then you need to connect directly. This quick start guide takes you on the first step.
Get the OData Service URL
Fortunately this isn’t too hard. Dynamics exposes its data through an OData service so you can pull all the data you could possibly want from your CRM instance. Your first step is to find your Odata Service Url, which can be found in Dynamics under Settings – Customizations – Developer Resources:
Connecting to Power BI
Once you have this URL, you can then connect to it in Power BI. Choose Get Data and under Other, select “Dynamics CRM Online”:
Chose Connect, and you will be prompted for the OData Service URL we picked up earlier. Enter that and click OK. It will then take a while collecting all the available queryable objects, then present you with a list of objects:
Then from the list, pick the object(s) you want – here I’ve picked the OpportunitySet – choose “Load” and the data will get pulled into Power BI to model as you wish!
Now of course it’s not that simple and we will explain more in later posts about how to improve performance, manage relationships and other tweaks!
Latest posts by James Beresford (see all)
- State of the Self Service BI market January 2017 - 19 Jan 2017
- Power BI Embedded Row Level Security available - 10 Jan 2017
- Healthcare Analytics at the Spinal Cord Injury Network Thought Leadership Forum - 16 Nov 2016