Attributes of an Account Dimension

The Account dimension defines the set of account members and the format of the account data.

The account dimension is the main dimension of the model. In addition to the basic columns of Member ID, Description, and Account Type, a set of technical properties is automatically created when the dimension is first set up.

Note
  • In live data models, the Account dimension is named Measures.
  • For models based on HANA views, a reduced set of attributes is available; hierarchies, financial data types, and currency types are not relevant. Also, a different set of values for Aggregation types is available.
  • For models based on S/4HANA views, these attributes are available: ID, Description, Scale, Decimal Places, and Visible.
  • If you're going to use a shared account dimension, see this help topic for important information: Public Account Dimensions.

Account Types

The Account dimension uses an Account Type attribute to automatically handle positive and negative values. From an accounting perspective, account members belonging to the Profit and Loss statement and the Balance Sheet have to be correctly stored in the database with either a positive or negative value so that the accounts balance correctly. In SAP Analytics Cloud, you can enter all values as positive numbers, and the switching of signs from positive to negative is handled automatically on the basis of the Account Type setting.

There are four financial account types: Income (INC) and Expense (EXP) items are included in the Profit and Loss account, and Assets (AST) and Liabilities (LEQ) are Balance sheet items. Automatic switching is applied to the account types INC and LEQ. Note that all formulas work on the displayed value, not the value saved in the database.

When importing data from an external system, a mapping feature is available to ensure that imported data also fits into this schema. This feature is switched on using the Reverse the Sign of the Data Based on Account Type check box in the Details panel when importing data. When this check box is activated, imported data will also be identified by account type and stored and handled correctly.

Currency Rate Types

If you are using currencies with this account, and you have added specific exchange rates based on rate types to the currency table, you can add the Rate Type column to the account grid by switching on Currency Conversion in the model preferences.

Attribute Details
Account Type
Select the account type for this type of data:
  • INC (Income – uses automatic sign-switching)
  • EXP (Expense)
  • AST (Asset)
  • LEQ (Liabilities and Equity – uses automatic sign-switching)
  • NFIN (Non-Financial)

The asset and liability types are aggregated over time and must be linked to an aggregation dimension in the model (such as the built-in Date dimension).

Rate Type

This attribute column is not visible by default, but you can add it by switching on Currency Conversion in the model preferences. The attribute is blank by default so that a generic exchange rate from the currency table will be applied for the account member. If you select a rate type (Average or Closing), the exchange rate in the currency table, which has also been flagged by this same type, will be used.

Unit & Currencies

Use this attribute to set the value type and display units. Select one of the following from the list:

  • Currency: Use this option for all monetary values. In this case, the unit defined in Scale is shown in all data output and the appropriate currency symbol is displayed after the numerical value.
  • Label: You can enter a text label (up to 30 characters in length) for this member to define your own display units; this can be a unit of measure or a packaging unit such as Bottles. The label you enter is displayed after the numeric value; for example, 25 Bottles.
  • %: The percentage option works in the same way, showing the percentage symbol after the value. In this case, the Scale attribute is also automatically set to percent.

The attribute can also be left blank. In this case, the abbreviated unit from Scale is displayed (see below).

Aggregation Type

For account members that are parent nodes, the aggregation type determines how values are accumulated from the leaf nodes. These aggregation types don't relate to any dimension.

  • SUM: This is the default aggregation type for income and expense values; this simply adds all values together.
  • NONE: If the value types of numerical data cells are different, aggregation may not be possible; this may be the case, for example, for price information or cells containing different currencies. For these account members, set the aggregation type to NONE. In tables in stories, cells that have not been aggregated are shown with a diagonal line drawn through them. The cell will be either empty, or, if all child values for a node in the hierarchy are the same, this single value is shown at the node level.
  • LABEL: Set the aggregation type to Label for any dummy nodes on the hierarchy where you want a text label to appear in the data grid without any calculation of values. In this case, the Description Text for the member is displayed as a label, and a simple dash character is used where otherwise an aggregated value would normally be displayed. You cannot set leaf nodes to type Label – this is immediately flagged on screen as an error.
Note
  • Accounts with a formula can't have an aggregation.
  • For models based on HANA views, a different set of aggregation types is available: SUM, MIN, and MAX.
Exception Aggregation Type

Use exception aggregation when you want to aggregate non-cumulative quantities. For example, if you have the quantity Inventory Count, you might want to aggregate the inventory count across all products, but not across time periods, because it doesn't make sense to add up inventory counts from multiple time periods.

In this case, you would choose the aggregation type SUM for Inventory Count, because you want to add up the inventory counts for all products. But if you don't specify an exception aggregation type, the inventory counts will also be summed across time. To prevent summing inventory counts across time periods, specify an exception aggregation type for the time periods.

For example, you might want to choose just the most recent set of Inventory Count values. In this case, you would choose the exception aggregation type LAST, and the exception aggregation dimension Date.

Exception aggregations relate to one or more dimensions. For example, for the AVG and LAST exception aggregations, a Date dimension is appropriate. If you select an exception aggregation type, you must also select an exception aggregation dimension.

You can also use exception aggregation when you've included aggregated quantities in formulas, to ensure that the formula calculations are performed before the aggregation. For more information, see Price * Volume Formulas and Aggregation in Formulas.

Type Description Formula Allowed?
SUM Adds all values together. Yes
MIN The minimum value. Yes
MAX The maximum value. Yes
COUNT Counts all the entries. Yes
COUNT excl. NULL Counts all the entries, excluding null values. This exception aggregation type is available for models based on “import data” connections (where the data is replicated into SAP Analytics Cloud), including models created only in SAP Analytics Cloud (for example, models created from Excel files). Yes, mandatory
COUNT excl. 0, NULL Counts all the entries, excluding zero and null values. This exception aggregation type is available for models based on “import data” connections (where the data is replicated into SAP Analytics Cloud), including models created only in SAP Analytics Cloud (for example, models created from Excel files). Yes, mandatory
AVG Calculates the average of all aggregated values. Select one to five exception aggregation dimensions. Yes
AVG excl. NULL Calculates the average of all aggregated values, excluding null values. This exception aggregation type is available for models based on “import data” connections (where the data is replicated into SAP Analytics Cloud), including models created only in SAP Analytics Cloud (for example, models created from Excel files). Yes, mandatory
AVG excl. 0, NULL Calculates the average of all aggregated values, excluding zero and null values. This exception aggregation type is available for models based on “import data” connections (where the data is replicated into SAP Analytics Cloud), including models created only in SAP Analytics Cloud (for example, models created from Excel files). Yes, mandatory
FIRST Shows the first (oldest) value in the selected time period; it could be used, for example, to show the number of employees on the first day of a month. Select only one exception aggregation dimension. Yes
LAST Shows the last (most recent) value in the selected time period; it could be used, for example, to show the number of employees on the last day of a month. Select only one exception aggregation dimension. Yes
Restriction
  • When the exception aggregation type COUNT is chosen, no aggregation type can be used.
  • If the exception aggregation dimension has a hierarchy, and this hierarchy is in the drill state, the aggregation along this hierarchy will not be shown (the cell will be crossed out).
  • If an account with a formula has an exception aggregation, no direct or indirect referenced base account is allowed to have an exception aggregation.

    Example:

    Account Formula Description
    A none Base account
    B [A] Account with formula based on account A
    C [B] Account with formula based on account B
    A has exception aggregation B has exception aggregation C has exception aggregation Allowed?
    Yes Yes Yes No
    Yes Yes No No
    Yes No Yes No
    Yes No No Yes
    No Yes Yes Yes
    No Yes No Yes
    No No Yes Yes
    No No No Yes
Exception Aggregation Dimension

If an exception aggregation dimension is required for the member (depending on the account type or aggregation type), enter it here. Choose a dimension from the dimension selector dialog, which shows all dimensions in the model and the built-in Date dimension.

Scale
To improve the presentation of numbers in stories, and hide numbers that are not significant, you can set this attribute to show just integers plus the specified number of decimal places. The unit value is then shown by the appropriate word or by an abbreviation. You can select one of the following options:
  • Thousand (3 numerical places – abbreviation k)
  • Million (6 numerical places – abbreviation M)
  • Billion (9 numerical places – abbreviation G. Note that G is the international standard abbreviation for billion)
  • Percent (% 2)

This feature is related to the setting of the Unit attribute that determines if the Scale word or just the abbreviated Scale letter is used (see also the example following this table):

  • If Unit is set to Currency, the word selected as the Scale value is used in the output.
  • If Unit is undefined (blank), the abbreviated Scale letter is used.
Decimal Places

This setting defines the number of digits displayed after the decimal point; select a value from 0–7.

Formula

Calculations and predefined formulas can be used for any value of the Account dimension.

Refer to the Formulas section for full details.

Calculate on

This column is still available, but deprecated, because it doesn't work in all cases. Instead, we recommend that you use exception aggregation. For details, see Aggregation in Formulas.

Example

This example illustrates the effect of the Scale and Unit settings.

For a member where the Scale has been defined as Million, Unit is blank, and Decimal Places is set to 2, the number 92624530 will be displayed in the data grid with an abbreviation as 92.62M.

If, on the other hand, Unit is set to Currency, the value will be the full word: 92.62Million.

If no Scale value is selected, the full number is shown formatted by appropriate separators; for example: 92,624,530.00