SOQL steps and Salesforce Direct

In Einstein Analytics we build our dashboards with datasets that we have constructed and run in the Data Manager. We do this because we want to denormalize our data to improve the performance of our queries. This does, of course, come with a price that we do not have real-time data, though we can schedule our dataflows to run often. If you must have real-time data this is where SOQL steps and Salesforce Direct comes in the picture.

Query types

Our dashboards consist of different steps or queries but will be of the type aggregateflex, which means the query is based on a dataset and created using the UI. But we can work with other query types one being SOQL, which allows us to query data directly from Salesforce without using a dataset. Previously this was only possible doing directly in the Dashboard JSON, but we now have the option of Salesforce Direct.

How many cases are open right now?

I am sure that there are many use cases that come to mind when thinking of how to use Salesforce Direct. To be honest, the new user interface makes it extremely easy to create your SOQL queries, so I don’t feel a need to go into a very complex use case here. We will simply look at how many cases there are open and group them by case type.

In your dashboard click on the blue “Create Query” and in the pop-up choose the tab “Salesforce Direct”. Now you’ll see a list of all the Salesforce objects where I’m picking the case object.

As you can see the explorer now opens showing count of row and you can use the UI to select groupings, measures and filters. In my case I want to see open cases by type, hence I have grouped by ‘Type’ and added a filter isClosed is false. Once done click on “Done” and add the new chart to the canvas.

If you take a closer look on the dashboard JSON (command+E or ctrl+E) you will notice that this is actually an aggregateflex query type. Below I have added part of the step in – I removed the visualization part. Notice three things. First, the “type”, which as I mention is aggregateflex. But also notice the “sobject” parameter which refers to the Salesforce object we are querying. Finally, notice that the “dataset” parameter is left empty, as we are not using any for this query.

"Cases_1": {
"type": "aggregateflex",
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Type"
],
"filters": [
[
"IsClosed",
[
false
],
"in"
]
]
},

...

"sobject": "Case",
"useGlobal": true,
"isGlobal": false,
"datasets": [],
"label": "Cases",
"broadcastFacet": true,
"receiveFacetSource": {
"mode": "all",
"steps": []
},
"selectMode": "single"
}

Who’s the logged in user

I have previously written a blog about how to find the logged in user and use this elsewhere in your dashboard. Perhaps you want to select a country based on who is logged in. Needless to say that this can be quite powerful and used in multiple use-cases. While that blog is still valid, there is now an easier way to do this.

We will start the query creation like before, click on “Create Query” select the “Salesforce Direct” tab but this time pick the user object.

In the explorer, we just need the table mode and pick a few fields; the important one is going to be the id.

Next, we need to add a dummy filter we can modify later. It’s easier to have the filter in the UI than having to remember the syntax later.

Now we want to modify the query, so we will click on the “Query Mode” button, so we can change the filter to be the logged-in user. We will do this by adding ‘!{User.Id}’. If you are familiar with looking at the logged-in user in Einstein Analytics in SOQL steps and security predicates, you will probably notice we do not need the dollar sign anymore. Once you are done just hit the “Done” button and we can drag it onto the canvas, though in most cases you want this to be a hidden query.

I won’t in this blog cover how to do the binding, please either check out the previous blog or my binding blog series which covers most bindings.

If we look at the dashboard JSON for this step you will notice that this is, in fact, a SOQL step. By going to the query mode and running the query we are switching out the aggregateflex step with the SOQL as you can see below; again I’ve removed the visualization parameters.

"User_1": {
"type": "soql",
"query": "SELECT Id, Name FROM User WHERE Id = '!{User.Id}' LIMIT 100",
"numbers": [],
"strings": [],
"groups": [],
"label": "User",
...

Finally, be aware you cannot use the logged-in user filter for an aggregateflex step, you must switch to the SOQL step to achieve this.

What can’t you do?

There are a few things to be aware of when you want to work with Salesforce Direct:

• You can only access Salesforce Direct when creating a new query within a dashboard.
• You can click explore on a SOQL step but you cannot modify it or save it as a lens, you will get an error that the lens must be aggregateflex, grain or SAQL.
• In explorer mode you cannot use compare tables incl. totals or conversational querying.
• In a dashboard, faceting and connect data sources will not work.
• Querying formula fields come in as blank or null values.

What data can be seen

As we are querying data directly from Salesforce the user who is logged in and viewing the dashboard will determine what data security is used. Meaning it is the profile, role hierarchy and sharing rules that will control the data that is portrayed in the dashboard. In other words, the security of the data is the same as if you are running an operational report in Salesforce.

A warning

So while it’s great with the option of querying data directly in Salesforce, it does come with a warning. There is a reason why Salesforce operational reports have limits and that is because querying a relational database is not fast and your dashboard will take a hit on the performance. So when you are discussing with your business users that are insisting on real-time data I would encourage you to be critical and not easily give in to their demands. Consider what real-time means for them; how often is the data updated and how often do they look at the dashboard? While there might be many reasons for making use of Salesforce Direct make sure your use case is solid and tested at scale, you don’t want to build a whole dashboard with SOQL step and realize that it takes a long time to load or times out.

Written by