How to Connect Power BI to Microsoft Dynamics CRM Online
Finding the CRM Connection URL
Before you begin, find your CRM connection URL.
1. Log in your CRM instance, and go to Settings.
2. Click Developer Resources.
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 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…
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.
4. Paste the URL as found in the first section of this document, then click OK.
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.
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.
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.
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.
- CustomerId Name
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.
- 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.
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.
17. We are ready to load the data. Click the Close & Apply button.
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.
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.
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.
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.
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).
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.