How to Connect Power BI to Microsoft Dynamics CRM Online

Here is a quick tutorial on connecting Power BI to Microsoft Dynamics CRM Online. This will get you up and running quickly to create simple dashboards in Power BI using your Dynamics CRM data.

First, if you don’t already, download Power BI Desktop here.
If you want to publish and be able to share your dashboard, sign up for a free Power BI account here.

Finding the CRM Connection URL

Before you begin, find your CRM connection URL.

1. Log in your CRM instance, and go to Settings.

Connecting Power BI and CRM Photo 1

2. Click Developer Resources.

Connecting Power BI and CRM Photo 2

3. At the bottom of this page, there is an Organization Data Service URL. This is the URL you want to use in the URL field after selecting Get Data in Power BI.

Connecting Power BI and CRM Photo 3

Connecting Power BI to Microsoft Dynamics CRM

Now let’s open Power BI Desktop and connect to Microsoft Dynamics CRM.

1. On the main PBI screen, select Get Data

2. Then select More…

Connecting Power BI and CRM Photo 4

3. In the Get Data form that pops up, scroll down and select Dynamics CRM Online. Note, you can also select this field if you are using CRM on-premises.

Connecting Power BI and CRM Photo 5

4. Paste the URL as found in the first section of this document, then click OK.

Connecting Power BI and CRM Photo 6

5. In the ‘Access an OData feed’ popup, first select Organizational account.

6. Then click the picklist and select the Organizational URL; then click sign in and enter your CRM login credentials.

7. Once you have successfully logged in, click Connect.

Connecting Power BI and CRM Photo 7

8. You should now see the Navigator popup form. We are going to keep it simple and start with reporting on the Opportunity table. In the search field, Type OpportunitySet.

9. Click/check the small box next to Opportunityset.

10. We don’t need every field from the Opportunity Entity, so we are going to narrow down the fields. Click Edit to choose the fields.

Connecting Power BI and CRM Photo 8

11. The Query Editor will open. Now we are going to select our fields for reporting. Click the Choose Columns button.

12. I recommend turning off the checkboxes in the Choose Columns popup. Click the Select All Columns field and make are the checkboxes are removed.

Connecting Power BI and CRM Photo 9

13. Now we are going to select the fields we want for reporting. In the Search Columns box, type in following fields names and check the box next to them. Below is an example. Keep in mind you want to select fields you actually use. If you have custom fields, go ahead and pick those as we did in the step above. Once all your fields are selected, click OK.

  • ActualCloseDate
  • CustomerId Name
  • ActualValue
  • EstimatedCloseDate
  • OwnerId
  • CloseProbability
  • EstimatedValue
  • StatusCode
  • StepName

14. Give the fields more user friendly names. To rename the fields, double-click in the header and type in the new name. You can name them anything you want. The table below is what I typically use.

Connecting Power BI and CRM Photo 10

  • ActualCloseDate – Actual Close Data
  • CustomerId Name – Oppt. Name
  • ActualValue – Actual Revenue
  • EstimatedCloseDate – Est. Close Date
  • OwnerId – Rep
  • CloseProbability – Probability
  • EstimatedValue – Estimated Revenue
  • StatusCode – Status
  • StepName – Stage

15. Update the columns that show ‘Record’. Technically this is a field that contains multiple values, so, we need to expand the column and select the value we are interested in. I’ll use the CustomerId field as an example. Click the icon in the header to see all the values available.

Connecting Power BI and CRM Photo 11

16. Uncheck the columns that you do not want. In this case, we only want the Id and Name field. Click OK

Notice that it “added” two new fields/columns. CustomerId.Id and CustomerId.Name; go ahead and rename these fields to CustomerId and Company. Don’t; forget to do this to all columns that have Record in it.

Connecting Power BI and CRM Photo 12

17. We are ready to load the data. Click the Close & Apply button.

Connecting Power BI and CRM Photo 13

18. Let’s start creating visualizations. Notice the fields we added are on the far right. We can simple click the checkbox or drag and drop them over the blank canvas. Start with checking Estimated Revenue and Stage. Tada, you just made a chart of Estimated Revenue by Stage. Feel free to create more visualizations. Pick different chart types and check out the selections under the paintbrush icon under the visuals buttons. Click the Save button and save your work.

Connecting Power BI and CRM Photo 14

19. Publish to the Power BI Portal. After you save your work, click the Publish button. If you previously signed into the Power BI portal, you should see a success message. Click the link in the message that says “Open ‘filename.pbix’ in Power BI. If you have not logged in, you will get prompted to do so; enter the credentials you used to create an account.

Connecting Power BI and CRM Photo 15

20. You should see your Dashboard in the Power BI portal. At this point, the dashboard is considered a Report in Power BI. In order to share your work with others, we have to Pin a visual to an existing Dashboard or create a new one. We can also Pin the entire page to a Dashboard by clicking the Pin Live Page button (go ahead and do this). Select New Dashboard and give it a name, then click Pin live.

Connecting Power BI and CRM Photo 16

21. If you want to share it with others, click the Share button in the upper right of the page. Type in their email and click the gold Share button. They will receive a link to your dashboard. If the user does not have a Power BI account, they will be prompted to create one before they can view the dashboard.

Connecting Power BI and CRM Photo 17

Voila!  You should be good to go.

While Power BI is designed to make the hard things easy, we understand that even simple tasks sometimes require time that you may not have.  If you need an extra pair of hands or help wrapping your head around the infinite number of possibilities that Power BI offers,  contact us.  We’d be glad to help you harness the Power of BI to unite your data and share new insights from Finance to Sales to Operations (and more).

Related Content

Recorded Webinar: 6 Things You Should Know About Power BI & Key Takeaways from Microsoft’s 2016 Data Insights Summit

Power BI Designer – Who is it for?

The Power BI Q&A Tool

If you’re using CRM as a platform for deploying line of business applications, make sure you’re getting the most from your investment by reading CRM Governance: What It Is, What It Isn’t, and How to Do It Right, an informative eBook written by governance experts.

By | 2017-10-20T20:24:13+00:00 April 15th, 2016|Sales & Service (CRM), Tech Tips|4 Comments
Alternative Text

Contributor: Michael Hammons

In his role as Director of Customer Experience, Mike leads initiatives to ensure a consistent, positive experience for every customer, from setting expectations in the first meeting through ensuring satisfaction upon project completion and beyond. In addition, His goal is to help customers identify ways to leverage their existing technology investments to address new initiatives and make continuous process improvements.

4 Comments

  1. Jasbir Singh June 1, 2018 at 7:59 am - Reply

    Thanks Michael. How can I get the report for week to week progress of sales opportunity in Power BI?

    • Taryn Murphy July 26, 2018 at 7:25 pm - Reply

      Hello Jasbir,

      If I understand your question correctly, you want to see changes that happen on sales opportunities from week to week. E.g. how many opportunities and what value were they for the week of 7/09 compared to what they were for the week of 7/16. From there I can add measures to show percentage change week over week, value changes, whether they are increasing/decreasing, etc.

      If that is the case, the challenge is a need for accessing historical data. Out of the box, when a user changes data they are updating the existing data and there is no historical context of what was changed and when. What we really need is what I typically call a “snapshot” table. This table takes a picture (or snapshot) of the data as it was during each week. We then use the snapshot tables for comparing the changes over time.

      One may think, maybe we can enable auditing and track changes that way. Sounds good in theory, but is much more challenging in reality. After turning on auditing, we have found there are so many changes to filter through and the way it is stored in the audit tables it’s just not worth the headache…and performance takes a big hit over time.

      One option is to create a new “snapshot” entity and write a plugin to write the desired opportunity table to this new entity. E.g. Every Saturday night, copy the Account, Opportunity, Est Value, Stage, Probability, Owner, etc. fields to the snapshot table. We have done this for several customers and it works pretty well.

      The best long term and higher performance option is to setup Data Export Service which replicates the CRM database to Azure SQL.

      Here is a link to setting up the Data Export Service. https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/replicate-data-microsoft-azure-sql-database

      Using this approach, you are setting up a reporting database. When data is changed/updated/deleted in CRM, the service automatically updates the reporting database; typically within seconds, and worst case minutes. This reporting database provides direct access to SQL Server which is why the performance is so much higher; plus, you also get the added benefit of using SQL Server Management Studio to create views, stored procedures, and other SQL specific features that can help with reporting. And this is where we would write a Store Procedure to create the snapshot table on whatever data we wanted to do historical reporting on.

      I hope this helps.

      Best Regards,

      Mike

  2. Rajul Shah June 18, 2018 at 7:29 am - Reply

    Hello,

    I am facing an issue since a long time. While fetching data of SystemUsers through Dynamics CRM Online, it takes hours to load the data. Even though, after hours data of SystemUsers aren’t loaded in my Power BI Desktop file. Please guide us how can we avoid this situation and load the data in as much as less time.

    Thanks in advance.

    • Taryn Murphy July 26, 2018 at 7:26 pm - Reply

      Hello Rajul.

      As your database grows in size, the performance of the API will continue to degrade. This is part of the problem. The API is really not designed for large data query jobs that Power BI places on it. Another potential issue is how the data is being pulled from the systemuser table.

      A couple of options:

      1. To import the systemuser table, or any table/entity for that matter, we recommend only importing the fields that are absolutely necessary for reporting.

      In the case of SystemUser, it’s usually just ownerid, fullname, jobtitle, and optionally the address fields. If you keep all of the fields in this table it has so many links to literally hundreds of other tables that causes this major performance hit.

      Here is an older blog post that helps with that…steps 10-15 are the important ones. https://www.akaes.com/blog/how-to-connect-power-bi-to-microsoft-dynamics-crm-online/

      2. If you are already limiting the fields you import from the systemuser table, then another option is to limit the data using a filter to limit the amount of data. E.g. in the screenshot below, I am in Query Editor and then right-click, select Text Filters, Equals….and it will only import users associated with the highlighted businessunitid.

      3. The next best option is to setup Data Export Service which replicates the CRM database to Azure SQL. You will find the performance significantly higher by taking this approach. We are recommending this approach to many of our customers.

      Here is a link to setting up the Data Export Service. https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/replicate-data-microsoft-azure-sql-database

      Using this approach, you are setting up a reporting database. When data is changed/updated/deleted in CRM, the service automatically updates the reporting database; typically within seconds, and worst case minutes. This reporting database provides direct access to SQL Server which is why the performance is so much higher. You also get the added benefit of using SQL Server Management Studio to create views, stored procedures, and other SQL specific features that can help with reporting.

      A final note on DES, we have a fixed fee service for configuring DES as well as providing some easier options for reporting with picklists/optionsets. Let us know if you would be interested in learning more about this option.

      Hope this helps.

      Mike

Leave A Comment