Over the past 10 years I've developed several data warehouse and reporting systems that included Salesforce data sources. These reporting projects are actually quite common due to exceptionally weak, out-of-the-box Salesforce reporting functionality and also the frequent desire to combine Salesforce data with other data sources. Historically, to query and import Salesforce data, you needed to write a bit of .NET code to reference the Salesforce API and issue Salesforce Object Query Language (SOQL) queries in SQL Server Integration Services (SSIS) Script Tasks. If that was too cumbersome, you could look into buying a third-party ODBC driver like CozyRoc, Easysoft, Progress or Simba’s ODBC drivers.
My tried and true favorite option—by far—for working with Salesforce data has been DBAmp. DBAmp is a Salesforce database replication add-in for SQL Server. It uses a Linked Server approach with stored procedures and jobs to do batch, full, or incremental replication of Salesforce objects including any custom objects or fields. DBAmp also works seamlessly with both SSIS and SQL Server Reporting Services (SSRS). It's easy to set up, well documented, and is optimized for the huge initial, full Salesforce loads that can be a challenge with other options due to data transfer timeouts.
New Salesforce Connector for Power Query
This week during the extremely popular Salesforce Dreamforce conference, Microsoft announced a new Salesforce connector for Microsoft Power Query in Excel. This new connector is available as a preview in both 32-bit and 64-bit flavors. Before you can install it, you'll need to ensure that you have the latest and greatest version of Power Query already updated. Once the Salesforce connector is installed, you'll see two new sources in the From Other Sources ribbon menu. You might need to scroll down the ever growing data source list to see them.
To connect and see your Salesforce data, you'll need to have a Salesforce account that is granted API access permissions. This is something you can add on yourself in your own free Salesforce Developer account or ask your Salesforce administrator to grant that permission to you. Note that when you connect to Salesforce via Power Query, you are required to log in with your Salesforce credentials. You'll only have access to that one specific user's data and not the entire account/organizational level data. Unlike a few of the other Salesforce connectivity options mentioned earlier that are bi-directional read/write, Power Query's Salesforce connection is a one way, read-only connection.
Once connected in Excel Power Query, you can connect directly to the underlying object tables using the Salesforce Objects menu. This command displays a full list of all user Salesforce objects. You'll then visually select one or more objects/tables to import along with filter criteria to import Salesforce data directly into the Excel workbook. Power Query automatically generates the needed SOQL queries.
In addition to raw Salesforce Objects, there is also a lovely Salesforce Reports connection that leverages the Salesforce Reports API. This user-friendly Salesforce Reports command allows import of Salesforce defined report data sets into Excel. This option is most likely the one non-technical sales users would appreciate and need versus trying to recreate those reports from raw object data exports that may be overwhelming.
Who needs Salesforce Wave?
Also this week at Dreamforce, a new reporting service from Salesforce called Wave was announced. Wave is a basic, entry-level, SaaS-based data visualization offering targeted at non-technical Salesforce users. Wave is not cheap. Pricing will be as follows:
- all users will pay a Platform access subscription
- those using existing API data connections (called Explorers) will pay an additional $125 user/month
- those requiring the importing of additional third party data (called Builders) will pay an additional $250 user/month
So why would anyone buy this overly priced Salesforce reporting solution when you can do the same thing much better and for far less money with Microsoft Power BI? Salesforce Wave is not nearly as flexible as Excel nor as powerful as Power View, Power Map, and all the options that Microsoft Power BI delivers today. If you have Salesforce, you need to check out the official Microsoft Power BI blog and YouTube video to learn more about this excellent alternative to the Wave hype. This is why you get paid the "big bucks" right?
