SQL or SOQL for Your Next Salesforce Analytics Project?
This article features a side-by-side comparison of SQL and SOQL to consider when working on a Salesforce project.
Join the DZone community and get the full member experience.Join For Free
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. Because 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.
The Key Differences
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.
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.
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.
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.
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.
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.
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.
Do the ODBC and JDBC drivers 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.
Published at DZone with permission of Suzanne Rose, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.