Pages

Wednesday, June 13, 2018

Einstein Analytics: Multi-Select Picklist to Text

As per Summer '18 release, Einstein Analytics is not really friendly with Multi-Select Picklist field. One of the issues, when you have multi-values in a record, it will show only the 1st value when you show the data as Value Table in Lense or Table wizard in a Dashboard.

Service Type contains multi-values in Salesforce


Service Type only show the 1st value in EA


As per this document, we can customize JSON dataflow to treat multi-select values as text. Once, you have the dataflow built:

  • Download the JSON file
  • Edit the file and add ,"isMultiValue": false after the field name and save it (you should backup the original JSON file)
  • Upload back the edited JSON file to Dataflow



Here is the result the multi-select values show as text in Einstein Analytics



Note: if you do not update the JSON dataflow as above, the data will flow into EA as multi-values, and it will work if you use it as list filter as an independent value, but the record selection will work as if the multi-values for the record.


ReferenceLimitations of Multi-Value Fields in Einstein Analytics



Sunday, June 10, 2018

Einstein Analytics: Getting started with cogroup

You can combine data from two or more data streams into a single data stream using cogroup. The data streams must have at least one common field. Only data that exists in both groups appear in the results.

example:
qs = cogroup qsd by 'State', qsp by 'State';
in this sample, data stream qsd contain field State and data stream qsp also contain field State, we can use it for grouping.

q = cogroup ops by 'Account', meetings by 'Company';
Account in ops data stream will have the same value with Company in meetings data stream.


Use case: show death per state percentage from 2 datasets.



Let's use cogroup to combine the dataset:
dsd = load "StateDeath2";
dsp = load "StatePopulation2";
ds = cogroup dsd by 'State', dsp by 'State';
ds = foreach ds generate dsp.'State' as 'State', sum(dsp.'Count') as 'Population', sum(dsd.'Count') as 'Death', (sum(dsd.'Count')/sum(dsp.'Count')*100) as 'Death (%)';

The Result


Let's try to use Opportunity and User datasets from our previous blog.
dsu = load "user1";
dso = load "opportunity";
ds = cogroup dsu by 'Id', dso by 'OwnerId';
ds = foreach ds generate first(dsu.Name) as 'Name', sum(dso.Amount) as 'Sum_Amount';
ds = order ds by Name;


If you notice, Angela is not shown on that chart, because she do not have any Opportunity records. Remember that only data that exists in both groups appear in the results.


Reference:




Saturday, June 9, 2018

Einstein Analytics: Opportunity Dashboard with SAQL Union to show all User

Let's go straight to the business requirements, "show all sales rep with their total sales amount, if there is no opportunity owned by the sales rep, sales rep name must be shown with $0."

High-level solution:
1. Load User master data
2. Load Opportunity data
3. Use UNION to combine the dataset

User data


Opportunity data


Build User master Lens group by Id and Name
q = load "user1";
q = group q by ('Id', 'Name');
q = foreach q generate 'Id' as 'Id', 'Name' as 'Name', count() as 'count';
q = order q by ('Id' asc, 'Name' asc);
q = limit q 2000;

Let's modify necessary SAQL:
1. Rename all q data stream to dsu -- for easier identifier and uniqueness
2. Rename projected 'Id' to 'User_Id', and 'Name' to 'User_Name' -- I'll tell you the reason later
3. Remove 'count' as we do not need it -- User_Id is unique
4. Add 'sum_Amount' with 0 in foreach -- I'll tell you the reason later
5. Remove limit

Here is the result
dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'User_Name', 0 as 'sum_Amount';
dsu = order dsu by 'User_Id';


Build Opportunity Lens group by OwnerId
q = load "opportunity";
q = group q by 'OwnerId';
q = foreach q generate 'OwnerId' as 'OwnerId', sum('Amount') as 'sum_Amount';
q = order q by 'OwnerId' asc;
q = limit q 2000;

Let's modify necessary SAQL:
6. Rename all q data stream to dso -- for easier identifier and uniqueness
7. Rename projected 'OwnerId' to 'User_Id' -- I'll tell you the reason later
8. Add 'User_Name' with "-" in foreach -- I'll tell you the reason later
9. Remove limit

Here is the result
dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', "-" as 'User_Name', sum('Amount') as 'sum_Amount';
dso = order dso by 'User_Id';


Combine the dataset with UNION
final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('User_Name') as 'User_Name', sum('sum_Amount') as 'sum_Amount';


The Complete SAQL
dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'User_Name', 0 as 'sum_Amount';
dsu = order dsu by 'User_Id';

dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', "-" as 'User_Name', sum('Amount') as 'sum_Amount';
dso = order dso by 'User_Id';

final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('User_Name') as 'User_Name', sum('sum_Amount') as 'sum_Amount';


The Moment of Truth



Explanation
  • we rename Id and Name in step (2) to have the same column name with step (7)  and (8)
  • we add 'sum_Amount' in step (4) to have the same column name with dso data stream
  • for our use case, we get the dataset aligned with the same column by adding dummy columns before using UNION to both data stream
  • In the last row, we use aggreagate function first() to return the first user name, as our union  start with dsu which contain user name, while dso at the second/last will always contain "-" for user name, see step (8)
  • In the last row, we also sum the 'sum_Amount' again, practically this is sum the 'sum_Amount'  with 0, remember we add 0 as dummy value in step (4)

Make it simple
Since sum_Amount always 0 in dsu, and User_Name always "-" in dso, we can just simply not need to add them to the data stream, and we will still get the same result, let's remove the unnecessary statement.

dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'Name';

dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', sum('Amount') as 'sum_Amount';

final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('Name') as 'Name', sum('sum_Amount') as 'sum_Amount';
final = order final by 'Name';




Reference:


Monday, June 4, 2018

Einstein Analytics: Filter Conditions in sfdcDigest

When you do not need to bring the whole data from a Salesforce object to Einstein Analytics (EA), you can filter the data when retrieving it in sfdcDigest.

With filter out unnecessary data flow to EA, this will help our dashboard builders do not need to keep filtering out certain unused data, such as inactive users for the User object. Filtering data also will increase performance on the Dataflow and consume less total of records stored in EA.

In this sample, I want to bring in all active users and not included Chatter users. So, there are 2 filters need to add in sfdcDigest:
- isActive = true
- userType = "Standard"

If you come from Salesforce background, you can simply enter isActive = true && userType = Standard, but this is different in EA. Until Summer '18 release, you need to manually enter this filter conditions in JSON format.

Add this filter in sfdcDigest under Filter Conditions:
[
{"field":"isActive","isQuoted":true,"value":"true","operator":"="},{"field":"UserType","value":"Standard","operator":"="}
]

If you see the first filter, isActive is a boolean field, so passing just true without "" will get an error when running the Dataflow, so we need to add "isQuoted": true, this is also applicable for a numeric and date field.

Let's see this in JSON Dataflow:



For the complete reference, check out this documentation Structured Filter in sfdcDigest Transformation.