Logo

analytics

My Wave…Ahem, Einstein Analytics Wish List PLUS A Bug!

SFDCr - Salesforce Ideas

I’ve been thinking about Einstein Analytics, its current feature set and the gaps impacting my day to day.  I thought I would list some of those here, in no particular order.  I’m sure I am forgetting some, so remind me in the comments and I can add as I think of more!

Alright…I know everyone doesn’t get a pony!  But we can try?

Edit Steps w/ Bindings In UI Outside of JSON

Currently, we still edit these steps with static bindings…but we do a lot of “\n” \” etc…

Edit SAQL & Still Use UI

Being able to edit the groupings, measures, filters and other features in the UI after making an edit to the SAQL would be a huge win.  You can always go back to before you made that change but currently it makes you think twice about the order of operations.

Allow “Locked” Containers

Imagine a container that is ‘frozen’ (just like “freeze panes” or locking the top row in Excel) and anything that you placed in that container – Titles, Pillbox, Filters would remain glued to the area of the dashboard that you selected.  It would be ‘immune’ from scrolling.  This would allow users the ability to scroll down to view other dashboard widgets but always have the filters/pillboxes to toggle from within easy reach.

Multi-Line Commenting

If I have to ‘dash-dash’ another consecutive line…

Advanced Filter Logic (And/Or) Outside of SAQL

Sure, AND (((we AND can) OR (do AND the)) OR advanced) AND in AND SAQL AND (but OR why OR not OR make OR it OR easier?)

This includes being able to select (in the UI) the same field to filter for two or more conditions!

Allow The Use of Bindings to Toggle Images

This would allow the limit of 20 pages to go a lot farther.

Bug – Incrementing After Mouse No Longer Clicking

I have to list this bug as I realized this is not yet resolved even as of Winter ’19 in my pre-release org.  As you can see below, you can click the mouse once on the arrows and it will continue to increment even after ‘mousing up’ during hover-over…while the mouse is no longer clicking!

Salesforce Analytics SAQL Join Multiple Datasets

Want to combine more than one Salesforce Wave datasets into the same chart widget in SAQL?  Well, you’ve come to the right place.  This is something that practically no one is discussing and it’s easy to feel like you’re on your own!

Additionally, In my SAQL experience I’ve determined that it’s all about the bindings!  Salesforce has a good guide but it could contain additional information.  It’s also easy to get confused between the classic dashboard/bindings and the new & improved bindings.

Today we are going to cover two primary objectives: (1) Combine at least two datasets into the same stream/chart widget. (2) Create a static step to present a variety of fixed options that will be fed into the query from #1 as a parameter that will control the results and make the dashboard interactive!

The Data

We are going to use data downloaded from the US Government – thrilling!

Dataset 1: US Population by State from 2010 Census

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

Dataset 2: Top 10 Causes of Death by State (Years 1999-2013)

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

With Dataset #2 we can do some really cool things like a donut to show the # of deaths by cause for a single year:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

We can also use a timeline chart to see the # of deaths by cause over time:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

The reason we want Dataset #1 is that some states have more people living in them than others (shocking, I know).  We want to take the # of deaths by state in 2010 and divide by the population of each state in 2010 to calculate deaths per capita.

Combining Multiple Datasets Into Single Widget

The primary rule with joining more than one dataset into a single SAQL query/widget/chart is that you have to use a dashboard.  You cannot do this within a single Lens.  A Lens is explicitly tied to a single dataset.

We also have to add a place-holder step/chart to the dashboard for both of the datasets we want to access before we can combine both datasets into a single step.

For example, if I just create a new dashboard, drag on a chart widget, click on it and then create a new step and navigate to the SAQL and create the query I want to use, which is this by the way:

q = load "X2010_Census_Population";
q = group q by 'State';
q = foreach q generate 'State' as 'State', sum('X2010_Census_Population') as 'sum_X2010_Census_Population';
q = order q by 'State' asc;
q = limit q 2000;
a = load "Deaths_for_top_10_causes_by_State";
a = filter a by date('YEAR_Year', 'YEAR_Month', 'YEAR_Day') in [dateRange([2010, 1, 1], [2010, 1, 1])];
a = filter a by 'CAUSE_NAME' != "All Causes";
a = group a by 'STATE';
a = foreach a generate 'STATE' as 'State', sum('DEATHS') as 'sum_DEATHS';
a = order a by 'State' asc;
a = limit a 2000;
x = union q, a;
x = group x by 'State';
x = foreach x generate 'State' as 'State', sum('sum_DEATHS') / sum('sum_X2010_Census_Population') as 'Deaths per Capita';
x = filter x by 'State' != "United States";
x = order x by 'Deaths per Capita' desc;
x = limit x 2000;

I get the following error:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

First, some notes on the code above.  We are loading “q” which is our 2010 Population by State Dataset (dataset #1 above) and simply grouping by state and providing the total population by state.  Then we are loading “a” which is our dataset that contains the # of deaths by state by year for the top 10 leading causes.  We are making sure we filter down to just 2010 and filtering out a cause of “All Causes” as these are already included in each granular cause.  Then we are just getting the total # of deaths by state.  We UNION these together (not really much different than a UNION in SQL) as “x” and run the division to get our ‘Deaths per Capita.’  We have to filter out a state that is listed in both datasets as “United States” as again, just like the “All Causes” scenario, these totals are already included within each individual state.

Here’s how you can use the same code (as above) but not get this error: “You can’t use the following datasets in this query because they aren’t included in the dashboard:”

Step 1: Drag a chart widget onto the dashboard, then click into the middle of the widget where it says “Chart.”

Step 2: Select one of the datasets you want to access in your query.

Step 3: Wave ahem, Einstein Analytics will have a default chart for Count of Rows.  You don’t need to change anything here – just click “Done.”

Step 4: Now click on the “Create Step” button in the right column of the Dashboard builder.  If it isn’t displaying, ensure you are in “Edit” mode (a Pencil Icon will be displaying in the buttons at the top right if you are not in Edit mode, click this button).  If you are in edit mode and you do not see “Create Step” then click somewhere on the background of the dashboard.

Step 5: Now in this step select the other dataset that you need, the one you didn’t select in Step 2.

Step 6: Here is where you can switch to the SAQL mode and write your query using data from both datasets and then join them together either using UNION or COGROUP.
SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

Click “Run Query” to make sure it’s working how you need it to and then click “Done” to finish creating the step.

Now you can use this step for your chart widgets (Drag and drop the step you just created) and delete the first chart widget and step you had to create in steps 1-3.  The fact that we have to go through steps 1-3 only to just get rid of them is unexplainable just for fun!

I went ahead and added a US Map widget in addition to the column widget.  The state with the highest deaths per capita (from the top 10 causes of death) in 2010 was….drum roll please….West Virginia!

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

Now we have a step that is accessing data from both datasets and providing the visualization needed.  Now, how do we add some interactivity?

Bindings for Interactivity

We are going to create a static step to present options to our dashboard user and a pillbox/toggle for the user to click on to input their selection.  Finally we are going to take the result of that input and update our main query step (previously created above) to change the widget based on this input.

We can make a static binding for anything we want.  Red/Blue, Avg/Total, East/West.  We are simply defining the text that will present to our users and the parameter value we are going to include in our query.

Let’s start with East/West.

The easiest way to create a static step is to CMD+E / CTRL+E into the JSON and locate where all the Steps are defined.  This image displays where the steps begin and the first step:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

Place your cursor before the first step and create some line breaks.  Then we will use the following code to create our static step.

 

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 "toggle_states": {
   "datasets": [],
   "dimensions": [],
   "groups": [],
   "isFacet": false,
   "isGlobal": false,
   "numbers": [],
   "selectMode": "single",
   "strings": [],
   "type": "staticflex",
   "useGlobal": false,
   "values": [
             {
         "display": "West",
         "value": [
         "West"
             ],
         "Region": "West"
             },
             {
         "display": "East",
         "value": [
             "East"
             ],
         "Region": "East"
              }
         ]
 },

The name of the static step is defined first, you could set this as anything you want.  Then we are just adding two values “West” and “East” and labeling them “Region” – this again could be anything you want to label it!  We also have ‘value’ and ‘display’ defined to show how you can add multiple properties.  ‘Value’ is the text that will display in our toggle pillbox to the end user.

Note the ‘selectMode’ is “Single.”  This is important as it defines whether we can select more than one at a time, whether one has to always be selected, etc.  For now we are going to use “Single” which means that one of these options is not required to be selected, but we can’t select more than one at a time.

After this code is entered you can click the “Done” button.  Now drag this step “toggle_states” onto the dashboard.  You will instantly see your two options available to you.  You can even run the dashboard (click the eye icon) and toggle between the two options.  Yes, they don’t do anything to your data yet…but they exist!

Now we are going to edit our original query from before to include input from this toggle step.

Use CTRL+E or CMD+E to get back into the JSON and locate your original step with the query for the deaths per capita.  Here are the groups, numbers, query, and strings for mine after I’ve updated it.  Essentially I’ve used a case statement to group the states into West & East.  It can even detect when the toggle button is not used and return ‘All.’  I’ve then added a filter that uses the input from the toggle step to filter down to only West or East states.

 "groups": [
 "State"
 ],
 "isFacet": true,
 "numbers": [
 "Deaths per Capita"
 ],
 "query": "q = load \"X2010_Census_Population\";\r\nq = group q by 'State';\r\nq = foreach q generate 'State' as 'State', sum('X2010_Census_Population') as 'sum_X2010_Census_Population';\r\nq = order q by 'State' asc;\r\nq = limit q 2000;\r\n\r\na = load \"Deaths_for_top_10_causes_by_State\";\r\na = filter a by date('YEAR_Year', 'YEAR_Month', 'YEAR_Day') in [dateRange([2010, 1, 1], [2010, 1, 1])];\r\na = filter a by 'CAUSE_NAME' != \"All Causes\";\r\na = group a by 'STATE';\r\na = foreach a generate 'STATE' as 'State', sum('DEATHS') as 'sum_DEATHS';\r\na = order a by 'State' asc;\r\na = limit a 2000;\r\n\r\nx = union q, a;\r\nx = group x by 'State';\r\nx = foreach x generate 'State' as 'State', case when \"{{column(toggle_states.selection,[\"Region\"]).asObject()}}\" == \"\" then \"All\" when 'State' in [\"Colorado\",\"California\",\"Utah\",\"Oregon\",\"Washington\",\"Idaho\",\"Nevada\",\"New Mexico\",\"Utah\",\"Wyoming\",\"Montana\",\"Texas\",\"Oklahoma\",\"Alaska\",\"Hawaii\",\"Kansas\",\"Nebraska\",\"South Dakota\",\"North Dakota\",\"Arizona\"] then \"West\" else \"East\" end as 'Type', sum('sum_DEATHS') / sum('sum_X2010_Census_Population') as 'Deaths per Capita';\r\nx = filter x by 'Type' == \"{{column(toggle_states.selection,[\"Region\"]).asObject()}}\" || 'Type' == \"All\";\nx = filter x by 'State' != \"United States\";\nx = order x by 'Deaths per Capita' desc;\nx = limit x 2000;",
 "selectMode": "single",
 "strings": [
 "State"
 ],
 "type": "saql",

Now I can toggle between all:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

West:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

or East:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

There are a lot of cool things you can do with selection bindings to give the user interactivity and control.  In this case we are using the parameter as a filter, but you can use it to change the actual metric.  For example, we could toggle between the calculation of ‘Deaths per Capita’ and ‘Sum of Deaths’.  I’ve even used 3 or 4 separate toggle steps with at least 2 to 3 options each for a single chart widget.  You can combine them to build a full featured interactive dashboard that allows for quick and valuable business insights!

There is so much more you can do with bindings, but because this post was primarily about how to combine multiple datasets in SAQL this was just a small taste.  Stay tuned for more!  Have you had a need to combine multiple datasets?  Has this post been helpful?  What other questions do you have?  I would love to hear in the comments or on twitter @SFDC_r

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!