Einstein Analytics: Demystifying Bindings – Part 3

It’s time for more SAQL bindings in this third part of demystifying bindings. In the first part of this series, we looked at the anatomy of a binding and the second part looked at how to use bindings for filters in a SAQL query, however, there are more parts to a SAQL query, so we will be looking at some more data serialization types in this third part.

A SAQL query

In the previous blog, we looked at the SAQL query below. We will use the same query to cover the next data serialization functions .asGrouping() and .asProjection(). As you might have guessed we need to use these when we want to bind groupings and measures. Each one of my sample bindings is using a static step as a source and for transparency, the values of the static step will be displayed in the sample.

q = load "DTC_Opportunity_SAMPLE"; 
q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["1 year ago".."1 year ago"]; 
q = filter q by 'Account_Type' == "Customer"; 
q = filter q by 'Amount' >= 2910928 && 'Amount' <= 8577295; 
q = group q by 'Industry'; 
q = foreach q generate 'Industry' as 'Industry', sum('Amount') as 'sum_Amount'; 
q = order q by 'Industry' asc; 
q = limit q 2000;

Now it’s important to note that the skeleton of the binding is the same as we covered in the first part of this blog series meaning our binding will still include a data selection function, step name of the source, binding function and details from the reference step. But to refresh a binding is structured as follows:

"{{Data_Selection_Function(step_name.Binding_Function, Details_From_Reference_Step).Data_Serialization_Function()}}"

Group Binding

Doing a group binding in SAQL becomes interesting. If you look at a SAQL query it’s not enough to define a grouping you also have to project it, hence you need to create two bindings in order for it to work. First, let’s have a look at how a grouping will look in SAQL.

q = group q by 'Industry'; 
q = foreach q generate 'Industry' as 'Industry', ...;

As you may have noticed I have not included the projection of a measure in the above example, as we will worry about that part of the query later. But you can clearly see there are two parts to the grouping and we would, therefore, need to use two different data serialization functions – you can read more about what the function does in the second part of this blog series. Regardless as you can see we cannot just focus on the grouping, hence the static step I will be using need to consist of three values, which we will be using in the two bindings we need.

"values": [
{
"display": "Industry",
"value": "AccountId.Industry",
"expression": "'AccountId.Industry'",
"alias": "Industry"
},
{
"display": "Stage",
"value": "StageName",
"expression": "'StageName'",
"alias": "Stage"
}
]

Let’s first look at how the binding will look for the group statement. The group statement starts with ‘q = group q by’ followed by the field to use for the grouping, it is the field which we will replace with a binding.

q = group q by {{cell(StaticGroup_1.selection, 0, "value").asGrouping()}}

As you can see we are using cell() as the data selection function and referencing the ‘value’ field from the static step. The data serialization is pretty straightforward we just use .asGrouping() without any need for references.

The second part of the group binding is a little more complex. Below you will see how it should look.

q = foreach q generate {{row(StaticGroup_1.selection, [0], ["expression", "alias"]).asProjection()}}, ...:

If we remember what the projection does it’s to take a field from the dataset, in this case, our grouping and define how it should be displayed. That essentially means we need to grab two values to use in our binding, which in this case are called ‘expression’ and ‘alias’, which both are escaped strings; ‘expression’ would be the field API name and ‘alias’ is whatever you want the label of the grouping to be. Since we need two values from the static step we will need to use row() as the data selection function, which we looked at in depth in the second part of this blog series. When we are projecting fields we will need to use the .asProjection() data serialization function in this function we don’t need to have any additional references.

Measure Binding

If you want to bind a measure you would have to use a similar approach as to a group binding. The only difference is we just need to project this field using the .asProjection() data serialization. If we first have a look at the SAQL you will notice that the measure is part of the same stream we use for projecting the group we have just separated the two with a comma.

q = foreach q generate 'Industry' as 'Industry', sum('Amount') as 'sum_Amount';

Now looking at the static step you will also see here that we are bringing in two values for each selection; ‘expression’ and ‘alias’.

"values": [
{
"display": "Sum Amount",
"expression": "sum('Amount')",
"alias": "sum_Amount"
},
{
"display": "Avg Amount",
"expression": "avg('Amount')",
"alias": "avg_Amount"
}
]

As I mentioned above, when we need to do a measure binding we will need to use the same data serialization function as when we are projecting a grouping. Hence we need the same type of information from our static step. The only difference is that our measure, of course, needs to include what type of calculation we need to apply. This also means that the binding is exactly the same.

q = foreach q generate ..., {{row(StaticMeasure_1.selection, [0], ["expression", "alias"]).asProjection()}};

A deeper look at the static step

A static step can, as you have seen, include multiple values, which mean you can control multiple selections with one click. We have used this approach in the group and measure binding. Looking at the static step for the group we have “value”, “expression” and “alias” each representing different parts of the query. The “value” we used for the field we want to group by hence the API name. The expression is again the API name of the grouping whereas the alias is what we want the grouping to be shown as in the chart.

Looking at the static step for the measure binding we don’t need the value. However, we have kept the expression and the alias. The expression is the aggregation we want to use and the API name of the field where the alias is again how we want that field to be displayed in the chart. Note that we can, in fact, remove “sum_”, but it’s recommended to keep if we want to keep the number display.

When you are using static steps for group and measure bindings in SAQL I can recommend keeping the naming convention of expression and alias. It makes it easier to differentiate how the two values should be used in a binding.

columnMap is still a thing

Finally, do remember when you are using group and measure binding regardless of it being in compact form or SAQL, we still have that thing called columnMap. If you want your binding to work you should familiarize yourself with this parameter, read more about in this blog.

Next up in this blog series we will be looking at date bindings.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts