Connect Salesforce from Data Virtuality Studio using Salesforce Connector

In this Data Virtuality tutorial, I want to show how SQL developers can connect to a Salesforce system from Data Virtuality. Data Virtuality is one of the leading software vendors providing data virtualization in the market. Data virtualization enables creation of a logical data warehouse where data is not stored persistently on the data warehouse but can be accessed on demand via theh virtualization layer.

Salesforce connector enriches the variety of data sources for an enterprise company data warehouse by providing on demand access to Salesforce data.

First of all, launch Data Virtuality Studio with a user account that has permission to create a new data source. Right click on "Data Sources" and on context menu select option "Add data source" to create Salesforce connection among others.

The first screen on data source creation wizard enables Data Virtuality administrator to choose the relevant data source type. Salesforce data source type is listed under Other node. You can also start typing Salesforce to filter it among all other data source types.

create new Salesforce data source

If you pass to the following step with pressing Next button, you can display the screen where to define the connection properties for the new Salesforce data source. This screen content changes for each data source type.

You can provide a suitable alias name for your new connection. This alias will be used along with the table/view names in SQL queries built in your Data Virtuality logical data warehouse solution.

User and Password information is your credentials used to connect to Salesforce. They should be valid for a successfull connection. Additionally you have to enter the "Security Token" during this step.

Data Virtuality Salesforce connector connection properties

You can leave other data source parameters as default and can test the connection if the credentials are correct by pressing "Test connection" button.

successful connection to Salesforce from Data Virtuality

Click Next button to continue in data source creation wizard. You can gather statistics for the new Salesforce data source if you want by marking the checkbox "Gather statistics"

Click Finish to complete defining the new Salesforce data source

Within Data Virtuality Salesforce connection, I could see 351 tables/views that I can query via SQL statements

Salesforce table and views in data virtualization layer

Following is a SQL SELECT query I used to test the connection on a new SQL Editor within Data Virtuality


SELECT

 "Id"

 ,"Name"

 ,"Type"

 ,"Status"

 ,"StartDate"

 ,"EndDate"

 ,"IsActive"

 ,"Description"

FROM

 "Salesforce_DVConnection.Campaign" limit 4;;

Code

Salesforce users can query data from the data source by eliminating deleted rows by using the filter "isDeleted = false" which is the default behavior when call the Salesforce API