Edit a measure created with SQL in a cube

Edit a measure created with SQL to change the properties that describe the measure; control its visibility and availability; and specify the SQL expression, format, and default aggregation used to calculate the measure.

Complete the following steps to edit a measure created with SQL in a cube:

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 with a measure you want to edit 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. On the canvas, locate the cube, measures node, and measure you want to edit.

   Note: To locate an element in a large semantic model, you can click the Model Structure tab and then navigate to the element by searching or scrolling through the list.

5. Next to the measure's name, click the More Actions icon and select Edit. The Edit SQL Measure window opens.

6. Edit options in one or more of the following sections:

   1. In the Mandatory Information section, edit one or more of the following options:

      Option	Description
      Measure Name*	A unique name for the measure created with SQL.
      Default Aggregation*	Default aggregation to use for the measure in reports.
      SQL Expression*	SQL expression used to calculate the measure's value. Note: Autocomplete options are available for standard SQL functions, keywords, and table columns within the cube's scope.

      *Required

   2. Expand the Optional Information section, and then edit one or more of the following options:

      Option	Description
      Format	Option that determines how the value for the measure is displayed (example: 0.00 or #, ###).
      Visible	Value indicating whether the dimension element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
      Dialect	SQL dialect that corresponds to the SQL expression that you wrote for the measure. In Advanced mode, you can write multiple expressions for the same measure by using a different dialect for each expression. For details, see Advanced mode.
      Data Type	The type of data resulting from the SQL expression used for the measure.

   3. Expand the Describe Measure section, and then edit one or more of the following options:

      Option	Description
      Caption	String of text that is displayed instead of the measure’s name. You can use captions to provide a user-friendly label for reports or for localization so that the measure’s name appears in the local language.
      Description	A description of the measure created with SQL.

7. Click Apply. Edits to the measure created with SQL are applied.

8. In the Semantic Model Editor, click Save to save changes to the model.