Einstein Analytics: Using SOQL

So far, we all know, in building dashboards in Einstein Analytics, we need to bring the data into Einstein Analytics and stored it as Dataset. In this blog, I will share how to directly get data from Salesforce using SOQL, this means we can create a chart wizard or table in  Einstein Analytics with Salesforce live data.

You need to know the basic JSON dashboard in Einstein Analytics. After you create the dashboard, create a step with the type = soql, e.g.

 "soql_step_name": {  
  "type": "soql",  
  "query": "SELECT Name from ACCOUNT",  
  "strings": ["Name"],  
  "numbers": [],  
  "groups": [],  
  "selectMode": "single"  
 }  

Once the step added, you can use it in any wizard. The isFacet and useGlobal properties don't apply to this step type. You can use a binding to filter other steps based on a selection in a soql step.

Let's see more samples:

 "soql1": {  
         "type": "soql",  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [],  
         "selectMode": "single"  
       }  

 "soql2": {  
         "groups": [],  
         "numbers": [  
           "foo"  
         ],  
         "query": "SELECT count(id) foo from ACCOUNT",  
         "selectMode": "single",  
         "strings": [],  
         "type": "soql"  
       }  

 "soql3": {  
         "groups": [],  
         "numbers": [],  
         "query": "SELECT Id,Name from USER where Id = '{!User.Id}'" ,  
         "selectMode": "single",  
         "strings": ["Id","Name"],  
         "type": "soql"  
       }  

  "soql4": {  
         "type": "soql",  
         "query": "SELECT NumberOfEmployees,Name,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [  
           "Type"  
         ],  
         "selectMode": "single"  
       }  

Notes:
- same as the normal step in JSON, the order of parameters will be ignored
- type parameter is "soql"
- query parameter must be valid soql and contain all fields needed
- fields from query result should be put under strings or numbers parameter
- groups parameter is optional, but needed when you have grouping in the wizard


Here is the wizard result from each step above:

step soql1

step soql2

step soql4

Use SOQL result for binding

 "soql1": {  
         "groups": [],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT order by NumberOfEmployees",  
         "selectMode": "single",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "type": "soql"  
       }  

 "all_1": {  
         "type": "saql",  
         "query": "q = load \"All_Accounts\";\nq = group q by all;\nq = foreach q generate {{cell(soql1.result,1,\"NumberOfEmployees\").asObject()}} as 'name1';\n",  
         "useGlobal": true,  
         "numbers": [],  
         "groups": [],  
         "strings": [],  
         "visualizationParameters": {...},  
         "label": "all_1",  
         "selectMode": "single",  
         "broadcastFacet": true,  
         "receiveFacet": true  
       },  

result:

* 18 is the result from the second row cell(soql1.result,1,\"NumberOfEmployees\").asObject()



Reference:

• soql Step Type Properties
• SOQL Step in Salesforce Analytics Cloud Wave