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:
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) http://bit.ly/1lMLZmg
Great post ShiSh! For more details on (or to download) the Simba Technologies ODBC driver for Salesforce, check out www.simba.com/.../salesforce-odbc. (Includes full docs, specs, etc.)
Fantastic. Need to add this link to a recent hot topic article...Thanks.