analytics

Analytics Up! A Primer on Riding the Salesforce Wave

Riding The Salesforce Wave - SFDCr
Posted by SFDCr

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!

 

Related Post

Leave A Comment