Configuring Custom Time Hierarchies

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use custom time hierarchies for customizing date, dimension members, hierarchies, and properties

Flexible Time Hierarchies

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

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. They also allow for company specific fiscal year calendars.

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, and Other.

Note
You can see an example of a weekly time pattern in the U00M_WeeklyHolidaySales model and U00S_SeasonalSales story in the SACMS1 solution folder.

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.

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.

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 Holidays, Easter 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.

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.

In the Add Property Values To Members figure above, 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.

Building 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 preceding Building a Custom Time Hierarchy figure, the hierarchy has 3 levels:

  • Level 3: Season
  • Level 2: Holiday
  • Level 3: Week
Note
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.

The Bar Chart with Custom Time Hierarchy figure is one of the end results. It displays sales values for each season. This meets a business requirement for many consumer products companies.

Use Custom Time Hierarchies

You need to evaluate weekly sales data by holiday season. In this exercise, you will perform the following tasks:

  • Copy a model with data
  • Configure the model for a custom time hierarchy:
    • 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
  • Create a chart by week
    • Compare the data to prior periods
    • Use a restricted measure to compare prior year data
  • Create a bar chart by holiday season
  • Drill down to the details by season
  • Compare current vs. prior year sales by season
    • Create a line chart
    • Create a calculated dimension for year

Prerequisites

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