How to create a custom user hierarchy

Contents[Hide]

1. Overview

This article demonstrates how to create a custom user hierarchy. In most cases when there are primary-foreign key relationships between facts tables, the levels and relationships are automatically detected by the Hierarchy wizard. Refer to the Automatic joins and hierarchies article for more details.

In case such levels are not automatically detected, you need to create a custom hierarchy, as can be seen from the sample below.

2. Data

For example, you have 3 different excel files, or one excel file with 3 different sheets having the Adventure Works for SQL Server 2012 equivalent of the following tables:

3. Pre-requisite

You need to first create a Data Connector. In this particular sample, an Excel-based Data Connector is used.

4. Steps

Here are the detailed steps on how to create a 3-Level custom user hierarchy (Product Category -> Product SubCAtegory -> Product):

  1. Create a new Hierarchy. Use the main menu to create a new user hierarchy.

    Create a new hierarchy
    Create a new hierarchy

  2. Add and Setup the First level of the hierarchy (Product Category)
    1. Expand your data connector in the EXPLORE window and find the ProductCategory$ table.
    2. Drag and drop the ProductCategory$ table to the canvas.
      Excel File/Sheet: ProductCategory$
      Excel File/Sheet: ProductCategory$
    3. Preview the contents of the level.

      Hierarchy Level 1 Preview
      Hierarchy Level 1 Preview

    4. Click Level 1 of the structure to open the dialog.

      Hierarchy Level 1 dialog
      Hierarchy Level 1 dialog

    5. Update the details. For example:
      • NAME = ProductCategory
      • DESCRIPTION = Product Category
      • CAPTION = Name
      • KEY = ProductCategoryID

      Hierarchy Level 1 dialog
      Hierarchy Level 1 dialog

  3. Rename the Hierarchy - use a meaningful name.

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

    Enter the new name
    Enter the new name

Add and Setup the Second level of the hierarchy (Product Sub-Category)

  1. Select the First Level of the hierarchy and click Append level

    Append new Level
    Append new Level

  2. Drag and drop the ProductSubcategory$ table to the DATA SOURCE field.
    Excel File/Sheet: ProductSubcategory$
    Excel File/Sheet: ProductSubcategory$
  3. Update the details. For example:
    • NAME = ProductSubcategory
    • DESCRIPTION = Product Subcategory
    • CAPTION = Name
    • PARENT KEY = ProductCategoryID
    • KEY = ProductSubcategoryID

    Hierarchy Level 2 dialog
    Hierarchy Level 2 dialog

  4. Add and Setup the Third level of the hierarchy (Product)
    1. Select the Second Level of the hierarchy and click Append level

      Append new Level
      Append new Level

    2. Drag and drop the Product$ table to the DATA SOURCE field.
      Excel File/Sheet: Product$
      Excel File/Sheet: Product$
    3. Update the details. For example:
      • NAME = Product
      • DESCRIPTION = Product
      • CAPTION = Name
      • PARENT KEY = ProductSubcategoryID
      • KEY = ProductID

      Hierarchy Level 3 dialog
      Hierarchy Level 3 dialog

5. Preview

Expand the levels to preview data

Preview of the 3-Level Hierarchy
Preview of the 3-Level Hierarchy

6. Using the hierarchy

A typical application for a user hierarchy (such as Product) is in the Data Cube Designer. A user hierarchy can be used to replace an output column of a data cube (such as ProductID) to gain the benefits of multi-level organization.

See Replace a data cube column with a hierarchy for more details.

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