Microsoft SQL Server 2012 Data Quality Services

Microsoft SQL Server 2012 Data Quality Services

 

Purpose: The purpose of this document is to provide a walkthrough of how to leverage Microsoft SQL Server 2012 Data Quality Services in conjunction with Master Data Services for Microsoft Dynamics Master Data Management scenario.

 

Challenge: The pain that organizations are experiencing around data accuracy, consistent reporting, regulatory compliance, etc. generated a lot of interest in Master Data Management. Master Data Management becomes extremely important when you have multiple different systems and you have to manage the data in different systems. In fact what we would also love to do is to control the quality of the data and be able to perform data cleanse, data matching and other data quality related activities.

 

Solution: The data-quality solution provided by Data Quality Services (DQS) enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage. DQS is a knowledge-driven solution that provides both computer-assisted and interactive ways to manage the integrity and quality of your data sources. DQS enables you to discover, build, and manage knowledge about your data. You can then use that knowledge to perform data cleansing, matching, and profiling. You can also leverage the cloud-based services of reference data providers in a DQS data-quality project.  

 

Scenario

 

By now we have already implemented Master Data Management scenario using Microsoft SQL Server 2012 Master Data Services. The next step is to integrate Master Data Services with Data Quality Services to deliver end-to-end Master Data Management solution. For the sake of simplicity in this document I’ll consider usual Master Data Management entities such as Customers and Products.

 

Walkthrough

 

In this walkthrough I’m using Microsoft Dynamics AX 2012 R2 Demo VM (AX2012R2A) which has Microsoft SQL Server 2012 installed and Microsoft Dynamics CRM Online (free trial)

 

After you install Microsoft SQL Server 2012, Master Data Services and Data Quality Services (Server and Client components) you have to run Data Quality Server Installer to perform necessary post installation tasks by using dedicated installer which will create appropriate DQS databases, set up security, etc. In case you have multiple instances of Microsoft SQL Server installed please locate appropriate DQSInstaller.exe file in corresponding Microsoft SQL Server Binn folder

 

 

Please note that the default Microsoft SQL Server instance may be different from the desired (MSSQLSERVER) which is the case for Microsoft Dynamics AX 2012 R2 Demo VM (AX2012R2A), that’s why it is critical to run appropriate DQSInstaller.exe during DQS installation  

 

Data Quality Server Installer 

 

 

Once DQS installation is complete you can launch DQS Client

 

SQL Server Data Quality Services – Client

 

 

The next step will be to create Knowledge base for future data quality related activities. In particular I’ll be doing data matching trying to detect duplicates

 

Knowledge base management

 

 

Please note that number of domains are available for Knowledge base “DQS Data”. In particular I’ll be using “US – Last name” domain to detect duplicates based on last name of customers

 

Actual data matching we’ll perform in Microsoft Excel

 

 

That’s why first off we have to establish connection to MDS Server

 

Excel Add-in – Add new connection

 

 

Manage connections

 

 

Once connection has been established

 

 

Now we can use Master Data Explorer to open up appropriate Model (“Dynamics”)

 

 

In this scenario I’ll work with customers and at the moment I have one customer record in Customers table in MDS database

 

 

In Microsoft Excel I can introduce new (External) data as shown below

 

 

But before I publish this data to MDS I can perform data matching and find out if I have any duplicates or similar data based on criteria defined. For these purposes I’ll use Combine data feature which allows me to combine MDS data and External data in order to perform data matching activities

 

Combine data

 

 

I’ll select what data I want to combine and map the fields appropriately. Once this is done the system shows “who is who” in SOURCE field

 

 

When I have the data for analysis I can use Match data feature to perform data matching

 

Match data

 

 

Please note that for Name field I specify Domain = US – Last name, so the system will apply matching rules for the analysis based on Last name. The data will be clustered in accordance to similarity as shown below in CLUSTER_ID field  

 

 

Finally I’ll Show Details to see the results of extended analysis

 

 

Please note that for External data the system assigned a score in SCORE field which shows degree of similarity within the cluster. It appears that “Alex Anikiiev” is 90% similar to “Alex Anikiev” which allows me to define and eliminate duplicate data before publishing this to MDS

 

You can also perform periodic data cleanse, data matching and other data quality related activities using MDS and DQS to ensure the quality of your master data

 

Summary: This document describes how to leverage integration of Master Data Services and Data Quality Services when implementing Master Data Management scenario for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM. In particular I explained how to use Data Quality services in order to perform data matching based on certain criteria. Microsoft SQL Server 2012 provides a robust platform for Master Data Management which not only includes Master Data Services, but also Data Quality Services, SQL Server Integration Services, etc. Master Data Services provides Web front-end as well as Excel Add-in which allows you to choose your favorite tool to perform Master Data Management activities.

 

Tags: SQL Server 2012, Dynamics AX 2012, Dynamics CRM Online, Master Data Management, Master Data Services, Data Quality Services, SQL Server Integration services, Customers, Products.

 

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.

 

Author: Alex Anikiev, PhD, MCP