Account calculations

Account calculations contain the core business logic in a financial model.

Administrators use Model Manager to attach calculations to members of the Account dimension, and for the most part involve interactions among its members.

Where account calculations are used

In budgeting, planning, and forecasting applications, where the goal is often to use existing data to generate new data based on a set of assumptions. Calculations also frequently involve manipulating large data sets across several dimensions.

Account calculations use MDX

To make the setup of calculations easier, FP&A uses a simplified syntax and includes a set of functions for constructing formulas. Any formula entered through Model Manager is compiled into MDX, the underlying expression language used by SSAS, and is saved as part of the cube definition.

Where calculations run

For flexibility, calculations are performed in a variety of ways using both the server and client as calculation engines. These calculations can be divided into two main categories:

  • Server-based calculations

    Calculations that run in the model in SQL Server Analysis Services (SSAS) or are executed using Process Manager services. These calculations may use MDX and SQL processing engines.

    These types of calculations are set up and maintained primarily using Model Manager, specifically in the Account dimension, but also in the Version and Time Perspective dimensions. The Account dimension is where most of the server-side calculation formulas are exposed in the Calculation Definition drawer.

  • Client-based calculations

    Calculations that run in templates.

Calculations types

Every calculation has a calculation method, which determines how the account is calculated and how it is used in relation to other accounts. There are several calculation methods.

A calculation can use multiple methods

You can use the Version dimension to assign different calculation methods to the same account. The various calculation methods belonging to an account are collected in a calculation definition. To access calculation definitions, in Model Manager open the Account dimension.

Rule sets

A calculation rule set is a collection of calculation methods used in an Account structure, assigned to a Version. For example, Calculation Rule Set 1 may be assigned to Actual, and Calculation Rule Set 2 to Plan.

Calculation rule sets are created and edited in Model Manager in the Version dimension (but are used in the Account dimension).

Calculation rule sets can be combined to create calculation definitions.

Create rule sets

Administrators can create multiple calculation rule sets for use with either actual data or planning data, on a version-by-version basis.

  1. In Model Manager, select the model and select Version.

  2. In the lip click .

    The Edit Rule Sets dialog opens.

  3. To add a rule set, click .
  4. Type the Name (up to 40 characters) and a Description of its purpose.
  5. Click OK.

Manage rule sets

  1. In Model Manager, select the model and select Version.

  2. In the lip click .

    The Edit Rule Sets dialog opens.

  3. Do any of the following:

    • To edit a rule set, select it and edit its properties.

    • To make an existing rule set the default, click Default Rule Set.

    • To delete a rule set, select it and click .

       Note:  Any members of Version that referenced the rule set now reference the default rule set.

  4. Click OK.

Calculation definitions

A calculation definition is a group of calculation rule sets for an account, and specifies how the numeric value of the account is derived. Each rule set is linked to a Version and is only evaluated when data from the linked Version is queried. The calculation definition is located in the Account dimension in Model Manager.

Calculation definitions are used where an account requires two different calculation methods, depending on the type of the Version.

 Example:  If the Version type is Actual, a sales revenue account might use an Input calculation method; whereas in the Plan version, the account would use a Formula calculation method. Thus the sales revenue calculation definition requires two calculation rule sets: Actual and Plan.

To apply different calculation methods by rule set

  1. From the account tree structure, select the account.

  2. In Calculation Definition, select Calculation definition varies by rule set.

    The Rule Sets drop-down appears.

  3. Select the rule set.
  4. Select a calculation method.

Calculation solve order

If a cell involved in a calculation is itself calculated (for example, the cell uses a Time Conversion formula or a Calculation Method formula) the Calculation Solve Order Number determines the order that the calculations run in. The calculation with the lowest number is executed first; the calculation with the highest number is executed last.

Calculation type Calculation Solve Order Number

Account formulas

 0

Measures.Ytd

 0

Time Conversion = Sum

 0
Time periods before the Start Period in Versions that are carried forward from another Version 1000

Time Conversion = First, Last, Avg

 2000

Time Conversion = Formula

 3000
Version formulas 4000