Comprehensive solution for crafting and managing sophisticated digital experiences
Build engaging websites with intuitive web content management
Leverage a complete UI toolbox for web, mobile and desktop development
Build, protect and deploy apps across any platform and mobile device
Build mobile apps for iOS, Android and Windows Phone
Rapidly develop, manage and deploy business apps, delivered as SaaS in the cloud
Automate UI, load and performance testing for web, desktop and mobile
Host, deploy and scale Node.js, Java and .NET Core apps on premise or in the cloud
Optimize data integration with high-performance connectivity
Automate decision processes with a no-code business rules engine
Transform your businesses in order to survive in a completely digitized and connected world driven by software innovation.
Globally scale websites with innovative content management and infrastructure approaches
Content-focused web and mobile solution for empowering marketers
Faster, tailored mobile experiences for any device and data source
UX and app modernization to powerfully navigate today's digital landscape
Fuel agility with ever-ready applications, built in the cloud
This tutorial shows how you can use the world's most widely used BI tool, Microsoft Excel, to get direct SQL access via ODBC to SaaS applications hosted in the cloud. More specifically, we'll look at how Excel power users can leverage PowerPivot to build enhanced analytic capabilities directly on top of Salesforce.com data (Sales cloud, Service cloud, Force.com, Database.com). This data can then be mashed up with other data sources for 360 degree analytics. This is self-service BI at its finest since absolutely no staging database is required; and you are leveraging existing SQL-92 skills to directly access live data.
Warning: I have seen business analysts at Salesforce shops swarm the work spaces of those who completed this tutorial.
1. Download and install the 15 day trial of the latest 32-bit or 64-bit DataDirect Connect XE for ODBC Salesforce driver for Microsoft Windows.
2. Create an ODBC data source to Salesforce.com. Here is a great video by Jeff Leinbach to create the data source.
Note: Since PowerPivot can load large amounts of data in-memory, it is recommended to run the ODBC driver in server mode to avoid running out of memory. To do this, go to the "SQL Engine" tab of the ODBC data source and change "SQL Engine Mode" to "1 - Server" and click "Edit Server Settings" and click services: "Start" button.
3. Launch the PowerPivot addon from Microsoft Excel 2010
4. Click small Database Icon to launch "Table Import Wizard" and choose "Others (OLEDB/ODBC)".
5. Specify "Use connection string" and click "Build". Then select to the ODBC Data Source for Salesforce and authenticate. Make sure to check "Allow saving password".
6. Click OK and Next
7. There are two options to import data. You can view the objects by selecting "Select from a list of tables and views to choose the data to import" or "Write a query that will specify the data to import".
SQL-92 INNER JOIN syntax is fully supported against Salesforce using DataDirect
8. Below is data pulled directly from Salesforce.
Got mobile devices like iPads, Surface, Android? Publish the PowerPivot project to your Sharepoint server for a live real-time data refresh against Salesforce.com using the same ODBC driver.
The reason we all love standards based SQL access is that everyone can access the data. Other SaaS solutions provide ODBC drivers such as NetSuite, Microsoft Dynamics, Service Now, Plex Online, etc. Or for direct SQL access to sources such as Aprimo, Eloqua, SugarCRM, RightNow, Success Factors, Taleo, ADP. Intaact, Concur we encourage you to check out the recently announced DataDirect Cloud.
Post a blog comment or call 1-800-876-3101 to speak with a live Solutions Consultant to learn how other organizations are making progress with direct real-time SQL access to Salesforce from Microsoft Excel.
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Copyright © 2016, Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks or appropriate markings.