Using Goals As Rollup Tables in Microsoft Dynamics CRM
A long time ago I wrote an SSRS report for a client using CRM 4.0. The report was great, and did everything it needed to do for the business. The problem was that it was used by the business a lot and it was not fast. The main reason the report was not fast was because it was doing its calculations on the fly every time it was run, and doing these calculations on large numbers of records across Filtered Views. Were we still in 4.0 I would have solved this problem with the replacement of ad hoc calculations with SQL rollup tables or even setting up report subscriptions or snapshots. However, CRM 4.0 is, dare I say it, old school at this point and the customer converted to CRM 2013. This allowed us to consider a new option that was part of even 2011: Goals.
It's worth noting that the report in question was driven off of a custom goals entity in CRM 4.0 which housed essentially the annual target and nothing else. The report calculated what the achievement was along a variety of different times (monthly for each month of the goal year, quarterly for each quarter of the year, the year itself, and then compared each of these across the previous year for the same). In short it was quite complicated.
A rollup table would serve two purposes: it would both speed up and simplify the SQL behind the report. This would require setting up an integration using SSIS or SCRIBE. Not the worst thing to have happen, as these are certainly viable options. However, what if the business wanted to change the criteria for these rollup tables? That would require a development effort. And what if someone wanted to see the criteria for making these rollup tables? That too would require a developer to at least validate the documentation of the criteria. What if there was a way to avoid setting up an integration, allow for both flexibility and visibility, and of course solve the problem that a rollup table is designed to solve? The answer is the Dynamics CRM Goals module.
Goals have many components, but at a basic level it measures the distance between a target and its achievement. It has a finite time limit and can have conditions over how its achievement can be defined. And every night (or predetermined interval) the system calculates the level of achievement automatically. This is very similar to an integration. Instead of using SQL, there is a Rollup Query combined with the time parameters on the Goal and the measurements in the Goal Metric. And instead of scheduling a job, the options in System Settings are set. And if anyone ever wants to see what records are a part of the goal, there’s this little feature:
That’s right, you can see what records are actually a part of the goal, which can be helpful in any troubleshooting. To that end, the conditions for the Rollup Query are also readily available. Without having to open Visual Studio, all the definitions of the goal and its underlying records are readily available.
To tie this all back to the original speed problem, the speed of the report, tying the report to a goal was the same thing as tying it to a SQL table. This along with a couple of other improvements shattered the rendering time of this report.
One of the things that is important to note is that this allowed the business to take control of the report and lessened their reliance on the IT staff or developers. Any questions about the data in the report was answered quite readily as both the criteria and the data were immediately available. Any changes can be done by modify the goal records that make up the report rather than the report or integration.
Now this isn’t a panacea of course. The general rule is that if you can build an Advanced Find you can build a goal. However, there are limits to Advanced Find where you might need the more powerful features of SQL or an SSRS report. But the simplest solution should always be investigated first.