Designing and Creating Hierarchies

Objectives

After completing this lesson, you will be able to:

  • Explain the prerequisites required for creating hierarchies.
  • Create a dimension and hierarchy.

Hierarchies

A hierarchy is used to establish parent-child relationships within your data. For example, suppose you have sales data for the following:

  • Worldwide

  • Region

  • Country

  • State/Province

  • City

A hierarchy allows you to organize these geographic areas into logical levels (also called nodes), from the largest area to the most granular.

The hierarchy column in a dimension is used to store the parent-child values. You can add hierarchies by selecting +Add Hierarchy on the toolbar. When you choose this option, a new hierarchy column is inserted into the dimension, and you must enter the name of the new hierarchy.

The hierarchy is visualized in the Preview panel in the dimension. You can use drag-and-drop in the Preview panel to arrange the members and build the relationships. If more than one hierarchy has been defined, you can select the one you want to display in the preview from the drop-down list.

General Information about Hierarchies

Dimension types that allow more than one hierarchy can have either one or more level-based hierarchies or one or more parent-child hierarchies, but not a combination of both.

In an account-based model, the Account dimension type has only one system-generated parent-child hierarchy, but in a measure-based model, multiple account hierarchies are allowed.

For the Date dimension type, hierarchies are predefined based on the model granularity, and whether you have enabled fiscal time for the model. You can specify a default hierarchy in the settings for the date dimension. In a measure-based model, additional custom hierarchies may be added. We will cover custom time hierarchies in more detail later in the course.

The Version dimension type does not have a hierarchy, because different versions are independent from one another and do not have parent-child relationships.

Types of Hierarchies

Two types of hierarchy are available:

  1. Level-based hierarchy: A level-based hierarchy organizes multiple dimensions or properties into levels, such as country, state, and city.
  2. Parent-child hierarchy: A parent-child hierarchy organizes the members of a single dimension into a set of parent-child relationships.

Level-based hierarchy

The following figure shows dimensions with a level-based hierarchy.

Add a level-based hierarchy from dimension settings

Parent-child hierarchy

The following figure shows dimensions with a parent-child hierarchy.

Dimension member IDs with a hierarchy column and a hierarchical display

Parent-child hierarchies can be maintained manually in SAP Analytics Cloud or imported from files or SAP systems.

Note

When working with parent-child hierarchies for a planning model, avoid situations where data can be booked directly to a parent node.

These situations include the following:

  • Structuring two or more hierarchies so that a member is a leaf node in one hierarchy and a parent node in a different hierarchy.

  • Updating a hierarchy so that a leaf node that has a value booked to it is changed to a parent node.

Watch this video to learn how to create a level-based hierarchy.

Create the Stores Dimension, Hierarchy, and Properties

Business Example

You are creating a model for your team and you need to create a public dimension (as it will be used in multiple models) with a level-based hierarchy and custom properties. There is also a requirement to perform roll-ups and report by property values.

In this practice exercise, you will:

  • Create a public dimension
  • Add properties to the dimension
  • Use the hierarchy builder to add hierarchies to the dimension
ExerciseStart Exercise

Log in to track your progress & complete quizzes