Logo

Tag Archive for soql

Wait for Dataflow to Run? Ain’t Nobody Got Time for That! Build a SOQL Step for Real-time Queries

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": []
 }
Einstein Analytics SOQL Step Binding by Year

Toggled by Year

Einstein Analytics SOQL Step Binding by Month

Toggled by Month

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).

Einstein Analytics SOQL Step Create Static Step

Create Static Step Through UI

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!

Analytics Up! A Primer on Riding the Salesforce Wave

Riding The Salesforce Wave - SFDCr

Salesforce Wave Analytics – An Introduction

So you want to ride the Wave, ahem build some really cool stuff in Salesforce’s Analytics product?  Great!  I’m so excited about the future of Wave.  It’s a joy to work in and it’s smooth visualizations and speedy updates allow for fast insights.

In this post, I am not going to spend a lot of time covering the already well covered basics of how to build Datasets, Lenses, & Dashboards within Wave.  Take this Module on Trailhead: Wave Analytics Basics or this one: Wave Dashboard Designer Basics

Or just sign up for a dev org here and get started: developer.salesforce.com/promotions/orgs/wave-de

Coming from a SQL background, what I have been very interested in and have attempted to follow very closely is the ability to edit the SAQL programmatically and do things outside the norms of what is allowed in the UI.  This has taken a few different paths over the years but I’m happy to report that we are now closer than ever before to make it as easy as possible for those who want to get into the code.

Why would you want to get into the code?  Because it allows you to customize the visualizations for your end user in any way that is supported by the product.  A quick example may be that your end user wants to see lost opportunities as a negative amount instead of a positive amount within a bar chart.  Just quickly edit the underlying code and use a Case statement to accomplish this:

That’s Great, Now How Do I Get Into The Code?

CTRL + E or CMD + E

It’s really that simple and it just depends whether you are using WIN/MAC.

Once you are in the code on a particular lens or dashboard, you will be viewing the JSON.

Think of the JSON as a blueprint for the entire dashboard; the queries are just one small part of it.  It defines the datasets, queries, widgets, bindings, colors, all the properties of the dashboard.

Why Is This Awesome?

I love self-contained code like this!  It means that all you need to re-build that dashboard or copy it to another org w/ changes is to copy and paste all of the JSON to a brand new dashboard.  I’m going to go ahead and try it below.  Watch me start with a brand new dashboard and quickly update with all my JSON from a previously created dashboard:

Isn’t that awesome?!  All I had to do was take my custom JSON that I painstakingly worked so hard to edit and paste it into a brand new dashboard.  Now I have rebuilt it and can continue working on a duplicate or test out new customizations.  I especially love this as I like keeping a text backup of different versions in case I need to go back to an earlier revision or reference an old query in a new project.

Query Salesforce Real-time With SOQL

As of the Salesforce Spring ’17 Release, it is now possible to directly query Salesforce objects in a SOQL step:

It’s relatively simple how it works.  Just create a dashboard, create a step for a single chart.  Then edit the JSON to go find that step.  Replace everything within that step with the code below:

"soql": {

  "type": "soql",

  "query": "SELECT Name from ACCOUNT",

  "strings": ["Name"],

  "numbers": [],

  "groups": [],

  "selectMode": "single"

}

Go ahead and type standard SOQL within the Query section.  Use workbench to validate it’s proper SOQL as it’s hard to troubleshoot in Wave.  I was happy to find that it supports column aliasing.  This allows you to rename the labels that will be used within the charts/tables to whatever your end users will best understand.  After you write your SOQL query, make sure to place the alias/column names into the proper ‘property’ as outlined in the table below.  For example, make sure to place your values in the numbers section.

Property Description
type Step type. Set to soql.
label Step label.
query SOQL query. For more information about SOQL queries, see Force.com SOQL and SOSL Reference.
strings Flags the specified fields as non-grouping dimensions. For example, you can flag a field as a dimension for a values table in which no groupings are allowed.
numbers Flags the specified fields as measures.
groups Flags the specified fields as groupings. For example, you can flag a field as a grouping for a pivot table or chart.

Using a table with the SOQL step while writing the query is helpful as it will display data even at times when a chart will not display any results.  The table will help you understand what is broken and what needs to be updated with the query:

Salesforce Wave - Dashboard Table for SOQL

There is practically no documentation on this feature (that I could find) or information on the limits of what you are allowed to do or not.  At first I thought it would not support grouping and aggregation, but I did determine that I was able to as long as I followed standard SOQL grouping rules (cannot group by formula field, cannot group by number field, etc.)  Once I overcame this limitation (workbench was a big help), I was able to write the query below which uses aliases, aggregation, and grouping.  Unfortunately due to the mentioned limitation of not being able to group on a number or formula field, I had to create a string field to store the value as well (two fields to store the same value?!) to group on.  In a production org we would need a trigger to keep them in sync.

 

            "soql": {

                "groups": [

                    "CustRating"

                ],

                "numbers": [

                    "CustomerRating",

                    "NumAccounts"

                ],

                "query": "SELECT CusRatingText2__c CustRating, COUNT(Id) NumAccounts,Min(CusRating__c) CustomerRating from ACCOUNT WHERE CusRating__c > 0 GROUP BY CusRatingText2__c ORDER BY Min(CusRating__c) ASC",

                "selectMode": "single",

                "strings": [

                    "CustRating"

                ],

                "type": "soql"

            },

This is so great, it allows me to query Salesforce directly and not wait for the scheduled Dataflow job to run to pull in fresh data into Wave!

See this example below for how it will pull in live data – look right above the 70 for Customer Rating.  At first it’s missing, then it appears:

All I did was switch the dashboard to edit mode, update an Account in Salesforce, and then switch back to the dashboard and close out of edit mode and it instantly updated the chart to reflect the org change.  This is pretty powerful stuff.

I would like to see the standard ‘refresh’ button within the dashboards actually re-query these SOQL steps.  The reason I went into edit mode and back out in the example above is that the refresh button will not force a refresh and pull in this new point like going into and out of edit mode.  I also would like to see a timed update – imagine being able to set it to re-query in 15-30 second intervals for placing one of these dashboards on a screen within the office that’s always up to date!

Simply hover over the selected point in the chart to see the additional pop-up details:

Salesforce Wave - SOQL Chart 70 Hover Over

Thanks for coming along on this first outing into the surf.  We have only scratched the surface of what is possible by getting under the hood with Wave.  I plan to spend a lot more time in Wave Analytics in the near future and will share my experiences.  What are you interested in or have already discovered with Wave?  I would love to hear about it!  Comment below or tweet them @SFDC_r!