The Periscope Salesforce datasource serves as an add-on and allows for data to be extracted from a Salesforce instance and loaded into the Periscope cache. Salesforce tables that have been cached can be queried in charts and joined to other cached tables.
To connect Saleseforce to Periscope, a Connected App must first be created within Salesforce.
Click "Setup" in the top right of Salesforce.
On the left navigation pane, within the "Build" menu expand "Create" and select "Apps."
Under the "Connected Apps" section, click "New" to create a new connected app.
Fill out the basic information and check "Enable OAuth Settings" to open the OAuth form. Use the following callback URL and add "Access and manage your data(api)" to the selected OAuth scopes.
Callback URL: https://www.periscopedata.com/salesforce_oauth
Click "Save" at the top of the new connected app form.
Copy the Consumer Key and Consumer Secret to enter into Periscope's new datasource form.
Once a connected app has been set up, navigate to the data-burger menu in Periscope and click "Add Database" to add a new datasource. In the database type dropdown, select "Salesforce," and fill out the form with the following credentials.
Dislay Name: This is the name that is visible in the datasource selector.
Login Email Address: This is the email address of the admin account used to log in to Salesforce.com.
Login Password: This is the password associated with the Login Email Address.
Login Security Token: This is the security token only; do not include the password as a part of this field. If the user does not have or has forgotten the security token, follow these steps in Salesforce:
Client ID: This is the "Consumer Key" from Creating a Salesforce Connected App above.
Client Secret Token: This is the "Consumer Secret" from Creating a Salesforce Connected App above.
After connecting Salesforce to Periscope, any tables to be used in a query must be cached. To cache a Salesforce table, navigate to the Cache Settings through the gear icon at the bottom of the sidebar and select the Salesforce instance from the datasource dropdown menu.
Click on the table to be cached to open its cache settings. Select "Full" fom the caching strategy dropdown.
An interval based strategy will cache the table on the interval selected in the dropdown
A schedule based strategy will cache the table at the selected time every day.
After the relevant tables have been cached, they can be queried within a chart like any other table.
SELECT * FROM salesforce_database.opportunties;
Once the tables have been cached they can be seen in the schema tab when the salesforce connection has been selected as a datasource. As the tables are stored on the cache, they will need to use Redshift's syntax. Documentation on how to query the Periscope cache can be found here and guidance on how to join Salesforce tables to other tables can be found in our cross-database join documentation here.