Many interviewers have started asking interview questions around SQL Server 2012 and one of the questions is DQS.
When we talk about business intelligence it’s all about DATA, DATA and DATA. One of the big problems with data is that it can come in crude and unpolished formats. For instance if someone must has entered “IND” and you would like to change it to “India” so that data is in a proper format. DQS helps you build a knowledge base for your data and you can then use this knowledge base to do data cleaning. You will see DQS as shown in the below image.
Once you open DQS you will find three sections as shown below Knowledge base, Data quality projects and Administration.
Knowledge base will help you define your validation rules. For instance you can see in the below figure how we are creating a validation called as “CustomerCode” and this validation checks if the data length is equal to 10.
You can also define correction rules like as shown one below. If you find data as “IND” change it to “India”.
Once you have defined you knowledge, next step is to run this knowledge base over a data. So create a DQS project and apply the knowledge base which you had created as shown in the below figure.
You can then define where the data can come from and also you can map which columns can have which validations. For instance you can see in the below screen for country and customer we have mapped different domains. Domains are nothing but validation rules.
Once done you can start the process and you would see a progress screen as shown below of corrected values and suggested values depending.
Finally you can export the cleaned data to SQL Server, Excel or CSV.
One more interesting SQL Server interview question around Subqueries and Co-related queries :- http://www.dotnetinterviewquestions.in/article_sql-server-interview-question:-what-are-sub-queries-co-related-queries-and-difference-between-them_119.html
The above question is taken from the book SQL Server interview question which is published by BPB publication.
