CRM systems have a tendency to suffer from an increasing amount of entropy as time goes by. Not only does the rate of accurate information available from them decrease as data quality decays over time (especially if no one’s in charge of actively maintaining it), they’re also susceptible to a phenomenon I’d describe as “the illusion of having data”. This is the assumption that simply defining a data model that holds a place for specific attributes or entities would actually result in data being collected into them.
“Build it & they will come” could be translated to “customize it & they’ll use it” when it comes to CRM systems and it is as good a strategy in designing business information systems as it is in any other walks of life – meaning not very. Sure, during the initial requirements specification phase for a CRM system it may feel like there has to be a field added to the customer’s profile for every possible variable that the business may need in the future. After having used the system for a while you’re very likely to be confronted with the reality that very few if any of the records have any data entered into these fields. Or even worse: you just continue to assume to have customer information that doesn’t really exist, potentially building further process automation and reporting on a very shaky foundation.
Since it’s quite a common phase in the lifecycle of a Dynamics CRM organization to sooner or later face a situation where you want to clean up the system from legacy data structures that no longer serve their purpose, I want to highlight a couple of tools that will help you on this journey towards a better organized CRM system.
Finding Fields Not on Forms
Persons who may get assigned the role of being the CRM system administrator alongside their “actual work” often approach the application as if it would consist of a set of forms that contain fields and… well, nothing much else. What this means in practice is that whenever a new business requirement comes up where additional information should be captured to serve a new process, product, organization structure or what have you, they’ll typically open either the account or contact entity and start adding new fields onto the forms.
As this process is repeated over and over again, the number of fields will grow and at some point some of the older ones will probably get removed from the form in an effort to make the system less cluttered. They’ll most likely be left in the system with their Searchable property still set to “Yes”, meaning using Advanced Find can become a nightmare with all the legacy fields listed. Also system views may still be using these fields that can no longer be edited. With the number of fields growing every day, it can simply become overwhelming to identify what’s in use and what’s not.
A good first step for finding the legacy data structures is to list the fields that are no longer used on any forms of the entity. Since Dynamics CRM by default does not offer tools for such analysis, the next place you should look for a solution is the community tools on CodePlex, starting from the nr. 1 toolkit for a CRM customizer, which is of course XrmToolBox. As it so happens, there’s yet again a tool in there that will help us in achieving our goal. The Metadata Document Generator offers a setting that allows us to export a list of attributes for the selected entities that are NOT contained in any of the forms (remember that there can be more than one per entity).
By choosing this setting and complementing it with the “include Valid for Advanced Find information” checkbox we can generate Excel sheets per each entity that list the unused fields, at least when it comes to the UI side of things.
Finding Fields With Little or No Data
Once we’ve spotted the fields that have potentially become redundant and we’d like to get rid off, the next question in our minds is likely to be how to verify whether there is any actual data stored in them that should be preserved. Similarly, just because a field is present on a form, that hardly proves that it would be populated for the records in the CRM database. These are the kind of questions that we can’t answer via metadata alone, instead we’ll have to dive into the actual data itself.
A quick way to examine the usage of specific fields without any custom tools or solutions is to use the built-in charts feature of Dynamics CRM. Let’s say we want to see if our users have actually entered data into the “No. of Exployees” field on the account form. By moving to a suitable view like “Active Accounts” (with a suitable filter to remain under the Aggregate Query Limit in terms of number of records), expanding the chart pane on the right side and clicking on the plus icon, we can start to define a new chart to help us in our ad-hoc data analysis needs. For the series we should choose a field that is populated for all of the records (I always use the record GUID field, meaning for the account entity I’d choose the “Account” field). In the category section we’ll then select the field on which we want to analyze the distribution of the data. While we can’t show exact percentages in the Dynamics CRM charts, the good ol’ pie chart visualization will quickly tell us the rough share of records with a “(blank)” value.
We don’t even need to save our charts to perform the analysis, since in many cases the live preview in the chart editor mode will already tell us how big a piece of the pie goes to blank values. By leaving our mouse cursor in the Category picklist and pressing the up/down arrows we can quickly scroll through different fields and view the distribution of values.
While this works for a reasonable amount of fields, it doesn’t necessarily produce a very good overview of the level of entropy in the CRM organization unless you write down these statistics manually onto an Excel sheet. To increase our performance in this task we can again look for a community provided tool to help us out.
Scanning Your Entities for Unused Fields
Paul Way has created a solution that fits the bill perfectly: CRM Data Detective. By uploading this solution into our CRM organization and simply navigating to [YourCRMorg]/WebResources/way_/detective/detective.htm we can choose any entity in the system and have its database contents analyzed by the Detective. The end result will be a list you can quickly scroll through and view the bar chart for the population rate per each attribute:
This will be a great tool for you in the discussions with business decision makers who’ve originally demanded the fields to be added into the CRM data model. If you’ve got fields on an entity that have a 2% utilization rate, chances are your system would become more valuable by removing such fields. The overall usability of your CRM environment would increase and you also wouldn’t need to suffer from the illusion of having certain data available, just because someone had once created a place for it in the CRM data model.
