Assign time dimension properties

Typically, you might sort your data by year or month. However, by designating a time dimension in the Data Source Model Editor, you can filter on a variety of date ranges and relative date options, such as previous periods, before [a user-defined period], after [a user-defined period], current period, or next period.

Analyzer supports many types of relative date filters. To apply them to a level of a time hierarchy, you need to define the time-specific properties for that level. This is because each data warehouse implementation may have a different date format and set of time hierarchy levels.

These steps show you how to assign a time property to a dimension in a multidimensional model.

1. Within the Data Source Model Editor, click the Analysis tab.

2. Select the dimension you want to assign as a time dimension.

3. Within the Properties pane on the right, click the Time Dimension box to assign the dimension as a time dimension.

4. Expand the dimension to display its hierarchy, then expand the hierarchy to display its levels. Choose the level for which you want to assign time dimension properties.

   The properties of the level display in the Properties pane.

   The options for Time Level Type and Source Column Format do not display for a child-level if the time dimension property is not set for its parent-dimension.

5. Set the Time Level Type and Source Column Format to match your data.

   These settings must match the format stored in your data source.

   These settings do not change how values display.

   For more information on time level types and formats, see Pentaho Schema Workbench.

Understand time level settings

The Time Level Type and Source Column Format drop-down menus in the Properties pane allow you to specify how time-specific data is formatted in your data source.

Time Level Type specifies the role of a level and the increment it represents.

Analyzer uses it for time-period functions and relative date filters.

Source Column Format specifies how the source column value is formatted for that increment.

Analyzer uses it when filtering to the levels of a time dimension.

Setting Source Column Format does not change how values display.

It describes your data format so filtering works as expected.

Common formats

Here are commonly used formats for time measurements:

• Years: yy, yyyy

• Quarters: Q, QQ, QQQ

• Months: M, MM, MMM

• Weeks: w, ww, W

• Days: d, dd, D, DDD, yyyy-MM-dd

• Hours: k, kk, H, HH, K, KK

• Minutes: m, mm

• Seconds: s, ss

If the formats in the drop-down list do not match your data, enter a custom value in Source Column Format.

Your format must follow the ICU SimpleDateFormat specification.

Examples

If a level has Time Level Type set to Quarters, the level corresponds to quarter increments.

If the source values are numeric (1 through 4), set Source Column Format to Q.

If the source values are strings (Q1, Q2, Q3, Q4), set Source Column Format to 'Q'Q.

If the source values include the year (2001-Q1, 2001-Q2, ...), set Source Column Format to yyyy-'Q'Q.

Set an ordinal column (optional)

Ordinal columns change the default sort order of a level when members are fetched from a database.

For example, month_name sorts alphabetically by default.

If you sort month_name by an ordinal column such as month_id, months can sort chronologically instead.

1. Select the level you want to update.

2. In the Properties pane, under Ordinal Column, click Edit.

   The Select Ordinal Column dialog box appears.

3. Select the column to use as the ordinal column, then click OK.