Accessing SalesForce Data Using SQL with Enzo Unified

In this article I will show you how to access SalesForce data to simplify data access and analysis, either in real-time or through a local cache, using SQL commands through the Enzo Unified data virtualization server. Most customers using SalesForce will need to access their data stored in SalesForce tables to run custom reports, or display the information on custom applications. For example, customers may want to build a custom monitoring tool to be notified when certain data changes are detected, while other customers want to keep a local copy of the SalesForce data for performance reasons.

High Level Overview

You may need to access SalesForce data for a variety of reasons: reports, data synchronization, real-time data access, business orchestrations... Most organizations facing the need to extract SalesForce data are looking for a way to get real-time data access into SalesForce, or access a local copy of the data for faster processing. Enzo Unified is a data virtualization server that gives you the ability to access SalesForce data in real-time, or automatically cached locally, either through SQL or REST commands.

Enzo Unified offers three data access and synchronization methods:

  • - Virtual Table
    A virtual table provides a view into a SalesForce object. The virtual table doesn’t actually store any data; it provides a simple way to get to the data through REST and/or native SQL requests.
  • - Snapshot
    A snapshot is created on top of a Virtual Table. Snapshots create a local copy of the underlying SalesForce data and make the data available to both REST and SQL requests. Snapshots buffer the data locally inside Enzo Unified, which provides a significant performance boost when querying data. Snapshots can be configured to be refreshed periodically.
  • - Integration
    When SalesForce data needs to be copied to an external system, such as SQL Server or SharePoint, Enzo Unified provides an Integration adapter that is designed to copy data changes to a destination. For example, with the Integration adapter, Enzo Unified can detect changes to an Account table in SalesForce and replicate changes made to that table in near-time to a SalesForce list. The Integration adapter will be covered in a future article.

For a broader overview of Enzo Unified, read this whitepaper. For a brief justification for this technology, see this article.

SalesForce Virtual Table

Let’s first create a Virtual Table in our SalesForce adapter; the virtual table is called Account1, and points to the Account table in SalesForce. Note that the adapter is already configured with my SalesForce credentials. SalesForce credentials are assigned to a Enzo login; in the example below, the credentials to connect to my SalesForce environment is attached to the ‘sa’ account in Enzo. Because multiple configuration settings can be saved, they are named; I am showing you the ‘bscdev’ configuration, and the (*) next to it means that it’s the default setting for this login.

image

To create a virtual table, let’s select the Virtual Tables tab. A virtual table in SalesForce is defined by a SOQL definition, which Enzo Unified runs behind the scenes to fetch the data. Clicking on the ellipses next to the SOQL statement allows you to edit the command. The edit window allows you to test your SOQL command. The columns of the virtual table are automatically created for you based on the command you specified.

 imageimage

Once the virtual table is created, retrieving data from the Account table is as simple as running this command:  SELECT * FROM SalesForce.Account1 – this executes the SOQL command against SalesForce behind the scenes, and once the data has been fetched it is returned to you.

You might wonder… where do I run this command from?  Since Enzo Unified is a data virtualization server, that understands native SQL Server requests, you can use SQL Server Management Studio (SSMS) to connect to Enzo Unified directly. Or you can use Excel, and connect to Enzo Unified as a SQL Server database. Or you could use ADO.NET from a .NET application for example, and declare a SqlConnection that points to Enzo Unified.

For example, here is the command executed from SSMS; the data came back live from SalesForce.

 image

And here is the data using Excel 2013, with the Connection Properties to Enzo Unified. As you can see, Excel sees the Account1 virtual table and is able to read directly from it.

image image

You can also use Visual Studio and create a SqlConnection to connect to Enzo Unified and fetch data directly using ADO.NET. For example the following command returns up to 100 records records from the Account1 virtual table and binds the result to a Data Grid:

SqlConnection conn = new SqlConnection(“server=localhost,9550;uid=enzo_login;pwd=enzo_password”);
conn.Open();
SqlCommand cmd = new SqlCommand(“SELECT TOP 100 * FROM SalesForce.Account1”, conn);
SqlDataReader reader = cmd.ExecuteReader();

DataSet ds = new DataSet();
DataTable dt = new DataTable("Table1");
ds.Tables.Add(dt);
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables[0]);
dataGridViewResult.DataSource = ds.Tables[0];

conn.Close();

 

Virtual Table Snapshot

As described previously, a snapshot is a local copy of the data and kept in Enzo Unified for faster retrieval. This allows you to create a simple local cache of remote data; in this case we will store the SalesForce account table in a Snapshot called AccountCache. The Snapshot is defined on the Account1 virtual table. Using Enzo Manager, select the Account1 virtual table (in the SalesForce adapter), and select the Snapshot tab.  The Snapshot below shows that it is refreshed daily; note that you can enter a Cron schedule to refresh the Snapshot at specific intervals.

image

Once created, the Snapshot becomes accessible through Enzo Unified using an EXEC statement:  EXEC SalesForce.AsyncResult ‘AccountCache’

The main difference is that the data is now coming from a local cache instead of the SalesForce Account table directly. As a result, the data may be somewhat delayed; however since the Snapshot offers a schedule for data refresh, you have control over how old the data is.

image

You can also access the Snapshot data from code using ADO.NET. The following code shows you how (note the EXEC call to the AsyncResult object).

SqlConnection conn = new SqlConnection(“server=localhost,9550;uid=enzo_login;pwd=enzo_password”);
conn.Open();
SqlCommand cmd = new SqlCommand(“EXEC SalesForce.AsyncResult ‘AccountCache’”, conn);
SqlDataReader reader = cmd.ExecuteReader();

DataSet ds = new DataSet();
DataTable dt = new DataTable("Table1");
ds.Tables.Add(dt);
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables[0]);
dataGridViewResult.DataSource = ds.Tables[0];

conn.Close();

 

Summary

This article shows you how to access SalesForce data using native SQL commands through Enzo Unified, using various tools and techniques, including Excel, SSMS and .NET code. Because Enzo Unified is a data virtualization server that understands native SQL and REST commands, anyone with the proper access rights can access SalesForce data without learning the underlying APIs. And as with most data virtualization platforms, Enzo Unified offers security options giving you the ability to control who can access which virtual tables and snapshots.

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.v

Print | posted @ Monday, November 7, 2016 11:34 AM

Comments on this entry:

Gravatar # Diploma colleges in haryana
by swamidevidyal at 11/10/2016 5:20 AM

Swami Devi Dyal is the best engineering college in haryana.If you need to set up your profession in Engineering you can get the confirmation in our college.For more insights about our college you can Visit our college or visit our site www.swamidevidyal.ac.in
Gravatar # re: Accessing SalesForce Data Using SQL with Enzo Unified
by tank trouble at 12/2/2016 11:55 PM


I know how it's an awesome game so I real want to Play it haha :) :)
[url=http://tanktrouble6.com]tank trouble[/url]
Comments have been closed on this topic.