asdfasdfsadf

Salesforce

Knowi enables data discovery, visualization, data manipulation, warehousing and reporting automation from Salesforce, along with the ability to merge that data with other data stores.

If you are not a current Knowi user, check out our Salesforce Instant Reporting page to get started.

Connecting

The following GIF image shows how to connect to Salesforce. Salesforce Connect

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

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

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

Queries & Reports

  1. 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 plugin your own SOQL queries and optionally post-process the data with Cloud9QL. Salesforce Query

    Report Name: Specify a name for the report.

    SOQL : Enter SOQL Query, or generate using the Data Discovery/Query Generator section.

    Cloud9QL Query: Optional syntax that can be applied to the results of the Salesforce data to further transform the results. Example:

    Data Discovery

    The optional Query Generator section enables data discovery of Salesforce Objects/fields and generate queries dynamically.

    Objects : List of Salesforce objects on your account. Type of select one.

    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.

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

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

  4. Click 'Preview' to see the results.

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

  6. 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.
queryStr Optional Cloud9QL query to manipulate SOQL data. See Cloud9QL docs
frequencyType 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 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 the current data and insert records where they are not present.

Datasource Example:

[
  {
    "name":"cloud9SF",
    "datasource":"salesforce",
    "authAccessToken":"00Di0000000HJa3!AQcAQLEG__Lx0XcKd1fTES_n_zw3OTDEFFQ_2fBzKQ3V_ccEgXGMSD60CKzdF0DgEOoy2bbVQOVB97mEiHWTkwGdE_mXOEjw",
    "authRefreshToken":"5Aep861z80Xevi74eVqb2OUstfLXdMKZCjxN.k1M7S9yMirMNThMTqGHYpbTcfHkI_ykPYfBMwjsHRYhNov1J",
    "sfInstanceURL":"https://na15.salesforce.com"
  }
]

Query Example:

[
  {
    "entityName":"Salesforce Cases",
    "dsName":"cloud9SF",
    "sfSOQuery":"SELECT count(id), account.name, Priority, CALENDAR_MONTH(createdDate) from case where createdDate >= LAST_N_QUARTERS:4 and account.name != '' group by  priority, account.name, CALENDAR_MONTH(createdDate)",
    "c9QLFilter":"select expr0 as Number of Cases, Name, Priority, month(expr1) as Date where name is not null",
    "overrideVals":{
      "replaceValuesForKey":["Date"]
    }
  }
]