Multi-level Account Hierarchy: Flatten, Aggregate, Join, Transform and Report!

In this use case, we will discuss how to leverage Recipes, hierarchical aggregations, and transformations in CRM Analytics to help a company “CRM Test Inc.” which has a complex multi-level account hierarchy in Salesforce without having a holistic view of all their accounts and child records (such as related opportunities, activities or any other custom child record).

The solution proposed in this blog works well if we are not sure how many levels there are in the hierarchy. To demonstrate the solution we will use the sample hierarchy below:

Once we are done creating the recipe, we will be able to leverage the dataset to create a dashboard where we view the entire account hierarchy and a roll-up of related opportunities, similar to the video below:

A couple of things to notice in the hierarchy above, before we get started:

• For the accounts above, users at CRM Test Inc. are creating activities, opportunities (and potentially other child records) under different account levels, and currently there is no clear way of seeing a roll-up of the child records to the ultimate parent account.
• Accounts under CRM Test Inc. could be on the same level reporting to the same ultimate parent account, with either other child accounts under it or no child accounts; this is the case for Omega systems (A2) and Open Source (A6), both being in Level 1 reporting to parent Omega, Inc.

In order to solve the challenge described above, we will be creating a Recipe in CRM Analytics, leveraging the flatten transformation in order to flatten the account hierarchy, then using an aggregate node to roll up child accounts to the parent, followed by joins and leveraging transformations in Recipes to work with multi-value fields.

Now that we talked a bit about the use case and requirements, let’s talk about the How… I have summarized the steps below with easy-to-follow screenshots and named each node according to its function! Once you are done with the recipe it will look similar to this (you might not end up with that final transform node at the end which just does some basic clean-up).

Flatten, Leverage Hierarchical Aggregation, Join, Transform and Report!

Now let’s get to the details!

Step 1: Creating a Recipe

Create a new Recipe in CRM Analytics and bring in an account input node by clicking “Add Input Data at the top left”.

NOTE: If you have never created a recipe before, I recommend you watch the Data Prep Video Series.

Step 2: Flattening your Account Hierarchy

Once you have added your account object, you will flatten the account hierarchy. The reason we want to flatten is that we want to be able to find the path upwards in the hierarchy. So, using our same example above, once we find the path, for Account Omega Inc, we will be able to see all records under it going all the way down to Displaytech (L4) as well as records each of the child account owns.

The way to flatten your account hierarchy is using the flatten transformation in a transform node. The Account ID will be the self ID and the Parent Account ID will be the Parent ID. In this use case, it is not needed to include the self ID (Include Record ID Column value) as part of the output fields.

NOTE: Follow this Help Article: Transformations for Data Prep Recipes to learn about the different transformations.

Step 3: Transforming our Data

When we flatten, by default there are two fields that are created: 1) hierarchy multi value field which is a list of all ancestors for each node in the hierarchy as well as 2) hierarchy path which will show the hierarchy starting with the parent ID, and all the way to the to top to ultimate parent ID.

The multi field will contain all the parents’ IDs. The path field will show the hierarchy from the account’s parent ID and all the way to the top-level, thus the string is depending on how many levels you have in your org.

So what do we do with these 2 fields?

1) Leveraging the hierarchy multi value field, we create a case statement to calculate how many levels are present in the multi value field using the new size function (highlighted in this blog) to return the number of elements in a multi value field, and leveraging the concat function to join the different levels. This will tell us if our Path contains 3, 4, 5 or more accounts.

You can see the formula in the screen shot below.

2) Leveraging the hierarchy path field, we can use the substr function to extract the last ID from the path since this ID will be our ultimate parent account! And if null it’s the ultimate parent or has no child accounts. The key here is to specify the length of the substring to return. Since I know my ID has 18 characters and that we start at 0, I just have to use char_length to figure out how many characters are in the path and subtract -17 to get the correct starting position and return the remaining characters, which is the ID for the ultimate parent account.

Note: to understand more about the different string functions available in the formula editor check out this Help article.

Please see the formula in the screenshot below.

Step 4: Hierarchical Aggregation

The next step is to leverage hierarchical aggregation to add the number of children under any given parent. Add an aggregate node to the recipe and make sure to toggle to turn on hierarchical aggregation. Select count of rows as the aggregate and add the ID fields as illustrated in the images below.

NOTE: To learn more about hierarchical aggregation check out this Help article: Aggregate Node: Hierarchical Aggregation.

Step 5: Join back the Children aggregates to your original node and Augment with Account

Now, you know the number of children available via your aggregate. The next step is to perform three joins to first add the aggregated values back to the main branch of the recipe and next get the names of your parent and ultimate parent accounts… so let’s perform the joins.

Since there are three different joins, I recorded a quick video to show the IDs you need to use in each separate join:

1. First step: join back the children to your flatten node, using the account ID from the left and account ID from right.
2. For the second join, we will grab the parent account ID from the left and account ID from the right.
3. For the third join, we will grab the ultimate parent ID from the left and the account ID from the right.

Remember to RENAME the API Name Prefix for the right columns so you can easily identify the origin in a lens!

Note: In Recipes, it doesn’t matter if you add several input nodes with the same object like in this example. This is just a visual representation and the recipe is smart enough to just digest the object once at the run time.

Step 6: Transform

In this final transformation, we need to take the number of children generated by the aggregate node and subtract 1. The reason is the node does a summary of all related records and includes itself in that number. As we don’t care about the parent account and just the children, we need to create a simple formula transformation referencing the number of children and minus 1.

Once the calculation has been performed we can drop the original children summary field from the aggregate and just keep the field we created in the step above.

Step 7: Register your dataset and verify!

After step 6, you are ready to verify your account levels in a lens. In order to do this, you need to register the dataset by creating an output node and naming the dataset.

If you are not sure how to explore your dataset in a lens I have created a quick video below:

Step 8: Add Opportunity data or Activity data to find underlying records

The next step is to add opportunity, activity, or other child object data, depending on your reporting needs. In my use case, I will be joining with opportunity data so I can see all the underlying opportunities under each child account and parent account as well as the roll-up to the ultimate parent. Now, remember if we are joining opportunity with account data, we need to choose the grain or lowest object which in this case would be the opportunity. For this reason, we will be performing a lookup, using opportunity as the left source and the newly created “CRM Test Inc Hierarchy:” dataset as the right source to make sure we keep the details of the opportunity and matching account records.

You can use the same recipe or a new recipe, add opportunity as input and then join this with your hierarchy dataset using account ID from the opportunity to join on account id from the hierarchy dataset

NOTE: Understanding your grain when creating datasets with recipes is key! The grain is the most detailed object with respect to the dataset in question. If we are building a dataset with Opportunity and Account data, Opportunity is our grain! Check out Dataset Best Practices: Grain Level, Join, Augments in this video, or take this trail on Trailhead.

Step 9: Create your Dashboard to have a comprehensive view of your accounts!

For our final step, we will use the dataset with opportunity data to create our dashboard! For the last piece, I created a short video as well!

Hope this solution works for you!

Written by