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: The processing method of Semi-Additive Measures changed in AtScale I2023.4.1 and later. To revert to the engine behavior in prior releases (which could result in incorrect results), you must set the query.planning.semiAdditive.defaultAggregationWhenOutOfScope engine setting to False. You do not need to restart the engine after. For more information on engine settings, see Engine Level Configuration Settings.

Before you begin

Procedure

To add a Semi-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.
Complete the requested information in the Create a Measure dialog; for details, see the sections below.
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 one dimension in the cube over which not to summarize values. Instead the result set is sorted in ascending order by the selected dimension's key and either the first (earliest) or last (latest) value of the result set is used.

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.

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.