Skip to main content
Pentaho Documentation

Edit Multidimensional Data Source Models

For multidimensional models, the Data Source Model Editor helps you add custom properties to a level, such as geographic or time dimensions. You can also use the Data Source Model Editor to perform tasks such as moving fields by dragging them, setting properties to a measure, adding levels to a hierarchy, moving levels up and down within the hierarchy, and removing fields.

Assign Geographic Properties

Analyzer lets you visualize data on a geographic map. For common geography types, such as Country, State, City, or Postal Code, the Data Source Wizard automatically populates geographic parent-dimensions and subsequent child-levels to use in Analyzer.

To refine the use of Geography Map visualizations in Analyzer, you can associate custom data fields with a particular geography type. After you identify the fields that represent a geography type, you can use Analyzer to visualize this geographic information on a map.

These steps show you how to assign geographic properties to a level with the User Console.

  1. Within the Data Source Model Editor, click the Analysis tab.
  2. Select each of the geographical levels individually and assign each level a Geography Type in the Properties pane. This classifies the data with the correct geography type.

The geography type is now defined for that level.

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 the format of your data. The Time Level Type and Source Column Format must match the format of the data stored in your data source for relative date filtering to work properly in Analyzer. This does not affect how your values display. For more information regarding the time level types and formats, see the section called Enable Relative Date Filters.

Use Ordinal Columns

Ordinal columns are used to alter the natural order of a level when fetching members from a database.

For example, the natural alphabetical order of the level month_name begins with April and ends with September, when represented by a text string. Alternatively, setting the dimension month_name so it is sorted by an ordinal column, such as month_id, enables you to sort the values in the level month_name in chronological order.

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

To set an ordinal column, in the Data Source Model Editor, choose the appropriate level, then click Edit in the Properties pane under Ordinal Column. The Select Ordinal Column dialog box appears.

Properties of Time Dimension Levels

The Time Level Type and Source Column Format drop-down menus in the Properties pane of the Data Source Model Editor 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 of the date it represents. Time Level Type is used by a number of time-period functions in Analyzer.

Source Column Format field lets you specify how a value in the source column is formatted and represents a date increment belonging to that level. This information is used when filtering in Analyzer to the levels of a time dimension.

Assigning the Source Column Format in the Data Source Model Editor does not change how the values in your data display, rather it enables you to describe the format of your data.

Here are several 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 options provided in the drop-down menu do not reflect the format of your data, you can override the formats by entering directly into the Source Column Format field. However, the format of your data must adhere to the ICU Simple Date Format specification.

For instance, a level may have Quarters as Time Level Type, indicating the level corresponds to quarter date increments. If that value is represented by the numbers 1 through 4, you would set the Source Column Format as Q.

Alternatively, the same increment could also be represented as strings Q1, Q2, Q3, and Q4. In this case, you specify the Source Column Format with the value 'Q'Q. The first 'Q' indicates a string, while the second, unquoted Q represents a numerical value, 1 through 4.

Or, your source column could contain values like 2001-Q1, 2001-Q2, 2001-Q3, 2001-Q4, in which case you would input yyyy-'Q'Q into the Source Column Format field