Ragged hierarchy Visualization

Here is the solution!

Hi all,

I was having the same problem and I could not find anything on the web that truely solved this issue, so I developed my own modeling that would address this problem of "blanck"s appearing in visualization from ragged hierarchies.

Here is my solution:

On a high level, the solution calls for your hierarchy data table to be segmented into separate data tables to then be interconnected with a 'many to one' relationship.  These hierarchy lookup tables then will be connected to your primary data table.

Take the following two examples I created to validate this method

Solution 1 (does not work):

1. Create a Hierarchy table that you will use as a lookup for your primary data.  the format of this table is shown below. 

      a. Create a primary key column that refers to your last level

      b. Create several columns that describe your hierarchy i.e. L1, L2, L3, ect.

      c. Do not repeat members or level blanks before the end of your hierarchy

2. Create a many to one relationship between your hierarchy lookup table and our primary table.

3. Run a matrix visualization b dragging the values column from your primary data table into the values field and drag all your levels (L1, L2, L2 ect.) into the row section of the visualization.

You will notice the "blank" values problem persists.  To resolve this, try solution 2 shown in the next set of pictures

l l

Solution 2:

1. Segment the larger hierarchy data lookup table into 4 separate data tables. Follow the picture examples below for exact designing

      a. As you move from one hierarchy table to the next, generate new levels and tables and eliminate those observations in subsequent tables once the final levels of that observation has been reached.  Please see picture below for further detail.

2. Link your tables together following the specific relationship cardinality as shown below

3. Run a matrix visualization by dragging the values column from your primary data table into the values field and drag your different levels (L1, L2, L3 ect.) from each new hierarchy data tables into the row section of the visualization.

Following these steps will resolve the blank values you see when displaying hierarchical data that is builded off of ragged hierarchies.

visualization input order:

I hope this helps you and anyone struggling with this issue.

Best,

Eric