Let’s Query Datasets via APIs!

Published in

CRM Analytics

7 min readJul 5, 2021

At times, users/clients want to query the datasets instead of downloading the data from tables/charts. It is very useful when someone wants to take the data that comes from these queries and use it as a plugin for other systems or automate the process for further analysis.

We are going to use part of the documentation for Salesforce APIs using Postman that was written by Amit Chaudhary. And then use this in conjunction with Tableau CRM APIs! Sounds simple? Let’s check it out.

Create a Salesforce Connected App

To do this, we need to be a Salesforce Admin. We follow these steps to create this Connected App for OAuth.

From www.varonis.com

Salesforce documentation says: “For a client application to access REST API resources, it must be authorized as a safe visitor. To implement this authorization, use a connected app and an OAuth 2.0 authorization flow.”

Now that we know what OAuth means, let’s create the connected app!We will use Salesforce Lightning to create this.

We start this from Setup. Under Quick Find, search for App Manager.

Then create a New Connected App on the top right.

Fill in the following:

Give a name to the Connected App (Chicago Taxi Data API)
Contact email (xyz@gmail.com)
Logo Image URL (Optional)
Icon URL (optional)
Add a description (optional)
Enable OAuth Settings == true (It is a checkbox)
Callback URL: https://login.salesforce.com/services/oauth2/callback (Production) and use https://test.salesforce.com/services/oauth2/callback for Sandbox
Selected OAuth Scopes. In our case it will be Access and Manage Wave Data (wave_api). PSA: Tableau CRM used to be called Einstein Analytics which used to be called Wave.

This is how the final Connection will look before Saving

Now we save this Connected App!

Salesforce provides us with these two very important things

Consumer Key: Shows under API (Enable OAuth Settings)
Consumer Secret: (Click to reveal)

Postman!

Now that we have successfully created a Connected App, we take a look at Postman. Install Postman (Download)!

These are two OAuth Endpoints in Salesforce other than the callback URL that we will need to input into Postman:

OAuth Endpoints in Salesforce
Authorization: https://login.salesforce.com/services/oauth2/authorize
Token Request: https://login.salesforce.com/services/oauth2/token

Note: Use test instead of login if using sandbox.

We start with a new Workspace!

Create Create a Request! Then click on Settings.

Very Important! — DO NOT FORGET THIS

Turn on Follow Authorization Header. By default it is turned off. Without this, the queries will fail “Invalid_Session”!

Next click on Authorization and then OAuth 2.0

Now we configure a New Token

Token Name (optional)
Grant Type: Authorization Code
Callback URL: https://login.salesforce.com/services/oauth2/callback
Auth URL: https://login.salesforce.com/services/oauth2/authorize
Access Token URL: https://login.salesforce.com/services/oauth2/token
Client ID: This is the Consumer Key Salesforce provides in the Connected App
Client Secret: This is the Consumer Secret that Salesforce provides in the Connected App (Copying this adds an extra line at the end. Delete that)

The first time we do this, Postman will ask to login to the Salesforce Org.

Click on Proceed!

Click Use Token

Fun Tableau CRM Query!

Click on Body. Then Raw. The default for Raw is Text which we will change to JSON!

Tableau CRM Query is a POST call instead of GET. Thus we change the GET to POST here.

Documents for Tableau CRM APIs:

This is the complete Tableau CRM API Documentation
And the guide for Query is here.

For Tableau CRM API query, we use /wave/query and the query has to be in SAQL!

The best way to use SAQL without converting every dashboard widget query to SAQL is using Dashboard Inspector! It is a very nifty tool in the dashboard that shows you the compact JSON query in SAQL!

For the final Query, we will use one of the existing Tableau CRM query in Chicago Taxi data (Let’s take the query we created for Conditional Formatting)

Query for Compare Table:

q = load "0Fb6g000000LwXHCA0/0Fc6g000004X0rqCAC";result = group q by ('Pickup_Community_Area__c', 'taxi.Name', 'Trip_Name__c', 'aff.Name');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'taxi.Name' as 'taxi.Name', q.'Trip_Name__c' as 'Trip_Name__c', q.'aff.Name' as 'aff.Name', max(q.'Trip_Total__c') as 'Trip_Total';result = order result by ('Pickup_Community_Area__c' asc, 'taxi.Name' asc, 'Trip_Name__c' asc, 'aff.Name' asc);result = limit result 2000;

The first part of load (0Fb6g000000LwXHCA0) is the dataset id. 0Fc6g000004X0rqCAC is the version id of the dataset. This is also very important! Every time we update a dataset via dataflow/recipe, a new version of the dataset is created. The older version id of the dataset will still work. But… anything new like a field, formula updates will not update in the query if the old one is used.

Ways to find the version id for the dataset:

From the Dashboard Inspector in the dashboard itself.
From Workbench!

Login to Workbench.
Click on Utilities -> REST Explorer
It comes up with part of the API URL: /services/data/v51.0/
Add wave at the end

Click on the one that says datasets and then on the folder thing that says datasets.

Now click on the dataset we need the current version id for. In this case, taxi_trips

3. Using Postman!

Use GET Method and use the URL: https://<instance id>.salesforce.com/services/data/v51.0/wave/datasets/<dataset id>

Another important thing for JSON query in APIs, there cannot be any line breaks in the query! Thus, we remove all line breaks. Thus:

q = load "0Fb6g000000LwXHCA0/0Fc6g000004X0rqCAC";result = group q by ('Pickup_Community_Area__c', 'taxi.Name', 'Trip_Name__c', 'aff.Name');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'taxi.Name' as 'taxi.Name', q.'Trip_Name__c' as 'Trip_Name__c', q.'aff.Name' as 'aff.Name', max(q.'Trip_Total__c') as 'Trip_Total';result = order result by ('Pickup_Community_Area__c' asc, 'taxi.Name' asc, 'Trip_Name__c' asc, 'aff.Name' asc);result = limit result 2000;

IMPORTANT: If there is a line break, we get a JSON_PARSER_ERROR

The type of JSON we use is called query

Since it is JSON, it starts and ends with curly brackets!

We also need to escape the double quotes within the query itself!

Thus, the final version of the query that goes into the body of Postman looks like:

{
"query": "q = load \"0Fb6g000000LwXHCA0/0Fc6g000004X0rqCAC\";result = group q by ('Pickup_Community_Area__c', 'taxi.Name', 'Trip_Name__c', 'aff.Name');result = foreach result generate q.'Pickup_Community_Area__c' as 'Pickup_Community_Area__c', q.'taxi.Name' as 'taxi.Name', q.'Trip_Name__c' as 'Trip_Name__c', q.'aff.Name' as 'aff.Name', max(q.'Trip_Total__c') as 'Trip_Total';result = order result by ('Pickup_Community_Area__c' asc, 'taxi.Name' asc, 'Trip_Name__c' asc, 'aff.Name' asc);result = limit result 2000;"
}