Calculations
All model calculations are done through a stored procedure called Appg_Calc_<model name>. The procedure takes a parameter for the user name (used for auditing), parameters for each of the model dimensions, then one parameter for first and last period to be calculated (which may be null to specify begiing of time/end of time). Each of the dimension parameters can be non-leaf node keys (including 0), in which case the calculation will be performed for each descendant of the specified node.
The store procedure must be regenerated from the front end every time the measures change. It can be called from a maintenance dashboard on save to recalculate data that has been changed. It can also be called from a workflow after loading new data.
The stored procedure has two steps, calculation and aggregation. Before and in between these steps there are hooks that can be used to customize the calculation.
Calculation
The calculation steps involved deleting target data from the RP_<model name> in case of timless models or, in case of timed models, setting all calculated measures to null. Then all calculated measures are calculated, one period at a time. It's important to note that each period is calculated independently, so that calculations can refer to accumulated values for prior periods.
Aggregation
Aggregation involves copying period data to the parent model. Each item is mapped to a combination of dimension members from the parent model. Parent model dimensions that do not exist in the submodel are set to the default write member. Each calculated measure that is mapped to an account/measure combination will use that mapping. Finally, data is aggregated by all dimension members and written to the parent model. The write operation goes through the standard model write mechanism to ensure auditability, but also to make sure it goes through any custom write/after write code.
Hooks
Hooks are stored procedures that the main calculation will call if they exist. These can be used to alter the results of the submodel calculation in any manner desired.
Hooks must be defined in the client schema.
Instead of Item
If the stored procedure [client].[InsteadItem_<model name>] exists, it will be called instead of performing the actual item calculation. The stored procedure will be responsible for calculating every measure and populating the RP_<model name> table entirely. [client].[InsteadItem_<model name>] takes the same parameters as the calc procedure.
If [client].[InsteadItem_<model name>] exists, before item and after item hooks will not be called.
[client].[InsteadItem_<model name>] will only be called once per run, not once per item.
Before Item
If the stored procedure [client].[BeforeItem_<model name>] exists, it will be called for every period before performing the calculation for that period. It can be used to prepopulate dummy measures that will be used in the calculation. [client].[AfterItem_<model name>] takes the same parameters as the calc procedure, plus a @currentPeriodId parameter which will be set to the key of the current period being calculated.
After Item
If the stored procedure [client].[AfterItem_<model name>] exists, it will be called for every period after performing the calculation for that period. It can be used to perform calculations that are too complex for existing formula language or that require out-of-model references. [client].[AfterItem_<model name>] takes the same parameters as the calc procedure, plus a @currentPeriodId parameter which will be set to the key of the current period being calculated.
Instead of Aggregation
If the stored procedure [client].[InsteadAgg_<model name>] exists, it will be called instead of performing the aggregation of data. The stored procedure will be responsible for aggregating the data and populating the parent model fact table. [client].[InsteadAgg_<model name>] takes the same parameters as the calc procedure.
If [client].[InsteadAgg_<model name>] exists, before item and after aggregation hooks will not be called.
All aggregation hooks are called only once per run, not once per period
Before Aggregation
If the stored procedure [client].[BeforeAgg_<model name>] exists, it will be called before performing the aggregation. [client].[BeforeAgg_<model name>] takes the same parameters as the calc procedure.
After Aggregation
If the stored procedure [client].[AfterAgg_<model name>] exists, it will be called before performing the aggregation. It can be used to trigger any parent model calculations required ater loading the submodel data. [client].[AfterAgg_<model name>] takes the same parameters as the calc procedure.