This section defines the types of calculations and calculation sequences used during different analytic processing routines.
Cube Processing
Calculation: This executes the standard calculation sequence for a single Data Unit. A Data Unit refers to a group of data cells for a specific Cube, Entity, Parent, Consolidation, Scenario, and Time Member. See Data Units in Workflow for more details on Data Units. Except for Dynamic Cell Calculations, all Member Formulas are written to execute as part of a Data Unit’s Calculation Sequence.
Translation: This executes a currency translation that occurs when the data for an Entity’s local currency needs to be translated to a foreign currency. The translation step executes after the system has run the Data Unit Calculation Sequence for an Entity’s local currency. After this is completed, the default translation algorithms use Foreign Exchange (FX) rates to generate and store a corresponding translated Data Unit. Finally, the Data Unit Calculation Sequence on the translated Data Unit to produce the final translated amounts are run.
Consolidation: The Analytic Engine provides pre-built financial intelligence through a statutory Consolidation Dimension that defines a sequence of Data Unit calculations and aggregations which include currency translations, Parent-level adjustments, complex ownership computations, and Intercompany Eliminations. For more details on Consolidation, see Consolidation.
Dynamic Cell Calculation: Dynamic Cell Calculations are a special type of Member Formula for Account, Flow, or User Defined Members. They are used to generate amounts for a Member on the fly (i.e., the results are calculated on-demand and are not stored). Dynamic Cell Calculations are often used for metric accounts (e.g., ratios involving other accounts) and are appropriate when the result of the Dynamic Calculation is not needed as the source number for another Stored Calculation. The use of Dynamic Cell Calculations can result in improved Consolidation performance because they do not generate stored numbers and are typically only executed when a number needs to be displayed, not during the Consolidation process.
Data Units and Formula Execution
OneStream executes formulas at a specific unit of work call a Data Unit. This section details the order and combination of logical processes that execute for a Data Unit. See Using Data Units in Workflow.
Guidelines on Formula Passes: There are 16 Formula Passes and one Dynamic Calculation Formula Type available on each Member. Formulas that do not depend on one another can exist in the same formula pass and will be calculated in parallel using OneStream’s advanced parallel formula processing engine.
As a basic guideline, customers should think about organizing formulas by account/collection type across OneStream’s available formula passes using the following examples.
Formula Pass 1 - 8: Trial Balance
Formula Pass 5 - 7: Translation logic related to Cash Flow
Formula Pass 8 or 9: Balance Account and CTA account
Formula Pass 9 - 16: Non-trial Balance
All formulas in a pass are processed at the same time, so they cannot have dependencies on one another.
Calculation Sequence of a Single Data Unit: The items below detail the specific list of tasks executed for each Data Unit’s calculation process. As an example, the following steps are executed for a single Data Unit when a user selects Calculate for a single Entity, Scenario, and Time period.
Data Unit Calculation Sequences (DUCS)
-
Clear previously calculated data for the Data Unit.
-
Run the Scenario’s Member Formula, which is typically used for seeding a Scenario’s data from another Scenario or from a prior year.
-
Run reverse translations by calculating Flow Members from other Alternate Currency Input Flow Members. This is part of the built-in ability for an Entity to accept input using multiple currencies.
-
Execute Business Rules (1 and 2). Up to 8 Business Rule files can be attached to each Cube.
-
Run Formula Passes (1 – 4) for the Cube’s Account Dimension Members, then Flow Members, and then User Defined Members. The Formula Pass is specified using each Member’s Formula Type property in the Dimension Library.
-
Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)
-
-
Execute Business Rules (3 and 4).
-
Run Formula Passes (5 – 8).
-
Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)
-
-
Execute Business Rules (5 and 6).
-
Run Formula Passes (9 – 12).
-
Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)
-
-
Execute Business Rules (7 and 8).
-
Run Formula Passes (13 – 16).
-
Account, Flow, UD1, UD2, … UD8 (Member Formula Execution)
-
Every time a data cell is written to the database, information with the stored cell about how it was stored (e.g., manually entered, calculated, consolidated, etc.) is included. If a number was calculated and stored as a result of a formula, it will always get cleared regardless of the metadata settings. The AllowInput property specifies whether a cell can be written to, and therefore if a formula stored a number, there would be information if the cell was calculated. If AllowInput is set to True, and a new number is typed over the same cell, it is stored as manually entered instead of a calculated cell.
Formula Execution for Statutory Consolidation Sequence
The consolidation process is run for a hierarchy of Entities. See Consolidation.
Formula Level Controls
In addition to the Cube-level settings described above, VB.NET If statements are often used in a formula to have that formula execute only for certain types of Data Units. The most common usage is to contain checks for Base-Level Entities and local currencies. The following If statement will cause the formula to execute only for Base-Level Entities and the Entity’s default currency. This is often used when the results of the calculation in the Base-Level Entity is intended to be consolidated to Parent-Level Entities.
If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyForEntity())) Then End If
