Meliorainsights.com
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About
Search

Meliora Insights, LLC

​Data Analytics and Visualization | Tableau CRM (Einstein Analytics) Consulting
​meliora - 'for the pursuit of the better'


A blog series distilling quantitative concepts /use-cases in Tableau CRM (Einstein Analytics).

Use of SAQL Index-Of() to pick out certain roles in a Flatten Transformation - by Dan Alindogan

3/8/2022

0 Comments

 

Here’s an interesting use-case for a client of ours. They need reports that will be available to users who have a certain role ‘X” as well as having the same security for the role 2 levels up. This means that it has to bypass the next level up. Illustratively, given a role path of x / y / z / k / m, the data needs to be seen by roles x and z. In order to effect that, a flatten transformation with the self-Id checked is used, followed by a compute expression and the’ index_of’ string function. Listed below are the steps and settings as well as a link to the SAQL function  “index_of” . 

To start off, the function “index_of” resolves to an integer marking the location of a specific character inside a string. 


https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_string_io.htm

index_of(“connecticut” , “c”, 1,1) = 1
index_of (“connecticut”, “c”, 1, 3) = 9
index_of(“connecticut”, “c” 1,4) = 0

Why should someone integrate the function “index_of” into their SAQL? One reason is to  isolate certain strings and use them when there is a flatten node dealing with Role Path. 
Here is an example of how we can use the “index_of” in a case where the role is in the security predicate. We want to show how we can filter out the appropriate role out of the string created by the flatten transformation.

Role Path:
 
  1. defg/hijk/lmno
  2. abcd/efgh/ijkl
  3. Wxyz
  4. Wwww/eeee/ffff/tttt/rrrr
Compute expression: How can we leave out any roles other than the 1st and 3rd? (with Self ID turned on in the flatten transformation node)

substring(‘Product’, 
            , case index_of (“/”, 1, 2) = 10 then 10
            , case index_of(“/”, 1,1) = 5 then 0 
                    Else 0 ) then +1, 4)

Resolves to:
  1. substring(‘Product’, 11, 4) = lmno
  2. substring(‘Product’, 11, 4) = ijkl
  3. substring (‘Product’, 1,4) = wxyz
  4. substring (‘Product’, 1,4) = Wwww

Note: the 2nd case statement (, case index_of(“/”, 1,1) = 5 then 0 )  is superfluous but is there to show that anything other than the 2nd occurrence is “/” is ignored since we are interested in the self id and the 3rd role.

After assigning the result of the ‘Resolves to’ list to a variable called ’ProperRole’, one can then insert it in the security predicate (ie… ‘ProperRole’ ==  $user.RoleID )

​
0 Comments

Compute relative vs. Compute expression. What is the difference?---- Dan Alindogan

1/31/2022

0 Comments

 
An interesting use-case came up with one of our supply chain clients. They needed to bin certain invoices into 1 of two categories--1) Full Service (any invoice that contains a 'Service Contract' line item OR 2) Retail (any invoice that does not have a service contract). This use-case utilizes 2 powerful TCRM nodes-- the compute relative and the compute expression.


At a high level, the compute relative enables a comparison up and down the table (ie rows) while the compute expression goes left > right ie columns. Down below I will show a step by step guide to how one would tackle this use case.


Step 1: Create a compute expression and label it as ‘Recasted Label’ (SAQL pseudocode follows)
Load ‘[Dataset]’


Case when ‘Description’’ == “Labor” then “AAA” else “[Description]”



Step 2: Use a Compute Relative Node to create a field called ‘Bin Type’ using the following settings in the Dialogue Box of that node
Group by InventorySort by ‘Recasted Label’ Asc
Create new field —> ‘Bin Type’
Aggregate f(x) = ‘first’



Step 3: Use a Compute Expression to create a new field “Class” using a CASE Statement.
 Case when ‘Bin Type’ != “AAA”    Then “Ship” else “Comprehensive”





Picture
Picture
0 Comments

Creating a waterfall chart from a time-series dataset

11/21/2021

0 Comments

 
so let's say you have a set of metrics that you want to create a time-series on. Maybe your team makes sales forecast on clients that changes over time. You can take a 'snapshot' of that forcast daily thru a scheduled dataflow and an append transformation node. At the end of a certain period your dataset may look like this...
Client Date Forecast 
A   January 3  $1000
B  January 3   $5000
A  March 2     $2000
B March 2     $3000
Code below creates a waterfall chart with a binding to control the grouping trigger.
q = load \"zds_IoTForecast5\";\nstartVal = filter q by {{column(qDateTest_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nendVal = filter q by {{column(q_SnapDate2_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nq_A = filter q by {{column(qDateTest_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nq_B = filter q by {{column(q_SnapDate2_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nstartVal = group startVal by all;\nstartVal = foreach startVal generate \"Start Value\" as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string(sum (startVal.'TotValueUSD'),\"$#,###\") as 'Forecast Delta';\nendVal = group endVal by all;\nresult = group q_A by '{{column(static_6.selection,[\"Valu\"]).asObject()}}' full, q_B by '{{column(static_6.selection,[\"Valu\"]).asObject()}}';\nresult = foreach result generate coalesce(q_A.'{{column(static_6.selection,[\"Valu\"]).asObject()}}', q_B.'{{column(static_6.selection,[\"Valu\"]).asObject()}}') as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',coalesce(sum(q_A.'TotValueUSD'),0) as 'A', coalesce(sum(q_B.'TotValueUSD'),0) as 'B';\nresult = foreach result generate '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string( B-A,\"$#,###\") as 'Forecast Delta';\nendVal = foreach endVal generate \"Ending Value\" as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string(sum (endVal.'TotValueUSD'),\"$#,###\") as 'Forecast Delta';\nresult = order result by '{{column(static_6.selection,[\"Valu\"]).asObject()}}' asc;\nfinal = union startVal,result,endVal;
 
 


Picture
0 Comments

Enabling fiscal year support for TCRM

11/21/2021

0 Comments

 
This setting is enabled in the Salesforce CRM side by your system administrator. Setting this enables accurate fiscal year groupings / filters in TCRM.

Picture
0 Comments

altering the JSON of a flatten transformation to surface the flatten path.

11/21/2021

0 Comments

 
After creating the dataset that contains a flatten transformation, you need to modify the dataset XMD and turn 'isSystemField' to 'false' . This will enable user to see the field in a lens to debug.

CODE BELOW
{ "flattnMgr": { "schema": { "objects": [ { "label": "ManagerPathText", "fields": [ { "name": "mgrPath", "label": "mgrPath", "isSystemField": false }, { "name": "ManagersList", "label": "ManagersList", "isSystemField": false } ] } ] }, "action": "flatten", "parameters": { "include_self_id": false, "self_field": "Id", "multi_field": "ManagersList", "parent_field": "ManagerId", "path_field": "mgrPath", "source": "LoadUser" } }

0 Comments

Multiple datastreams and calculated fields,  co-grouped with a TOTAL at the end of the detail rows through the 'total' group by all field.

11/21/2021

0 Comments

 
Picture
0 Comments

SAQL sample of bindings / date filters , number_to_string formatting $#,####),etc.

11/21/2021

0 Comments

 
q = load \"zds_OpptySnapshot\";\nq1 = filter q by  {{column(timeStampText_4.selection,[\"Snapshot1\"]).asEquality('timeStampText')}};\n
q1 = filter q1 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,\"Valu\").asString()}};\n
q2 = filter q by {{column(timeStampText_5.selection,[\"Snapshot2\"]).asEquality('timeStampText')}};\n
q2 = filter q2 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in
{{cell(q_FY2_1.selection,0,\"Valu\").asString()}};\n
result = group q1 by 'Stage' full, q2 by 'Stage';\nr
esult = foreach result generate coalesce(q1.'Stage', q2.'Stage') as 'Stage', number_to_string(sum(q1.'Amt'), \"$#,####\") as 'Snapshot1 Amt',
number_to_string(sum(q2.'Amt'), \"$#,####\") as 'Snapshot2 Amt', number_to_string((sum(q1.'Amt')- sum(q2.'Amt')), \"$#,####\") as
'Difference';\nresult = order result by ('Stage' asc);\nq3 = filter q by
{{column(timeStampText_4.selection,[\"Snapshot1\"]).asEquality('timeStampText')}};\nq
3 = filter q3 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,\"Valu\").asString()}};\n
q4 = filter q by {{column(timeStampText_5.selection,[\"Snapshot2\"]).asEquality('timeStampText')}};\n
q4 = filter q4 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,\"Valu\").asString()}};\n
tot = cogroup q3 by all full,q4 by all;\n
tot = foreach tot generate \"Totals\" as 'Stage', number_to_string(sum(q3.'Amt'), \"$#,####\") as'Snapshot1 Amt',
number_to_string(sum(q4.'Amt'), \"$#,####\") as 'Snapshot2 Amt', number_to_string((sum(q3.'Amt')- sum(q4.'Amt')), \"$#,####\") as 'Difference';\
nfinal =union tot,result;\n",
                "receiveFacetSource": {
                    "mode": "all",

0 Comments

November 21st, 2021

11/21/2021

0 Comments

 
Picture
0 Comments

flatten transformation and surfacing the security predicates

6/4/2021

0 Comments

 
The flatten transformation is a powerful node for flattening heirarchy. A typical use-case is for enforcing 'who sees what'  up the reporting chain  through the use of security predicates. Code below details the steps in tweakign the JSON of the dataflow to be able to see the heirarchy in string form. This is used for debugging purposes. As an example, if you have a  flatten node to see the id and the managers id and the manager of the manager,etc, you  need to add the schema section in the JSON in order for you to see the manager path in the UI part of the lens in TCRM.

{ "flattnMgr": { "schema": { "objects": [ { "label": "ManagerPathText", "fields": [ { "name": "mgrPath", "label": "mgrPath", "isSystemField": false }, { "name": "ManagersList", "label": "ManagersList", "isSystemField": false } ] } ] }, "action": "flatten", "parameters": { "include_self_id": false, "self_field": "Id", "multi_field": "ManagersList", "parent_field": "ManagerId", "path_field": "mgrPath", "source": "LoadUser" } }
0 Comments

COGROUP and publishing totals per column: I've frequently quipped how Tableau CRM makes hard things easy and easy things hard. Totals at the top of a grid widget is a royal pain vs. excel or other BI tools. You have to do it manually through SAQL. Here i

4/29/2021

0 Comments

 
q = load \"zds_OpptySnapshot\";\nq1 = filter q by  {{column(timeStampText_4.selection,[\"Snapshot1\"]).asEquality('timeStampText')}};\nq1 = filter q1 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,\"Valu\").asString()}};\nq2 = filter q by {{column(timeStampText_5.selection,[\"Snapshot2\"]).asEquality('timeStampText')}};\nq2 = filter q2 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,\"Valu\").asString()}};\nresult = group q1 by 'Stage' full, q2 by 'Stage';\nresult = foreach result generate coalesce(q1.'Stage', q2.'Stage') as 'Stage', number_to_string(sum(q1.'Amt'), \"$#,####\") as 'Snapshot1 Amt',number_to_string(sum(q2.'Amt'), \"$#,####\") as 'Snapshot2 Amt', number_to_string((sum(q1.'Amt')- sum(q2.'Amt')), \"$#,####\") as 'Difference';\nresult = order result by ('Stage' asc);\nq3 = filter q by {{column(timeStampText_4.selection,[\"Snapshot1\"]).asEquality('timeStampText')}};\nq3 = filter q3 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,\"Valu\").asString()}};\nq4 = filter q by {{column(timeStampText_5.selection,[\"Snapshot2\"]).asEquality('timeStampText')}};\nq4 = filter q4 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,\"Valu\").asString()}};\ntot = cogroup q3 by all full,q4 by all;\ntot = foreach tot generate \"Totals\" as 'Stage', number_to_string(sum(q3.'Amt'), \"$#,####\") as'Snapshot1 Amt', number_to_string(sum(q4.'Amt'), \"$#,####\") as 'Snapshot2 Amt', number_to_string((sum(q3.'Amt')- sum(q4.'Amt')), \"$#,####\") as 'Difference';\nfinal =union tot,result;\n",
                "receiveFacetSource": {
              
0 Comments
<<Previous
Forward>>
Site powered by Weebly. Managed by Hostwinds
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About