Einstein Analytics: Demystifying Bindings – Part 2

In the first part of this blog series, we covered the anatomy of a binding by looking at each component. Now, to be honest, most bindings you will write, at least in the beginning, will be using the data serialization functions .asString() and .asObject(), as they cover the most common use cases and is all you need when your query is in compact form. But (yes there is a but here) when we use SAQL queries we have to start including other data serialization functions depending on what we are trying to do.

The Meaning of Data Serialization

Before going into the different functions and how they are used let’s take a step back. It’s important to remember why we use data serialization functions and what purpose they have in a binding. If you read the first part of this blog series you might remember that data serialization helps change the output of the binding to a format that is used in the query. And if you didn’t read the first part yet I would strongly recommend you do, since this builds on the learnings from that blog. Now when we use compact form the different parts of the query is similar regardless of it being a measure, grouping or filter, which is why we can just use .asString() and .asObject(). However, when we switch to SAQL the syntax of a query is very different and the query is expecting the bindings to deliver the input in a very specific format, hence why we have other data serialization functions we can leverage. If you are finding this blog post interesting I assume you have knowledge of SAQL but for clarity let’s have a look at a sample SAQL query. Notice I have used the standard DTC Opportunity dataset from the Trailhead org in case you want to generate the examples yourself.

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;

As you can see it does have a specific syntax and in order for our binding to work we need to make sure the syntax is followed.

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 quickly refresh a binding is structured as follows:

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

.asWhat()

So which functions do we have available? Well, .asWhat() is definitely not one of them, but there is quite a few in order to support all part of a SAQL query. If you look at the documentation in addition to .asString() and .asObject() you’ll see that we can use:

• .asDateRange()
• .asRange()
• .asEquality()
• .asGrouping()
• .asProjection()
• .asOrder()

In this part let’s have a look at examples of how we can use bindings in our SAQL filters, we will save the rest for another blog.

Filter bindings

Most SAQL queries start with filters, so let’s have a look at the options we have there; .asDateRange(), .asRange() and .asEquality(). All bindings start with ‘q = filter q by’ followed by the filter logic, it is the filter logic which we will replace with a binding. Depending on the type of filter we can use one of the mentioned data serialization functions. Each one of my sample bindings is for simplicity using a static step as a source and for transparency, the values of the static step will be displayed in the sample. Hence the user needs to pick a value where the output for a selection is just one column and row, which means I can use the cell() as the data selection function and I, of course, need to use a selection binding and not a result binding.

The Date Binding

The date filter in SAQL looks something like below. As you can see we need to define the date components and the start and end period.

q = filter q by date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day') in ["1 year ago".."1 year ago"];

In order to get that specific format we can use the .asDateRange(). As you can see below the static step that I am using will contain a start (min) and end (max) period, which will be used in the binding.

"values": 
[

{

"display": "CY",

"value": 
[

"current year",

"current year"

]

},

{

"display": "LY",

"value": 
[

"1 year ago",

"1 year ago"

]

}

]

Let’s have a close look at how the binding would look like, as you can see the only change is in the data serialization, which is highlighted below.

q = filter q by {{cell(StaticFilterDate_1.selection, 0, "value").asDateRange("date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day')")}};

In the parentheses after .asDateRange, we need to define the date field we need to match the time period with. In a date filter without a binding you will notice that we are using the date components Year, Month and Day and we are using date() to join these components together in a full date field. So we need to add that string of date(‘Year’, ‘Month’, ‘Day’) into the parentheses and of course, remember to escape the string like the example above.

The Range Binding

Adding a filter on a measure where we use the ‘between’ option would In SAQL look like below.

q = filter q by 'Amount' >= 2910928 && 'Amount' <= 8577295;

This type of filter we have a min and a max value just like with the .asDateRange() data serialization function. However, this is not a date but a measure so you can see the syntax is a little different, which also means we have a different data serialization function we can use, which is .asRange(). In the below static step, you will see we have a min and a max value for each selected value.

"values": 
[

{

"display": "Low",

"value": 
[

0,

5000000"
]

},

{

"display": "High",

"value": 
[

5000000,

10000000

]

}

]

Looking at the actual binding and data serialization it should look like below.

q = filter q by {{cell(StaticFilterRange_1.selection, 0, "value").asRange("Amount")}};

Similarly to the date binding we need to define the field we want to apply the range to by adding the API name of that field within the parentheses – in this case, ‘Amount’. Also in this example, we need to escape the string which is why we are using the backward slashes and double quotes.

The Equality Binding

The most commonly used filter is the equality filter where one field or column is equal to a specific value. If we look at a SAQL example that could look like below.

q = filter q by 'Account_Type' == "Customer";

The way we ensure the correct format for the binding is to use the .asEquality() data serialization. My static step only needs to contain the value I want to pass on in my binding. You can see the values below.

"values": 
[

{

"display": "Account Type: Partner",

"value": "Partner"

},

{

"display": "Account Type: Customer",

"value": "Customer"

}

]

The binding we need to use is looking something like below.

q = filter q by {{cell(StaticFilterDim_1.selection, 0, "value").asEquality("Account_Type")}};

Just as the two previous examples you will notice that we are just adding the API name of the field we want to match our value with within the parentheses, which is called ‘Account_Type’. Like the other examples, we also need to escape this string.

Range Bindings Without a Static Step

As mentioned for simplicity I used static steps as my source values, however, you can, of course, use other step types as well. When you are looking at binding ranges like with dates and measures we are looking for a min and a max value in order for it to work. Now in the static step, we have combined min and max as one and called it value, which means we are able to use cell() as the data selection function. In other use cases, you may want to use the specific widgets for dates and ranges, in this case, the output would be two values or columns ‘min’ and ‘max’, which we would need to use in our binding. Since we need to use both columns we cannot use the cell() data selection function and instead we need to use the row() data selection function.

We didn’t cover the row() data selection function in the previous blog post, however, the components are somewhat the same. Below you will see how the binding would look like for both .asDateRange() and .asRange(). The highlighted parts are showing the difference from before where we used cell() as the data selection function and a static step as the source.

{{row(Close_Date_1.selection, [0], ["min", "max"]).asDateRange("date('Close_Date_Year', 'Close_Date_Month', 'Close_Date_Day')")}}

{{row(Amount_1.selection, [0], ["min", "max"]).asRange("Amount")}}

When we are using the row() data selection function we need to define which row(s) we want to take data from, which is done right after the binding function. In the example we are using [0], meaning we are taking the very first row. We could define multiple rows separating them with a comma or leave the array blank including all rows in the binding.

Following the row selection, we need to define the columns or fields we want to take. Since the output of a range and a date widget is ‘min’ and ‘max’ we are adding those values in the array and of course escaping the values. Other than that the binding stays as when we used static steps and the cell() data selection function.

This is how you can use the data serializations .asDateRange(), .asRange() and .asEquality() to make your SAQL query dynamic.

In the third part of this blog series, we will be looking at .asGrouping() and .asProjection().

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