---
schema_version: "1"
id: eba2ec889525083d367c9fe4ec645cca0bbfc5944b66ff1ee8d70574b42fc33c
title: flatten Transformation
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_flatten_transformation.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_flatten_transformation.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 0ff0f121f7a191fe842d8fe6d144029400fad303a022d1c233ded9eaf03b9ee4
description: 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.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# 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.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png)

Note Flatten your hierarchy before you join datasets. For example, if you want a flattened hierarchy in data joined from Account and Opportunity objects, flatten the Account dataset before joining the Opportunity dataset. If you flatten after the join, the transformation doesn’t create the flatten hierarchy data you expect.

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.

![Example](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_example.png)

Example

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](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_flatten_transformation_operation.htm&release=264.0.0&type=5)**  
    When you define a flatten transformation, you set the action attribute to `flatten Transformation` and specify the parameters.