Skip to main content

Measures

Data for each item is stored in measures. Each model can have an arbitrary number of measured defined. For each measure, the following attributes are required:

  • Measure Name -- this is the name of the measure, which will become a field in the corresponding RP table. Formulas can refer to the measure by using its name inside square brackets, i.e. [Employee Name]
  • Measure Type -- the type of measure: Input, List, Rate or Calculated; details below
  • Data Type -- the underlying SQL data type of the measure; the UI provides some common data types, but most valid SQL data types can be used
  • Summary Visibility -- how the measure will displayed in the summary (table) view; can be set to one of Editable, Visible or Hidden. Calculated measures can only be set to Visible or Hidden.
  • Detail Visibility -- like above, but but for the detail (form) view.
  • Account/Measure Mapping -- indicates to what account and measure in the parent model the aggregated data for this measure is going to be stored. Mapping can be null, indicating that data for that measure is not transferred to the parent model.
  • Display Format -- how the measure should be displayed to the end user (number, percent etc.)
info

Summary/Detail Visibility and Display Format only affect visibility of measures in Tables/Form objects that connect directly to a submodel. Custom table/form objects that use SQL queries to pull data from the tables are not impacted.

Input Measures​

Input measures are measures that should be input directly by the end user. For timeless models, input measures are stored in the RT_<model name> table.

List Measures​

List measures are similar to to input measures, except the user can only select from a predefined list. The Formula field for a list measure should contain the name of a valid list in the tenant (either system or custom list). When editing, the end user will be prompted to select an element from the list but in the table we will store the int value of the selected element. For timeless models, list measures are also stored in the RT_<model name> table.

Rates​

Measures of type Rate are not associated with items, instead these are global rates which must be maintained separately. Rates are stored in the RR_<model name> and associated with members from submodel dimensions. Each rate entry also has an optional start/end date and a sequence number. Multiple values can be specified for a rate, as long they have different sequences or different start dates.

Where referencing a rate in an item calculation the applicable rate is determined as follows:

  • only rates where the start of the current period falls between start date and end date are considered
  • a check is made to make sure that all dimension members associated with the item are descendants of the dimension members associated with the rate entry
  • if multiple entries for the rate match, the entry with with the lowest sequence will be used
  • if no entries are found, the rate value will be null

Calculated Measures​

Calculated measures are calculated based on input measures and rates. Calculations are done one period at a time and calculated measures values are stored per period in addition to all other submodel dimensions.

The Formula field of the measure determines how the calculation is performed. Measure formulas support standard SQL expressions, including any scalar SQL function, plus a few predefined RP functions. Other measures can be referenced in formulas by enclosing them in square brackets:

iif([Employment Type] = 1, [Annual Salary], [Hourly Wage] * [Annual Hours]) * [FTE]

Other calculated measures can be referenced as long as they are declared before the current measure. Circular dependencies between calculated measures will generate an error. Calculations involving yearly accumulated values are exempt from this rule as these are done for prior periods which are already fully calculated.

Calculated measures are always stored in the RP_<model name> table.

Measure Order​

Measure order is important for two reasons: it dictates the order in which measures appear in table/form widgets and it also dictates in which order calculations are performed. The RP module will attempt to calculate as many measures as possible at once, creating additional calculations every time we have dependencies between calculated measures.

Updating Measure Definitions​

Adding, removing or changing measures' data type will require the end user to process models, then regenerate the calculation code. The processing operation will update the fact tables with the correct fields. No model processing is required if only changing formulas, but calculation code still needs to be regenerated to reflect the new calculations. See Submodel Calculations for more details.

Measure Configuration​