Data-entry triggers
A data-entry trigger is a rule that specifies which accounts cause a procedural calculation to recalculate during data entry.
Example: If you select the account Price to trigger the procedural calculation for the account Revenue, then each time Price is updated during data entry, Revenue is automatically recalculated (assuming the formula Revenue = Price x Volume).
Trigger accounts
A "trigger account" is an account (always an Input account) that is referenced by a formula in another account. In the preceding example, Price is a trigger account.
Alternatives to triggers
If you want users to be able to immediately see the effect of their data inputs on the Procedural Calculation accounts that reference them, you can also use cell formulas in data-entry templates.
Defined in Model Manager
Data-entry triggers are created in Model Manager's Edit Procedural Calculations dialog.
Where triggers are used
Data-entry triggers can be used in the following areas:
- Ad Hoc Analysis, in data-entry mode
- A template, in data-entry mode
- A Line Item Schedule
How triggers work
Data-entry triggers "fire" when a user performs any of the following actions when in data-entry mode:
- In Ad Hoc Analysis: clicks Refresh or navigates the data (for example, changes the member selection, drills up/down, expands, collapses, and so on)
- In a template: clicks Refresh, changes the page, or performs a top-down spread
- In a Line Item Schedule: Saves
Turning on triggers
By default, triggers are "off".
To turn them on, the administrator must change a setting in web.config, and specify a Calculation Mode (see below).
Calculation Modes
For performance reasons, there is more than one setting for data-entry triggers. The modes are as follows:
-
Calculation Mode: Off
The default setting. The system completely ignores data-entry triggers.
-
Calculation Mode: Batch
The appropriate setting in most situations where you need Procedural Calculation accounts to automatically recalculate based on trigger account changes.
Tip: Consider using template cell formulas instead: often they result in better performance and automatically recalculate values without the user having to click Refresh.
-
Calculation Mode: Individual
This setting should only be used if recommended by Prophix Support or Professional Services. Normally, the Individual setting is only required in models with accounts that involve complex nested calculations. This setting ensures that all Procedural Calculation accounts are updated based on data-entry triggers (even in a complex model) but can result in very poor performance if a large number of calculated values are produced based on trigger account changes.
Caution: Calculation Mode is a system-wide setting and therefore affects all users in all data-entry situations in all cubes.
Example: Comparing calculation modes
This example compares calculation modes for a set of data-entry triggers.
Assume an Account dimension that contains the following members:
| Account | Definition | Procedural Calculation trigger accounts |
|---|---|---|
| Units | Input | |
| Price | Input | |
| Revenue | Procedural Calculation = Units * Price | Units, Price |
| Unit Cost | Input | |
| Expenses | Procedural Calculation = Units * Unit Cost | Units, Unit Cost |
| Net Income | Standard Formula = Revenue - Expenses | |
| Profit % Revenue | Standard Formula = Net Income / Revenue * 100 | |
| P%R | Procedural Calculation = Net Income / Revenue * 100 | Units, Price, Unit Cost |
In data-entry mode, if a user updates the values for Units, Price, and Unit Cost and then clicks Refresh, the result in each Calculation Mode is as follows:
-
Calculation Mode: Off
No calculations occur. The user sees no change to any of the calculated accounts after clicking Refresh.
-
Calculation Mode: Batch
First the system saves the updated values for Units, Price, and Unit Cost to the cube. The system then runs the calculations for all the Procedural Calculation accounts based on the data in the cube. The user sees updated values for Revenue and Expense (and thus also for Net Income and Proft % Revenue, since they are calculated "on-the-fly" as per all Standard Formulas. However, the data for account P%R does not change, because it is calculated based on Net Income and Profit % Revenue before the calculated values for Revenue and Expenses are saved to the cube.
-
Calculation Mode: Individual
No "batch" calculations or read/write operations are performed. Instead, everything is calculated sequentially and as a result, the user sees the expected calculated values for all accounts (including P%R). Thus the upside to this setting is that it produces the expected results for complex linked calculations. The only downside to this setting is that the processing is slower than a single batch calculation/update, and so from a performance perspective, Batch mode is preferable.
For admins
How to change the Calculation Mode
As stated above, to turn on data entry triggers, you must change the Calculation Mode setting in web.config for the web client web service. Be sure to keep the following in mind:
- Make changes to web.config with care—an invalid value for the web client web service produces unpredictable results.
- Software upgrades to