Disaggregation of Values During Data Entry

In SAP Analytics Cloud, when you enter or change data for a planning model cell, the value is automatically spread to leaf members that aggregate up to it. This process is called disaggregation.

For planning models, data is stored in leaf members of each dimension hierarchy. Parent members only show the aggregated values of their children, and generally don't contain values on their own. Because of this, disaggregation happens whenever you change data that represents more than one combination of leaf members for all dimensions.

Tip

Keep in mind that disaggregation doesn’t just happen over the dimensions added to your table axes. For the other dimensions, disaggregation will occur unless they’re filtered to a single leaf member.

Accounts and measures can use different aggregation types, including a default aggregation type and an optional exception aggregation type for specific dimensions. These aggregation types determine whether data entry is supported in different circumstances, and how data entry will be disaggregated.

Note

If your model contains both measures and dimensions, a modeler can choose whether to use aggregation settings from the accounts or measures. To learn more, see Set Structure Priority and Create Custom Solve Order.

Classic account models don’t have separate measures, so the accounts always set the aggregation type.

There are a few ways that you can get more control over disaggregation while planning in a table:

Changing Data for Booked Values

When you change a booked value, the leaf members that aggregate up to that value are adjusted to reflect your change. Usually, this happens proportionally.

Some aggregation types don’t support data entry on booked parent members, so the corresponding cells won’t be input enabled in a table. You can check the reason that a cell is greyed out by selecting Start of the navigation pathShow/Hide Next navigation step Reason for unplannable dataEnd of the navigation path from the table action menu (More Actions) and selecting the cell.

Entering Data on Unbooked Values

When you enter a value in an unbooked cell, which displays a dash character (-) instead of a value, the following rules are used to determine how to disaggregate the value along each dimension:

  • If the unassigned member (#) is available as a leaf member of the source cell, this member receives the same value as the source cell, and other members of the dimension remain unbooked.
  • The unassigned member may not be available, for example, because it is filtered out of the table, because it does not aggregate up to the source cell, or because it does not exist for dimensions such as date and account. In these cases, the value is spread to leaf members of the dimension based on the aggregation type.
  • This aggregation type is determined either by account or measure settings, depending on which has priority. When the account or measure just has a default aggregation type, all dimensions will use it for disaggregation. With an exception aggregation type defined, specific dimensions can use that aggregation type instead.

Aggregation type

Description

SUM

The source value is divided equally among the leaf members. For example, if you enter one million in a cell with two leaf members, the leaf members receive 500,000 each.

AVERAGE and AVERAGE excl. NULL

(Available as exception aggregation. To support disaggregation, they need to be used with SUM.)

Each leaf member receives the same value as the source cell. In this case, the leaf members receive one million each.

NONE

Each leaf member receives the same value as the source cell. The leaf members receive one million each.

FIRST

(Available as exception aggregation)

The first leaf member receives the same value as the source cell. If you enter one million for Q1 using the calendar year, for example, January receives one million.

LAST

(Available as exception aggregation)

The last leaf member receives the same value as the source cell. If you enter one million for Q1, March receives one million.

For some exception aggregation types, data can’t be disaggregated from unbooked cells. In this case, you can enter data directly to leaf members of the exception aggregation dimensions, but not to their parents. If the exception aggregation dimensions aren't added to a table axis, they need to be filtered to a single leaf member to enable data entry:
  • SUM

  • NONE

  • MIN

  • MAX

  • AVERAGE excl. 0, Null

  • MEDIAN (and variants that exclude null and zero values)

  • FIRST QUARTILE (and variants that exclude null and zero values)

  • THIRD QUARTILE (and variants that exclude null and zero values)

  • AVERAGE when used with NONE as default aggregation

  • COUNT (when booked, leaf members will only show a value of 1)

For other aggregation types, data entry is not supported at all, for example:
  • LABEL

  • COUNT excl. NULL and COUNT excl. 0, NULL

  • NONE when used with SUM as default aggregation

Note

If you enter data for an aggregated account member, its children may include different account types such as Income and Asset accounts as well as Expense and Liabilities and Equity accounts. In this case, accounts with different aggregation behavior from the source account do not receive values from disaggregation.

For more information on the aggregation types and sign switching, see Attributes of an Account Dimension.

If you do not want to book values to the Unassigned member for a dimension, you can use one of the following methods:
  • After the value is booked to an Unassigned member in the table, select it and select Start of the navigation path Next navigation step Distribute ValuesEnd of the navigation path to distribute the value to other members.

  • To prevent values from being booked to the Unassigned member, filter the dimension and select all members except the Unassigned member. You can apply this filter to an individual table by selecting Add Filters in the Builder panel, or to the entire story by selecting (Story Filter) from the top navigation panel.

Note
  • If your model contains many dimensions where the Unassigned member is not available, spreading data to all leaf members for each dimension may result in slow performance. In this case, you'll either get a warning about slower performance, or a message to filter the data or change the target cell to create fewer data records in a single operation.

  • Limits apply to data entry on unbooked cells: data can't be disaggregated to more than 650 000 records from a single cell. For a mass data entry session, the combined limit for all changed cells is 6 500 000 records.

  • In some cases where you are working with more than one hierarchy for a dimension, data may be booked directly to parent nodes. For more information, see Entering Values with Multiple Hierarchies.