Pages

Sunday, January 13, 2019

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 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 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 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 soql3

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:


1 comment:



  1. "soql": {
    "groups": [],
    "numbers": [
    "Quantity",
    "Amount"
    ],
    "query": "SELECT omsAccount__r.Region_Category_Headword__c Region,SUM(Total_QTY_No_Sample__c) Quantity,SUM(Round_DL_Total_Amount__c) Amount from omsInvoice__c where Business_Type__c = 'Kips' AND RecordType.DeveloperName = 'Invoice' AND omsStatus__c != 'Cancelled' AND Total_QTY_No_Sample__c > 0.00 AND omsInvoiceDate__c>=2019-04-01 Group By omsAccount__r.Region_Category_Headword__c order by omsAccount__r.Region_Category_Headword__c ASC NULLS LAST",
    "selectMode": "single",
    "strings": [
    "Region"
    ],
    "type": "soql"
    }
    },


    Please help to add below filter in SOQL Step


    And (NOT omsInvoiceNo__r.omsAccount__r.Name like '%Headword%')
    And (NOT omsInvoiceNo__r.omsAccount__r.Name like '%Kips%')

    ReplyDelete

Page-level ad