Designing and creating time hierarchies

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain the prerequisites required for creating hierarchies

Build a custom time hierarchy

The hierarchies you build depend on the properties available in the date dimension. For user-managed date dimensions, you must have at least one hierarchy.

Once the properties have a semantic type assigned, you can add them as levels within the hierarchy. Make sure to follow these guidelines:

  • The hierarchy has the mandatory Year semantic type as the highest level.
  • The semantic types within the hierarchy are ordered from the biggest to the smallest time unit. For example, Month must be below Quarter, and above Week.
  • Each semantic type within the hierarchy is only used once, except for the Other semantic type.
  • If the hierarchy uses the semantic type Month, do not include the semantic type Period, and vice versa.
  • The lowest level of the hierarchy is the ID property if there is one. Otherwise, it must be a unique property.
  • Description properties cannot be used in the hierarchy.

In the example below, the hierarchy has 3 levels:

  • Level 3: Season
  • Level 2: Holiday
  • Level 3: Week

To create a new hierarchy from the Hierarchy Builder, simply click + Create New Hierarchy and then drag and drop the required levels into the custom time hierarchy.

Hierarchy builder screen with levels for season, holiday, and week
The seasonal hierarchy that we are illustrating is just one example of how custom time hierarchies can be used.

The date hierarchies provide many options when it comes to analyzing sales over time. For example, date can be displayed in the rows of a table using a custom seasonal time hierarchy. In addition, the season and holiday custom properties can also be displayed in the columns if needed.

To open the custom time hierarchy, first select the hierarchy from the story. Then select the hierarchy expansion level and display date dimension properties. Once you have done this, you can display your data in a table or chart, as in the example below.

A custom time hierarchy being used in a story.

The chart below is an example of where the holiday season hierarchy has been applied. This meets a business requirement for many consumer products companies. In the top chart, it displays sales values for each season. In the bottom chart, you can see the data for the Easter season.

Two charts. One with external sales per date for actual, the second drilled down for Easter, showing the weeks that are part of the Easter season.

Use custom time hierarchies

Task 1:

Business example

You need to evaluate weekly sales data by holiday season in order to present the findings to you team.

Task flow

In this practice exercise, you will complete the following tasks:

  • Configure the model for a custom time hierarchy
  • Create a chart by week
  • Create a bar chart by holiday season
  • Drill down to the details by season
  • Compare current year sales against prior year sales by season

Save progress to your learning plan by logging in or creating an account

Login or Register