(R)Serve Einstein Discovery Predictions in Tableau
There were many big announcements during Tableau Conference(ish) last year, but one that had the spotlight was bringing together Tableau and Einstein Analytics. Being able to bring Einstein Discovery Predictions into Tableau opens a world of possibilities! For example, you could do something like this:
At conference, we could see an Extension bring those predictions into a Tableau Dashboard; but, what if you want to bring them into a Sheet? What if you don’t want to wait until the official release of an Einstein Calc? Or, for those of you reading in the future, what if you haven’t been able to upgrade to the version that supports it? Read on…
What are we going to do?
In short, we are going to ask Einstein Discovery to make a prediction for the data displayed in a Tableau Viz (a sheet). Once we get it back, we are going to display that prediction in the same viz, side-by-side. We are going to do this using functionality available in any Tableau versions above 8.3: Analytics Extensions (formerly known as External Services).
There are a couple different ways I’d recommend approaching this post:
- If you prefer a high level overview, scroll down and watch the first 5 animated GIFs. Feel free to skip the reading.
- If you prefer a DIY approach, watch the GIFs first, read the whole post, and then dive into completing the prerequisites and steps 1–4.
- If you are somewhere in between, watch the GIFs first and read the whole post; you’ll still want to complete the prerequisites, but I’ve included a link to the workbook so you don’t have build it from scratch.
Prerequisites
- Install Tableau Desktop.
If you don’t have a copy yet, you can download and install a free 15-day trial. - Install R and launch RServe.
The easiest way to do this is to follow the instructions on page 8 of the Using R and Tableau whitepaper. The one difference in newer versions of Tableau Desktop is on step 3.a.: go to the “Help” menu, select “Settings and Performance”, and then select “Manage Analytics Extensions”. - Sign up for a developer org and setup a connected app in Salesforce.
For the latter, I followed the instructions in Step 1 of this blog post, overall that whole document is a good read. Completing step 1 will give you the Client ID and Client Secret that you’ll need to make the API calls. - Deploy an Einstein Discovery Model.
In order to request a prediction, we need to create a model first. Go to Trailhead to learn about Einstein Discovery and how to do this. While I recommend you to go through the whole trail, the only requisite is for you to complete the Create a Story course. Once done, you can deploy your model:
Let’s do it!
Step 1: Build a sheet to test connectivity with the Einstein Prediction Service
We’ll be using the same data set from the Create a Story course. Connect to a Text file, find the file in your machine, and go to Sheet 1. Easy Peasy!
You’ll want to create 4 String parameters: [ClientID], [ClientSecret], [Username], and [Password]. Replace the values of those parameters with your own:
ClientID: The connected app’s (prerequisite #3) Client ID.
ClientSecret: The connected app’s (prerequisite #3) Client Secret.
Username: Your Salesforce username.
Password: Your Salesforce password.
WARNING! Your credentials will be stored in plain text in the Tableau workbook. If you save the file, make sure you keep this workbook in a safe location. You can also set the parameter values to blank before you save the workbook, and update them when you open the workbook again. Remember, this is not the official feature release, you won’t need to worry about this when Einstein Calcs are supported out-of-the-box!
Step 2: Create a calculated field to sign-in to the Einstein Prediction Service
We’ll use this sheet to test we can connect to the Prediction Service. If the test is successful, we’ll receive an authentication token (a string) that we can use to make follow-up calls to the Prediction Service.
Create a calculated field with this formula in it (feel free to copy-paste it into the calculated field in your workbook):
SCRIPT_STR(
//* Code to be executed by R: Login to EA and return a Session token //
"library(httr)
library(jsonlite)
loginInfo ← paste('grant_type=password&client_id=',.arg1[1],'&client_secret=',.arg2[1],'&username=',.arg3[1],'&password=',.arg4[1],sep='')
res=POST(paste('https://login.salesforce.com/services/oauth2/token?', loginInfo, sep=''))
data = fromJSON(rawToChar(res$content))
data$access_token
",
//* Credential Parameters **//
[ClientID],
[ClientSecret],
[Username],
[Password]
)
So, what are we doing here? Let’s break it into smaller chunks:
SCRIPT_STR()
SCRIPT functions allow Tableau to communicate with RServe (or other Analytics Extensions). The expected value returned from RServe is a String, therefore the STR.
library(httr)library(jsonlite)
To communicate with the Prediction Service, we need to use web services and send/receive data via JSON objects. We’re importing 2 libraries (httr and jsonlite) that will help us handle those 2 jobs, respectively.
loginInfo ← paste('grant_type=password&client_id=',.arg1[1],'&client_secret=',.arg2[1],'&username=',.arg3[1],'&password=',.arg4[1],sep='')res=POST(paste('https://login.salesforce.com/services/oauth2/token?', loginInfo, sep=''))
Before we can make any calls to the Prediction Service we need to authenticate. We do this by providing our loginInfo: Client ID, Client Secret, Username, and Password. Who do we provide this to? To the res URL.
data = fromJSON(rawToChar(res$content))
data$access_token
In exchange for valid credentials, we get a successful response (or an error, otherwise): data. This is a JSON object, and the piece from that object we need is the access_token.
[ClientID],
[ClientSecret],
[Username],
[Password]
Last but not least, the parameters. Remember we created 4 parameters in Step 1? It’s their turn now. The .arg expressions within the SCRIPT function map to each parameter’s value : .arg1[1] with [ClientID], .arg2[1] with [ClientSecret], .arg3[1] with [Username], and .arg4[1] with [Password].
Step 3: Create a sheet where a prediction would be useful
Now that we know we can connect to the Prediction Service, let’s put together some data that we could enrich with a prediction.
We’ve setup the Einstein Discovery Model to maximize CLV (Customer Lifetime Value), so we need some customer data; moreover, when we deployed the model we specified that “Division” and “Type” are actionable variables: attributes we can change for a customer (i.e. we can decide the division the customer should work with, but we can’t change if the customer is a private or a public company). So, let’s build a sheet with these fields: drag and drop [Account ID], [Division], and [Type] to rows; drag and drop [CLV] to columns. Filter to the top 20 accounts. Easy Peasy! x2
WARNING! x2
In this example we are using the same data set to train the model and to get predictions for. Why would you want to get a prediction for something you already know happened? You wouldn’t, this is a oversimplification for this post; however, once you can make calls to the prediction service, you can use any data set you want in Tableau!
Step 4: Create a calculated field that gets an Einstein Discovery Prediction
Finally! Let get those predictions into Tableau!
Create calculated field with this formula in it (feel free to copy-paste it into your workbook):
SCRIPT_REAL(
//**** Code to be executed by R: Get an Einstein Discovery Prediction ****//
"library(httr)
library(jsonlite)
##### Login and get a token #####
loginInfo = paste('grant_type=password&client_id=',.arg1[1],'&client_secret=',.arg2[1],'&username=',.arg3[1],'&password=',.arg4[1],sep='')
res=POST(paste('https://na134.salesforce.com/services/oauth2/token?', loginInfo, sep=''))
data = fromJSON(rawToChar(res$content))
token = data$access_token
##### Send a prediction request to a Einstein Discovery Model #####
predictionDefinition=c(.arg5[1])
type=c('RawData')
columnNames=c('Division','Type')
rows = toJSON(data.frame(.arg6,.arg7), dataframe = 'values')
predictionDataFrame = list(predictionDefinition=predictionDefinition, type=type, columnNames=columnNames, rows='dataset')
body = gsub('\\\\', '', toJSON(predictionDataFrame, auto_unbox = TRUE))
body = paste(substr(body,1,nchar(body)-10), rows, '}', sep='')
##### Get the prediction response and parse out the value #####
predictionRequest = POST('https://na134.salesforce.com/services/data/v49.0/smartdatadiscovery/predict', body = body, encode = 'json', content_type_json(), add_headers(Authorization = paste('Bearer', token)))
response = fromJSON(rawToChar(predictionRequest$content))
response$predictions$prediction$total
",
[ClientID], [ClientSecret], [Username], [Password], // Credential Parameters
'1OR4R0000008P15WAE', // Model ID, replace with yours
STR(ATTR([Division])), STR(ATTR([Type])) // Model Parameters
)
Just like last time, let’s break it into smaller chunks:
SCRIPT_REAL(//**** Code to be executed by R: Get an Einstein Discovery Prediction ****//"library(httr)library(jsonlite)
##### Login and get a token #####loginInfo = paste('grant_type=password&client_id=',.arg1[1],'&client_secret=',.arg2[1],'&username=',.arg3[1],'&password=',.arg4[1],sep='')res=POST(paste('https://na134.salesforce.com/services/oauth2/token?', loginInfo, sep=''))data = fromJSON(rawToChar(res$content))token = data$access_token
Yes! You’ve seen this before. This is exactly the same code we used in Step 1 to get an authentication token. We need this token to make the follow-up calls that would get the actual predictions.
##### Send a prediction request to a Einstein Discovery Model #####
predictionDefinition=c(.arg5[1])
You can deploy multiple models in Einstein Discovery. In order to request a prediction from the right one, you need its ID, which you can get from Salesforce as show below (in my case it’s 1OR4R0000008P15WAE):
We’re almost there, but we need to be able pass data from the Tableau viz into the Model. In this example it’s data about the customers we want the predictions for.
type=c('RawData')
columnNames=c('Division','Type')
rows = toJSON(data.frame(.arg6,.arg7), dataframe = 'values')
predictionDataFrame = list(predictionDefinition=predictionDefinition, type=type, columnNames=columnNames, rows='dataset')
The model is expecting 2 actionable variables: Division and Type, this is what we define in columnNames. Then, we’ll get data from the viz in rows via the .arg6 and .arg7 expressions. And, just as if we’re creating a table, we’ll put rows and columns together in the predictionDataFrame.
body = gsub('\\\\', '', toJSON(predictionDataFrame, auto_unbox = TRUE))
body = paste(substr(body,1,nchar(body)-10), rows, '}', sep='')
Lastly, the Prediction Service is expecting data in a JSON object with a particular format dictated by Salesforce and the predictionDefinition (Model ID), so these 2 lines are in charge of formatting the object and storing it in body.
##### Get the prediction response and parse out the value #####predictionRequest = POST('https://na134.salesforce.com/services/data/v49.0/smartdatadiscovery/predict', body = body, encode = 'json', content_type_json(), add_headers(Authorization = paste('Bearer', token)))response = fromJSON(rawToChar(predictionRequest$content))response$predictions$prediction$total
It’s prediction time! We have the authentication token and the body (model definition & viz data) for the predictionRequest, we can use this to make a POST call to the Einstein Prediction Service. If all works out, we’ll get a JSON response with a ton of interesting information. For now, we’ll only use prediction$total value, but you can also get the prediction’s baseline, top predictive factors, prescriptions for actionable variables, etc.
[ClientID], [ClientSecret], [Username], [Password], // Credential Parameters'1OR4R0000008P15WAE', // Model ID, replace with yoursSTR(ATTR([Division])), STR(ATTR([Type])) // Model Parameters
But how does the code know where to pull the viz data from? It’s very similar to what we did in Step 1:
- We’re still using the same 4 parameters for authentication, mapping .arg1[1] to [ClientID], .arg2[1] to [ClientSecret], .arg3[1] to [Username], and .arg4[1] to [Password].
- We’re hard-coding the Model ID value we got from Salesforce ('1OR4R0000008P15WAE') and assigning it to the predictionDefinition via .arg3.
- Now, to pass data from the viz into rows, we’ll be mapping .arg6 with STR(ATTR([Division])) and .arg7 with STR(ATTR([Type])). This is what really connects the data in the viz, with the data frame sent to Einstein Discovery.
Guess what? WE ARE DONE. We’ve trained and deployed a model in Einstein Discovery that maximizes Customer Lifetime Value. We’ve build a viz in Tableau Desktop with data for our current customers. We’ve connected both using External Services and R so data and predictions can be seen side-by-side in a Tableau Viz.
Ah, yes, the Easter egg! Thank you for going through all this with me! Please feel free to download the workbook, and leave any thoughts or questions on the comments below.
