Connecting to Dynamics CRM Online with Power BI

Power BI provides the ability to connect to a number of online services and one of these is Dynamics CRM Online. This is how you connect to Dynamics Online once you have logged into Power BI.

Connect to the Service

At the bottom left, you’ll see a button for “Get Data”.  and clicking that reveals a number of different methods for connecting to datasets.  Dynamics CRM Online is under services.

Click on the Microsofy Dynamics CRM and then click on Connect and you’ll see a dialog box that looks like this:

The URL for your service will be your tenant URL + “/XRMServices/2011/OrganizationData.svc”.

Logging In

The next dialog box will prompt for authentication.  Assuming you have the normal authentication set up, the best select here is oAuth2 which will redirect you to Microsoft’s single sign on experience to log you into Dynamics CRM Online.

Once you have logged in successfully, Power BI will synchronize with your Dynamics CRM Online repository and provide a dataset representing your CRM data.

The Default CRM Dashboard

One of the cool things is that Power BI doesn’t just load up a raw dataset – each service has a set of default dashboards that are in context with the service.

For CRM, you get dashboards for:

  • Revenue (last 12 months)
  • Revenue (last 3 months)
  • Open Revenue (closing next 90 days)
  • New Opportunities Trend (by month, year, rating)
  • New Leads (last 90 days)
  • Win Rate (last 12 months)
  • Open Opportunities (closing next 90 days)
  • Sales Pipeline (by sales stage)
  • Leads (by lead source)
  • Lead Conversion Rate (last 90 days)
  • Average Deal Size (last 12 months)
  • Revenue (by owner)
  • Open Revenue (by owner)
  • Average Time to Close (in days)
  • Revenue Trend
  • Deal Size, Deal Age, Revenue
  • Top 10 Accounts (by revenue)
  • Win Rate, Win Count Trend
  • Activity Trend
  • Revenue by Region/Country
  • Top Open Opportunities
  • Deal Size, Deal Age Trend

Accessing it from your Phone

Microsoft has an IOS, Windows Phone and Android app for viewing your dashboards.  This works well with Dynamics CRM data for getting access to your vital data at a glance while on the road!

Manipulating your Dashboard

There are a number of easy things you can do to configure your Dynamics CRM dashboard.

  • You can drag and drop the individual dashboard widgets to change their order.
  • If you click on the widgets, they will drill down into more detailed reports.
  • Create your own new sales reports

Here is a new dashboard I created show accounts sorted by revenue.

Christopher Woodill

About ME

Enterprise technology leader for the past 15+ years…certified PMP, Six Sigma Black Belt and TOGAF Enterprise Architect. I collaborate with companies to help align their strategic objectives with concrete implementable technology strategies. I am Vice President, Enterprise Solutions for Klick Health.

Leave a Comment