Add a measure created with SQL to a cube

Add a measure created with SQL to a cube when you want to calculate the values for the data you are measuring in a cube by using an SQL expression that references data from multiple tables or views in the cube.

Complete the following steps to add a measure created with SQL:

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. In the Semantic Models list, navigate to the model that has the cube to which you want to add a measure. You can find the model by searching or scrolling through the list.

4. Select the model and click Open. The model opens in the canvas.

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

   Notes:

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

   • 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.

6. Select Add with SQL, and then click Confirm. The Add with SQL dialog opens.

7. In the Mandatory Information section, enter the following, required information:

   Option	Description
   Measure Name	Name for the measure that is unique in the cube for all measures.
   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 generic SQL functions, keywords, and table columns within the cube's scope.

8. (Optional) Expand the Optional Information section and 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 measure 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.

9. (Optional) Expand the Describe Measure section and 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.

10. Click Create. The measure is created with SQL and added to the Measures node inside the cube.

11. Click Save to save changes to the model.