SQL SERVER – Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 – Introduction to DQS

Data Quality Services is a very important concept of SQL Server. I have recently started to explore the same and I am really learning some good concepts. Here are two very important blog posts which one should go over before continuing this blog post about Data Quality Services.

This article is introduction to Data Quality Services for beginners. We will be using an Excel file

Click on the image to enlarge the it.

In the first article we learned to install DQS. In this article we will see how we can learn about building Knowledge Base and using it to help us identify the quality of the data as well help correct the bad quality of the data.

Here are the two very important steps we will be learning in this tutorial.

  • Building a New Knowledge Base 
  • Creating a New Data Quality Project

Let us start the building the Knowledge Base. Click on New Knowledge Base.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs1

In our project we will be using the Excel as a knowledge base. Here is the Excel which we will be using. There are two columns. One is Colors and another is Shade. They are independent columns and not related to each other. The point which I am trying to show is that in Column A there are unique data and in Column B there are duplicate records.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs3
Clicking on New Knowledge Base will bring up the following screen. Enter the name of the new knowledge base.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs4

Clicking NEXT will bring up following screen where it will allow to select the EXCE file and it will also let users select the source column. I have selected Colors and Shade both as a source column.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs5

Creating a domain is very important. Here you can create a unique domain or domain which is compositely build from Colors and Shade.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs6

As this is the first example, I will create unique domain – for Colors I will create domain Colors and for Shade I will create domain Shade.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs7

Here is the screen which will demonstrate how the screen will look after creating domains.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs8

Clicking NEXT it will bring you to following screen where you can do the data discovery. Clicking on the START will start the processing of the source data provided.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs9

Pre-processed data will show various information related to the source data. In our case it shows that Colors column have unique data whereas Shade have non-unique data and unique data rows are only two.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs10

In the next screen you can actually add more rows as well see the frequency of the data as the values are listed unique.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs11

Clicking next will publish the knowledge base which is just created.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs12

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs13

Now the knowledge base is created. We will try to take any random data and attempt to do DQS implementation over it. I am using another excel sheet here for simplicity purpose. In reality you can easily use SQL Server table for the same.
SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs131

Click on New Data Quality Project to see start DQS Project.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs14

In the next screen it will ask which knowledge base to use. We will be using our Colors knowledge base which we have recently created.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs15

In the Colors knowledge base we had two columns – 1) Colors and 2) Shade. In our case we will be using both of the mappings here. User can select one or multiple column mapping over here.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs16

Now the most important phase of the complete project. Click on Start and it will make the cleaning process and shows various results.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs17

In our case there were two columns to be processed and it completed the task with necessary information. It demonstrated that in Colors columns it has not corrected any value by itself but in Shade value there is a suggestion it has. We can train the DQS to correct values but let us keep that subject for future blog posts.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs19

Now click next and keep the domain Colors selected left side. It will demonstrate that there are two incorrect columns which it needs to be corrected. Here is the place where once corrected value will be auto-corrected in future.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs20

I manually corrected the value here and clicked on Approve radio buttons. As soon as I click on Approve buttons the rows will be disappeared from this tab and will move to Corrected Tab. If I had rejected tab it would have moved the rows to Invalid tab as well.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs21

In this screen you can see how the corrected 2 rows are demonstrated. You can click on Correct tab and see previously validated 6 rows which passed the DQS process.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs22

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs23

Now let us click on the Shade domain on the left side of the screen. This domain shows very interesting details as there DQS system guessed the correct answer as Dark with the confidence level of 77%. It is quite a high confidence level and manual observation also demonstrate that Dark is the correct answer. I clicked on Approve and the row moved to corrected tab.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs24

On the next screen DQS shows the summary of all the activities. It also demonstrates how the correction of the quality of the data was performed. The user can explore their data to a SQL Server Table, CSV file or Excel.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs25

The user also has an option to either explore data and all the associated cleansing info or data only. I will select Data only for demonstration purpose.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs26

Clicking explore will generate the files.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs27

Let us open the generated file. It will look as following and it looks pretty complete and corrected.

SQL SERVER - Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 - Introduction to DQS dqs28

Well, we have successfully completed DQS Process. The process is indeed very easy. I suggest you try this out yourself and you will find it very easy to learn. In future we will go over advanced concepts.

Are you using this feature on your production server? If yes, would you please leave a comment with your environment and business need. It will be indeed interesting to see where it is implemented.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Business Intelligence, Data Quality Services, Data Warehousing, DQS, SQL Server
Previous Post
SQL SERVER – DQS Error – Cannot connect to server – A .NET Framework error occurred during execution
Next Post
SQLAuthority News – A Quick Note on @Pluralsight Video – Call Me Maybe Developer Way

Related Posts

26 Comments. Leave new

  • Has anyone experienced issues where knowledge base publish runs for days with “in process please wait” and never completes? It looks like SQL server is processing a delete statement from DQS_Projects. Any help would be greatly appreciated.

    Reply
  • I need DQS for government housing board database.

    Reply
  • I know this is probably not checked, but how do you get the program to update the line
    Belt Green Light

    it should be dark if i am not mistaken.

    Reply

Leave a Reply