---
schema_version: "1"
id: 3a91ee16f7776b6b906d5fd222cdb9869bb9be76bc7bf22724f8b084d217754d
title: "Flatten Transformation: Flatten Hierarchies"
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_recipe_transformation_flatten.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_transformation_flatten.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: a6fa9d5ac81b4fc396cd3ec8acece2426076c52be3deb789d6d5de66ca855520
description: In CRM Analytics, 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: Flatten Hierarchies

In CRM Analytics, 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.

### Required Editions

## Flatten Transformation: Flatten Hierarchies

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small permission">
        <colgroup>
          <col style="width:50%">
          <col style="width:50%">
        </colgroup>
        <thead class="slds-text-title_caps ">
          <tr class="">
            <th scope="colgroup" class="slds-cell-wrap" colspan="2" style="text-align:center;vertical-align:top;" id="d26775e39">User Permissions
              Needed</th>
          </tr>
        </thead>
        <tbody>
          <tr class="">
            <td data-label="User Permissions Needed" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e39 ">To create a recipe:</td>

            <td class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e39 ">Edit Analytics Dataflows OR Edit Dataset Recipes</td>

          </tr>
        </tbody>
      </table>

When you configure the Flatten transformation, 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 record ID (also known as the child node). After you run the recipe, each record contains two generated columns that store the hierarchy for each record ID. One column contains a list of all ancestors for each node in the hierarchy. The other column contains the hierarchy path.

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

Important The source dataset row maximum is 20 million.

Consider the number of levels you’re flattening with the row size of your dataset. The Flatten transformation can flatten:

- Up to 300 levels with a < 1million-row dataset.
- Up to 100 levels with a 15 million-row dataset.
- Up to 50 levels with a 20 million-row dataset.

See the Roles and Role Path columns in the following flattened dataset to see how ancestors are stored.

    
| Record ID (child node) | Role Name | Parent Record ID | Roles | Role Path |
| --- | --- | --- | --- | --- |
| 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 record ID in the generated hierarchy columns. The following dataset shows the record ID in bold.

    
| Record ID (child node) | Role Name | Parent Record ID | Roles | Role Path |
| --- | --- | --- | --- | --- |
| 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** |

If a hierarchy is incomplete or contains a circular relationship, the Role and Role Path values aren’t calculated. For example:

- If Regional Manager 1 isn’t present in the dataset, a flattened hierarchy isn’t generated for Salesperson 1 and Salesperson 2.
- If Salesperson 1 reports to Regional Manager 1, who reports to Vice President 1, who reports to Salesperson 1, a flattened hierarchy isn’t generated for these roles.

To create a flatten transformation:

1. In a Transform node of a Data Prep recipe, select the text (dimension) column that contains all the record IDs (or child nodes) in the hierarchy. The record ID column can’t contain null values.![The selected Role ID column contains all nodes in the hierarchy.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_transformation_flatten_selectChildNodeColumn.png)
2. In the Transform toolbar, click the Flatten button (![Flatten button](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_button_flatten.png)).
3. Enter the following transformation details. <table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small ">
            <colgroup>
              <col style="width:30.03003003003003%">
              <col style="width:69.96996996996997%">
            </colgroup>
            <thead class="slds-text-title_caps ">
              <tr class="">
                <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d26775e521">Property</th>
                <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d26775e523">Description</th>
              </tr>
            </thead>
            <tbody>
              <tr class="">
                <td data-label="Property" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e521 ">Record ID Column</td>
                <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e523 ">Column that identifies each record. Also known as the child node.</td>
              </tr>
              <tr class="">
                <td data-label="Property" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e521 ">Parent ID Column</td>
                <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e523 ">Column that contains the parent of each node in the hierarchy. For example,
                  the Regional Manager 1 role is the parent of the Salesperson 1 role in a role
                  hierarchy.</td>
              </tr>
              <tr class="">
                <td data-label="Property" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e521 ">Include Record ID Column Value</td>
                <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e523 ">Indicates whether to include the record ID column value in the generated
                  hierarchy nodes and path columns.</td>
              </tr>
              <tr class="">
                <td data-label="Property" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e521 ">Hierarchy Nodes Column Label</td>
                <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e523 ">
                  <p>Name of the multivalue output column that contains a list of all ancestors
                    in the hierarchy, in order from the lowest to the highest level. The Flatten
                    transformation creates this column and generates the list of ancestors for each
                    node in the hierarchy. For example, for Salesperson 1 role, the hierarchy of
                    ancestors is:
                  </p>
                  <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>Sales Manager 1, Regional Manager 1,​Vice President 1, CEO</code></pre>
                  <p style="margin-bottom:0;">.
                    To hide this column from queries and dataset exploration, select <strong class="uicontrol">Is a
                      system column</strong>. By default, this column isn’t created as a system
                    column. </p>
                </td>
              </tr>
              <tr class="">
                <td data-label="Property" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e521 ">Hierarchy Path Column Label</td>
                <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d26775e523 ">
                  <p>String that contains the hierarchical path of all ancestors in the
                    hierarchy, in order from the lowest to the highest level. Nodes are separated by
                    forward slashes. The Flatten transformation creates this output column and
                    generates the ancestry path for each node in the hierarchy. For example, for a
                    salesperson role in a role hierarchy, the value is:
                  </p>
                  <pre class="line-numbers slds-box slds-box_x-small slds-theme_shade slds-m-top_medium slds-m-bottom_medium slds-scrollable_x"><code>Sales Manager 1/Regional Manager 1​/Vice President 1/CEO</code></pre>
                  <p style="margin-bottom:0;">.
                    To hide this column from queries and dataset exploration, select <strong class="uicontrol">Is a
                      system column</strong>. By default, this column isn’t created as a system
                    column.</p>
                </td>
              </tr>
            </tbody>
          </table>![Specify the required properties to configure the Flatten transformation.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipe_flattenConfig.png)
4. Preview the transformation results to check that the flattened hierarchy is right.
    
    Preview shows only the record ID and its parent in the hierarchy nodes column and only the record ID in the hierarchy path column. You have to run the recipe to see all ancestors in both columns.
    
    ![The Preview shows the first two role values for the hierarchy path and the first value for the hierarchy nodes column.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipe_flattenPreview.png)
5. Click **Apply** to add the transformation to the Transform node.
6. To collapse the view and see the Graph area, click the Collapse button (![Collapse button](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_button_collapse.png)).

Run the recipe to generate the hierarchy nodes and hierarchy path columns in the dataset. Only the first value in the hierarchy nodes column appears when exploring the dataset in a lens. In general, a lens shows only the first value of a multivalue column.

![The values table shows the hierarchy path but only the child node in the hierarchy nodes column.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipe_flattenedDatasetInValuesTable.png)