The CRM 2015 release from Microsoft is a great leap forward and it will enable partners to deliver awesome CRM solutions to customers in a faster timeframe and with more confidence than before. One of the items we wanted to address was how North52 compares to some of the new CRM 2015 features and in particular Calculated Fields and Rollups.
We believe that the out of the box calculated fields and rollups are great for simple scenarios but fall significantly short for mission critical scenarios. In order to get a clear understanding of where our concerns lie we really need to explore the design principals Microsoft used to build these features. Fortunately the guys at Green Beacon have written a great blog post on this topic:
North52 BPA was architected from day 1 with Enterprise CRM deployments in mind so > 1000 users, complex business rules and tricky calculations. We believe you really need to start from this position to maximize performance and flexibility. The following sections outline in detail the design differences between CRM 2015 & North52 BPA but the key take away we would like to impress is that North52 BPA is a true business rules engine running natively in the CRM sandbox and so has far more flexibility, scale and potential to add extra functionality.
Rollups
Rollups are not real-time. The best configuration you can do is set the schedule for each rollup to execute on an hourly basis. This is just not adequate when you are deploying mission critical customer solutions.
As an example take a bank where they have a credit limit field on the Account entity for a credit card. Suppose a person has a credit limit of $5,000. If that person uses their credit card in Shop A to buy an item worth $4,000 and then 10 minutes later goes to shop B to make a purchase worth $3,000 as the CRM rollup field won’t re-calculate for 1 hour the second purchase would go through even though the credit limit is set to $5,000 but the overall amount spent is $7,000. This makes rollups a non-starter for lots of business scenarios.
Rollups limited to related entities. Many times we have had a customer come to us and ask can we Sum up data values but limit the data to be summed by a value held in a configuration type entity with no relationships to the entity being summed. Medium & large CRM deployments in the real world need this level of flexibility as they can have tens to hundreds of rollup fields with slight variations that need to be handled with a simple configuration entity that easy to maintain and change in the future.
Calculated Fields
Calculated fields are not persisted to the database. They are only calculated when a user retrieves a record(s) (i.e. Retrieve and RetrieveMultiple events). Some of our concerns about calculated fields are:
Performance I: Calculated Fields are implemented as ‘computed fields’ in the database which means they are calculated each time a user retrieves a single record or when the user selects a list view of records. To get an idea of the number of computations involved:
Let’s say we have a system with 1000 users, there are 5 calculated fields on the Account entity, each calculated field is placed on the default view and the ‘Records Per Page’ value is set to 250 for each user. So imagine if all the users come back from lunch at the same time and click refresh on the default view of Active Account records. Behind the scenes on the CRM database it needs to compute:
1000 x 5 x 250 = 1.25 million (Yes, that’s over 1 million calculations)
If you had a medium CRM deployment of 100 users & enforced a rule about only 50 ‘records Per Page’’ it’s as follows,
100 x 5 x 50 = 25,000 (Calculations)
Now while these are big numbers SQL Server is super smart and so maybe 90%+ of the time these calculations will all be done in memory so it will be blazing fast. But it does raise concerns that there is a potential bottleneck on the database tier for anything other than small CRM deployments.
In contrast North52 BPA would perform the calculation once, store the value and then CRM would just use its standard retrieval mechanism. In addition the North52 calculations all execute in the CRM sandbox so you have practically unlimited horizontal scalability. Unlike the database where you can only scale vertically.
Performance II: As calculated fields are implemented as ‘computed fields’ (User Defined Functions under the covers) there can be no sharing of retrieved data. Each calculated field performs a minimum of 1 database query for the current entity. This means if you had 5 basic calculated fields on the account entity behind the scenes it would perform the exact same database query on the Account entity 5 times. The same goes for any related entities that you use in your calculated field.
Again take a system with 1000 users; There are 5 calculated fields on the Account entity, each field is placed on the default view and the ‘Records Per Page’ value is set to 250 for each user. Let’s assume the calculated field use 1 related entity to perform the calculation. Now when the 1,000 users come back from lunch and hit the refresh button:
1000 x 5 x 250 x2 = 2.5 million (Database Queries)
In contrast the North52 BPA engine can share any data that it retrieves between the calculations and thus significantly cut down on the number of database queries.
Performance III: No configuration capability as to how the underlying database queries execute. All calculated fields execute on the database as normal queries but there is no configuration option to specify the ‘no-lock’ optimization hint. This will result in a significant increase in locks on the database which could lead to blocking locks or even deadlocks. North52 BPA’s default setting is the same as Microsoft one but with the capability to provide a configuration parameter to specify no lock any database queries.
Number of supportable functions: When you start to think about these performance metrics it also raises the question of how many new functions can Microsoft potentially add to the existing list of 13 functions in the future. It makes it highly unlikely that they can match the 200+ functions that North52 has based on this architecture. One small item in the Green Beacon article is that the Calculated Fields behind the scenes generate the UDF function. Therefore what you really have is a mini T-SQL code generator when you create a calculated field. This is in contrast to the core of North52 BPA which is a business rules engine that executes natively in the CRM sandbox and so has far more flexibility and scale.
Only 1 action allowed per calculation. Calculations within Dynamics CRM only allow you to update a single field and it must be on the current record. In contrast North52 BPA allows you to update all fields on the current record or any other entities.
Workflow/Plugin Triggers: You cannot trigger any Workflows or Plug-Ins off these calculated fields. That means you are left with having to create a PBL rule or workflow to act as the trigger to execute the calculated field – you need to perform a check to decide whether or not to call an action.
For example, if you had the calculated field below and wanted to check whether a line item is over $1,000 you need to set a trigger on each field in the calculation to meet your requirement. It works but the level of indirection makes it tricky.
Quote Line Amount = ( ( (priceperunit*noitems) + freightcost) + salestax) – discountamount
Interestingly workflows in CRM 2013 can already handle many scenarios of what calculated fields offer. It has functions like Increment By, Decrement By, Multiply By, Clear and Workflows have the same set of date-time functions built in. Calculated fields are really just a cleaner implementation.
Limitations for both Calculated Fields and Rollups
The following provides a summary of the limitations and issues with Calculated Fields and Rollups. North52 has none of these limitations and far exceeds their capabilities.
- Workflows, Actions, Plug-Ins cannot be triggered by updates.
- Latest values are not available automatically in create and update pipeline for plugins.
- Both are read only to a user. No ability to edit (i.e. override) the field.
- Both cannot be updated by Workflows, Actions, Dialogs, Plug-Ins.
- Fields available only from the current and related lookup entities (N:1). No ability to gather data from 1:M, N:N or unrelated entities such as a configuration entity.
Limitations for Rollups
- Rollups are not calculated in real-time and so CRM designers will need to be extremely careful when architecting solutions as the present value in the rollup field may not be the actual value.
- Workflows, Actions, Dialogs and Plug-Ins will be severely affected by this lack of real-time.
- Defined over a single directly related entity with a 1:N relationship.
- Rollup of a rollup field is not supported.
- Only 100 rollups are allowed per system and a maximum of 10 on any given entity.
- Rollup fields may not use calculated fields or other rollup fields in their formula.
- If a new Rollup is imported via a solution then a mass update happens immediately leading to potential performance issues on Production systems.
- The table below shows the missing aggregating functions across different CRM data types. For example on a Whole Number field if you needed to Sum up employee counts across several Account records you could not do this.
| Data Type | COUNT | MAX | MIN | SUM |
| Whole Number | x | |||
| Decimal | x | x | x | x |
| Currency | x | x | x | |
| Date & Time | x | x |
Limitations for Calculated Fields
- Calculated fields do not calculate when the user imports data or data gets updated via the SDK.
- Calculated fields do not work with all CRM data-types. Multiple lines of text, Customer, Lookups and Party Lists data types are not available. For example you cannot use the Concat() function with the Description field on the Account entity.
- Only Calculated fields that use ‘simple’ fields can be sorted. Therefore you can’t use them for sort data in a query if the field itself references fields from other entities, or other calculated attributes.
- While specifying the conditions, you can select AND or OR operator but cannot combine the two in the same condition.
- Two calculated fields cannot reference each other in their respective formulas.
- Records must be saved first before calculated field is updated when form refreshes.
- Very limited set of operators/functions (see table) compared to 200+ offered by North52 BPA
| Operators \ Functions | Items |
| Math Operators | =, +, -, /, * |
| String Functions | CONCAT, TRIMLEFT, TRIMRIGHT |
| Date Functions | ADDHOURS, ADDDAYS, ADDWEEKS, ADDMONTHS, ADDYEARS, SUBTRACTHOURS, SUBTRACTDAYS, SUBTRACTWEEKS, SUBTRACTMONTHS, SUBTRACTYEAS |
If you would like to know more or have any questions please drop us an email at info@north52.com
About North52
North52 is a leader in the creation of software products for the Microsoft Dynamics CRM platform. As a Microsoft partner, North52 has an impressive and rapidly growing list of customers benefiting from its products. Headquartered in Cork, Ireland , North52’s core values of the right Technology, the right People and the right Partnerships ensures delivery of great products for their customers.
For more information, visit www.north52.com or contact info@north52.com