Add Semi-Additive Metrics

In AtScale, creating a Semi-Additive metric allows you to choose dimensions over which the fact data should not be aggregated. Instead, you have the choice of returning the first or last non-empty value of a result set.

info

Semi-Additive Metrics and Calculated Metrics that depend on Semi-Additive metrics cannot be used in User-Defined Aggregates (UDAs). However, AtScale System-Defined Aggregates are able to use Semi-Additive metrics and their dependent Calculated metrics in aggregates.

note

To configure the default totaling behavior for semi-additive metrics, you can use the query.planning.semiAdditive.defaultAggregationWhenOutOfScope setting. When this setting is set to True (default), the first/last non-empty subtotal or grand total is shown only when the query contains one of the metric's configured semi-additive dimension attributes. When this setting is False, the first/last non-empty subtotal or grand total is shown when the query does not contain a configured semi-additive dimension attribute.

You do not need to restart the engine after changing this setting. For more information on engine settings, see Configuring Global Settings.

Creating semi-additive metrics

To create a semi-additive metric:

1. In the Repo Browser, locate the dataset you want to base your metric on, open its context menu and select Create metric. The Edit Metric panel appears.

2. Complete the following fields:

   • Display name: The name of the metric that appears in BI tools. The name should denote that this metric is Semi-Additive so query users can distinguish it from a regular additive metric.
   • Unique name: The unique name of the metric. This value must be unique across all repositories and subrepositories.
   • Description: A description of the metric.
   • Dataset: The dataset that contains the source column the metric is based on. This should be the model's fact dataset.
   • Target column: The dataset column the metric is based on.

3. In the Aggregation type field, select one of the following options: Average, Sum, Min, Max.

4. In the Semi-additive measure field, select either First Non-Empty or Last Non-Empty. The Semi-additive attribute field appears.

5. In the Semi-additive attribute field, select the dimension attribute(s) whose values should not be summarized. The result set will be sorted in ascending order by these dimensions’ keys, and either the first (earliest) or last (latest) value of the result set will be used.

   The dropdown list contains all of the attributes used on related dimensions, as well as the hierarchies they appear in. You can select multiple from the list; see the example below for a scenario in which you might want to do this.

   Example

   Suppose you’re creating a model to analyze data from an investment company. The amount of each account is recorded on a date, and each account has an investment strategy round. The data analysts want to see the balance of the accounts.

   You create a metric with the Semi-additive measure field set to Last Non-Empty and select both the Date and Strategy Round attributes. The analysts can then apply this single metric to the Date dimension and create the two following tables:

   • The last amount on the accounts for each year, month, etc.

   • The balance of all accounts for the last investment rounds.

   In short, this functionality makes your metrics more flexible: you can satisfy the analysts’ two requirements without having to create multiple metrics.

   note

   If you do not see a dimension in the drop-down list, make sure the dimension has been added to the model and that it only has one key level defined. If the dimension has multiple hierarchies, you should duplicate the key level for each hierarchy rather than create two separate key levels.

6. 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 Metrics.
   • Formatting: Select the format for the values. If you select Custom, you must enter a custom data format. For more information, see Formats for Data Values.

7. In the Visibility in Published Data Sources section, enable/disable the Include in the list of available metrics option. When enabled, the metric appears in deployed versions of the model.

8. Click Apply.

The new metric appears in the metrics/ folder of the Repo Browser.