How to create a custom user hierarchy

Contents[Hide]

1. Overview

This article demonstrates how to customize the setup of a hierarchy (sometimes called a 'user hierarchy') created from the main menu. In many cases, such as when the data source contains multiple columns or there are relationships defined, the levels are automatically detected and added by the Hierarchy wizard. Refer to the Automatic joins and hierarchies article for more details.

In case such levels are not automatically detected, you can set up the hierarchy's keys and captions yourself, as in the example below.

2. Example data

In the example depicted below, an Excel file with three different sheets is used as the data source:

3. Steps

3.1. Add the first level

Create a new Hierarchy from the main menu.

Create a new hierarchy
Create a new hierarchy

To set up the first level of the hierarchy (Product Category), expand your data connector in the Explore window and find the data source (e.g., ProductCategory$), then drag it to the canvas.

Your data source can be different for each level or the same for all levels, but either way each level below the first must include parent key values that match the keys of the level above.

If you want to ensure additional levels are not added automatically, press Shift on the keyboard while dropping.

Drag and drop the first level data source
Drag and drop the first level data source

The first level can now be previewed.

Level 1 preview
Level 1 preview

Click on the first level under Structure to open its settings.

Level 1 dialog
Level 1 dialog

Update the details. For example:

  • Name: ProductCategory
  • Description: Product Category
  • Caption: Name
  • Key: ProductCategoryID

Level 1 dialog completed
Level 1 dialog completed

Right-click the hierarchy and choose Rename to give the hierarchy a meaningful name for when it's used.

Right click the hierarchy to rename
Right click the hierarchy to rename

3.2. Add the second level

To add another level, select the first level and click Append Level in the toolbar.

Append new level
Append new level

Drag and drop the data source for the second level (e.g., ProductSubcategory$) onto drop a data source.

Drag and drop the second level data source
Drag and drop the second level data source

Update the details. For example:

  • Name: ProductSubcategory
  • Description: Product Subcategory
  • Caption: Name
  • Parent Key: ProductCategoryID
  • Key: ProductSubcategoryID

Level 2 dialog completed
Level 2 dialog completed

3.3. Add the third level

Add the third level by selecting the second, then choosing Append Level in the toolbar.

Append third Level
Append third Level

In our example, we drag and drop the Product$ sheet onto drop a data source.

Excel File/Sheet: Product$
Excel File/Sheet: Product$

The third level settings in our example:

  • Name: Product
  • Description: Product
  • Caption: Name
  • Parent Key: ProductSubcategoryID
  • Key: ProductID

Level 3 dialog completed
Level 3 dialog completed

4. Preview

Under Preview, expand the members to preview the multi-level hierarchy.

Preview of the 3-level hierarchy
Preview of the 3-level hierarchy

5. Using the hierarchy

This hierarchy can now be used to replace a regular column of data (such as ProductID) to gain the benefits of our multi-level organization of data. This can be done in a metric set when working with data directly from a data connector, or in the Process Result of a data cube.

6. 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