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:
-
Open the main cube canvas.
-
Locate the fact dataset for your cube model.
-
Click the plus icon or drag a column to the Measures panel.
-
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.
-
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.
-
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 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.
-
-
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.
-
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.
-
Choose Save.
The new measure appears in the Measures panel and the Preview panel of the cube.