Data Visualization for Salesforce using PowerBI
| TL;DR: Democratizing Analytics capabilities for Line of Business (LOB) applications is a powerful capability of Power BI. Power BI in combination with LOB applications can empower Retailers with Insights from key business processes. Here is an example of Power BI integration with Dynamics CRM https://bit.ly/1sC4MVg by the Power BI Team. I worked on an example of Power BI integration with Salesforce. Here is some of the Salesforce data surfaced using Power BI: https://bit.ly/1lMLZmg The rest of the blog entry focuses on the how-to. |
Microsoft Power BI for Office 365 is a collection of features and services that enable you to visualize data, share discoveries, and collaborate in intuitive new ways. Microsoft Power BI for Office 365 provides an organization-wide self-service business intelligence (BI) infrastructure.
I have been experimenting with using Power BI as the analytics and data visualization layer for several line of business applications. Dynamics CRM is closely integrated with Power BI and you can use OData to connect to Dynamics CRM Online to explore and visualize your sales, customer and marketing data directly in Excel. Here are some details on connecting to Dynamics CRM Online using Power BI.
In addition, to doing analytics and data visualization for Dynamics CRM, I was curious if Power BI could be used with Salesforce. My experiments led to locating suitable ODBC drivers for Salesforce. I found these:
I used the Simba Salesforce ODBC Driver for my experiment. For testing this out, I created a Salesforce trial account using the Developer Edition at https://developer.salesforce.com/?language=en. I used the Developer Edition mainly because the standard trial version did not give me access via the API. Once, I created the account, I needed the security token from Salesforce for the API access:
- To get the security token for your account, if you do not already have it, go to Salesforce and log in. Select your name and choose “Setup”.
- Expand the “My Personal Information” tab on the left and then “Reset My Security Token”.
- Important to note that this security token is to be appended to your Salesforce password when logging in using the ODBC Driver.
The next step is to setup a DSN (Data Source Name). To do this, open the ODBC Administrator:
Select the System DSN Tab and Sample Simba Salesforce DSN. No changes need to be made to the driver configuration to evaluate the driver. User credentials will be asked during connection.
To build the tabular model using Excel 2013, launch Excel and go to PowerPivot. Within PowerPivot, select From Other Sources and choose Other (OLEDB/ODBC)
Select Build in the Table Import Wizard:
Click on the Provider Tab and select Microsoft OLEDB Providers for ODBC Drivers & click Next:
In the Connection Tab, select Use Connection String and click on Build. In the Select Data Source dialog, click on the Machine Data Source tab and select Sample Simba Salesforce DSN and click OK:
In the Salesforce connection dialog, key in your Salesforce user name and password. Important: Append the Security Token from Salesforce to the password : PasswordSecurityToken. Click OK.
The connection string will be populated in the Data Link Properties Window:
Click OK and you will see the connection string in the Table Import Wizard. Add a Pwd=YourPassword+SecurityToken at the end of the string and click Next:
At this point you can either select from a list of tables to import from Salesforce or choose to write a query.
For my experiment, I chose to select from a list of tables. I picked two tables to run the test: Accounts and Opportunity:
I used PowerPivot to setup the relationship between the two tables:
.. and built out a simple visualization using Power View. This is the interactive visualization published on O365: (This is just a quick visualization of some of the data and not representative of the kinds of insights that could be surfaced) https://bit.ly/1lMLZmg