Skip to main content
Pentaho Documentation

Working with Measures in Analyzer

Overview

In Analyzer, you can work with measures in several ways to accomplish tasks, such as renaming a measure, creating a calculated measure, and editing a calculated measure. When updating the details of a measure or adding a calculated measure, you are making changes to the data model from within Analyzer. When you save these changes, they are now a part of the data source for other users to access. We call these types of changes to the data source inline modeling since these changes are reflected immediately in the data source you are working with. This approach allows you to make model changes easily in Analyzer which benefits other users immediately.

To make these types of changes, you must be assigned the 'Manage Data Sources' operation permission in Users and Roles. For more information on setting and maintaining permissions and roles for users, see Use Pentaho Security.

Viewing and Updating Measure Properties

You can update the properties on base measures and calculated measures within Analyzer. For example, you may want to rename 'QTY' to 'Quantity,' change the aggregation method from a sum to an average, or adjust the MDX formula on a calculated measure. When you make and save such a change to a measure, you are making a change to the data source which will affect all users who are creating reports based on that data source. In addition, when you save your report after making inline modeling changes, the Undo and Redo buttons are unavailable. Therefore, it is recommended that you complete all your inline modeling changes, including undoing or redoing those changes, prior to saving the report.

Notes

  • While most users can view the properties of a measure in Analyzer, select users can edit some of these properties. To edit, you must be assigned the 'Manage Data Sources' operation permission in Users and Roles. For more information on setting and maintaining permissions and roles for users, see Use Pentaho Security.
  • You can only update the properties for base measures and for calculated measures which were added to the data source in Analyzer. However, you can only edit the Display Name for calculated measures created in PDI and published to Analyzer. To change additional properties, you will need to edit the calculated measure in PDI.  See Using the Annotate Stream Step for SDR for more information.
  • You can use hidden fields to update calculated measure formulas. When you select the Show Hidden Fields option in the View menu for the Available Fields list, measures set as 'hidden' are available for selection in the Calculated Measure Properties dialog box. To view hidden measures, you need the Manage Data Source permission. See Hide and Unhide Available Fields for additional details.

To View or Update Base Measure Properties

These instructions are for both measures and calculated measures created in PDI and published in Analyzer. Be aware that there are some slight differences in the Properties dialog box based on whether you have selected a measure or a calculated measure.

  1. In the Available fields list on the left, click the name of the measure you want to view or edit, and then click the down arrow to the right of the measure name. A shortcut menu appears.
  2. Click Properties. The Properties dialog box for that measure displays.  Note that if you are assigned the 'Manage Data Sources' operation permission, you can edit several of these fields. Otherwise, these fields are read-only.  

                                                    

  1. You can view and/or edit the following fields.
    Field Description
    Display Name The name of the measure as it displays in the model.
    Aggregation The aggregation type is how the measure combines the data. Use the drop-down list to select an aggregation type from a system-defined list. Options include:
    • SUM
    • AVERAGE
    • COUNT
    • COUNT_DISTINCT
    • MINIMUM
    • MAXIMUM

    Note that COUNT and COUNT-DISTINCT are only applicable for non-numeric fields.

    This field only displays for measures, not for calculated measures.

    Format

    Choose how this measure should be formatted, such as currency, general number, or percentage. Use the drop-down arrow to select a format from a system-defined list, or select the 'Text' icon next to the field to enter a custom format.

    See Format Field Options for more information on selecting the appropriate format for your report.

    Description The description of the measure, if any. This field is always read-only.
    Type The type of field, such as measure. This field is always read-only.
    MDX The formula for the measure as an MDX statement. This field is always read-only.
  2. When finished editing, click OK to save and apply your changes, or Cancel to close the dialog box without saving your changes to the measure.

To View or Update Inline Calculated Measure Properties

These instructions are for calculated measures created within Analyzer using inline modeling. Note that inline calculated measures display with the function symbol ('fx') in the Available Fields list.

  1. In the Available fields list on the left, click the name of the calculated measure you want to edit, and then click the down arrow  to the right of the measure name. A shortcut menu appears.

  1. Click Properties. The Properties dialog box appears for that calculated measure.

  1. You can view and/or edit the following fields. Note that some of the fields only display for users who are assigned the 'Manage Data Sources' permission.
    Field Description
    Name The name of the measure as it displays in the model.
    Format

    Choose how this measure should be formatted, such as currency, general number, or percentage. Use the drop-down arrow to select a format from a system-defined list, or type in the field to enter a custom format. Note that the Format field only displays when the value for the field is a number or a date. If you do not specify a format, the default value of the first base measure is used as the format.

    See Format Field Options for more information on selecting the appropriate format for your report.

    Decimal Places Optionally, specify the number of decimal places for the results.
    Type The type of field, measure, such as calculated measure. This field is always read-only.
    MDX The formula for the measure as an MDX statement. This field is always read-only.
    Formula In the right panel, enter the formula for your calculated measure. You can write the MDX statement, or you can use the list on the left to drag measures into the right panel. You can also use the symbol buttons below to help create your statement, or just use your keyboard to write the expression.
    Create subtotals using measure formula Select this check box to use this calculated measure when adding up subtotals in your report.
  2. When finished editing, click OK to save your changes to the calculated measure to the data source, or Cancel to close the dialog box without saving your changes.

Adding a Calculated Measure to the Data Source

Calculated measures are user-defined measures based on a user's customizations to base measures in the data model. By applying a formula, in this case an MDX statement, to a selected base measure, the user creates a calculated measure to use in her Analyzer report.

Since Pentaho 6.0, you can save your calculated measure to the data model while creating or editing your report in Analyzer. Previously, if you wanted to add a calculated measure to the model for others to use in their reports, you had to submit a request to IT and wait until it was available. Now you can save the calculated measure to the model from within Analyzer so you can use that new measure without reloading your report. In addition, after you save your report, other users can  immediately benefit from your calculated measure.

When you save a calculated measure to the data source, you are making a change to the data model and not just to the report. As a result, when you save your report, the Undo and Redo buttons are unavailable. Therefore, it is recommended that you complete all your changes to the data model, including undoing or redoing those changes, prior to saving the report.

If you want to create a calculated measure to only use in a particular report, see Creating a Calculated Measure in a Report.

To Add a New Calculated Measure to the Data Source

When you create a calculated measure using a measure in the Available Fields list in Analyzer, it is added to the data source when you save your report. To create a calculated measure within Analyzer, create or open an existing report in Analyzer.

  1. In the Available Fields list, click a measure to select it and then click the down arrow next to it.                                                                                            
  2. From the shortcut menu which appears, select Create Calculated Measure. The Create Calculated Measure dialog box appears.

  1. In the Name field, enter a name for your calculated measure.
  2. In the Format field, specify how you want the results of your measure to appear in your report. Optionally, specify the number of decimal places for the results. If you do not specify a format, the default value of the first base measure is used as the format.
  3. In the right panel, enter the formula for your calculated measure. You can write the MDX statement, or you can use the list on the left to drag measures into the right panel. You can also use the symbol buttons below to help create your statement, or just use your keyboard to write the expression.
  4. Select the Calculate subtotals using measure formula check box to use this calculated measure when adding up subtotals in your report.
  5. Click OK to save this calculated measure. When you save your report, your calculated measure will also be saved to the data model. Once you save the report, the measure will be available for future reports which use this data source. Optionally, click Cancel to close the dialog box without saving your changes.

Note

  • You can use hidden fields to create calculated measures. When you select the Show Hidden Fields option in the View menu for the Available Fields list, measures set as 'hidden' are available for selection in the Create Calculated Measure dialog box. To view hidden measures, you need the Manage Data Source permission. See Hide and Unhide Available Fields for additional details.

To edit a calculated measure you have created and saved in Analyzer, see Updating Calculated Measure Properties.