416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Creating a Time Dimension

Hide navigation
RSS
Modified on Wed, 02 Oct 2013 05:00 PM Categorized as Dimensions
Click to return to: Documentation | Defining Dimensions


Overview

This article shows how to create a time dimension and preview its hierarchies, levels, and level members.

Note: If you plan to use your time dimension with data from Microsoft Access, Microsoft Visual FoxPro, or the ODBC provider (including Amazon Redshift), you must configure advanced settings (Step 4).

Creating a time dimension

Step 1 - Create a time dimension from the Design Explorer

To create a time dimension from the Design Explorer:

  1. Click Design Explorer in the Sidebar.
    Accessing the Design Explorer from the Sidebar.
  2. Select the Dimensions folder, then click its menu icon.
    The Dimensions menu.
  3. From the Dimensions menu, choose New Time Dimension.
  4. In the text box below the Dimensions folder, type a name for your time dimension, then press ENTER.
    Naming your time dimension.

A new dimension item is created in the Design Explorer and the Time Dimension Designer is displayed in the content area of Dundas Dashboard.

The Time Dimension Designer.

The Time Dimension Designer.


Step 2 - Configure general settings

To configure general settings for your time dimension:

  1. Select the General tab of the Time Dimension Designer.
  2. (Optional) Type a description for your dimension in the Description box. You can enter text up to 2000 characters in length.
  3. (Optional) Enter a URL in the Additional info URL box. Use this option to link to a website that provides more information about your dimension. You will be able to access this link from the Additional Information dialog.
  4. (Optional) Change the name of the dimension member that represents All values.
  5. Next, you must define the date range for the time dimension by choosing a start date and an end date. For either date, you can choose an absolute date value via the date picker, or use a relative date value (⪚ relative to the beginning of the current year, current month, &etc;). If you want to use a relative start date or relative end date, select the Relative start and/or end date option. A dropdown list for selecting a relative date will appear beside the Start date and End date fields.
    Dropdown lists for choosing relative dates.
    Note: The relative date option cannot be used in conjunction with the Advanced tab (⪚ storing the time dimension as a permanent table using an existing data connector). In addition, relative dates cannot be used with Microsoft Access or Visual FoxPro data connectors.

  6. Choose the start date for your time dimension using one of the following options:
    • Type a new starting date in the Start date box.
    • Click the calendar icon to open a date picker for selecting the start date.
    • Click the dropdown list (if available) beside the calendar icon. Choose one of the following relative start dates:
      • Beginning of today
      • Beginning of the current week
      • Beginning of the current month
        Choosing a relative date.
      • Beginning of the current year
      • Advanced - This option opens the Advanced Parameter Options dialog, which lets you specify the relative date as an offset from one of the four choices above. The unit for the offset value depends on your choice. For example, an offset of 2 from the beginning of the current month implies that the start date will be two months from the beginning of the current month.
  7. Choose the end date for your time dimension using one of the following options:
    • Type a new end date in the End date box.
    • Click the calendar icon to open a date picker for selecting the end date.
    • Click the dropdown list (if available) beside the calendar icon. Choose one of the following relative end dates:
      • End of today
      • End of the current week
      • End of the current month
      • End of the current year
      • Advanced - This option opens the Advanced Parameter Options dialog, which lets you specify the relative date as an offset from one of the four choices above. The unit for the offset value depends on your choice. For example, an offset of -1 from the end of the current year implies that the end date will be one year before the end of the current year.
        Setting advanced parameter options.
  8. Click the First day of the week dropdown list and choose the starting day of your week.
  9. Use the Time Periods check boxes to select the time periods you want to appear as the levels of your time dimension hierarchy.
  10. Select one or more calendars (&ie; hierarchies) for your time dimension by using the Calendar check boxes.
  11. If you selected the Fiscal calendar option:
    • Choose the starting month using the Start month dropdown list.
    • If you want the calendar to use the Calendar year + 1 naming convention, select the check box provided.

Step 3 - Set the formatting for level members

The Formatting tab in the Time Dimension Designer lets you:

  • Change name of each level (e.g. so you can have shorter names such as ‘Year’ or ‘Month’ which will also appear in parameter controls).
  • Modify the date format string that is used to format the text of the members in each level of your time dimension.

The Formatting tab in the Time Dimension Designer.

The Formatting tab in the Time Dimension Designer.


To change the name of a level:

  1. In the Formatting tab of the Time Dimension Designer, select the cell in the Name grid column that corresponds to your level.
  2. Click inside the grid cell to begin editing.
  3. Type the new name for the level as desired.

To change the format string for a level:

  1. In the Formatting tab of the Time Dimension Designer, locate the cell in the Format grid column that corresponds to your level.
  2. You can enter a custom format string or choose from a pre-defined list of common formats:
    • To enter a custom format string, click inside the grid cell to begin editing. Type a new date format string in the text box, then press ENTER. For examples of valid format strings, see Date and Time Format Strings.
      Custom datetime format.
    • To choose from a pre-defined list of common formats, click the Choose a pre-defined format button to open the Member format dialog. Select a format from the list, then click OK.
      The Member format dialog.
  3. Verify your new format by reviewing the updated text in the Sample grid column in the Formatting tab.

ISO 8601 formats

If your time dimension is based on the ISO 8601 calendar, you have the option of choosing an ISO 8601 format for the members of the Day level:

  • ISO8601_WEEK - a date using week numbers (⪚ 2006-W18-3 for May 3, 2006).
  • ISO8601_ORDINAL - an ordinal date (⪚ 2006-122 for May 3, 2006).

The Member format dialog for a time dimension based on the ISO 8601 calendar.

The Member format dialog for a time dimension based on the ISO 8601 calendar.


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:

  1. Create a new time dimension using the Fiscal calendar option. Set the fiscal year to start in July and use the Calendar year + 1 naming convention.
    Fiscal year starts in July.
  2. Preview the dimension hierarchy. By default, the month level shows the fiscal month and fiscal year values.
    Previewing the default fiscal hierarchy.
  3. To change the month level formatting to display the calendar month instead of the fiscal month, use the following Format string for the Fiscal Month element in the Formatting tab: @MMMM, 'FY'yyyy

    The “@” character at the beginning of the format string tells Dundas Dashboard to display calendar month and fiscal year values.
    Selecting a mixed format string for the month level.
  4. Preview the dimension hierarchy again to see the desired calendar month and fiscal year values.
    Calendar month and fiscal year values.
  5. To change the month level formatting to display the calendar month and the calendar year, use the following Format string for the Fiscal Month element in the Formatting tab: !MMMM, yyyy

    The “!” character at the beginning of the format string tells Dundas Dashboard to display calendar month and calendar year values (⪚ July, 2010 under the 1st fiscal quarter of fiscal year 2011).

Step 4 - Configure advanced settings

If your time dimension does not use relative start/end dates, the Advanced tab will be available in the Time Dimension Designer. The Advanced tab lets you configure a storage location for your time dimension on a per-data connector basis for performance reasons.

Three storage location options are available:

  • The Using temporary table option is the default setting for data providers that support the creation of temporary database tables. With this option, a temporary table is created in your data source before each query that uses your time dimension is performed.
  • The Not available option indicates that the corresponding data connector cannot be used with your time dimension.
  • The Using permanent table option lets you create a permanent table in the data source that is associated with an existing data connector. This can help to improve query performance as a temporary table does not have to be created before each query that uses your time dimension is performed.

Note: If you switch from the Using permanent table option back to one of the other two choices, any permanent table that you created will remain in the data source (&ie; it won't be automatically deleted).

Data source limitations

If your data connector is for Microsoft Access, Microsoft Visual FoxPro, or ODBC (including Amazon Redshift), you must use the permanent table option because these data providers do not support the creation of temporary tables.

In the case of Microsoft Access, before choosing the permanent table option, check the following:

  • Ensure that the Microsoft Access file is not read-only.
  • If the Dundas Dashboard application is running on Windows XP, give the ASPNET user account the FullControl permission on the Microsoft Access file. You can do this in Windows XP from the Security tab of the Properties dialog for the Microsoft Access file.

Creating a permanent table

To create a permanent table for your time dimension:

  1. Select the Advanced tab in the Time Dimension Designer.
  2. Locate the row in the Time Dimension Storage Location grid that corresponds to your data connector.
  3. Set the Usage dropdown list to Using permanent table. The Create Permanent Table dialog is displayed:
    • In the dialog, enter a new name for the permanent table if desired. The default table name is based on an arbitrary GUID value.
      The Create Permanent Table dialog.
    • Click Create Table. The permanent table is created in your data source and the Table Name column in the Advanced tab is updated with the name of the newly created table.
      The Advanced tab of the Time Dimension Designer.

Updating an existing permanent table

To update an existing permanent table for your time dimension:

  1. Locate the row in the Time Dimension Storage Location grid that corresponds to your data connector.
  2. Click the corresponding Update Table button to regenerate the permanent table. Note that this button is only enabled if the time dimension is currently checked in.

Step 5 - Preview and save your changes

To complete the configuration of your time dimension:

  1. Preview your time dimension by following these steps:
    • Click the Preview tab in the Time Dimension Designer.
    • Use the Hierarchy dropdown list to choose the time dimension hierarchy you want to preview.
    • Fully expand the (All) item to see the members of your time dimension hierarchy.
      Preview of a time dimension.
      If a particular level has too many members, the preview will show only a subset of the members (&ie; members from the start and end of the level). The excluded members are replaced by a single Remaining items entry.
      Too many level members.
  2. Click Save from the Dundas Dashboard Toolbar.
    The Save button.
  3. Click Check In from the Dundas Dashboard Toolbar.
    The Check In button.
    Optionally enter a comment in the Check In dialog, then click Check in.

Related topics


Click to return to: Documentation | Defining Dimensions

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.