Designing and creating 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
  • 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 largest area to the most granular.

The hierarchy column in a dimension is used to store the parent-child values.

In a classic model, the account dimension has only a single hierarchy but multiple are allowed in new model types.

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

An organization and generic type dimension can have one or more level-based hierarchies, or one or more parent-child hierarchies, but not both.

The following types of dimensions hierarchy features:

  • In a classic model, the account dimension has only a one system-provided parent-child hierarchy but in the new model, multiple account hierarchies are allowed.

  • For the date dimension, 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 the New Model, additional custom hierarchies may be added.

  • The version dimension does not have a hierarchy, as different versions are separate and do not have parent-child relationships.

Scenario: Hierarchies

Flexible time hierarchies and date dimensions

Date dimension

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

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. Also, a model can contain more than one date dimension if needed.

The supported weekly time patterns are:

  • 4-4-5
  • 4-5-4
  • 5-4-4
  • 13x4
Date settings for week-based date patters with drop down showing week pattern options.

Notes on flexible time hierarchies and data dimensions

You can customize date dimensions members, hierarchies and properties to make time more flexible and suited to your planning and analytical needs.

Flexible time hierarchies help you plan and analyze data along an individual time hierarchy and they also allow for company-specific fiscal year calendars.

Data table with date and time columns that can be adapted to suit business needs.
  • The concept of special periods refers to periods that are used to store adjustments postings. Some companies, for example, have a 12 month calendar along with 4 special periods, one for each quarter.
  • Date dimensions can be maintained either manually or automatically by the system.
  • Leaving the management to the system means that the dimension's 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 freely.
  • You can edit the dimension parameters just like you would with a standard date dimension. However, managing dimension members manually unlocks hierarchy management, the ability to create custom properties, and editing properties within the dimension view.
  • If you’ve set the date dimension to user-managed, you can edit the predefined hierarchies that come with the time granularity that are applied to the dimension.
  • You can add properties to date dimensions and specify dates of interest for your reporting or planning purposes, and account for specific periods like holidays, or special sales seasons for instance.
  • Within a hierarchy, each property you create is assigned to a semantic type. A semantic type is a time unit assigned to a property within the hierarchy:
    • Year
    • Half-year
    • Quarter
    • Month
    • Period
    • Week
    • Day
    • Other

Custom date properties

You can add properties to date dimensions and specify dates of interest for your reporting or planning purposes and account for specific periods quickly, like national holidays or special sales periods, for instance.

Date hierarchies are level-based which means they are based on columns (properties) in the Data dimension. So if you need a custom hierarchy you need to add properties first. Each property you create is assigned to a semantic type. A semantic type is a time unit assigned to a property within the hierarchy: Year, Half-Year, Quarter, Month, Period, Week, Day, and Other.

Click Add Property in the Dimension Details pane to add dimension details to a custom time property and a semantic type of year

The semantic type Other is reserved for custom properties that you want to attach to other properties as linked descriptions. In the example below, you can see the Month, Day, Week, and Month, Day properties used as descriptions and linked to their respective properties.

Easter is never the same week from year to year, and if you’re working in sales, reporting on the Easter sales peak can be complex. Using customs properties, you can align the campaigns across the years using time series and make the comparison easier. Rather than trying to compare Easter sales with Easter being on different weeks, you can instead align the years with weeks leading up to Easter and the following weeks to have a single reference, and align the data points on a single time-line.

By flagging periods that are crucial to your business, you’ll be able to pinpoint them promptly and speed up your analysis.

The Season column is used to identify the seasons and will make up level 3 of the hierarchy. The Holiday column contains the assignment of each week to a season and will be the 2nd level of the hierarchy. The weeks will be in level 3.

Holiday season values in custom time property.

In the example above, you can see that week 202216 is in middle of the Easter season. However, we are assuming for our example that the Easter celebration (buying patterns) spans over several weeks, including the previous two weeks. Therefore, week 202215 has a holiday value of -1Week, for example, 1 week prior to Easter week. Week 202214, has a holiday value of -2Week, for example, 2 weeks prior to Easter week, and so on.

Note
The value -1Week or -2Week is arbitrary. The business requirement is to display the week before Easter week as -1Week when Easter season is drilled down on in the story.

Custom time hierarchies

With custom time hierarchies, you can customize date dimensions members, hierarchies, and properties to make time more flexible and suited to your planning and analytical needs. Flexible time hierarchies help you plan and analyze data using one or more hierarchies. They also allow for company specific fiscal year variants.

Date dimensions can be maintained both manually and automatically by the application. Leaving the management to application means that the dimensions 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 freely.

You can edit the dimension parameters just like you would with a standard date dimension. However, managing dimension members manually unlocks hierarchy management, the ability to create custom properties, and editing properties within the dimension view.

If you’ve set the date dimension to user-managed, you can edit the predefined hierarchies that come with the time granularity that are applied to the dimension.

Switching a time dimension to user-managed

In the preceding User Managed Date Dimension figure, the date dimension is being set to User Managed. The 4-4-5 week pattern was set previously in the model preferences. Calendar year is being used since fiscal year was not turned on.

Create the stores dimension, hierarchy, and properties

Task 1:

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.

Task flow

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

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

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

Login or Register