Create a time dimension

Contents[Hide]

1. Overview

Dundas BI includes a time dimension with hierarchies ready to use for filtering, sorting, grouping, and drill down.

Default time dimension
Default time dimension

This time dimension has various settings you are able to customize. You can also create new time dimensions based on Gregorian or other calendar systems such as FiscalISO 8601, and Reporting (Marketing).

Related video: Introduction to Time Dimensions

2. Creating a new time dimension from the main menu

First, go to the main menu, click New, and then click Time Dimension.

New time dimension
New time dimension

This opens the New Time Dimension dialog.

Click inside the File name text box. Enter the name of your time dimension in the Save File dialog and select the Time Dimensions folder location. Then click Submit.

Choose a name and location for your time dimension
Choose a name and location for your time dimension

Returning to the New Time Dimension dialog, select the type of calendar you want: Gregorian, Fiscal, ISO 8601, or Reporting (Marketing). The remaining fields are different depending on your choice.

2.1. Gregorian

A Gregorian calendar is also known as a Western calendar and is the most widely used general purpose calendar.

To set up a Gregorian calendar, first decide what day you want as the first day of the week. By default, this is Sunday.

Next, select the time periods. The time dimension hierarchies and their levels will be automatically created based on this selection.

Gregorian calendar fields
Gregorian calendar fields

Note
Adding time periods at lower levels increases the number of members. For example, going from days to seconds increases the number by a factor of 86,400 (24×60×60). Choosing a short time period over data spanning a wide range of dates can increase memory requirements if used in a data cube stored in-memory.

Adjust the date range of the time dimension according to the data you're working with, which can determine the range of dates selectable in filters.

By default, the start date is set to a fixed value equal to eight years before the beginning of the current year. The To or end date is not fixed by default and is instead set to the token value End of current year. By using a token, the end date will change automatically as time advances from year to year.

The end date is specified with a token by default
The end date is specified with a token by default

Click Submit to create the time dimension.

Time dimension created
Time dimension created

2.2. Fiscal

A Fiscal calendar corresponds to the 12-month accounting period (or fiscal year) for an organization. This period may be different from the calendar year.

To set up a Fiscal calendar, choose the first day of the week.

If you want the calendar to use a Calendar year + 1 naming convention, select the Calendar year name + 1 option.

Then choose the starting month for the fiscal year.

Fiscal calendar fields
Fiscal calendar fields

Select the time periods, which will define the time dimension hierarchies and their levels.

Update the date range as described in the Gregorian case, and then click Submit to create and check in the time dimension.

2.3. ISO 8601

The ISO 8601 calendar is an international standard which is based on week numbers (e.g., 2016-W01 represents the first week of 2016).

To set up the ISO 8601 calendar, select the time periods, which will define the time dimension hierarchies and their levels.

Update the date range as described in the Gregorian case, and then click Submit to create and check in the time dimension.

ISO 8601 calendar fields
ISO 8601 calendar fields

2.4. Reporting (Marketing)

A Reporting calendar is commonly used in retail and manufacturing industries. This calendar divides a year into 4 quarters, where each quarter is comprised of 13 weeks. The 13 weeks within each quarter may be further divided into a combination of 4-week months and 5-week months. For example, a 4-5-4 Reporting calendar defines a quarter using the combination of a 4-week month, followed by a 5-week month, and then followed by a 4-week month. The other possible combinations are 4-4-5 and 5-4-4 which are the month rules you can select when you set up a time dimension with a Reporting calendar.

Reporting calendar fields
Reporting calendar fields

The Reporting calendar option effectively lets you define a month-week hierarchy. Select the time periods, which will define the time dimension hierarchies and their levels.

Once you're done selecting the various options, click Submit to create and check in the time dimension. You'll need to do this before you can format or preview the levels of the time dimension.

2.5. Time hierarchies and attributes

The hierarchies available from a time dimension are based on the indicated type of calendar and the selected time periods. For example, the built-in default time dimension has a Gregorian calendar with the Year, Month, Week, and Day time periods. Since weeks are not divisible into months, the resulting hierarchies are:

  • Year > Month > Day
  • Year > Week > Day

Within each hierarchy there may also be attributes available:

  • Quarter of year
  • Month of year
  • Week of year
  • Day of week
  • Day of month
  • Hour of day

These attributes are available for any calendar type and behave differently from the Month and Day levels of hierarchies:

  • The Month and Day levels group values occurring during a single month occurring in a particular year, or a single day occurring during a particular week:

    Month level of a hierarchy
    Month level of a hierarchy

  • Month of year groups the values by month from all years, and Day of week groups the values occurring during a single day of any week:

    Month of year attribute
    Month of year attribute

  • Week of year groups the values by week number for all years:

    Month level of a hierarchy
    Month level of a hierarchy

  • Day of month groups the values by day number for all months and years:

    Day of month attribute
    Day of month attribute

After adding a time dimension hierarchy, you can additionally click to add or drag one of its attributes the same way you can add attributes from non-time hierarchies. You can also drag one of the attributes on its own.

Time dimension attributes
Time dimension attributes

3. Formatting time dimension levels

You can format a time dimension's levels when you open an existing time dimension from the main menu. You can also double-click it in the Explore window, or right-click (long-tap) it and choose Edit.

In the Time Dimension dialog, scroll down and click to expand the Formatting section, which lets you choose different formatting options for each hierarchy level as well as for different cultures.

Formatting options
Formatting options

If the time dimension you're editing has multiple hierarchies, first select a Hierarchy for formatting.

Next, choose a Hierarchy Level to format.

Set the desired formats in the Caption Format and Short Caption Format boxes. These can be .NET standard date and time or custom date and time formats.

Tip
Short formats are used where upper level details are not needed, such as when displaying each level as a separate row of axis labels on a chart.

The Culture dropdown optionally allows you to set up different caption formats for users logged in with different cultures (languages). You can click Add new culture and enter a localization culture code/tag such as fr-Fr or de-AT, then switch between options in the culture dropdown while setting up your formats.

Click the Submit button at the bottom of the dialog to save your changes.

3.1. Fiscal month formatting

Fiscal time dimensions provide the option of formatting months using either fiscal or calendar values, or a combination of both.

After opening your fiscal time dimension, scroll down and expand the Preview section, and observe that the month level is formatted to display Fiscal Month Number and Fiscal Year values.

Month level shows Fiscal Month Number (01) and Fiscal Year (2011)
Month level shows Fiscal Month Number (01) and Fiscal Year (2011)

The following table summarizes the formats that are supported when setting up the captions in the Formatting section:

First Character of FormatDisplaysOptions
Not @ or ! Fiscal Month Number and Fiscal Year
  • Use MM to show Fiscal Month Number
  • Use yy or yyyy to show Fiscal Year
Example: 'FM' MM, yyyy
@ Calendar Month and Fiscal Year
  • Use MM, MMM, or MMMM to show Calendar Month
  • Use yy or yyyy to show Fiscal Year
Example: @MMMM, yyyy
! Calendar Month and Calendar Year
  • Use MM, MMM, or MMMM to show Calendar Month
  • Use yy or yyyy to show Calendar Year
Example: !MMMM, yyyy

As an example of displaying the Calendar Month Name and Fiscal Year values, set the Hierarchy Level to Month and change the Caption Format from 'FM' MM, yyyy to @MMMM, yyyy.

Changing the caption format for the month level
Changing the caption format for the month level

Click Submit to save your changes. Re-opening the time dimension again and going to the Preview section shows the results:

Month level shows Calendar Month Name (July) and Fiscal Year (2011)
Month level shows Calendar Month Name (July) and Fiscal Year (2011)

3.2. Fiscal year formatting

You can change the default fiscal time dimension formatting to include both calendar years at the Year level by using {CYStart:<format>} and {CYEnd:<format>} placeholders, where you can specify the formats for each.

For example, with the Hierarchy Level set to Year, change the Caption Format to 'FY' {CYStart:yyyy}/{CYEnd:yy}.

Changing the caption format for the year level
Changing the caption format for the year level

After submitting the dialog to save your changes, opening the time dimension again and expanding the Preview section shows these results:

Year level shows both calendar years
Year level shows both calendar years

3.3. Week ending formatting

To format the Week level to include the end of the week, use {CWStart:<format>} and {CWEnd:<format>}.

For example, if the Caption Format is set to {CWStart:MMM/d/yyyy} 'to' {CWEnd:MMM/d/yyyy}, weeks will be displayed in the style May/26/2019 to Jun/1/2019.

3.4. Calendar quarter ending formatting

To format the Quarter level to include the end of the quarter, use {CQStart:<format>} and {CQEnd:<format>}.

For example, if the Caption Format is set to {CQStart:MMM/yyyy} 'to' {CQEnd:MMM/yyyy}, quarters will be displayed in the style Apr/2019 to Jun/2019.

4. Previewing your time dimension

Open the time dimension from the main menu. You can also double-click it in the Explore window, or right-click (long-tap) it and choose Edit.

In the Time Dimension dialog, scroll down to find the Preview section which lets you preview the hierarchy levels and members of your time dimension.

For example, the figure below shows the preview for a Reporting calendar used by a large retail company.

Previewing a Reporting time dimension
Previewing a Reporting time dimension

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri