Create a time dimension

Contents[Hide]

1. Overview

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

Default time dimension
Default time dimension

You can also create new time dimensions with hierarchies based on Gregorian or other calendar systems such as Fiscal and ISO 8601 (an international standard based on week numbers).

Related video: Introduction to Time Dimensions

2. Create 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. These will be the levels of the time dimension hierarchy.

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.

By default, the start date is set to a fixed value equal to 8 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 different than 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 be the levels of the time dimension hierarchy.

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 be the levels of the time dimension hierarchy.

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.

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.

3. Formatting time dimension levels

Go to the Explore window, expand the Time Dimensions folder and locate your newly created time dimension.

Right-click on the time dimension and select Edit.

Edit the time dimension
Edit the time dimension

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 string such as 'fr-Fr' or 'de-DE'.

Set the desired format strings for this culture in the Caption Format and Short Caption Format boxes.

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

Click Submit to save your changes.

3.1. Display short captioned labels on a chart

As an example, consider a time dimension hierarchy where the Quarter level has been given the short caption format: 'Q's yyyy

Set the short caption format for the Quarter level
Set the short caption format for the Quarter level

Next, drag the above time hierarchy (OrderDate) and a measure (OrderQty) from a data cube to the dashboard canvas.

Open the Data Binding Panel for the resulting table visualization and edit the OrderDate hierarchy.

In the Configure Metric Set Element dialog, set the Level to Quarter. Re-visualize the table as a bar chart.

By default, you'll see that the bottom axis labels of the chart are displayed using the regular/full caption format for the Quarter level of the time hierarchy.

Bottom axis labels use the regular caption format
Bottom axis labels use the regular caption format

To display the axis labels using the short caption format instead, open the Data Binding Panel for the chart.

Click the Bindings tab and scroll down to find the BOTTOM AXIS 1 LABEL binding.

Go to Explore and expand the OrderDate time hierarchy.

Drag the Quarter level and drop it onto the binding. The bottom axis labels now use the short caption format.

Bottom axis labels now use the short caption format
Bottom axis labels now use the short caption format

3.2. 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 month level formatting to display the Calendar Month Name and Fiscal Year values instead, expand the Formatting section.

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)

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

The ! (exclamation) character at the beginning of the format string tells Dundas BI to display calendar month and calendar year values (e.g., July, 2010 should appear under the 1st fiscal quarter of fiscal year 2011 in the preview).

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

4. Previewing your time dimension

Go to the Explore window, expand the Time Dimensions folder and locate your newly created time dimension.

Right-click on the time dimension and select 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