Data Source Model Editor

The Data Source Model Editor helps you edit models created in the Data Source Wizard.

You can update relational and multidimensional models. You can move fields by dragging them. You can mix fields from different tables. You can add fields to multiple categories. You can remove fields you do not need.

For advanced changes, use Pentaho Schema Workbench or Pentaho Metadata Editor.

Models edited in Metadata Editor or Schema Workbench cannot be edited again in Data Source Model Editor.

Get started

The Data Source Model Editor opens in two cases:

• You select Customize model in the last step of the Data Source Wizard.

• You edit an existing data source later.

1. Open Manage Data Sources.

2. Select a data source.

3. Select Edit.

4. Select Reporting for a relational model, or Analysis for a multidimensional model.

5. Edit the model as needed.

   For example, drag a field to a folder in the model pane.

6. Select OK.

The model builds and appears in the Data Source list.

Tour the Data Source Model Editor

When you open the editor, the center pane shows the active model. The model is organized into categories and fields.

Data Source Model Editor interface

Item	Name	Function
1	Available pane	Displays a list of the available tables and columns of the data source.
2	Analysis / Reporting model pane	Displays the active data source model.
3	Properties pane	Displays the properties associated with a selected category or field.

Analysis toolbar tasks

Analysis Toolbar

Icon name	Function	Definition
Add Measure	Add a measure to the model.	A measure is a property on which calculations can be made, such as a sum, count, or average.
Add Dimension	Add a dimension to the model.	A dimension provides filtering, grouping, and labeling. A dimension is typically a product, location, salesperson, or time.
Add Hierarchy	Add hierarchies to a dimension.	Hierarchies organize data by strict ranks.
Add Level	Add levels to a hierarchy.	A level is a set of objects within a hierarchy that share the same rank.
Add Member Property	Add custom member properties to a level.	Member properties are attributes of a level.
Move Up / Down	Move levels up and down in a hierarchy.	Changing level order changes ranks within the hierarchy.
Remove field	Remove a field from the model.	n/a
Clear Model	Clear the entire model.	n/a

Reporting toolbar tasks

Reporting toolbar

Icon name	Function	Definition
Add Category	Add a category to the model.	A category is a structure that contains linked objects.
Add Field	Add a field to a category.	A field is a place where data is stored within a category.
Move Up / Down	Move items up and down in a list.	Use this to reorder items.
Remove field	Remove a field.	n/a
Add Member Property	Add custom member properties to a level.	Member properties are attributes of a level.
Clear Model	Clear the entire model.	n/a

Add member properties to a level

You can add member properties to any level. You can use them in Interactive Reports and Analyzer reports.

1. Select the Analysis tab.

2. Drag a field from Available to the Analysis pane.

3. With the field selected, select Add Member Property.

4. Enter a name for the new member property, then select OK.

5. Expand the level, then select the new member property.

   A yellow warning icon indicates you must set the source column.

6. Select Fix Missing Column.

7. In Select Source Column, select a column, then select OK.

Edit multidimensional data source models

For multidimensional models, you can add custom properties to levels. These properties improve Analyzer behavior. This includes geography maps and relative date filters.

Assign geographic properties

Analyzer can visualize data on a map.

For common geographies (country, state, city, postal code), the Data Source Wizard can populate geography levels. If your model uses custom fields, set Geography Type on each relevant level.

1. Select the Analysis tab.

2. Select a geographic level.

3. In Properties, set Geography Type.

Repeat for each geographic level you want available for map visualizations.

Assign time dimension properties

Designating a time dimension enables relative date filters. This includes previous period and next period.

To enable these filters, define time properties for the relevant levels in your time hierarchy.

1. Select the Analysis tab.

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

3. In Properties, select Time Dimension.

4. Expand the hierarchy, then select the level you want to configure.

5. Set Time Level Type and Source Column Format to match the format stored in your data source.

Time Level Type and Source Column Format do not display for a child level if Time Dimension is not set for its parent dimension.

Understand time level settings

Time Level Type and Source Column Format describe how your time data is stored. Analyzer uses these settings for time-period functions and relative date filters.

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

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

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 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. This applies 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 sort chronologically.

1. Select the level you want to update.

2. In Properties, under Ordinal Column, select Edit.

3. In Select Ordinal Column, select the column to use, then select OK.

Delete a data source

Deleting a data source breaks reports and charts that use it.

1. Open Manage Data Sources.

2. Select a data source.

3. Select Remove in the Data Sources toolbar.

4. In Remove Data Source, select Remove.

If you do not want to delete the data source, select Cancel.

Tips

• A CSV data source treats all columns as a single table.

• For reporting and analysis from a database table, you must select a fact table.

• Only numeric columns from the fact table can be measures.

• All levels in a single hierarchy must come from the same dimension table.

• Analyzer annotations are not visible in the Data Source Wizard.

• Saving in the wizard overwrites annotations added in Analyzer.