Saving Data, Calculation and Aggregation in PBCS

This document explains the concepts of Saving Data, Calculation and Aggregation in PBCS and gives examples of how the concepts apply within the different plans.

Excel

Accountants are very familiar with using spreadsheets where calculation and aggregation are normally (but not always) automatic and where data is saved (or discarded) as a complete file. Users may also be familiar with Manual Calculation in Excel and pressing F9 to perform calculations. This mode in set in Excel in the Calculation options section, under Workbook Calculation, choosing Manual.

Accountants are also familiar with a different practice in Excel for Pivot Tables – these must be explicitly refreshed to reflect changed data, but then provide fast reporting.

PBCS

Saving

Saving data in PBCS refers to saving the data to the cloud. Typically data is entered via a form and clicking Save at the top right of the form (or choosing Submit in SmartView) will save the data to the database. While entering data formatting indicates which data has changed and only the changed or new data is saved to the database as the existing data is already there.

When the form is saved, it is also possible to automatically run rules which can perform calculations and/or aggregations. These are set up as part of the form design. The user instructions for a form normally indicate what rules are run when the form is saved.

Calculating

Calculation refers to the process of computing values from data.

In PBCS calculation is normally not performed automatically as data is entered but via a rule run when the form is saved or when the user chooses to run the rule (e.g. by right-clicking and selecting it). An example of this is ‘Calculate This Row’ in Staff Plan which uses the data such as Start Date, Pay Multiplier, Cost Centre Allocation etc. that are stored in the database for the current Dimension Members (ie Person, Payscale and Point, and Cost Centre) to calculate the costs. The calculated information is stored in the database for the Dimension Members. The calculated costs are not automatically updated when data are changed.

Aggregating

Aggregation of data refers to the process of summing data for various levels of the database. Again, this typically happens automatically in Excel but often does not in PBCS to allow for better performance. Aggregation typically is relevant for the Entity Dimension (e.g. from Cost Centre to School, to College, to University level) and for the Account Dimension (e.g. from detailed account to Salaries and Wages to Pay to Net Direct Expenditure). In PBCS Aggregation is normally not performed automatically but via a rule run when the form is saved or when the user chooses to run the rule (e.g. by right-clicking and selecting it).

Scheduling Calculation and Aggregation

The design of UCD’s PBCS has aimed to separate Calculation and Aggregation from Saving where necessary to achieve reasonably fast saving, while also giving Finance Managers the ability to perform calculation and aggregation when they need to so that they can be sure that they are reporting on correctly updated information.

Jobs are also run frequently so that calculation and aggregation for all of the university are performed automatically in the background so that information can never be more than a few hours out of date if Finance Managers either forget to perform calculations or aggregations, or prefer not to do so other than in exceptional cases.

Example

Is the screenshot below, selected data from the Staff Plan is shown:

The Salary & Costs are calculated, driven by some but not all of the user data (e.g. the Category does not affect costs). This is represented with blue dashed arrows.

The costs are aggregated to the level of Person / PayScale / Entity.

In this case Person is the level for all persons, All PayScales is the aggregation level for Payscale and Biosystems is the school (entity) to which this data is aggregated.

• If a user runs Calculate This Row, PBCS only Calculates data for the selected row, it does not Aggregate it.
• If a user runs Calculate This Form, PBCS calculates each row and also aggregates the information to the level of the form (ie for Biosystems in this example).
• If a user runs Aggregate Entity, PBCS will aggregate the data to the College level and also to University level.