Designing and Creating Time Hierarchies

Objectives
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 custom time hierarchies

Build a Custom Time Hierarchy

Time Hierarchy Overview

The Date dimension defines the start and end dates of the model's time frame. It also specifies the granularity of the time units used in the model (year, quarter, month, week, or day).

It can be maintained both manually and automatically by the system. Leaving the management to the system means that the dimension member values are generated from the parameters within the Dimension Settings panel in the Modeler, and in the model Preferences. Conversely, maintaining the Date dimension manually allows you to edit the master data and manage all dimension members and hierarchies freely.

You can specify a default time hierarchy to display in stories, and optionally configure the date dimension to organize data by fiscal year instead of calendar year. A model can contain more than one date dimension if needed.

Weekly Time Patterns

The supported weekly time patterns are:

  • 4-4-5
  • 4-5-4
  • 5-4-4
  • 13x4

Custom Time Hierarchy

Custom time hierarchies help you plan and analyze data using one or more hierarchies. They also allow for company-specific fiscal year variants.

To create a custom time hierarchy, you must first set the Dimension Details for the Date dimension type to User-Managed. Now you can add your own custom time hierarchies and edit the predefined hierarchies that come with the time granularity applied to the dimension.

Time hierarchies are level-based which means they are based on properties in the Date dimension. If you need a custom hierarchy you must add properties first.

Semantic Types

Each property you create is assigned to a Semantic Type, which is a time unit assigned to a property within the hierarchy. The Semantic Type options are:

  • Year
  • Half-Year
  • Quarter
  • Month
  • Period
  • Week
  • Day
  • Other

The semantic type Other is reserved for custom properties that you want to attach to other properties as linked descriptions.

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 largest to the smallest time unit. For example, Month must be below Quarter and above Week.
  • Each semantic type within the hierarchy is used only 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.

The following chart is an example of a custom time hierarchy displaying sales values for each season and then drilled down to the Easter Season.

Use Custom Time Hierarchies

Business Example

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

To to this, you need to configure your model for a custom time hierarchy in order to allow you to create a series of charts and drill down to the details by season, and compare current year sales against prior year sales by season.

Task 1: Configure the Model for a Custom Time Hierarchy

Task Flow

In this simulation, you will be guided through completing the following tasks:

  • Ensure the model's week pattern is already set to 4-4-5
  • Activate the user based time settings
  • Create custom properties for holidays and seasons
  • Create a custom time hierarchy by week, holiday, and season

Task 2: Create a Chart by Week

Task Flow

In this simulation, you will be guided through completing the following tasks:

  • Create a chart by week
  • Compare the data to prior periods
  • Use a restricted measure to compare prior year data

Task 3: Create a Bar Chart by Holiday Season

Task Flow

In this simulation, you will be guided through completing the following tasks:

  • Create a bar chart by holiday season
  • Drill down to the details by season

Task 4: Compare Current to Prior Year Sales by Season

Task Flow

In this simulation, you will be guided through completing the following tasks:

  • Create a line chart
  • Create a calculated dimension for year
  • Compare current year sales against prior year sales by season

Log in to track your progress & complete quizzes