Sonoma Partners Microsoft CRM and Salesforce Blog

Cats and Dogs getting along in PowerBI Desktop

Today's post is written by Brendan Landers, VP of Consulting at Sonoma Partners.

I have always found PowerBI to be interesting, but recently PowerBI has become incredibly powerful with the introduction of PowerBI Desktop last summer.

Last week I attended the inaugural Microsoft Data Insights Summit in Redmond and 75% of the sessions at the Summit contained demos around PowerBI Desktop. It’s clear that Microsoft is investing a lot of time and energy in the space and finally have a winner.

I’d like to showcase an example of using PowerBI desktop to pull data from a Salesforce environment.  Many of our customers own PowerBI but don’t know how to get started. Many don't know that connecting to a Salesforce environment is even possible.  Not only is it possible, but it’s very simple.  Also, you can just as easily connect your Salesforce data with data from other applications (even Microsoft CRM). 

From PowerBI Desktop, select Get Data, select Other and select Salesforce Objects.  Note that you can also connect directly to Salesforce Reports.  This would help if you want to only bring in a subset of the data, but for this example I will bring in everything for Account and Opportunity.


Next, you indicate where you want to connect to.  If you select Production you will login to your production SFDC org.  In this case, I am connecting to a test org so I enter the URL of the test org and click OK.


Next, you will select the objects you’d like to bring in.  For my purposes I will bring in Accounts and Opportunities and click OK.


This will pull in all the Account and Opportunity fields, and relate the two data sets.  Now we can start building our PowerBI reports.  To illustrate, I will select Amount field from the Opportunity.  You will notice PowerBI understands the datatype of the field and shows the Sigma () character indicating the field can be summarized.  I will then select the Close Date field.  PowerBI automatically puts the Date field on the axis and provides a date hierarchy for your chart.  Note that you can change the date hierarchy if the standard hierarchy doesn’t work for you.  Next I will remove Year from the date hierarchy by simply clicking the X next to it.  With just a few clicks I now have a Pipeline bar chart by quarter. 


Now that I have that report, I realize I’d like to see the pipeline by account owner.  While I can get to the OwnerID field on the Account object, I realize I’d like the name of the owner, so I’d like to bring this in from the User table.  Unfortunately, I only brought in Accounts and Opportunities, but I can simply bring in the User table without losing any of my work by following the same steps as before only choosing the User table this time.  Now I have three objects, and again, PowerBI has created the relationships for me so I don’t need to tell the tool how the tables are related.  You can view the relationships by clicking on the Manage Relationships button.  This is also where you can create relationships if PowerBI doesn’t catch on for you.


One of my favorite parts of PowerBI is how the datasets are linked in a way when you drill into one the other reports will drill down also.  There are several ways to view a Pipeline by Account Owner, but for this example I am going to add a slicer.  To do so, I’ll click on the slicer Visualization, and then add Last Name from the user table to the slicer.  I now can slice this report (and any other I add to this PowerBI file) by the Account Owner. 


When I have the reports how I like them, I simply publish out to PowerBI and they are available to those that can see PowerBI content from my company.  This is a very basic example of PowerBI working with SFDC, but hopefully it demonstrates the ease with which you can create valuable reports using PowerBI Desktop.



Topics: Salesforce