You are here:
flatten Transformation
The flatten transformation flattens hierarchical data. For example, you can flatten the Salesforce role hierarchy to implement row-level security on a dataset based on the role hierarchy.
When you configure the flatten transformation to flatten a hierarchy, you specify the field that contains every node in the hierarchy and the field that contains their corresponding parent based on the hierarchy. The flatten transformation generates one record for each hierarchy node, which we refer to as the self-ID. Each record contains two generated columns that store the hierarchy for each self-ID node. One column contains a comma-separated list of all ancestors for each node in the hierarchy. The other column contains the hierarchy path.
To see how ancestors are stored, review the Roles and RolePath columns in the following flattened dataset.
| Role ID (Self-ID) | Role Name | Parent Role ID | Roles | RolePath |
|---|---|---|---|---|
| 1 | Salesperson 1 | 10 | 10, 20, 30 | \10\20\30 |
| 2 | Salesperson 2 | 10 | 10, 20, 30 | \10\20\30 |
| 3 | Salesperson 3 | 11 | 11, 20, 30 | \11\20\30 |
| 10 | Regional Manager 1 | 20 | 20, 30 | \20\30 |
| 11 | Regional Manager 2 | 20 | 20, 30 | \20\30 |
| 20 | Vice President 1 | 30 | 30 | \30 |
| 21 | Vice President 2 | 30 | 30 | \30 |
| 30 | CEO | Not applicable | Not applicable | Not applicable |
You can also configure the flatten transformation to include the self-ID node in the generated hierarchy columns. The following dataset shows the self-ID in bold.
| Role ID (Self-ID) | Role Name | Parent Role ID | Roles | RolePath |
|---|---|---|---|---|
| 1 | Salesperson 1 | 10 | 1, 10, 20, 30 | \1\10\20\30 |
| 2 | Salesperson 2 | 10 | 2, 10, 20, 30 | \2\10\20\30 |
| 3 | Salesperson 3 | 11 | 3, 11, 20, 30 | \3\11\20\30 |
| 10 | Regional Manager 1 | 20 | 10, 20, 30 | \10\20\30 |
| 11 | Regional Manager 2 | 20 | 11, 20, 30 | \11\20\30 |
| 20 | Vice President 1 | 30 | 20, 30 | \20\30 |
| 21 | Vice President 2 | 30 | 21, 30 | \21\30 |
| 30 | CEO | Not applicable | 30 | 30 |
For flattening to function correctly:
- There can’t be a cyclical dependency in the hierarchy data; that is a parent node can’t point back to a child node as its parent.
- The hierarchy must be complete; that is every child node must have a parent node.
For example, the Role and Role Path values aren’t calculated:
- For Vice President 1, Regional Manager 1, and Salesperson 1 when Salesperson 1 reports to Regional Manager 1, who reports to Vice President 1, who reports to Salesperson 1, because the hierarchy has a cyclical dependency.
- For Salesperson 1 and Salesperson 2 when Regional Manager 1 isn’t present in the dataset, because the hierarchy isn’t complete.
Let’s look at an example. You want to create a dataset that contains all opportunities. For each opportunity, you want to include user and role information about the opportunity owner. Also, to implement row-level security based on the role hierarchy, each opportunity record must also contain a list of all ancestors above each opportunity owner based on the role hierarchy. To generate the list of ancestors, use the flatten transformation to flatten the role hierarchy.
You create the following dataflow definition file:
{
"Extract_Opportunity": {
"action": "sfdcDigest",
"parameters": {
"object": "Opportunity",
"fields": [
{ "name": "Id" },
{ "name": "Name" },
{ "name": "Amount" },
{ "name": "StageName" },
{ "name": "AccountId" },
{ "name": "OwnerId" }
]
}
},
"Extract_User": {
"action": "sfdcDigest",
"parameters": {
"object": "User",
"fields": [
{ "name": "Id" },
{ "name": "Name" },
{ "name": "Department" },
{ "name": "UserRoleId" }
]
}
},
"Extract_UserRole": {
"action": "sfdcDigest",
"parameters": {
"object": "UserRole",
"fields": [
{ "name": "Id" },
{ "name": "Name" },
{ "name": "ParentRoleId" }
]
}
},
"Flatten_UserRole": {
"action": "flatten",
"parameters": {
"source": "Extract_UserRole",
"self_field": "Id",
"parent_field": "ParentRoleId",
"multi_field": "Roles",
"path_field": "RolePath",
"include_self_id":false
}
},
"Augment_User_FlattenUserRole": {
"action": "augment",
"parameters": {
"left": "Extract_User",
"left_key": [ "UserRoleId" ],
"relationship": "Role",
"right": "Flatten_UserRole",
"right_key": [ "Id" ],
"right_select": [
"Id",
"Name",
"Roles",
"RolePath"
]
}
},
"Augment_Opportunity_UserWithRoles": {
"action": "augment",
"parameters": {
"left": "Extract_Opportunity",
"left_key": [ "OwnerId" ],
"right": "Augment_User_FlattenUserRole",
"relationship": "Owner",
"right_select": [
"Name",
"Department",
"Role.Id",
"Role.Name",
"Role.Roles",
"Role.RolePath"
],
"right_key": [ "Id" ]
}
},
"Register_OpportunityWithRoles_Dataset": {
"action": "sfdcRegister",
"parameters": {
"alias": "OppRoles",
"name": "OppRoles",
"source": "Augment_Opportunity_UserWithRoles",
"rowLevelSecurityFilter": "'Owner.Role.Roles' == \"$User.UserRoleId\" || 'OwnerId'
== \"$User.Id\""
}
}
}To flatten the Salesforce role hierarchy, the flatten transformation uses these input fields from the UserRole object.
- Id—Id identifies each node in the Salesforce role hierarchy.
- ParentRoleId—ParentRoleId identifies the parent as defined in the role hierarchy.
After traversing through each parent-child relationship in the UserRole object, the flatten transformation generates one record for each role ID. Each record contains all ancestor roles for each role in the hierarchy. The flatten transformation generates two output columns—Roles and RolePath—to store all ancestor roles for each role.
- flatten Transformation Parameters
When you define a flatten transformation, you set the action attribute toflatten Transformationand specify the parameters.
