Skip to main content
Version: Next

Add Semi-Additive Measures

In AtScale, creating a Semi-Additive measure 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.

note

To configure the default totaling behavior for semi-additive measures, you can use the query.planning.semiAdditive.defaultAggregationWhenOutOfScope engine 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 measure'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 Engine Level Configuration Settings.

Before you begin​

See also: Semi-Additive Measures.

info

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

Procedure​

To add a Semi-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. Complete the requested information in the Create a Measure dialog; for details, see the sections below.

  5. Click Save Measure.

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

Field reference​

Name​

The user-friendly name that users will see when they connect to the cube from a client tool. The name should denote that this measure is Semi-Additive so query users can distinguish it from a regular additive measure.

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 if you want - just don't change the Query Name once the cube has been published.

Visible?​

If selected, the measure will be visible in 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."

Description​

This is 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.

Aggregation Type​

For Semi-Additive Measures, you can only choose AVERAGE, SUM, MIN, or MAX.

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.

Semi-Additive Measure​

Set to First Non-Empty or Last Non-Empty. Then choose 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 following example for a scenario in which you might want to do this.

note

If you do not see a dimension in the drop-down list, make sure the dimension has been added to the cube 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.

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 measure with the Semi-Additive Measures field set to Last Non-Empty and select both the Date and Strategy Round attributes. The analysts can then apply this single measure 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 measures more flexible: you can satisfy the analysts’ two requirements without having to create multiple measures.

Formatting​

Choose the format for the values. You can specify how values should be formatted for certain types of attributes in a cube. Value formatting controls how the values appear to users in their BI tools. Setting the format preference in the AtScale cube ensures that all BI users see the data values in the same way.

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.