Procedural Calculation process
The Procedural Calculation process updates all formula leaf accounts (accounts that use the Formula – Leaf formula calculation method) in cubes that have the procedural calculations option selected. The process should be used if changes have been made to model data such as through a data import, or if it is necessary to refresh the data in procedural calculation accounts. This option is set up in the Account dimension. During the execution of each procedural calculation, writeback data is transferred to the fact table and the cube is then updated automatically. This process should normally be run after transferring writeback data, removing data, or updating the cube.
Parameters
This process has the following parameters:
-
Description: Optional. Up to 250 characters.
-
Connection: Select the connection that for the cube.
-
Cube: Select the cube that contains the procedural calculations that you want to run.
-
Procedural Calculations to Execute: Select the Version that contains the procedural calculations that you want to run, then select the procedural calculations that you want to run.
-
Member Combinations to Process: Select the member combinations that you want included in the process.
How to optimize performance
By default, the Procedural Calculation process runs all procedural calculation accounts against all leaf dimension members in the selected Version. Depending on the total number of leaf member combinations in the cube and the complexity of formulas, calculation time may vary from seconds to several minutes.
Performance may be significantly improved by restricting the process to impacted areas of the model. This is useful when data has changed in only a few accounts, or if changes are restricted to specific dimension members. The fewer leaf member combinations, the faster the performance.
Other factors that affect Procedural Calculations process performance include the following:
- The volume of cube data—especially important as calculations are processed for all dimension member combinations where there is data. If there are lots of data points, then slower performance should be expected. As a best practice, empty and orphaned records should be removed to reduce data volume. Compressing the fact table may also result in improved performance of SQL-based processes. The Optimize Fact Table process can be used to remove orphaned records and compress the fact table.
- The number of calculations that can be executed using SQL processing.
- General cube design and the structure of dimensions (level-based versus parent-child).
- Interactions among existing MDX calculations that are installed in the cube.
Note: There is no need to run an Update Cube process immediately before or after a Procedural Calculations process. The Procedural Calculations process already includes intelligence to update the cube as required to ensure that correct data is produced. The only exception is if there is a chance that the Procedural Calculations process may not update any data at all when it is executed—in that case, an Update Cube process will not be performed. Typically this behavior is desirable, but depending on what other actions might have preceded the Procedural Calculations process, it might be sensible to manually run an Update Cube process prior to running the next set of calculations.
More about this process
The following actions are performed when a Procedural Calculations process runs:
- Any data in the write-back table is transferred to the fact table.
- The selected cube is processed.
- Procedural calculations are executed, in the defined order, in two phases:
- Query data from cube (MDX)
- SQL fact table (SQL)
- The resulting data is written to the fact table.
- The cube is processed after each calculation is executed (if at least one record is written to the fact table)
In cases where a member combination already contains a data value for the account being calculated, but one or more of the accounts referenced in the formula is empty for the same member combination, when the process runs, the calculation engine may skip this member combination. This may cause unexpected results.
For example, in a specific Product/Region combination, assume Revenue has an existing value of 1000 and Revenue = Price x Volume, if Price is empty, the formula will not be executed and Revenue will remain at 1000. In these cases, run the Transfer Writeback Data and Remove Data processes before the executing the Procedural Calculations process.