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 Measure	List of Measures
     Additive Measures	Average, Distinct Count Estimate, Max, Min, Non-distinct Count, Population Standard Deviation, Population Variance, Sample Standard Deviation, Sample Variance, Sum
     Non-Additive Measures	Distinct Count, Distinct Sum, 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:

   • Override Default Handling: For information about this field, see About Queries on Dimensions that are Unrelated to One or More Queried Measures.
   • Formatting: Choose the format for the values. See Formats for Data Values for a description of the available formats.

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.