Skip to main content
Pentaho Documentation

Edit Data Source Models

Overview

Walks you through using the Data Source Model Editor for refining your data models.

The Data Source Model Editor guides you through editing relational and multidimensional models created using the Data Source Wizard. The Data Source Model Editor gives you the ability to move fields by dragging them to the appropriate location, mix and match fields from different tables, add fields to more than one category, or remove a field altogether.

You can edit the models with the Data Source Model Editor or use Schema Workbench or Metadata Editor for more advanced modifications. Relational and multidimensional models that have been edited in Metadata Editor and Schema Workbench can no longer be edited in the Data Source Model Editor.

The following tasks are things you can do to refine your relational and multidimensional data models with the Data Source Model Editor. Also included are some things that you can do specifically to refine your multidimensional data models, including:

  • Assign Geographic Properties
  • Assign Time Dimension Properties
  • Use Ordinal Columns
  • Properties of Time Dimension Levels

Edit a Data Source Model

Use these steps to edit a data source model in the User Console.

  1. Open the Data Source Model Editor by clicking Manage Data Sources, selecting a data source, and clicking the Edit icon. The Data Source Model Editor appears.
  2. Select the Reporting tab to edit a Reporting relational model, or the Analysis tab to edit the Analysis multidimensional model.
  3. You can now edit the model or clear the model and start building a model from scratch. For instance, you can choose a field and drag it to the appropriate folder on the Analysis/Reporting model pane. This causes that field to be displayed and used when designing reports.
  4. Click OK when you are done editing your model.

The model builds and appears in the Data Source list.

Delete a Data Source

When you delete a data source, all reports or charts that end-users created using that data source no longer render.

  1. Open the Data Sources window by clicking Manage Data Sources on the console Home page, selecting a data source, and clicking the Edit icon.
  2. Click on a data source in the list to highlight it.
  3. Click on the Remove icon in the Data Sources tool bar. The Remove Data Source window appears.
  4. Click Remove to delete the data source. If you choose not to delete the data source, you can click Cancel to exit the window.

The data source is removed and is no longer available to your users. All reports or charts using that data source no longer work.

Add Member Properties to a Level

Within the Data Source Model Editor, you can add member properties to any level of a data source model for use in generating Interactive and Analyzer Reports.

  1. Click the Analysis tab.
  2. Select a field from the Available pane to the Analysis pane.
  3. With the field selected, click the Add Member Property icon. The New Member Property dialog appears.
  4. Enter a name for the new member property. Click OK.
  5. Set the source column for the new member property. Expand the level with new member property and choose the member property. A yellow caution icon appears next to the parent dimension, hierarchy, and level of the new child member property. The icon indicates you need to specify the source column for the new member properties before preceding.
  6. Specify the source column for the new member property. Click Fix Missing Column. The Select Source Column dialog appears.
  7. Chose a column from the available list. Click OK.

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. In order 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 effect 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.