Bypass Dataflow Schedule Using Real Time SOQL Queries

Have you ever wished you could display time-sensitive widgets on an Einstein Analytics dashboard that update in real-time?  By using SOQL query steps, you can avoid having to wait for the dataflow to finish running during the next regularly scheduled refresh.

How To Create A SOQL Step

I recommend using workbench.developerforce.com to help craft the SOQL step if you are not familiar with SOQL.  There is a bunch of good information out there on how to format your query including aggregate functions and using ‘rollup’ with grouping.  After you know the general query you need, create a new dashboard (blank dashboard).

You have a couple options to create the SOQL Step.  You could create a standard step using the UI and then jump into the JSON to convert it to a SOQL step, or you could just paste code to create a new SOQL step manually.  We are going to use the latter method here.  After following this guide, however, you will have all the skills you need to use the former method when you need to build from scratch.

CTRL/CMD+E to get into the JSON and locate this line:

 "steps": {},

Paste the following code in-between the curly braces on that line:

"soql": {
 "groups": [
 "CloseDate"
 ],
 "numbers": [
 "sumAmount",
 "NumOpps"
 ],
 "query": "SELECT sum(Amount) sumAmount, CloseDate CloseDate, COUNT(Id) NumOpps from Opportunity WHERE IsWon = True AND CloseDate > 2018-12-31 GROUP BY CloseDate ORDER BY CloseDate ASC",
 "selectMode": "single",
 "strings": [
 "CloseDate"
 ],
 "type": "soql"
 }

You should now be able to click “Done” button and navigate back to the dashboard designer.  You can drag on a chart of table widget and then drag over the new “soql” step you created on top of the chart to visualize the data that is pulled.

What did we create?  We created a step that pulls won opps with a close date post 2018-12-31. You may need to adjust this date as needed to pull results and/or ensure you are not pulling such a large range to have performance impacts.  You can change this query as needed to fit your situation.  You can even use relative dates like “= Today”.  Here is one that is grouped by year and provides the count and avg amount for opps by year.  Rollup also includes a total row for the entire range pulled.

"soql": {
 "groups": [
 "CloseDate"
 ],
 "numbers": [
 "avgAmount",
 "NumOpps"
 ],
 "query": "SELECT avg(Amount) avgAmount, calendar_year(CloseDate) CloseDate, COUNT(Id) NumOpps from Opportunity WHERE IsWon = True GROUP BY rollup (CloseDate) ORDER BY CloseDate ASC",
 "selectMode": "single",
 "strings": [
 "CloseDate"
 ],
 "type": "soql"
 }

How To Add Bindings/Interactivity

Does using a real-time step query prevent you from building in user interactivity?  No way! You can add Bindings – even in a SOQL step!

In the following example, we are able to toggle between a Year grouping and a Month grouping by using a related “groupToggle_1” static step.  This pillbox toggle uses the static step, and based on the user’s selection passes in one of two values into the “soql” step.

 "groupToggle_1": {
 "broadcastFacet": true,
 "label": "groupToggle",
 "selectMode": "singlerequired",
 "start": {
 "display": [
 "Year"
 ]
 },
 "type": "staticflex",
 "values": [
 {
 "display": "Year",
 "value": "calendar_year(CloseDate)"
 },
 {
 "display": "Month",
 "value": "calendar_month(CloseDate)"
 }
 ]
 },
 "soql": {
 "groups": [
 "CloseDate"
 ],
 "numbers": [
 "avgAmount",
 "NumOpps"
 ],
 "query": "SELECT avg(Amount) avgAmount, {{cell(groupToggle_1.selection,0,\"value\").asString()}} CloseDate, COUNT(Id) NumOpps from Opportunity WHERE IsWon = True GROUP BY rollup (CloseDate) ORDER BY CloseDate ASC",
 "selectMode": "single",
 "strings": [
 "CloseDate"
 ],
 "type": "soql",
 "start": []
 }

If you haven’t built a static toggle step before for this, just click the big “Create Step” button from the dashboard designer and choose “Create a Static Step with Custom Values” from the bottom of the prompt.  Then, just fill out the information you need (you can always change it later in the JSON).

Visualize Data As Chart Or Table

After you create your step and any associated bindings, format your visualization!  This is the part that you know and love about Einstein Analytics.  Modify the formatting of the chart or table for the best representation of the data you pulled.

There are a variety of use cases for this:

• Today’s Orders
• Today’s Activities – Calls/Meetings/etc.
• Today’s Closed Opps
• Today’s Cancellations
• Today’s Approval Requests
• Today’s New Opps
• Today’s Urgent Cases

Anything that may be created or change in-between dataflow rebuilds that needs to be displayed or highlighted for your end user is a potential to use SOQL steps.  Primarily I could see this as being valuable for current intraday data.  What use cases have you found?  Where have you gotten stuck?  I would love to hear how this has worked for you in the comments!