SQL or SOQL for Your Next Salesforce Analytics Project?

SQL or SOQL for Your Next Salesforce Analytics Project?

July 12, 2016 0 Comments

You missed our webinar? We’re sad. We'll make it up to you with this recap and webinar replay!

This webinar is broken into four major sections:

  • Contrast SQL vs SOQL queries for analytics
  • How to get SQL access to Salesforce data
  • MicroStrategy connectivity to Salesforce.com
  • Why use MicroStrategy with Salesforce.com for analytics

Why are SQL and SOQL different?

SOQL (Salesforce Object Query Language) provides an object-like manner in which to query for information to make information easily accessible for developers. Since Salesforce is a multi-tenant system with multiple users sharing the database, Salesforce also uses SOQL to prevent queries from negatively affecting customers who rely on shared resources.

If SOQL is this great, why use SQL? Applications and professionals expect SQL. Standard SQL access instantly connects thousands of standard applications to the Salesforce platform. DataDirect JDBC and ODBC drivers provide direct SQL access to Salesforce quickly and easily.

Here is the DataDirect Architecture for direct SQL access to Salesforce.

DataDirect Architecture for direct SQL access to Salesforce

The Key Differences

Field SelectionFiltersFilters (Continued)JoinsAggregates, Group By, Order ByInserts, Updates, Deletes


After the main presentation, we hosted a live Q&A with all of the attendees. You all came up with some awesome questions! Here they are along with our answers.

Question 1

Is there a character limitation for SQL query?

For SQL queries, it’s all dependent on the database and how large of a query they can handle. SOQL has a 20,000-character limit, and that does become tricky for applications when they’re selecting a large number of fields or they have a very large filter list. On the SQL side, that limit is much larger.

Question 2

Is there a performance impact with layering the ODBC layer on top of the rest API?

In that case, yes, there is, because the layered approach has to rely upon the performance of the rest API. But going from SQL to SOQL, the DataDirect ODBC driver takes a lot of steps to ensure optimal performance. For example, when doing JOIN operations, parent-to-child relationships are favored to limit the number of rows returned. Filter expressions are pushed down whenever possible in order to reduce the size of the results and push the processing onto the Salesforce.com API side. But yes, there is a performance impact.

Question 3

How do you expose Salesforce reports using a SQL interface?

Salesforce reports are exposed through the ODBC and JDBC APIs as stored procedures, so you would get those through SQL procedures and SQL procedure columns much like you would with a relational database.

Question 4

How are transactions handled against Salesforce using the JDBC driver with app servers such as JBoss?

Salesforce does not support transactions in the sense that a traditional relational database would, and there are many applications which require transactional support. Through JDBC and ODBC, we have a mechanism for emulating transaction support.  The only caveat there is that you do not have the capabilities to roll back a transaction.

Question 5

Which version of MicroStrategy is being promoted?

Right now, it’s Version 10. The latest version we have is Version 10.4, but everything that I spoke about today is good for Version 10.

Questions 6

How is the Salesforce connection licensed in MicroStrategy?

From MicroStrategy’s side, we don’t license it. We just give you a mechanism to connect, so as long as you have your own user ID and password to connect to Salesforce, that’s all we need. There’s no cost from our side that we charge for you to connect to Salesforce.

Question 7

Does the ODBC and JDBC driver support write-backs?

Yes, the ODBC and JDBC drivers support the capability to insert, update, and delete records. They do this through the normal SQL way of doing it.

View the Webinar

Did any of this pique your interest? Learn all about the great data query debate, SQL vs SOQL for Salesforce analytics in the free webinar replay. You deserve to have the best for your Salesforce Analytics.

View the Webinar

Suzanne Rose

Suzanne Rose

Suzanne is passionate about promoting the Progress Data Connectivity and Integration business and corporate initiatives through social media and other marketing channels using extraordinary and compelling content and effective metrics. She is also team lead for DCI content developers, new hires and interns.

Comments are disabled in preview mode.
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
2020 Progress Data Connectivity Report
2020 Progress Data Connectivity Report
Read More
Getting Ahead of the Hybrid Data Curve
Read More
Creating Quick, Codeless Connectivity with Autonomous REST Connector
Read More