Skip to main content

๐Ÿ“† Calendar Dimension

This article introduces how to create a calendar-type dimension for a semantic model and how to use it in a story dashboard.

Creating a Calendar Shared Dimensionโ€‹

When it comes to time-related analysis, data often needs to be aggregated at different time granularities, such as year, quarter, month, and day. Therefore, we need to specify the time dimension for the multidimensional model during modeling. The data for the time dimension should be fixed data unrelated to actual business data, such as a list of dates where the month and year do not vary between companies. Thus, we only need to generate the corresponding table data as needed.

In this article, we use the time_by_day table as the calendar table to create a shared time dimension. The previous article Shared Dimension has already introduced how to create a shared dimension, so we won't repeat it here.

Steps are as follows:

  1. Create a new shared dimension based on the time_by_day table, name it Time, label: Calendar;
  2. After creation, it defaults to the default hierarchy, open the property settings interface, and modify the Primary Key: time_id
  3. In the dimension tab, modify the dimension Type: Time
  4. Create the following level fields:
    1. Year
      1. Label: Year,
      2. Name: Year,
      3. Field: the_year,
      4. Unique Members: true,
      5. Time Level Type: Year
      6. Semantic Type: Calendar Year
    2. Quarter
      1. Label: Quarter,
      2. Name: Quarter,
      3. Field: quarter,
      4. Unique Members: false,
      5. Time Level Type: Quarter
      6. Semantic Type: Calendar Quarter,
      7. Time Formatter: [yyyy].['Q'Q]
    3. Month
      1. Label: Month,
      2. Name: Month,
      3. Field: month_of_year,
      4. Unique Members: false,
      5. Caption Field: the_month,
      6. Time Level Type: Month
      7. Semantic Type: Calendar Month,
      8. Time Formatter: [yyyy].['Q'Q].[M]
    4. Day
      1. Label: Day,
      2. Name: Day,
      3. Field: the_date,
      4. Unique Members: true,
      5. Time Level Type: Day
      6. Semantic Type: Calendar Day,
      7. Time Formatter: [yyyy].['Q'Q].[M].[yyyy-MM-dd]
  5. Save

Points to note:

  • Only the Year and Day fields have the Unique Members attribute set to true, while Quarter and Month fields are false, because the values of quarter and month_of_year cannot independently serve as summary dimensions, otherwise the summary results would not be meaningful.
  • We specified the Caption Column for Month as the_month, so that when displayed, the month description is not just in numeric form but in language text (here, English words).
  • The dimension type is set to Time, and each level must have a Level Type set, so the analysis engine can correctly parse calendar-related functions, such as:
    • ParallelPeriod([level[, index[, member]]])
    • PeriodsToDate([level[, member]])
    • WTD([member])
    • MTD([member])
    • QTD([member])
    • YTD([member])
    • LastPeriod(index[, member])
  • The semantic type corresponds to the Time Level Type, and the Formatter attribute is for the front-end program to convert the system's current Date into the actual value of the multidimensional model, referring to the date formatting format of https://date-fns.org/.

Creating Multiple Hierarchiesโ€‹

In addition to the default hierarchy, we can also create multiple hierarchies for a dimension, for example, creating a hierarchy based on the week convention:

Click the Dimension menu in the menu, click New Hierarchy, go to the new tab, and enter the following information in the Hierarchy property settings interface:

  1. Name: Weekly
  2. Caption: Weekly Calendar
  3. Dimension Table, Table Name: time_by_day
  4. Primary Key: time_id

Create the following level fields:

  1. Year
    1. Field: the_year,
    2. Unique Members: true,
    3. Time Level Type: Year,
    4. Semantic Type: Calendar Year
  2. Week
    1. Field: week_of_year,
    2. Unique Members: false,
    3. Time Level Type: Week,
    4. Semantic Type: Calendar Week,
    5. Time Formatter: [yyyy].[W]
  3. Day
    1. Field: day_of_month,
    2. Unique Members: false,
    3. Time Level Type: Day,
    4. Semantic Type: Calendar Day,
    5. Time Formatter: [yyyy].[W].[Do]

Adding the Calendar Dimension to the Multidimensional Modelโ€‹

Open the Sales model created in the Multidimensional Data Model, and drag the calendar dimension (Time) into the dimension area. Select the Time dimension and modify its Foreign Key: time_id. After saving, you can use the calendar dimension of the model.