Removing duplicates in Microsoft Dynamics CRM

In last month's edition of the DQC I reviewed some data quality features built into Microsoft's CRM package, namely detect a duplicate upon create or update, duplicate detection rules and duplicate detection jobs.  I left off with a promise to dive deeper into how you remove the duplicates once you've detected them. 

Before I get into the details, I want to emphasize that without customization, removing duplicates is not a batch process.  In other words, you remove duplicates one at a time.  Don't kill the messager; learn from the message.  If there is one area within the data quality space that Microsoft needs to improve on, it's this one. 

Duplicate consolidation, in my experience, is rarely so exception based that it can be done in such a tedious manner.  Not to mention that those organizations that are most afflicted with duplicates generally have  a large customer base.  When you have a customer base in the millions, duplication ratios can be as high as 10% or more.  Consolidating 100,000 duplicates one at a time is almost pointless.  By the time you catch up, you've created more duplicates.



  That said, let's move on.  So you've detected duplicates and now you want to eliminate them from your data. 

If you remember from last month's post, read up here if you don't, a duplicate detection job returns potential duplicates and allows you to browse each one along with it's potential match.  Consult the screenshot below for a view of what that looks like.

[caption id="attachment_306" align="aligncenter" width="500" caption="Duplicate Detection Job results"][/caption]

In the lower pane of the screenshot above there is a toolbar option (3rd from the left) is a icon to merge the two highlighted records.  This is where the consolidation effort begins.

One of the best features of the merge functionality is that it has the flexibility to build a composite, or best of available information, master record.  Briefly, the master record is the record which is retained as the active record.  It also allows the end user to select one record over another.  An example of these features is outlined in the screenshot below.  First let's look at the option where each element of the master record are selected.

[caption id="attachment_312" align="alignleft" width="500" caption="An example of an all inclusive master record selection"][/caption]

Here's a look at the composite option:

[caption id="attachment_315" align="aligncenter" width="500" caption="An example of a composite master record selection "][/caption]

Notice in the all inclusive example the entire left hand column is highlighted in blue, whereas in the composite option only those elements selected via the radio button are highlighted in blue.  This is a visual indication of what data elements will be retained in the master record.

This is one of my favorite pieces of functionality with regard to the merge option.  Often end users vary in the data they provide and it is always better for an organization to retain as much information about their customers as is possible.

I specifically chose the composite screenshot presented because it illustrates one of those important aspects in customer data quality.  Noticed that the element selected from the right hand side was a middle initial.  This data element is invaluable when performing data matching and having that element can make an important distinction between two different customers later on down the road.

Once you've defined what your master record looks like, either via the all inclusive or composite method, it is time to commit that selection.  The screenshot below illustrates how this is performed.

[caption id="attachment_316" align="aligncenter" width="500" caption="How to commit your master record selection"][/caption]

An important option in the commit process is one enabled through the checkbox provided visible in the screenshot above.  Not every field in a record is in all cases exposed via the merge utility.  As a result the option made available through the checkbox allows you to make sure that, as the label indicates, select every field with data from the chosen master record even if there is a different value in the other record.  Simply put, it is an overwrite function that retains all the data from the selected master record beyond what is visible in the merge screen.

Once you've reviewed and are confident in your selection, you simply click on the OK button.  Provided there are no commit locks on the record, which indicates that another user has one of the two records open and is actively working on it, you will receive the following dialog box confirming your consolidation success.

[caption id="attachment_317" align="aligncenter" width="441" caption="Duplicate elimination success!"][/caption]

It is critical to note that the subordinate, or non-master record, is NOT deleted from the system.  It is simply deactivated.  This is to say that a flag (statecode) is changed to inactivate.  One important note about the statecode field is that unlike conventional notation a value of '1' is not active in Microsoft Dyanmics CRM.  Instead Microsoft chose the value '0' as active and '1' as inactive.  Consequently all non-master records in CRM have a statecode value of '1'.  This little fact can save hours of data analysis and perserve the samity of your DBAs, so it is worth noting.

I hope this information was beneficial to you Microsoft Dyanmics CRM users and administrators.  As usual I welcome all comments, questions, and suggestions.  So please feel free to comment on this post and I'll try and replay in a timely manner.