X

Best Practices from Oracle Development's A‑Team

Loading SalesForce Data into BICS with Data Sync

For other A-Team articles about BICS and Data Sync, click here

Introduction

Version 2.2 of the Data Sync tool was released Septembert 2016 and added the ability to connect to a number of different data sources via JDBC.

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 (BICS).

 

Data Source Article With Details
SalesForce this article
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

 

 

Main Article

Downloading Latest Version of Data Sync Tool

Be sure to download and install the latest version of the Data Sync Tool from OTN through this link.

For further instructions on configuring Data Sync, see this article.  If a previous version of Data Sync is being upgraded, use the documentation on OTN.

Setting up Salesforce Connection

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:

 

passkey1

 

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.

 

Passkey2

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:

jdbc:oracle:sforce://login.salesforce.com;SecurityToken=##SECURITYTOKEN##

replacing ##SECURITYTOKEN## with the Salesforce token from step (1).

passkey3

b. In the 'JDBC Driver (Optional)' field, enter:

com.oracle.bi.jdbc.sforce.SForceDriver

A_CONNECTION2

c. In the 'Schema Table Owner (Optional)' section - select the Salesforce schema that houses the data required, and then click 'OK'.

Cursor

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.

Critical_Updates_2

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).

 

Critical_Updates

3. Return to Data Sync. Testing should now be successful.

Connection_Successful

Making 'Audit' Columns Available in SalesForce Tables

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:

Cursor

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.

Cursor

4. Update the 'config' file

In your favourite text editor, open the XXXX.config file, in this example 'salesforce.config' and find this entry:

auditcolumns=none

and update to:

auditcolumns=all

 

The updated file should look something like this:

Cursor

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 BICS), 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.

Cursor

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 BICS

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.

 

Import Data from a SalesForce Object

1. Select 'Project' / 'Pluggable Source Data' and then 'Data From Object':

 

Weds_1

2. Make sure 'Discover objects by listing' is selected, then hit 'OK'

Weds_2

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.

Cursor

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.

Cursor

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 BICS.  Performance can be improved by excluding fields that are not required in BICS.  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 BICS.

Cursor

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:

Cursor

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'

Screenshot_8_9_16__12_46_PM

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.

 

Import SalesForce Data with SQL Code

1. Select 'Project' / 'Pluggable Source Data' and then 'Manual Entry'

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 BICS.  If the plan is to load an existing table, enter that name here.

In the DB Connection make sure the 'SalesForce' connection is selected:

Cursor

2. In the drop down for 'Data from' select 'Query', then OK.

Cursor

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)

select
contact.accountid,
contact.email,
contact.lastname,
contact.firstname,
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,
opportunity.amount,
opportunity.description,
opportunity.campaignid,
opportunity.expectedrevenue
from contact, opportunity
where opportunity.accountid=contact.accountid

Cursor

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.

Cursor

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.

Cursor

6. Run the job to test it.

 

Summary
This article walked through the steps to configure the Data Sync tool to be able to connect and extract data from SalesForce.

For further information on the Data Sync Tool, and also for steps on how to upgrade a previous version of the tool, see the documentation on OTN.  That documentation can be found here.

For other A-Team articles about BICS and Data Sync, click here

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha