D365UG UK: Data Integrity & Quality – Tips & Tools

A bit of a mouthful of a title! I had a great time earlier today presenting at my first ever virtual event, D365UG UK. Many thanks to the organising team for a smoothly run event!

My topic today was using a variety of free tools to unearth some data quality issues in your CRM installation that you may be unaware of, and some suggestions on common causes and possible resolutions.

The tools I used were:

• Advanced Find
• FetchXML Builder
• SQL 4 CDS
• Data Export Validation Tool

Those last three are all free XrmToolBox tools, so head over to the tool store to install them today.

The slides are available on the D365UG UK forum, but I also promised to share the script that you can run in SQL 4 CDS to check many of the common scenarios we looked at. Many of these could also be run in either Advanced Find or FetchXML Builder if you prefer.

-- Accounts assigned to inactive users
select a.name
from
    account a
    inner join systemuser u on a.owninguser = u.systemuserid
where
    a.statecode = 0 and
    u.isdisabled = 1

-- Accounts with a disabled primary contact
select a.name
from
    account a
    inner join contact c on a.primarycontactid = c.contactid
where
    a.statecode = 0 and
    c.statecode = 1

-- Accounts with a primary contact that isn't part of the account
select a.name
from
    account a
    inner join contact c on a.primarycontactid = c.contactid
where
    a.statecode = 0 and
    (c.parentcustomerid is null or c.parentcustomerid <> a.accountid)

-- Contacts with the same firstname and lastname
select contactid, firstname, lastname
from
    contact
where
    statecode = 0 and
    firstname = lastname

-- Contacts with the same lastname and email address
select contactid, firstname, lastname
from
    contact
where
    statecode = 0 and
    lastname = emailaddress1
    
-- Common firstnames
select firstname, count(*)
from
    contact
where
    statecode = 0 and
    firstname is not null
group by
    firstname
order by
    count(*) desc
    
-- Common lastnames
select lastname, count(*)
from
    contact
where
    statecode = 0 and
    lastname is not null
group by
    lastname
order by
    count(*) desc
    
-- Contacts with a parent contact
select c1.contactid, c1.firstname, c1.lastname
from
    contact c1
    inner join contact parent on c1.parentcustomerid = parent.contactid
where
    c1.statecode = 0

-- Orphaned contacts
select contactid, firstname, lastname
from
    contact
where
    statecode = 0 and
    parentcustomerid is null

-- Active contacts in inactive accounts
select contactid, firstname, lastname, account.name
from
    contact
    inner join account on contact.parentcustomerid = account.accountid
where
    contact.statecode = 0 and
    account.statecode = 1