MS Dynamics CRM 2015: Understanding Calculated and Rollup Fields
The upcoming release of Microsoft Dynamics CRM 2015 contains two new features to be discussed here: Calculated and Rollup attributes.
At a very high level, Microsoft has designed ways for customers and designers to configure entity attributes that perform data calculations. Prior to this release, these calculations needed to be performed in either plugins or using custom workflow activities/third party tools.
These two new features are administered as a standard entity attribute. Calculations and Rollups are available for Single Line of Text, Options Set, Two Options, Whole number, Decimal Number, Currency, and Date and Time attribute types. Rollup is not available for the Option Set or Two Options attribute types.
Selecting one of the aforementioned attribute types will allow you to designate the field as “simple,” “calculated,” or “rollup” attribute.
Calculated fields are virtual fields, not physical, not committed to the database; but available on views, forms, and even SELECT statement running queries against an on-premise implementation.
Clicking “Edit” creates the attribute and launches the rule design form – an interface very similar to the Business Rule form. Fairly complex conditional criteria may be created to perform business rules, and ultimately a calculation. Microsoft has provided some out-of-box functions, with similar behavior to intellisense (see below), although I have not seen (or sought out) any Software Development Kit (SDK) information yet. Trial and error resulted in CRM preventing invalid calculation syntax and argument types.
At a high level, the basic types of calculation which can be taken are:
1. Numeric calculations
2. Date math
3. Text formatting/concatenation
In this very simple example, we calculate a rudimentary expected value of open opportunities: expected value = probability * value:
How are these virtual fields managed?
They are defined on the BASE table as a “computed field” and returned in the SELECTS from the table. The computation itself (a SQL version of the formula defined in CRM) is stored in a custom database function, residing in the MSCRM database. Upon publish CRM generates this custom function, specifically affiliated with the attribute which is executed at the database layer, not within the CRM application tier. Upon each form load, view rendering, or any other step which requires this attribute, the database performs the calculation to return the value through the application layers.
A few points worth noting:
1. Calculated fields can calculate based on other fields, including rollup fields (they persist) and even other calculated fields. However, two calculated fields cannot reference each other in their respective formulas: CRM will throw a circular reference error on the validation of the violating formula.
2. Although not personally confirmed, rumor has it that calculated field values are not available in the plugin contexts.
3. Since this is virtual and only present in the retrieves, no messages can be involved by an action on it, hence no workflows.
Unlike calculated fields, rollup fields are not virtual – they are physical attributes with data persisted on the BASE table. Rollups are meant to be aggregate calculations of child entities. Think number of open opportunities, activities, revenue projections, total earned revenue, etc.
The design form is virtually the same as the calculated field design UI, but requires related entity selection criteria to perform the aggregation:
How are rollup fields calculated and persisted to the database?
Each rollup field is comprised of three physical fields:
1. Fieldname – stores the calculated value
2. Fieldname_date – stores the last time the field was updated
3. Fieldname_state – this appears to track if a rollup calculation has failed or is in a “bad” disposition – although I have not seen a state field in a non-zero value yet, the underlying database objects suggest non-zero values are possible.
Each rollup field is stored as a record in a table named RollupPropertiesBase. This table has various properties describing the Rollup Field, an important one being its Primary Key: RollupPropertiesId. This attribute value, a GUID, correlates to a number of tables and stored procedures that have been created. The naming conventions are as follows:
b. Stored Procedures
Out of box, an asynchronous job executes once per hour, attempting to recalculate the rollup attribute. The platform is invoking the stored procedures, which are using the bootstrap tables and entity attributes to recalculate. I have not profiled the entire process, but it appears relatively robust.
In additional to the hourly system job, users may manually force a system recalculation. If a rollup field exists on a form, hovering over it displays a refresh icon, and clicking will invoke the recalculation process. The image below displays the same rollup field in both the form header and body. Hovering over the header version also allows recalculation.
I have added the out of box “Open Deals” rollup field found on the Account entity. It is a rollup of open Opportunities.
A few points worth noting:
1. If you choose to use a hierarchy (seen in the image above), then the rollup calculation performs a hierarchical aggregation of the tree: children and below.
2. Upon recalculation, the record’s modifiedon is NOT updated, meaning an update message is not invoked, meaning no workflow or plugin hooks. I’ll let somebody else definitively prove that out. I’m not sure if this behavior is desirable or not.
3. In looking at p_Rollup_Inc procedure, it seems the platform has a 50K record count governor on the total number of children records to aggregate, and if this amount is exceeded, an exception is raised – to somewhere – research still needed. This governor may be overrideable.
4. Rollup fields may not use calculated fields or other rollup fields in their formula.
5. Rollup fields support COUNT, MAX, MIN, and SUM operations.
Out of box, Microsoft ships a Field Level Security profile named System Administrator. In this profile, out of box rollup fields are included with Read permissions enabled.
For custom rollup fields, the rule designer UI displays a message suggesting the field be secured. If you wish to secure the fields, be aware that update and create permissions are disabled – only read permissions. Not having a create permission makes sense, but the lack of update is a questionable choice.
*Note – There does not appear to be a way to prevent a user from invoking a manual calculation, which is arguably a security gap, and could lead to performance issues if any user could force recalculations. I would have expected that the Update permission to regulate this.
Notice the disabled Update and Create options:
After uncovering how Microsoft has architected these features, offloading the Central Processing Unit (CPU) calculations to the SQL Server makes the most sense, but there is clearly a tradeoff in performance for enterprise organizations with “enterprise” data volumes. Thus, adding too many calculated fields will clearly create a performance strain on the SELECTS, and creating too many rollups will also strain both the SQL Server and Async service.
I’m sure we can expect Microsoft to release a white paper in the coming months outlining performance behavior of both the Async service and SQL Server as it pertains to both calculated and rollup fields.
If you’re using CRM as a platform for deploying line of business applications, make sure you’re getting the most from your investment by reading CRM Governance: What It Is, What It Isn’t, and How to Do It Right, an informative eBook written by governance experts.