Skip to content

Click in-app to access the full platform documentation for your version of DataRobot.

Salesforce Lightning Connector for Data Prep

User Persona: Data Prep User or Salesforce Admin

Note

This document covers all configuration fields available during connector setup. Some fields may have already been filled out by your Administrator at an earlier step of configuration and may not be visible to you. For more information on Data Prep's connector framework, see Data Prep Connector setup. Also, your Admin may have named this connector something else in the list of Data Sources.

Configure Data Prep

This connector allows you to connect to a Salesforce org as an import source. The following fields are used to define the connection parameters.

Note

Using Salesforce Lightning Edition is not a prerequisite for using this Connector. The Connector will work with any Salesforce org that supports the REST API v40.0 or later.

General

  • Name: Name of the data source as it will appear to users in the UI.
  • Description: Description of the data source as it will appear to users in the UI.

Tip

You can connect Data Prep to multiple Salesforce orgs (Sandbox, Dev, UAT, etc). Using a descriptive name can be a big help to users in identifying the appropriate data source. If you are a Data Prep SaaS customer, inform Data Prep DevOps of how you would like this set.

Web Proxy

If you connect to Salesforce through a proxy server, these fields define the proxy details.

  • Web Proxy: If a web proxy server is required, the following fields are required to enable a proxied connection.
  • Proxy host: The host name or IP address of the web proxy server.
  • Proxy port: The port on the proxy server for Data Source.
  • Proxy username: The username for the proxy server.
  • Proxy password: The password for the proxy server. *Leave username & password blank for an unauthenticated proxy connection.

User Credentials

The user authentication can be done through a Shared Account or an Individual Account. If you choose to authenticate with an individual account, the user will be prompted to enter credentials to access this Data Source. If you choose to authenticate with a shared account, the following fields are required.

  • Salesforce URL: The URL for Salesforce. By default, https://login.salesforce.com.

  • If you’re connecting to a Salesforce Sandbox, set the URL to: https://test.salesforce.com.

  • Session Security: The session security options to use when connecting to Salesforce. Select 'API Security Token' if Salesforce is not configured to use Trusted IP Ranges. If 'Trusted IP Ranges' is selected, an API Security Token value will not be required.

  • User: The username for a shared account used to authenticate with Salesforce.
  • Password: The password for a shared account used to authenticate with Salesforce.
  • API Security Token: A user-specific Salesforce API security token that allows API-based access. This token is typically emailed to the Salesforce user each time the password is changed. It is not required if 'Trusted IP Ranges' is selected under Session Security.
  • OAuth App FieldsTo set up Data Prep as an OAuth App in your Salesforce org, please follow the steps below in the Configuring Salesforce section below. Once you’ve completed these steps, you will be able to find the values required.

  • Consumer Key: A value used by the Data Prep Salesforce Lightning connector to identify itself to Salesforce. In OAuth 2.0 the value is referred to as the client_id.

  • Consumer Secret: A secret used by the Data Prep Salesforce Lightning connector to establish ownership of the Consumer Key. In OAuth 2.0 the value is referred to as client_secret.

Configuring Salesforce:

Salesforce Lightning Connector leverages the Salesforce REST API and OAuth. This step must be completed by a Salesforce Admin.

In order to establish connectivity:

  • A Salesforce Administrator must create a "Connected App" in Salesforce.
  • A Salesforce Administrator must obtain OAuth credentials for the "Connected App" to identify the client (Data Prep Connector) that is accessing Salesforce.
  • Each individual user must authenticate.

Create a "Connected App" in Salesforce

Please use the Salesforce documentation linked immediately below for background on Connected Apps and navigation inside Salesforce. Use the guide below for how to configure the specified fields.

Salesforce Instructions: https://help.salesforce.com/articleView?id=connected_app_create.htm&type=5

  • Enable OAuth Settings: Select this option.
  • Callback URL: Salesforce requires a callback URL to be defined, but it is not required for Data Prep.

  • For simplicity, please use your Data Prep URL.

  • Selected OAuth Scopes:

  • Under "Available OAuth Scopes", select "Access and manage your data (api)" and click the "Add" button.

  • Require Secret for Web Server Flow: Select this option as Data Prep will securely store your OAuth Consumer Secret.

  • Be sure to hit “Save”.

OAuth credentials for the "Connected App"

Upon Saving:

  • You will be presented with a page that contains the OAuth credentials for the "Connected App".

  • Consumer Key: Copy and save this.

  • Consumer Secret: Click the "Click to reveal" link.

  • *Trusted IP Range for OAuth Web server flow*

    • If the customer wishes to further restrict access to a list of IP Addresses, Data Prep server IP addresses would be added here.

Data Import Information

Via Browsing

  • All Objects are listed in alphabetical order.

  • Custom Objects will be listed alphabetically as well and will include “__c” at the end.

  • Objects with a Namespace will also be listed alphabetically under their Namespace.

  • Folder of Reports at the top.

Via SQL Query

Using Queries with the Salesforce Connector relies on Salesforce’s own query language, known as SOQL, for Salesforce Object Query Language. For information on this, please see: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm.

Here several example Queries:

  1. Query which has escape characters in it

    SELECT Id FROM Account WHERE Name LIKE 'Ter%'

  2. Query with functions in it select count(Id) from Account

    SELECT Name, MAX(Amount), MIN(Amount) FROM Opportunity GROUP BY Name

  3. Query with Alias

    SELECT a.Id, c.Id, c.name FROM Contact c, c.Account a WHERE a.name = 'MyriadPubs'

  4. null in where clause

    SELECT AccountId FROM Event WHERE ActivityDate != null

  5. SubQuery in Where clause

    SELECT Id, Name FROM Account WHERE Id IN ( SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost' )

  6. with GroupBy clause

    SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

  7. Field as alias SELECT Name n FROM Opportunity

  8. Query with TYPEOF SELECT TYPEOF What WHEN Account THEN Phone, NumberOfEmployees WHEN Opportunity THEN Amount, CloseDate ELSE Name, Email END FROM Event

  9. Relationship query (to parent)

    SELECT Contact.FirstName, Contact.Account.Name FROM Contact

  10. Relationship query (to child)

    SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts) FROM Account

  11. Relationship query with custom objects

    SELECT Opportunity__c, Id, Opportunity__r.Name, Opportunity__r.Owner.Manager.Email, Opportunity__r.Owner.Email FROM Opportunity_Change__c

  12. SubQuery in Select clause

    SELECT Amount, Id, Name, ( SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems ) FROM Opportunity

Best Practices

  • Exporting to Salesforce:

  • Most organizations have a defined process for bulk updating information within Salesforce, for this reason, the Data Prep Salesforce Connector only supports import.

  • To export data to Salesforce, download a CSV file of your data locally and upload to Salesforce following your organization’s guidelines.
  • For more information on how to bulk load data into Salesforce, please see: https://help.salesforce.com/articleView?id=data_import_wizard.htm&type=5

Technical Specs

  • Utilizes Salesforce REST API v40.0

FAQ/Troubleshooting/Common Issues

Salesforce Custom Reports Row Limit

Salesforce API limits Salesforce Reports to a result set of 2000 rows.

Column Data Types Handling

When importing data from Salesforce, Data Prep will handle column data types differently when browsing vs. querying:

  • Browse and Import:

  • Column data types are identified using Salesforce object metadata and mapped to Data Prep's internal types.

  • SOQL query import:

  • Salesforce SOQL results do not return the specific datatypes for columns and Data Prep does not parse queries or query results to determine column data types. As a result, all query results are interpreted as Text.

Example

When importing Salesforce Opportunity SObject row with "CloseDate" column containing "2013-11-13", CloseDate would be imported as:

  • Browse and Import: DateTime

  • "2013-11-13T00:00:00.000-08:00"

  • SOQL Query: Text

  • "2013-11-13"


Updated October 28, 2021
Back to top