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 cube only when the cube 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 cube.

     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 cube.
  • 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

       Tip:  To avoid slow performance, try to restrict your procedural calculations to a sub-set of member combinations, and always use SQL processing if applicable.

  • 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 – Procedural calculation

  • In larger models, calculations that use the Formula – Leaf formula method (discussed below) can be "expensive" in terms of performance.
  • To improve performance, you can use the Procedural calculation method, which causes the calculations to run only when prompted, either through the Procedural Calculation process or the data-entry trigger rule (in Ad Hoc Analysis or a template).

  • A trigger rule allows you to specify the accounts in which data entry triggers the selected procedural calculation.

     Example:  If you select the account Price to trigger the procedural calculation for Revenue, each time a user updates Price, Revenue is automatically recalculated.

  • When run, the Calculation process physically writes the results of the calculation to the fact table of the cube.
    • This can be useful because it allows the results of the calculation to be consumed by other processes.
    • One example is Currency Conversion, which is SQL-based and only evaluates data physically stored in the fact table.

    • The only way to currency convert a calculated account is to utilize a procedural calculation.

     Tip:  To avoid slow performance, try to restrict your procedural calculations to a sub-set of member combinations, and always use SQL processing if applicable.

  • You manage this type of calculation using the Edit Procedural Calculations dialog.

Formula – Leaf formula

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

     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 cube with only a trivial number of dimensions and members.

     Note:  The Leaf formula method is inefficient for large-scale leaf-only calculations. However, when this method is selected, the Procedural calculation method appears as an option, and can be used to help to improve performance.

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.

Get data

  • This method retrieves data from another model based on a dynamic or a specific dimension member combination.

     Example:  An account may require the actual inventory data of Company A from February 2017; The selected members would be Inventory (Account dimension), Actual (Version dimension), Company A (Organizations dimension), and February 2017 (Time dimension).

  • This method is best used for modeling global assumptions or statistics that need to be captured at a higher granularity or different dimensionality than that of the current model.

  • Care needs to be exercised as it also taxes performance.

     Note:  Optionally, the Get data method can retrieve data from the current model. (This eliminates the need to write custom MDX standard formula in this common use case.)

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 – Procedural calculation Applies the specified formula to the cells within the account that represent the intersection of leaf members for all dimensions in the cube. Runs through all leaf dimension member combinations and operates on demand, either through a data-entry trigger or a Calculate process; the results are persisted in the fact table. Good when the calculations involve basic operators using SQL processing; performance may be slower if the number of leaf member combinations to process is large. Yes
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. When this method is selected, the Procedural calculation option appears, which can be used to help to improve performance. No
Get data Retrieves data from a selected dimension member of another model. Runs through all dimension member combinations (leaf and non-leaf); the results are not persisted in the fact table. Good when the accounts referenced in the other model are not calculated and when used in low numbers. No
  *Data stored in leaf member combinations will be currency converted. Calculated non-leaf results will not be converted.

Apply multiple methods

You can apply multiple calculation methods to an account for different versions, using account calculation rule sets. Calculation rule sets are “labels” that are set up in the Version dimension and are applied to accounts in various versions.

The following example shows two rule sets defined in Actual and Plan, and their Account calculation methods. Note how in the Actual version there is greater use of the Input method, whereas the Plan version makes greater use of Formula methods.

Account Rule set in version Actual Rule set in version Plan
Units Input Input
Unit Price Standard Formula: [Gross Sales]/[Units] Non-Leaf Formula: [Gross Sales]/[Units]
Unit Cost Standard Formula: [Total Costs]/[Units] Non-Leaf Formula: [Total Costs]/[Units]
Gross Sales Input Procedural Calculation: [Units] * [Unit Price]
Data-entry triggers: [Units], [Unit Price]
Total Costs Input Procedural Calculation: [Units] * [Unit Cost]
Data-entry triggers: [Units], [Unit Cost]
Net Profit Standard Formula: [Gross Sales]-[Total Costs] Standard Formula: [Gross Sales]-[Total Costs]
P % R Standard Formula: [Net Profit]/[Gross Sales] * 100 Procedural Calculation: [Net Profit]/[Gross Sales] * 100
Data-entry triggers: [Units], [Unit Price], [Unit Cost]
A Standard Formula: [P % R] + 1 Standard Formula: [P % R] + 1
B Procedural Calculation: [A] + 1
Data-entry triggers: [Gross Sales], [Total Costs]		 Procedural Calculation: [A] + 1
Data-entry triggers: [Units], [Unit Price], [Unit Cost]

The procedural calculation order for the Plan rule set is defined as follows:

  1. Revenue P % R
  2. Costs
  3. P % R
  4. B

 Use Case:  Move data between cubes.