* This blog was last tested on Oracle Analytics Cloud Data Sync (2.6.1) + SalesForce 2021 + ADW 18c by Jay Pearson (Oracle A-Team) *
This functionality has been available since Version 2.2 of the Data Sync tool, released September 2016, when the ability to connect to a number of different data sources via JDBC was added to the product.
Setting up these Data Sources in Data Sync varies by the source. Rather than create a single article to cover them all, 3 different articles have been created.
Select the link in the right column of the table below to view the appropriate article.
This article covers the steps to extract data from SalesForce and load that into BI Cloud Service (ADW).
|Data Source||Article With Details|
|Service Cloud / RightNow||link to article|
|Greenplum, Hive, Impala, Informix, Mongodb. Postgresql. Redshift, Sybase, and Instruction for other JDBC data sources where the JDBC driver is available||link to article|
Be sure to download and install the latest version of the Data Sync Tool from Oracle Analytics Downloads.
For instructions on how to configure ADW as the target, see this article.
For general instructions on configuring Data Sync, see this article.
For further information on the Data Sync Tool, upgrade instructions, OAC Data Sync readme (Data Sync release notes and known issues), and OAC Data Sync Documentation (Data Sync install and user guide) can be found here.
1. Obtain a Security Token from Salesforce. If you have this already, skip to step 2.
a. In the Salesforce GUI select 'My Settings' under the drop down beneath the user name as shown:
b. Expand the 'Personal' menu item on the left of the page and select 'Reset Security Token'. Complete the process and the token - a set of alphanumeric characters - will be e-mailed to the user.
2. In the Data Sync tool, create a new connection for SalesForce.
a. Chose the 'Generic JDBC' driver, enter the username and password, and edit the 'URL'. The URL format should be:
replacing ##SECURITYTOKEN## with the Salesforce token from step (1).
b. In the 'JDBC Driver (Optional)' field, enter:
c. In the 'Schema Table Owner (Optional)' section - select the Salesforce schema that houses the data required, and then click 'OK'.
d. Save and test the connection.
If this error is encountered:
Failure connecting to "SalesForce"! [Oracle DataDirect][SForce JDBC Driver][SForce]UNSUPPORTED_CLIENT: TLS 1.0 has been disabled in this organization. Please use TLS 1.1 or higher when connecting to Salesforce using https.
Then changes to the security settings in SalesForce will be required. To do this:
a. In the search box of SalesForce - type 'Critical Updates' and then select the 'Critical Updates' object that appears below.
b. Select the 'Deactivate' option for 'Require TLS 1.1 or higher for HTTPS connections' option (click the next image to see it in more detail).
3. Return to Data Sync. Testing should now be successful.
SalesForce has Audit / Metadata columns available on many tables. These are especially useful for incremental loads as they provide 'CREATED BY', 'CREATED DATE', 'MODIFIED BY', 'MODIFIED DATE' columns, among others. HOWEVER - by default, the JDBC driver configuration does not return these fields, so some editing needs to be done.
The SalesForce connection, described above, must be created before making these changes.
1. Shutdown Data Sync Tool
Exit out of Data Sync completely, by closing the GUI, and then selecting 'Exit' out of the Data Sync application from the task bar:
2. Find Files Related to SalesForce JDBC Configuration
When the SalesForce connection is created in Data Sync, a number of files are created in the root of the Data Sync directory by the JDBC driver. These files will be named after the user created in the connection string. For instance, if the user connecting to SalesForce is JSMITH@GMAIL.COM, then the files will be named JSMITH.app.log, JSMITH.config etc. In the example below, the username was 'SALESFORCE'.
3. Delete the 'map' file
In the Data Sync directory, find the files listed below. DELETE the xxxx.SFORCE.map file. In this case 'salesforce.SFORCE.map'. This file contains metadata including tables and columns available in SalesForce. This needs to be deleted so that it can be rebuilt with the audit columns included.
4. Update the 'config' file
In your favourite text editor, open the XXXX.config file, in this example 'salesforce.config' and find this entry:
and update to:
The updated file should look something like this:
Save the file,
5. Confirm Audit Columns are now visible
Open the Data Sync tool. Go to 'Connections' and 'Test' the SalesForce connection you had created previously. This will regenerate the .MAP file, this time containing all of the audit columns.
When tables are imported (see the next section 'Loading SalesForce Data into ADW), the following audit columns should be available. NOTE - the 'SYS_ISDELETED' field data type is not recognized by Data Sync and comes in as 'UNKNOWN-UNSUPPORTED'. This needs to be changed in the Target tables. It can be changed to a VARCHAR with length of 1.
NOTE - if new fields are added to SalesForce, then steps 1-3 should be repeated so that the 'MAP' field can be refreshed and the news fields made available in the Data Sync tool.
Loading SalesForce Data into ADW
As it does with many data sources, the Data Sync tool provides several methods to load data in from the source. Two will be covered here. Importing Data from a SalesForce Object, and Importing Data from a SQL query.
1. Select 'Project' -> New -> Add new source object -> Source = SalesForce -> Discover objects(s) from Catalog':
2. Hit 'OK'
3. Select SalesForce Object(s) to Load
If the SalesForce object name is known, or even the first letters of the name, then that may be entered in the 'Filter' box. So for example, to return all SalesForce objects that begin with the letters ACCE, then 'ACCE*' (without the quote marks) would be entered in the filter. To see all objects, leave the default '*'. Then hit 'Search'. That will bring up a list of the available SalesForce objects matching the Filter criteria.
Select the Object(s) required by checking the 'Import Definition' check box, and then hit 'Import'
In this example the filter was left as default, and ACCOUNT table was selected.
4. Best Practice
Data Sync will give some recommendations. Read those, then select 'OK'
You may get a warning for 'unsupported datatypes'. Click 'OK'.
Whether a warning is received or not, it is good practice to confirm that all field data types have been identified correctly, and only those that are truly not nullable have that flag set. This will prevent errors when running a Job.
a. Go to 'Project', 'Target Tables/Data Sets' and select the target table created in the last step. In this example 'ACCOUNT', and then in the lower window pane, select 'Table Columns'.
b. Go through each table column to confirm that the data type is set correctly, and in cases where the Data Type is listed as 'UNKNOWN-UNSUPPORTED' change that. In this case that can be changed to a VARCHAR with length of 1. NOTE - a field needs to be in an 'ACTIVE' state before it can be edited.
c. Go through each field and confirm that only rows that are truly non-nullable have the box unchecked. If a field has the 'Nullable' flag unchecked, but contains null values, then the load will fail.
Selecting Objects from SalesForce using this method will bring back every field from the Object, whether it is required or not. This can result in a lot of data being extracted and loaded in ADW. Performance can be improved by excluding fields that are not required in ADW. This is good practice to go through and inactivate any such fields.
d. With the Pluggable Data Source created in the previous steps still selected, check the 'Inactive' column to remove fields not required. In this example the CLEANSTATUS, CUSTOMERPRIORITY, DANDBCOMPANYID and DESCRIPTION are marked as 'Inactive'. When the Data Sync tool reads from SalesForce, it won't select these rows, so the extract will be smaller and will download faster, as will the upload up into ADW.
e. Be sure to hit the 'Save' button after making any changes.
NOTE - SalesForce has some field names that are considered Reserved Words in Oracle. For instance, many SalesForce tables have the column 'ROWID' which is an Oracle DB reserved word.
The Data Sync tool will automatically rename ROWID to ORARES_ROWID, as shown in the 'mapping' sub-select of the ACCOUNT table:
5. Incremental loads
a. To set up Incremental Loads, go to 'Project' / 'Pluggable Source Data', and select the Pluggable Data Object created in the previous steps. Then in the lower window pane select 'Edit' and then click on the value in the 'Load Strategy' box to open up the Load Strategy options.
b. Select 'Update table'
c. Chose a suitable Key(s) field for the user key, and date field for the filter. The Audit fields, described in the section 'Making 'Audit' Columns Available in SalesForce Tables', may make good candidates for the date filter field.
6. Run the job to test it.
1. Select 'Project' -> New -> Add new source object -> Source = SalesForce -> Enter objects's properties manually:
Enter a logical name for the pluggable source data object that will be created. The Target Name will be the table that Data Sync creates in ADW. If the plan is to load an existing table, enter that name here.
In the Connection make sure the 'SalesForce' connection is selected:
2. In the drop down for 'Data from' select 'Query', then OK.
3. In the Query Override, enter the SQL statement.
This could be in the form of a select *, for instance
select * from account
or a select that specifies the field names for a single table
select accountnumber, accountsource, annualrevenue, billingstate, description, industry, rowid, sys_lastmodifieddate from account
or a select that joins multiple tables and includes calculations (in this case to find the last modified date of the data from 2 tables, and to create a unique ID)
case when contact.sys_lastmodifieddate > opportunity.sys_lastmodifieddate then contact.sys_lastmodifieddate else opportunity.sys_lastmodifieddate end as lastmoddate,
contact.rowid + opportunity.rowid as uniqueid,
from contact, opportunity
4. Check the newly created target's data type.
As before, go to the 'Target Tables / Data Sets' created from the new Pluggable Data Source and make sure that the Data Type is correct and there are no 'Unknown/ Unsupported' data types. Also adjust the 'Nullable' column so that only columns that are truly Not Null remain unchecked.
5. Set up Incremental Updates
As before, change the Load Strategy to 'Update' table and select a suitable Key(s) field for the user key, and date field for the filter.
6. Run the job to test it.
This article walked through the steps to configure the Data Sync tool to be able to connect and extract data from SalesForce.