Account calculation methods

Every account calculation has a calculation method, which you assign in Model Manager using the Calculation method drop-down.

Input

  • The default calculation method for new accounts, Input allows all users to enter or import data to the account.
  • Based on the Account dimension structure, bottom level accounts are automatically assumed by FP&A to be Input accounts.
  • The values in Input accounts automatically consolidate from leaf members to higher level non-leaf members in all dimensions (except Version).

  • The data in this type of account is stored in the fact table and is loaded into the model only when the model is processed. Input accounts are not affected by other accounts.

     Tip:  To keep users from entering data to a specific account, select its Prevent Data Entry option. (The data remains visible in both reports and data entry screens.)

Aggregate children

  • This method calculates the account value by combining the data of its direct child accounts.
  • You can specify whether a child account is to be added, subtracted, or ignored when calculating the parent account.
  • The data in accounts using this calculation method is not persisted directly in the fact table of the model.

  • All aggregations are performed automatically when the account is queried and exist only in the model.

     Tip:  If the number of members in the dimension is expected to be high (2,000+), use a level-based dimension structure.

Formula – Standard formula

  • This method calculates the account value using the specified calculation rules, at all levels of the hierarchy, for all dimensions.
  • This method is typically used for calculations, such as ratios, that need to be recalculated on demand throughout the dimensional structure of the model.

  • Formulas that are suitable include: addition, subtraction, multiplication by a constant, division by constant, and ratio calculations.

     Example:   Profit = Revenue - Expenses

     Example:   Salaries = Full Time Equivalent Staff * Average Salary

     Caution:  Any formula that must be calculated first at the leaf levels of a dimension and then aggregated to higher levels should not use the Standard Formula calculation type.

  • Data in accounts using this calculation type is not stored directly in the fact table of the model but exists only in the model.
  • The performance of calculations using Standard formula is affected by the composition of the model including (but not limited to):
    • the number of dimensions
    • the type of dimensions used (parent-child versus level-based)
    • data volume
    • number of members in the dimension

    • number of inter-account calculation dependencies

  • The dimension type affects performance in cases where there are lots of members and the hierarchy is deep.
    • Although parent-child dimensions are most flexible from a design perspective, some performance degradation may occur when the number of members increases.
    • Level-based dimensions are preferred when dimensions are large (typically 3,000+ members).
    • The main reason is that SSAS is able to make better use of internal aggregations with level-based dimensions, which is not possible when using parent-child dimensions.

  • Complex formulas involving several accounts that are also calculated using Standard formula may impact performance.

     Example:  If A=B*C*D, where B , C, and D are also calculated, each time A is queried, Analysis Services must also calculate B, C, and D.

    • Since results are not stored in the fact table, the calculation must be performed at query time.

    • If there are several calculations of this type in the model, query response times could slow down considerably.

Formula – Leaf formula

  • This method applies the specified formula to the intersection of leaf members for all dimensions in the model.

     Example:   The Cost of Goods Sold account would require a formula for leaf members only:

      Leaf Members Non-Leaf Members
      Department A Department B Total
    Cost of Goods Sold = Revenue x %COGS = Revenue x %COGS Sum
  • Business requirements often demand this type of calculation because the order of calculation and data aggregation is important.
    • In most cases, data needs to be calculated for an account at leaf levels before it is aggregated to higher levels.
    • A classic example of a leaf calculation requirement is a driver-based calculation such as Revenue based on Price and Volume.
    • The drivers Price and Volume are both entered at leaf cells of the model, and Revenue must be calculated at leaf levels before it is aggregated, because Price does not normally aggregate.
    • Hence, recalculating Revenue at aggregated levels (using a Standard Formula approach) would yield incorrect results.
  • Typically, there are many more leaf combinations than non-leaf combinations.
    • When a user queries data at higher levels, data at leaf levels is aggregated up the dimension hierarchy to return consolidated values.
    • In SSAS, this data is not physically stored in the fact tables, and must be resolved each time it is requested.

    • As a result, leaf calculations usually perform very slowly on anything other than a model with only a trivial number of dimensions and members.

Formula – Nonleaf formula

  • This method applies the specified formula to the cells within the account that are based on a non-leaf member in at least one dimension.

     Example:  Related to the example above, Cost of Goods Sold would require a formula for non-leaf members only:

      Leaf Members Non-Leaf Members
      Department A Department B Total
    Cost of Goods Sold Input Input = Revenue x %COGS
  • Accounts using this method can accept data, either through direct input or import, at leaf member combinations.
  • However, its value is recalculated at non-leaf member combination using the supplied formula.
  • This method is useful when it is necessary to enter data for assumptions at leaf levels and then recalculate an average total at non-leaf levels.
    • Examples are common in planning applications, such as driver and pricing-based calculations.
    • A classic planning example is Price, which frequently requires an input at leaf levels, but cannot be aggregated to non-leaf levels since it produces nonsensical data.
    • Instead, what is desired is an average Price to be calculated at non-leaf levels based on a formula involving Revenue and Volume. In this case, it makes sense to make Price a Formula Non-Leaf account.

How to choose

What calculation method should you choose?

The following table summarizes/compares the calculation methods:

Method Purpose Execution method Performance Currency converted?
Input Allows all users to enter data. Depending on the Account dimension structure, FP&A automatically assumes leaf-member accounts to be input accounts, and no set up is required by the administrator. Data is stored in the fact table; data is consolidated automatically based on the dimension hierarchy. Excellent Yes
Aggregate children Calculates account values by adding, subtracting, or ignoring the values of its direct child accounts, as specified by the administrator. Runs each time the model is queried; the results are not persisted in the fact table. Good, but may be affected by dimension hierarchy design. No
Formula - Standard formula Calculates the account values using the specified calculation rules. Runs through all dimension member combinations each time the model is queried; the results are not persisted in the fact table. Excellent when formulas are simple and the calculations minimal, but may be affected by dimension hierarchy design. No
Formula - Nonleaf formula Applies the specified formula to the cells within the account that are based on a non-leaf member in at least one dimension. Runs through all non-leaf dimension member combinations each time the model is queried; the results are not persisted in the Fact table. Excellent, owing to the limited number of non-leaf combinations in most models. No*
Formula - Leaf formula Applies the specified formula to the cells within the account that represent the intersection of leaf members for all dimensions in the model. Runs through all leaf dimension member combinations each time the model is queried; the results are not persisted in the fact table. Inefficient for large-scale leaf-only calculations. No
  *Data stored in leaf member combinations will be currency converted. Calculated non-leaf results will not be converted.