Connecting to Dynamics CRM with Power BI

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:

Dynamics CRM Odata Feed URL

Dynamics CRM Odata Feed URL

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”:

Dynamics CRM with Power BI Connection

Power BI Dynamics CRM Connection

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:

Dynamics CRM Object List

Dynamics CRM Object List

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!

Cheers, James

The following two tabs change content below.
James is an experienced BI Professional who has been delivering solutions for clients for over a decade. He is an expert in Microsoft Business Intelligence, having written two books, presented at TechEd twice and runs a popular blog "BI Monkey".