Share via

Data Quality in SQL Server 2012 Part 4: Data Quality Services Client

  • Article

Data Quality Services, introduced in the previous blog post, includes two main components: DQS Server and DQS Client. There’s also a DQS Cleansing transformation included in SSIS in SQL Server 2012, but I won’t be going into that in this post. In this post, I’ll be talking about these two, core components.

At its simplest, you can think of these two as DQS Server doing all the hard work and DQS Client being the user interface.

DQS Server is installed as part of the SQL Server Setup. It includes database for storing the knowledgebase information and the policies and rules that are applied to business data during cleansing and matching activities. It also includes the DQS engine, which is what applies all the information and rules in the databases.  

DQS Client is a stand-alone client that can be used by people in different roles to clean data, maintain the knowledge bases or configure DQS Server. You have to have sufficient permissions, either on the SQL Server as a whole or as a specific DQS role, in order to open the DQS Client and connect to the appropriate DQS Server.

DQS Client allows users and administrators to perform key duties: knowledge base management, data quality projects and administration.

Knowledge Base Management

As described in the previous post, the concept of a knowledge base is core to DQS. Within the knowledge base, you have a number of domains. A domain is a representation of a type of data that is used by the business, such as a customer name, an address, or a contact phone number. For each of these domains, there are certain values which can be trusted and certain values that are invalid. The example I used in the previous post was of counties. County would be a domain, with values like Nottinghamshire and Berkshire as trusted values because they are known to be real counties. You can include synonyms, so that the value of Notts is known to be referring to the same thing as Nottinghamshire.

Within the Knowledge Base Management part of the DQS Client, you can create and manage domains, set the trusted values and give examples of invalid values. Each knowledge base contains one or more managed domain.

Defining your knowledge base and all the domains, values and rules within it can be done manually through the DQS Client. It can also be done through the Knowledge Discovery process to automatically generic rules that can then be approved and included in the knowledge base.

Data Quality Projects

Once you’ve built your knowledge base and created the rules which are applicable to your data, you need to use them. The Data Quality Projects section of the client tool lets you apply your knowledge base for either cleansing of data or data matching.

Cleansing projects apply rules to find data that is incomplete, inaccurate or not in line with your policies. DQS applies the rules from your knowledge base where applicable and can come up with suggestions if there are some values which are not in the domain, but which are similar to domain values. These suggestions allow you to catch typos without having to think of a rule for every single mistake that might possibly be made.

Matching projects pull in the data and look for records which are similar. DQS applies the matching rules defined in the client and, using things like synonyms and similarity of phrases, highlights duplicate records. It can combine records where there is a definite match or highlight ones where there is a possible match, including a confidence score based on the similarity of the records and the specifics of the rules.

Both of these projects then allow you to export the cleaned data so that it can be used by your systems.

Administration

This section of the tool lets you track on-going activities within DQS and update the configuration settings.

A data steward can use this part of the tool to view what processes are in progress within DQS. That person can then choose to stop processes if required.

This is also the place to define the settings for using reference data. The idea of reference data is that you can pull in information from an external source, for example the Azure data marketplace, and use that as a comparison to clean up internal data (e.g. matching addresses to postcodes). You would use the administration part of the DQS Client to configure these connections.

You can also change some settings in this section, for things like logging and notifications.

So you can see that the DQS Client tool is a vital component of a data quality project because this is where so much of DQS functionality is implemented from.

Comments

  • Anonymous
    January 01, 2003
    Hi John It's not the last post in the series - I just never posted part 5. :)

  • Anonymous
    January 01, 2003
    Is the series finished or are there more parts to come? I suppose that if I had read part 1 - 4 I would know the answer.

  • Anonymous
    January 18, 2013
    Hi Jessica, Could you please clarify whether the exported cleaned data can be used in the same way the un-cleaned data was used? Say, for example, we used a table from the DB for cleansing, after the cleansing is done and the cleansed data is exported [I assume a  new table would be created in the DB with cleansed data] to the DB. So, can we use this new table instead of the old one in the same way? Thanks in advance!