Connecting

Login to Knowi and select Settings -> Datasources from the left down menu.

Click on Salesforce. An authentication popup will be shown. Sign in to your Salesforce account to complete the OAuth process, where a secure token is exchanged between Salesforce and Knowi.

Note: To connect to a Salesforce Sandbox environment, select the 'Salesforce Testing' icon.

Save the Connection. Click on the "Configure Queries" link on the success bar.

Alternatively, Use our Cloud9Agent to connect to your Google Analytics account. This can securely pull data inside your network. See agent configuration for more details.

Queries & Reports

This section outlines how to pull data from Salesforce. Knowi provides a Data Discovery tool for interacting with Salesforce and generating SOQL queries. You can also plug in your own SOQL queries and optionally post-process the data with Cloud9QL.

Metrics : One or more Metrics that you'd like to track. Click on the metric for aggregation (count/sum/Date truncation) options.

Dimensions : One or more fields to group/bucket the results by. For Date based fields, click on the field for additional options (Date/Week/Month) options.

Filters : One or more filters to group by. Select a metric, which will open up a dialog with filter type and value.

Sort : Sort the results (Ascending/Descending) for a field.

Limit: Maximum number of records to pull.

Scheduling: Configure how often this should be run. Select 'None' for a one-time operation. The results are stored in your warehouse in Knowi.

Overwrite Strategy (for scheduled query runs):

This determines how the data is stored in your warehouse in Knowi:

i. If empty, data will be added on to the existing data for this dataset within Knowi. Or,

ii. "All": Any existing data for this dataset will be replaced by this results.

iii. One or More Field Names (Example: "A,B,C"): A new record is created where the values of the combination of the field names do not exist, and, updates current records for the field grouping where it exists. For example, if this is set to say "Date, Type", existing data with the same Date and Type values will be updated with the latest data, and new records created when they do not exist.

Click 'Preview' to see the results.

Click on 'Save' to complete setting up the report. This also sets up this data extraction on a schedule, if configured.

Click on 'Dashboards' link on the success bar to access dashboards. You can drag and drop the newly created report from the widget list into to the dashboard.

Cloud9Agent

Use Cloud9Agent as an alternative to UI based connectivity outlined above. The agent runs inside your network to extract data from Salesforce and sends the extracted/manipulated data into your Knowi warehouse. Check out Cloud9Agent to download your agent.

For sample Salesforce configuration, see the datasource_example_Salesforce.json and query_example_Salesforce.json examples folder under the Cloud9Agent install directory.

Highlights:

Connects to Salesforce using OAuth tokens

Pulls data using Salesforce API, with optional manipulations using Cloud9QL

Obtain the Refresh token using the Connect step in the UI section above.

Datasource Configuration:

Parameter

Comments

name

Unique Datasource Name.

datasource

Set value to salesforce

authAccessToken

OAuth Access Token generated by Salesforce

authRefreshToken

OAuth Refresh Token generated by Salesforce.

sfInstanceURL

Specify your Salesforce Instance, if applicable

Query Configuration:

Query Config Params

Comments

entityName

Dataset Name Identifier

identifier

A unique identifier for the dataset. Either identifier or entityName must be specified.

dsName

Name of the datasource name configured in the datasource_XXX.json file to execute the query against. Required.

sfSOQuery

Required. Use to pull the appropriate data from Salesforce. See SOQL docs for more details.

Alternative to runOnSourceChange, pick up the file on a schedule. One of minutes, hours, days, weeks, months. If this is not specified, this is treated as a one-time query, executed upon Cloud9Agent startup (or when the query is first saved)

frequency

Indicates the frequency if frequencyType is defined. For example, if this value is 10 and the frequencyType is minutes, the query will be executed every 10 minutes

startTime

(Optional) Can be used to specify when the query should be run for the first time. If set, the frequency will be determined from that time onwards. For example, is a weekly run is scheduled to start at 07/01/2014 13:30, the first run will run on 07/01 at 13:30, with the next run at the same time on 07/08/2014. The time is based on the local time of the machine running the Agent. Supported Date Formats: MM/dd/yyyy HH:mm, MM/dd/yy HH:mm, MM/dd/yyyy, MM/dd/yy, HH:mm:ss,HH:mm,mm

overrideVals

This enables data storage strategies to be specified. If this is not defined, the results of the query is added to the existing dataset. To replace all data for this dataset within Knowi, specify {"replaceAll":true}. To upsert data specify "replaceValuesForKey":["fieldA","fieldB"]. This will replace all existing records in Knowi with the same fieldA and fieldB with the current data and insert records where they are not present.