Add simple measures in bulk to a cube

When a cube’s fact table has many fact columns, and you need to create one or more simple measures for each column, you can create simple measures in bulk by defining criteria to select columns and apply an aggregation function to those columns.

Complete the following steps to add simple measures to a cube, in bulk:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

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

   The Semantic Model Editor opens.

3. Open the semantic model that contains the cube to which you want to add measures to in bulk by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. Select the cube you want to edit, and then click the Add a Measure icon. The Add a Measure dialog opens.

   Note: If the Add a Measure icon is not visible, click the More Actions icon, and then select Add a Measure.

5. Select Add Simple Measure, and then click Confirm. The Add Measure dialog opens.

6. Select Bulk Measures. The Add Measure window opens with the Condition section displayed.

7. (Optional) To ensure that measure names are unique inside each cube, in the Name the Measures subsection, turn on the Use Custom Name toggle to display the Measure Name fields and then enter a prefix or suffix or both.

   Notes:

   • By default, measure names are assigned the same name as the related column name.

   • If measure names are repeated during bulk creation, the system displays a warning and appends a number to each measure name so that it is unique. The appended number represents the order in which the measure was created (example: Measure (1)).

   • When using physical column names to name measures, underscores are replaced with spaces and each word in the column name is capitalized (example: birth_date is changed to Birth Date).

8. In the Select the Criteria subsection, select the Subject that the system should use as criteria when selecting columns during bulk measure creation. Subjects include:

   • All Columns: Considers all columns of the table.

   • Column Key: Selects among the table’s key columns (defined physically as Primary or Foreign Keys in the database) .

   • Column Type: Matches columns by the column type.

   • Column Name: Matches columns by using physical names (contains, starts with, ends with).

9. (Optional) If you selected a Subject that displays an additional option to configure in the Select the Criteria subsection, enter the information for that option:

   Subject	Option	Description
   Column Key	Key Type*	The type of key that the system looks for when selecting columns during bulk measure creation. Key types include the following options: All Primary Key Foreign Key
   Column Type	Column Type*	The type of column that the system looks for when selecting columns during bulk measure creation. Types available to choose from are determined by the information in the cube’s fact table.
   Column Name	Filter*	Filter option and text that the system uses to filter the columns by their name during bulk measure creation. Filter includes the following options: Contains Ends With Starts With

   * Required field

10. In the Define Aggregation subsection, edit or change the following options:

    Option	Description
    Aggregation	Method used in reports for grouping and calculating data for each one of the new measures that will get created. Aggregation options include: Average Count Distinct Count Maximum Minimum Sum (default value)
    Excluding Columns	List with the columns that you want the system to exclude from the bulk measure creation process. This list is populated with the fact table columns that matched the criteria previously defined for the bulk measure creation. Leave empty in case you don't want the system to exclude any.

11. Click Create. The measures are created and added to the Measures node inside the cube.

12. Click Save to save changes to the model.