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

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 )

​