Live Dashboards anyone?

Salesforce Direct and SOQL

In Winter ’20, the Einstein Analytics team added Salesforce Direct to Einstein Analytics. This is a huge win for anyone who needs at least some parts of dashboards to show live data. Before Salesforce Direct, the way to create live dashboards were using SOQL as a query step in JSON. This still exists but with Salesforce Direct, we can get part of the way to the SOQL query before needing to turn it to SOQL, if at all. The beauty of this is, anyone who doesn’t know SOQL or isn’t very familiar with how it works can now create live dashboards!

In this blog, we will learn how to create Salesforce Direct and SOQL dashboards along with all the caveats that exist. The focus will be more on the SOQL part of the dashboard though. For more on Salesforce Direct, please refer to Rikke Hovgaard’s blog on Salesforce Direct.

Data

As with other blog posts in this publication, we will use the Taxi Data. We create custom objects and fields in Salesforce to use Salesforce Direct / SOQL. The schema builder is presented here:

Schema Builder for the Taxi Data

Then we load the data to the objects and fields to create new records.

Taxi Data Snapshot

Salesforce Direct

Now, we go to Analytics Studio, create a new dashboard and name the dashboard. Let’s call it “Trips Taxis take in Chicago” and save it in an appropriate app! Now, change the layout of the dashboard to your liking. You can change the name of the layout, columns, layout, row height, cell spacing, background color, etc.

It is a good practice to always name the page we are working on. In case, we create multiple iterations of the same page, these provide an easy method to figure out what the page was created for. Let’s name the first page “Home”.

Now, let’s add 2 containers to the dashboard. Containers help in moving widgets easier. In this case, we will use 1 container for filters and the other for charts.

Next, we will start by using Salesforce Direct on the Trips Object. To do this, click on Create Query, then select Salesforce Direct and then Trips (Trips__c).

Salesforce Direct — Trips Object

At first glance, we see that there are 195K rows of data. Now, we add groups, measures, and filters in the appropriate sections of the lens.

Measures: Sum of Trip Totals Reported and Sum of Miles

Groups: The first two groups are Taxi__c (Taxi Id in other words) and Pick Up Community Area. Next, we will add a date-time field which in this case is Start Time. Note that unlike dataflow/recipe dates, you can group by Year, Quarter, Month, Week, Day only. Groups for Year-Quarter, Year-Month, etc. is not available. In this case, we will select Day.

Measures and Groups

Name the widget (Trips). And click Done.

We now want to add filters to this dashboard. Let’s filter by Taxi Affiliation and Pick Up Community Area. To do this, we pull the list widget on to the dashboard and then use Salesforce Direct to populate these widgets. Taxi Affiliation comes for the Taxi Affiliation object and Pick Up Community Area from Trips Object. Format the filter widgets:

• Update instantly = false
• Measure field = none
• Show widget actions = false
• Apply Global Filters = false (Salesforce Direct / SOQL don’t work with Global Filters yet)
• Selection Type = Multiple Selection Required for Pickup Community Area and Single-Selection Required for Taxi Affiliation. (We will see why this later)

Add the Trips widget to the dashboard and change the chart type to Combo with these settings:

Combo Chart specifications

Salesforce Direct Queries

When we select Multiple Selection Required, the filter selects the first one on the list. As opposed to static datasets, Salesforce Direct, Pick initial values doesn’t work. So, we have to manually select the filters each time we want to interact with the dashboard.

If we interact with the dashboard now, we will see that Taxi Affiliation doesn’t affect the Trips dataset because it is not a part of the widget. But, Pickup Community Area interacts.

For Taxi Affiliation to interact with Trips widget, we need to convert Salesforce Direct to SOQL. To do this, edit the widget and click on the Query Mode on the top right of the lens and add a space at the end of the query. We just need to convert this to SOQL and when we add a space, it allows us to “Run Query” changing the format to SOQL instead of Salesforce Direct. Thus, click “Run Query”. Then click Update to update the widget. Once we click on Query, Salesforce Direct becomes a SOQL statement.

This is how the query looks initially. As we can see, this is pure SOQL that we write in Workbench or inside Apex classes.

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) day_Start_Time__c, SUM(Total_Reported__c), SUM(Miles__c) FROM Trips__c GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

Trips have a lookup to Taxi and Taxi has a lookup to Taxi Affiliation as we saw above in the Schema Builder. We will use this relationship to filter the Trips widget with the Taxi Affiliation filter we had created.

In SOQL when we write a simple filter with one static value, we use:

WHERE field1 = 'some_value'

Similarly, when we have to use multiple static values, we use:

WHERE field1 IN ('value1', 'value2', 'value3')

Note the parenthesis and the single quotes. When performing multi-select in SOQL, we will need to replicate this statement.

Here, we will use the same concepts but make it dynamic.

Let’s start with one static value which in our case means Taxi Affiliation filter set to Single-Selection (Required). To do this, we write our usual Einstein Analytics bindings for SAQL. We do this in JSON. To go to the JSON mode, press Ctrl/Cmd+E and find the widget. In this case Trips_1. This is the WHERE clause we add to the query. The WHERE clause comes after FROM clause.

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) day_Start_Time__c, SUM(Total_Reported__c), SUM(Miles__c) FROM Trips__c WHERE Taxi__r.Taxi_Affiliation__r.Name = '{{cell(Affiliations_1.selection, 0, \"Name\").asString()}}' GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

This WHERE clause says, lookup (relation) from Taxi object to Taxi_Affiliation object’s Name (thus __r instead of __c) and match it to the first selection of the Affiliations_1 filter widget. Notice that we have single quotes before and after the binding. This is required for SOQL to read it as a string as we saw in the static example.

In addition to that, we will add names to the measure. Otherwise, it will show as expr_0, expr_1 etc. The updated query looks like:

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) Day, SUM(Total_Reported__c) Total_Reported, SUM(Miles__c) Miles FROM Trips__c WHERE Taxi__r.Taxi_Affiliation__r.Name = '{{cell(Affiliations_1.selection, 0, \"Name\").asString()}}' GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

Notice that even though, we named DAY_IN_MONTH(Start_Time__c) as Day, in the Group By clause we still use DAY_IN_MONTH(Start_Time__c). SOQL doesn’t recognize Day or any other coined name in the GROUP BY clause.

SIngle selection SOQL Widget Interaction

For dynamic multi-select WHERE clause, we will use the Pickup Community Area filter.

To add more filters to the WHERE clause we use logical espressions like AND/OR. In this case, we will use AND. Pickup Community Area is part of the Trips object, thus, we don’t have to traverse the relationship tree of the objects in this case.

Note that even though Pickup Community Area is already part of the Trip Object, it doesn’t work with the chart widget because it has been converted to SOQL. Converting a widget to SOQL greys out the Faceting for that widget. Thus, we need to add all filters that we want the chart or other widgets to facet with the WHERE clause.

SELECT Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) Day, SUM(Total_Reported__c) Total_Reported, SUM(Miles__c) Miles FROM Trips__c WHERE Taxi__r.Taxi_Affiliation__r.Name = '{{cell(Affiliations_1.selection, 0, \"Name\").asString()}}' AND Pickup_Community_Area__c IN ('{{join(column(PUC_1.selection, [\"Pickup_Community_Area__c\"]), \"', '\").asString()}}') GROUP BY Taxi__c, Pickup_Community_Area__c, DAY_IN_MONTH(Start_Time__c) ORDER BY Taxi__c ASC, Pickup_Community_Area__c ASC, DAY_IN_MONTH(Start_Time__c) ASC LIMIT 250

Here, we use a few interesting commands in addition to what we already used in single-select to ensure a multi-select WHERE clause will work dynamically.

• AND: Logical expression to append filters.
• join: This joins all strings together.
• column: cell is for single selection, but when we do multi-selection, it is a column we are selecting.
• How do we join the multiple strings?: We use a single quote followed by a comma, space, single quote. And we need the query to understand that the single quote is a string, so we enclose the whole single quote, comma, space, single in double quotes and then escape the double quotes!

Now, we have it — A Saleforce Direct and SOQL Live dashboard that interacts with Single-Selection and Multiple Selections.

Final Salesforce Direct + SOQL Live Dashboard

Acknowledgment: Thanks to my friends who helped me figure out how multi-select can work in SOQL.

Learn more:

• Rikke Hovgaard’s blog on Binding and Salesforce Direct
• Peter Lyons videos