Skip to main content
Version: I2023.4.1

Add Additive or Non-Additive Measures

You can add additive or non-additive measures to a cube by choosing a column in the fact dataset, and choosing a supported aggregate calculation to apply to the data in that column.

Before you start

When using Databricks SQL as a data warehouse, you can use the databricks.estimateddistinctcount.deviation engine setting to specify the maximum relative standard deviation allowed for the approx_count_distinct aggregate function. The default value is 0.02.

Procedure

To add an additive or non-additive measure:

  1. Open the main cube canvas.

  2. Locate the fact dataset for your cube model.

  3. Click the plus icon or drag a column to the Measures panel.

  4. In the Create a Measure dialog, enter the following general details:

    • Measure Name: The user-friendly name that users will see when they connect to the cube from a client tool.
    • Query Name: The alias that the AtScale query engine will use for the target column. This can be the same as the Target Column name, or you can enter a more readable name.
    • Description: A description of the data that this measure contains. Some BI tools can show this description, some can't. It's a good practice to enter descriptions to help other people get familiar with the data in the cube.
  5. In the Sources section, enter the following details:

    • Dataset: The source dataset that contains the column that the measure is based on. This should be the fact dataset of the cube model.
    • Target Column: The dataset column that the measure is based on.
  6. In the Aggregation Handling section, enter the following details:

    • Aggregation Type: The aggregate calculation to apply to the data.

      Type of MeasureList of Measures
      Additive MeasuresAverage Distinct Count Estimate Max Min Non-distinct Count Population Standard Deviation Population Variance Sample Standard Deviation Sample Variance Sum
      Non-Additive MeasuresDistinct Count Percentile
    • Semi-Additive Measure: This field appears when the selected aggregation type is Max, Min, or Sum. Keep the value set to Default Behavior.

    • Quality: These options appear when the selected aggregation type is Percentile. For details, see Non-Additive Measures.

    • Percentile: This field appears when the selected aggregation type is Percentile. For details, see Non-Additive Measures.

  7. In the Data Handling and Formatting section, enter the following details:

  8. In the Visibility in Published Data Sources section, enter the following details:

    • Include in the list of available measures: If selected, the measure will be visible in published versions of the cube. You may want to make a measure not visible if you intend to use it in a calculation, but not as a measure on its own.
    • Folder: (Optional) The name of a folder that this measure should go in. If your cube has a lot of measures, folders are a good way to organize them.
  9. Choose Save.

    The new measure appears in the Measures panel and the Preview panel of the cube.