Learning the Salesforce Analytics Query Language (SAQL)

Salesforce Einstein offers its own query language to retrieve your data from various sources, called Salesforce Analytics Query Language (SAQL). The lenses and dashboards in Einstein use SAQL behind the scenes to manipulate data for meaningful visualizations. In this article, we see how to use Salesforce Analytics Query Language effectively.

Using SAQL

There are the following three ways to use SAQL in Einstein Analytics:

• Creating steps/lenses: We can use SAQL while creating a lens or step. It is the easiest way of using SAQL. While creating a step, Einstein Analytics provides the flexibility of switching between modes such as Chart Mode, Table Mode, and SAQL Mode. In this chapter, we will use this method for SAQL.
• Analytics REST API: Using this API, the user can access the datasets, lenses, dashboards, and so on. This is a programmatic approach and you can send the queries to the Einstein Analytics platform. Einstein Analytics uses the OAuth 2.0 protocol to securely access the platform data. The OAuth protocol is a way of securely authenticating the user without asking them for credentials. The first step to using the Analytics REST API to access Analytics is to authenticate the user using OAuth 2.0.
• Using Dashboard JSON: We can use SAQL while editing the Dashboard JSON. We have already seen the Dashboard JSON in previous chapters. To access Dashboard JSON, you can open the dashboard in the edit mode and press Ctrl + E.

The simplest way of using SAQL is while creating a step or lens. A user can switch between the modes here. To use SAQL for lens, perform the following steps:

1. Navigate to Analytics Studio | DATASETS and select any dataset. We are going to select Opportunity here.
2. Click on it and it will open a window to create a lens.
3. Switch to SAQL Mode by clicking on the icon in the top-right corner, as shown in the following screenshot:

In SAQL, the query is made up of multiple statements. In the first statement, the query loads the input data from the dataset, operates on it, and then finally gives the result. The user can use the Run Query button to see the results and errors after changing or adding statements. The user can see the errors at the bottom of the Query editor.

SAQL is made up of statements that take the input dataset, and we build our logic on that. We can add filters, groups, orders, and so on, to this dataset to get the desired output. There are certain order rules that need to be followed while creating these statements and those rules are as follows:

• There can be only one offset in the foreach statement
• The limit statement must be after offset
• The offset statement must be after filter and order
• The order and filter statements can be swapped as there is no rule for them

In SAQL, we can perform all the mathematical calculations and comparisons. SAQL also supports arithmetic operators, comparison operators, string operators, and logical operators.

Using foreach in SAQL

The foreach statement applies the set of expressions to every row, which is called projection. The foreach statement is mandatory to get the output of the query. The following is the syntax for the foreach statement:

q = foreach q generate expression as 'expresion name';

Let’s look at one example of using the foreach statement:

1. Go to Analytics Studio | DATASETS and select any dataset. We are going to select Opportunity here.
2. Click on it and it will open a window to create a lens.
3. Switch to SAQL Mode by clicking on the icon in the top-right corner. In the Query editor you will see the following code:

q = load "opportunity";

q = group q by all;

q = foreach q generate count() as 'count';

q = limit q 2000;

You can see the result of this query just below the Query editor:

4. Now replace the third statement with the following statement:

q = foreach q generate sum('Amount') as 'Sum Amount';

5. Click on the Run Query button and observe the result as shown in the following screenshot:

Using grouping in SAQL

The user can group records of the same value in one group by using the group statements. Use the following syntax:

q = group rows by fieldName

Let’s see how to use grouping in SAQL by performing the following steps:

1. Replace the second and third statement with the following statement:

q = group q by 'StageName';

q = foreach q generate 'StageName' as 'StageName',

sum('Amount') as 'Sum Amount';

2. Click on the Run Query button and you should see the following result:

Using filters in SAQL

Filters in SAQL behave just like a where clause in SOQL and SQL, filtering the data as per the condition or clause. In Einstein Analytics, it selects the row from the dataset that satisfies the condition added.

The syntax for the filter is as follows:

q = filter q by fieldName 'Operator' value

Click on Run Query and view the result as shown in the following screenshot:

Using functions in SAQL

The beauty of a function is in its reusability. Once the function is created it can be used multiple times. In SAQL, we can use different types of functions, such as string  functions, math functions, aggregate functions, windowing functions, and so on. These functions are predefined and saved quite a few times. Let’s use a math function power.

The syntax for the power is power(m, n). The function returns the value of m raised to the nth power. Replace the following statement with the fourth statement:

q = foreach q generate 'StageName' as 'StageName',

power(sum('Amount'), 1/2) as 'Amount Squareroot',

sum('Amount') as 'Sum Amount';

Click on the Run Query button.

We saw how to apply different kinds of case-specific functions in Salesforce Einstein to play with data in order to get the desired outcome.

[box type=”note” align=”” class=”” width=””]The above excerpt is taken from the book Learning Einstein Analytics, written by Santosh Chitalkar. It covers techniques to set-up and create apps, lenses, and dashboards using Salesforce Einstein Analytics for effective business insights. If you want to know more about these techniques, check out the book Learning Einstein Analytics.[/box]