Creating a semantic model

Create a semantic model to organize physical data into a multi-dimensional structure. Use semantic models to analyze data and support business decisions.

Note: Only JDBC connections are supported.

In this topic

• Create a basic semantic model

• Create a cube in a semantic model

• Create a shared dimension

Create a basic semantic model

Create a basic semantic model with the minimum information. Provide a model name and physical data connection details.

Complete the following steps to create a basic semantic model:

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor:

   • If you are using the Modern Design, in the left menu, click Semantic Model Editor.

   • If you are using the Classic Design, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Click + Add New Model.

4. Enter a unique Model Name.

5. Select the data connection to use:

   • Existing Data Connection

     1. Select the connection from the Data Connection list.

     2. (Optional) Add parameters. Turn on Add parameters?. Click Add+ for each parameter row. Enter a Name and Value for each parameter.

        Use parameters to configure Mondrian model processing, JDBC, or dynamic values. The following names are reserved and cannot be used: Provider, Jdbc, JdbcDrivers, JdbcUser, JdbcPassword, Catalog, CatalogContent, CatalogName, DataSource, and DataAccessNotListedCatalog.

   • New Data Connection

     Important: A data connection added during model creation is exclusive to that model. You cannot reuse it elsewhere in the product.

     1. Select New Data Connection.

     2. Enter connection information:

        • URL (required): URL of the data connection.

        • Driver: Driver needed to access the data connection.

        • User (required): User name for the data connection.

        • Password: Password for the data connection.

     3. (Optional) Add parameters. Turn on Add parameters?. Click Add+ for each parameter row. Enter a Name and Value for each parameter.

        Use parameters to configure Mondrian model processing, JDBC, or dynamic values. The following names are reserved and cannot be used: Provider, Jdbc, JdbcDrivers, JdbcUser, JdbcPassword, Catalog, CatalogContent, CatalogName, DataSource, and DataAccessNotListedCatalog.

     4. (Optional) Click Test Connection.

6. Click Create Model.

The semantic model is created and opened in the canvas.

What to do next: Create a cube with fact tables, dimensions, and measures.

Create a cube in a semantic model

Create a cube with a fact table, dimensions, and measures. The cube contains aggregated data from a semantic model’s physical connection.

You can create a new cube by dragging a view or table onto a blank area of the canvas. Then select how to use it.

Note: When you drag a view or table onto the canvas, you can also create a shared dimension. For details, see Create a shared dimension.

In this topic

• Add a fact table to a cube

• Add a dimension to a cube

• Add a measure to a cube

  • Add a simple measure to a cube

  • Add simple measures in bulk to a cube

  • Add a calculated measure to a cube

  • Add a measure created with SQL to a cube

• Add a degenerate dimension to a cube

• Use a shared dimension in a cube

Add a fact table to a cube

Add a fact table that contains the data you want aggregated in the cube. You can create a new cube or add the fact table to an existing cube.

Notes:

• A cube can have only one fact table.

• Using an inline table as a fact table is supported only in Advanced mode. For details, see Advanced mode.

Complete the following steps to add a table or view as a fact table:

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor:

   • If you are using the Modern Design, in the left menu, click Semantic Model Editor.

   • If you are using the Classic Design, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model you want to update:

   1. In the Semantic Models list, find the model.

   2. Click Open.

4. In the Data Source tab, find the table or view you want.

5. (Optional) Click Preview next to the table or view.

   Note: You can preview one table or view at a time. The default maximum rows shown is 100. Administrators can change the row-limit property in application.properties. The file is in \Pentaho\server\pentaho-server\pentaho-solutions\system\semantic-model-editor. Restart the server to apply the change.

6. Add the table or view as a fact table:

   1. For an existing cube without a fact table, drag the table or view onto the cube. When Do you want to: opens, drop it on Use as Fact Table.

   2. To create a new cube, drag the table or view onto a blank canvas area. When Do you want to: opens, drop it on Use as Fact Table.

7. (Optional) If you created a new cube, rename it.

   Note: The default cube name is “Cube” plus a number. Example: Cube 3.

8. Click Save.

Add a dimension to a cube

Add a dimension so users can group data for analysis. You can create a new cube or add the dimension to an existing cube.

Complete the following steps to add a table or view as a dimension:

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor:

   • If you are using the Modern Design, in the left menu, click Semantic Model Editor.

   • If you are using the Classic Design, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model you want to update:

   1. In the Semantic Models list, find the model.

   2. Click Open.

4. In the Data Source tab, find the table or view you want.

5. (Optional) Click Preview next to the table or view.

   Note: You can preview one table or view at a time. The default maximum rows shown is 100. Administrators can change the row-limit property in application.properties. The file is in \Pentaho\server\pentaho-server\pentaho-solutions\system\semantic-model-editor. Restart the server to apply the change.

6. Add the table or view as a dimension:

   • For an existing cube, drag the table or view onto the cube. When Do you want to: opens, drop it on Use as Dimension.

   • To create a new cube, drag the table or view onto a blank canvas area. When Do you want to: opens, drop it on Use as Dimension.

   A dimension is created and the Dimension Editor opens.

7. In Dimension Name, enter a unique Name within the cube.

8. (Optional) If the dimension is not linked to a fact table, connect it:

   Note: If the cube does not have a fact table, add one first. See Add a fact table to a cube.

9. In Dimension Type, select the dimension type.

10. (Optional) Configure Optional Information and Describe Dimension.

11. In the dimension tree, select the hierarchy.

    Note: You can add more than one hierarchy to a dimension.

12. Configure the hierarchy, then select the level.

    Note: You can add more than one level to a hierarchy.

13. Configure the level, then click Apply.

14. (Optional) If you created a new cube, rename it.

15. Click Save.

Add a measure to a cube

Add a measure to quantify cube data. Measures support operations like slicing, dicing, and drilling down.

Note: The cube must have a fact table before you can add a measure.

Complete one of the following procedures:

• Add a simple measure to a cube

• Add simple measures in bulk to a cube

• Add a calculated measure to a cube

• Add a measure created with SQL to a cube

Add a simple measure to a cube

Add a simple measure based on a column in the cube’s fact table.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model that contains the cube.

4. Select the cube, then click Add a Measure.

   Note: If Add a Measure is not visible, click More Actions. Then select Add a Measure.

5. Select Add Simple Measure, then click Confirm.

6. Enter values in Mandatory Information.

7. (Optional) Configure Optional Information and Describe Measure.

8. Click Create.

9. Click Save.

Add simple measures in bulk to a cube

Create multiple simple measures at once by selecting columns and an aggregation.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model that contains the cube.

4. Select the cube, then click Add a Measure.

   Note: If Add a Measure is not visible, click More Actions. Then select Add a Measure.

5. Select Add Simple Measure, then click Confirm.

6. Select Bulk Measures.

7. (Optional) Turn on Use Custom Name to add a prefix or suffix.

8. Configure the criteria and aggregation.

9. Click Create.

10. Click Save.

Add a calculated measure to a cube

Add a calculated measure using an MDX formula.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model that contains the cube.

4. Locate the cube, then click Add a Measure.

   Notes:

   • If Add a Measure is not visible, click More Actions. Then select Add a Measure.

   • To find elements in large models, use the Model Structure tab.

5. Select Add Calculated Measure, then click Confirm.

6. Enter values in Mandatory Information.

7. (Optional) Configure Optional Information and Describe Measure.

8. Click Create.

9. Click Save.

Add a measure created with SQL to a cube

Add a measure using an SQL expression that references multiple tables or views.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model that contains the cube.

4. Locate the cube, then click Add a Measure.

   Notes:

   • If Add a Measure is not visible, click More Actions. Then select Add a Measure.

   • To find elements in large models, use the Model Structure tab.

5. Select Add with SQL, then click Confirm.

6. Enter values in Mandatory Information.

7. (Optional) Configure Optional Information and Describe Measure.

8. Click Create.

9. Click Save.

Add a degenerate dimension to a cube

Add a degenerate dimension when you want to describe aggregated data using only the fact table.

Note: The cube must have a fact table before you can add a degenerate dimension.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model that contains the cube.

4. Locate the cube, then click Add a Degenerate Dimension.

   Notes:

   • If the icon is not visible, click More Actions. Then select Add a Degenerate Dimension.

   • To find elements in large models, use the Model Structure tab.

5. Configure the degenerate dimension, hierarchy, and level.

6. Click Apply.

7. Click Save.

Use a shared dimension in a cube

Use a shared dimension to keep dimensions consistent across cubes.

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor.

3. Open the semantic model you want to update.

4. Locate the shared dimension and the cube.

   Note: To find elements in large models, use the Model Structure tab.

5. Drag the shared dimension onto the cube. When Do you want to: opens, drop it on Use as Dimension.

6. Enter a unique Name for the dimension usage.

7. Configure required fields, then click Apply.

8. Click Save.

Create a shared dimension

Create a shared dimension for aggregated data you want to reuse across cubes. For example, create a shared time dimension and use it in several cubes.

Complete the following steps to add a table or view as a shared dimension:

1. Sign in to the Pentaho User Console (PUC).

2. Open the Semantic Model Editor:

   • If you are using the Modern Design, in the left menu, click Semantic Model Editor.

   • If you are using the Classic Design, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model:

   1. In the Semantic Models list, find the model.

   2. Click Open.

4. In the Data Source tab, find the table or view you want.

5. (Optional) Click Preview next to the table or view.

   Note: You can preview one table or view at a time. The default maximum rows shown is 100. Administrators can change the row-limit property in application.properties. The file is in \Pentaho\server\pentaho-server\pentaho-solutions\system\semantic-model-editor. Restart the server to apply the change.

6. Drag the table or view onto a blank canvas area. When Do you want to: opens, drop it on Use as Shared Dimension.

   The Shared Dimension Editor opens.

7. Configure the shared dimension, hierarchy, and levels.

8. Click Apply, then click Save.