Create a time dimension

Contents[Hide]

1. Overview

Dundas BI includes a built-in, default time dimension which contains hierarchies you can use for filtering, sorting, grouping, and drill down.

Default time dimension
Default time dimension

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

Adjust the date range of the time dimension according to the data you're working with. For example, if you have data for years prior to the From or start date, you must change this date to avoid having your data be classified as Unknown in your visualizations.

Note
Adding time periods at lower levels substantially increases the number of members. For example, going from days to seconds increases the number of members by a factor of 86,400 (24×60×60). Choosing a time period with a short interval over a wide range of dates may cause you to run out of memory when storing the data cube in memory.

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 dimension hierarchies

The hierarchies of the time dimension are defined 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. The resulting hierarchies are:

  • Year > Month > Day
  • Year > Week > Day
  • Month of year
  • Day of week

The Month of year and Day of week hierarchies do not depend on the type of calendar and behave differently from the Month and Day levels in the first two hierarchies.

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

    Month level of a hierarchy
    Month level of a hierarchy

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

    Month of year hierarchy
    Month of year hierarchy

3. Formatting time dimension levels

To format a time dimension's levels, open it 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 Formatting section which lets you choose different formatting options for level members per localization culture.

Expand the Formatting section to see the available options.

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.

Click Add new culture and enter a localization culture code/tag such as 'fr-Fr' or 'de-DE'.

Set the desired formats for this culture 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.

Add other cultures and corresponding formatting as desired. Or, change the formatting for the Default Culture.

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

3.1. Fiscal month formatting

If you are defining a fiscal time dimension, you have the option of formatting the month level to display fiscal and/or calendar values. The following example demonstrates this.

First, create a new time dimension using the Fiscal calendar option. Set the fiscal year to start in July and use the Calendar year name + 1 naming convention. Also select Year, Quarter, and Month as the time periods (i.e., levels).

Create a time dimension using fiscal calendar
Create a time dimension using fiscal calendar

Click Submit to create and check in the time dimension. Then open the time dimension for editing again. Scroll down and expand the Preview section.

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)

To change the formatting, expand the Formatting section.

The following table summarizes the Caption formats that are supported:

First character of Caption FormatDisplaysOptions
Not @ or ! Fiscal Month Number and Fiscal Year
  • Use MM to show Fiscal Month Number
  • Use yy or yyyy to show Fiscal Year
@ Calendar Month and Fiscal Year
  • Use MM, MMM, or MMMM to show Calendar Month
  • Use yy or yyyy to show Fiscal Year
! Calendar Month and Calendar Year
  • Use MM, MMM, or MMMM to show Calendar Month
  • Use yy or yyyy to show Calendar Year

To display 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. Then open the time dimension again and go to the Preview section.

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

Similarly, to change the month level formatting to display the Calendar Month Name and Calendar Year values instead, expand the Formatting section.

Set the Hierarchy Level to Month and change the Caption Format to !MMMM, yyyy.

To change the year format to include both calendar years, once again expand the Formatting section. 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

Click Submit to save your changes. Then open the time dimension again and go to the Preview section.

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

Alternatively, change the Caption Format to 'FY' {CYStart:yyyy}/{CYEnd:yyyy} for both years to appear in four digits (for example 2011/2012).

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: 7am-6pm, ET, Mon-Fri