If you have dealt with hierarchies from Salesforce (SFDC), or from most normalized transactional systems, you have faced some challenges to use those hierarchies in Tableau. Those systems treat hierarchies as a recursive model, storing Parent to Child relationships in only 2 columns. The recursive model approach to hierarchies is very efficient in terms of storage, and flexible as it allows for a variety of levels and branches of different lengths. Yet, usage relies on User Defined Functions which are not available in Tableau, as it would break its columnar aggregation logic. There is a quick and dirty approach in Tableau: rebuild the hierarchy using Groups, but it is limited to 2 levels and requires manual maintenance in Tableau, not ideal… A real hierarchy in Tableau requires each level to be defined as its own column in the data source. Breaking down the hierarchy in levels is very desirable for Tableau or any other BI tools really, as it enables filtering and aggregating by any node.
I am presenting here an approach than enables instant refreshes of any SFDC hierarchy extraction and conversion to a Tableau friendly format. It is using Alteryx, but the logic can be applied to any data prep tool, really… You won’t even need Alteryx to follow along my demonstration.
The business scenario I use to illustrate is a SFDC Role Hierarchy accessed through Setup/Manage Users/Roles:
The roles recorded in SFDC as a recursive hierarchy are tying to many objects in SFDC, such as opportunities through their owner, and are therefore very useful to roll up, say bookings or pipeline, to any organizational level, which we will define this way:
In this case, I need to present my bookings broken down by B2B vs. B2C, and all I have from SFDC, is a hierarchy table found in the UserRole table structured like this:
Of course, the Id in SFDC will look more like “00E61000000LmLMEA0” than a 2 digit Id I made up for my data sample, for clarity, which happens to reflect the level… Since SFDC does not enforce much rules when it comes to hierarchies, there could be a lot of branches with different levels, with some B2B leafs found at levels other then level 4. Yet, the model will still work, as it will pick up automatically any changes by mapping the branches of the tree iteratively top down. The desired output will be this table, much more Tableau friendly:
Here is the sequence of processing, captured as an Alteryx workflow:
That is a lot of processing, so I will break the workflow into steps:
Join to find the Parent Role Name and segregate top level (no parent) from branches:
Another Join to submit the top branch(es) connecting Level 1 and 2, as a start of the branch exploration:
formula to create Path for top branch and initiate the level counter:
formula to create Path for top node/leaf:
The secret sauce: an iterative macro that will conduct branch exploration and generate one row per branch/link between 2 nodes found:
There are 2 inputs, one for the Parent data and one for the Child data that get joined, and 2 outputs, one for the iteration, and one for the output that will union all the branches data. This iterative macro will loop through branches until there is no more child found.
formula (convoluted but dynamic) to create a full path description:
Creation of one row per level by splitting the Path. Works dynamically with any number of levels:
Multi Row formula to populate the Level # attribute, dynamic as well:
Cross Tab to convert level rows into level columns:
Dynamic Rename to label columns into Level_#:
Done! The resulting table can be used to join opportunities by their owner and their role found in the User table. I can now sum my opportunities amount by any level or part of the hierarchy!
You can play with the hierarchy in Tableau and see for yourself the Bottoms Up vs. the Top Down views…
I am sharing the packaged Alteryx workbook here.