Create a ragged hierarchy

Contents[Hide]

1. Overview

This article demonstrates how to create a ragged, unbalanced, or self-referencing hierarchy. A ragged hierarchy can skip levels, as shown in the image below:

A ragged hierarchy, where the city Washington has no state
A ragged hierarchy, where the city Washington has no state

Ragged hierarchies that extend to different levels in each branch are also known as "unbalanced", as shown in the image below:

An unbalanced hierarchy, where Florida has no cities
An unbalanced hierarchy, where Florida has no cities

The number of levels is determined by the data, and may change over time.

2. Data

One structure is used to define a ragged hierarchy. The top level is determined by the rows where the parent ID is empty, while the rest will be based on their parent ID and will appear in one of the levels below the parent.

Example of data that can be used to create a ragged hierarchy
Example of data that can be used to create a ragged hierarchy

You may also have an optional column of values defining the level of each row, which is what makes it possible to "skip" a level, by placing a value two or more levels below its parent. In the example data above, Mexico City's parent is Mexico in the Country level, even though other cities have a parent value in the StateProvince level.

3. Steps

Create a new Hierarchy from the main menu, choosing Ragged.

Create a ragged hierarchy
Create a ragged hierarchy

The hierarchy canvas will appear. Drag the data defining the hierarchy onto the canvas from the Explore window.

Drag the hierarchy data onto the canvas
Drag the hierarchy data onto the canvas

On the left side of the editor under Definition, set up the following:

  • Member Caption Source - The values used displayed as the captions to users.
  • Member Parent Key Source - The values identifying the parent member's key values.
  • Member Key Source - The values that uniquely identify each member. The child member's parent keys must match these.
  • Level Source (Optional) - Values that identify the level. This can be un-set, and the levels will be generated based on each member's parent keys. If set, the level will be determined by and named for these values, and it may be possible for a member's parent to be two or more levels above. Level values can be text (string) or numeric.

The figure below shows how our example source data maps to our ragged hierarchy definition.

Source data mapped to the hierarchy definition
Source data mapped to the hierarchy definition

The following image shows two versions of a hierarchy defined with our sample data and used in a table visualization. The first result sets the optional Level Source, while the second leaves it un-set.

Result of setting Level Source compared with leaving un-set
Result of setting Level Source compared with leaving un-set

You can review the hierarchy in the preview section.

4. Using the ragged hierarchy

You can use a ragged hierarchy the same way as for a standard hierarchy, replacing a column that can be matched with your hierarchy's keys. See Automatic joins and hierarchies for details on replacing a column with a hierarchy when editing a metric set, dashboard, or other view. You can also promote a column to your hierarchy in the Process Result of a data cube.

Note
The ragged hierarchy must be linked to fact data (measure values) only for "leaf" members, or members with no children.

The following sections demonstrate how you can visualize and work with a ragged hierarchy selected in your metric set.

4.1. Visualizing levels

There are special options available when deciding what level(s) to display from a ragged hierarchy in addition to the usual options. To access these, click the hierarchy in the Data Analysis Panel to edit it, and click the token menu to the right of the level dropdown:

  • All Members - This option selects all members from all levels at once, applicable for visualizations that can display multiple levels. This can be useful since the number of levels might change over time according to the data.
  • Leaf Members - This option displays only the members with no children. This is similar to selecting the lowest level, except that for a ragged hierarchy, these members might be in different levels.

Choosing a ragged hierarchy level token option
Choosing a ragged hierarchy level token option

You can also add a Level filter to your dashboard or other view and access these options from its token menu.

4.2. Tables

The table visualization has a layout property called Row Header Layout that can be used to show a ragged hierarchy structure in different ways:

4.3. Charts

The figure below demonstrates a chart's expander buttons that can be used to expand or collapse the ragged hierarchy's members:

The ragged hierarchy displayed in a bar chart
The ragged hierarchy displayed in a bar chart

You can choose to set a chart up to initially display multiple levels at once along its axis, with each level in a separate row, as described in Visualization tab examples. By default, an expanded axis label always displays its children directly adjacent.

4.4. Filters

A hierarchy value filter allows you to expand each member to view its children, and expand down to different depths depending on whether each member has any members below it.

A hierarchy value filter connected to the ragged hierarchy
A hierarchy value filter connected to the ragged hierarchy

Note
If you choose a cascading member filter instead, it will be unable to list members that skip a level.

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri