Data Quality Services (DQS)

Matching related and duplicate Customer Records using SQL Server Data Quality Services (DQS)

Welly.Lee — Mon, 17 Jun 2013 17:11:00 GMT

SQL Server Data Quality Services (DQS) enables you to match records using your matching rules defined in DQS Knowledge Base. There are two common use cases where you would like to match related records. One is to identify duplicate in your records, such as same customers appear as separate records in your dataset. Another common case is to identify related customers. For example, you would like to identify two separate customer records that belong to the same parent company. In this blog article, I am going to show how to create a simple matching rules and use them to match duplicate and related records.

For this article, I am using the same data set and the knowledge base created in the previous blog article: How to cleanse Customer Data using SQL Server Data Quality Services.

The following are the high level steps:

A. Add matching policy to DQS Knowledge Base

B. Create and execute a DQS matching project

A. Add matching policy to DQS Knowledge Base

Start Data Quality Client. For information about this step, refer to Run the Data Quality Client Application.
In the Data Quality Client home screen, under Knowledge Base Management:

Click Open Knowledge Base

Note: this article builds on the an existing Knowledge Base created from the previous article to illustrate how domain definition for cleansing helps matching. However, it is not required that you create matching policy from an existing Knowledge Base. You can start by creating a new Knowledge Base.

Select MyCustomerKB then select Matching Policy under Select Activity then click next

Note: If you accidently click open without changing activity to matching policy, you will open the domain management screen. Unfortunately, there is no way to navigate to matching policy screen from domain management screen, so you have to first publish the Knowledge Base and start over step 2 to go to Matching Policy screen.

In the Map screen:

Connect to a data source to test your matching policy. Select the columns in your data set to the domains in your Knowledge Base by selecting from the drop down. Click Next to finish mapping.

Note:

You only need to map the columns needed for matching purposes. In my example, I only select CustomerName, City and Province and I left out CustomerID (unique row identifier) and last update columns as I do not need those columns for matching
If you start with a new Knowledge Base or your Knowledge Base does not have the domain needed for your matching, you can click the Create Domain icon () to create the domain directly from this screen.

In the Matching Policy Screen:

Create rule for matching by Customer Name

Click icon to Create a matching rule () and give a name (e.g. Match By Customer Name). In this example, we would like to identify related customer by assuming that customer which has similar/same name belong to the same organization (This is a simplified example to illustrate the capability, in reality, you would use many more domains to achieve the goal-- more about using multiple domains for matching rules later).
Click Add a new domain element icon to add domain for the matching policy. Specify weight = 100% (more about this later).
Click Start button to test your rule.

Note:

When performing matching, DQS automatically applies all domain definition in the Knowledge Base. As we are using an existing Knowledge Base which includes term definition for "co" as the same as "Company", DQS uses it to match "Consolidate Co Ltd" and "Consolidation Company Ltd". If you start with a new Knowledge Base or do not have the term based relation defined for CustomerName matching, then the 2 records will not be matched.

Create rule for matching by City and State

Click the icon to Create a matching rule again to create a second rule and give a name (e.g. Match by City State). In this example, we would like to identify duplicate customer recording assuming that a customer has only 1 store for a given city in the same state (It is not a realistic example, but you can imagine extending this to an address where if two businesses records have the same address, then the records could be a duplicate).
Click Add a new domain element icon to add domain to the matching policy. Click down arrow next to the domain name and from the drop down, select City domain.
Click Add a new domain element icon again to add the second domain, and select State from the drop down. When you have more than 1 domain in your rule, you will need to distribute weight across the domains. DQS will calculate matching score for each domain then use the weight to calculate overall matching score for the rule. For our example, you can distribute equal weight (50-50) across the 2 domains. Click Start button to test your rule.
Note:

As the case of the CustomerName, DQS use the domain value definition in the city and state for matching purposes. As "LA" was defined as synonym for "Los Angeles", DQS match the 2 records as 100% match
In the rule editor, you can also specify how the matching should be performed: similar (which we have been using in our example) or exact. The exact matching requires the value to be identical. This is typically used when you want to match things such as BirthDate or Identifier (e.g. SSN, CustomerID, etc.). If you have domains that can use exact matching, it is recommended that you mark it as prerequisite to improve performance. In order to match records from a dataset, DQS compare a record with all others, specifying prerequisite, effectively partition your dataset base on the value of the column. This may significantly improve performance.

Click Start to test your rule.

Click Next to continue.

In the matching results screen:

Click Start to test. While in the previous step, you test against specific matching rule, in this step, you can see how matching results from ALL of the rules. All the records that are matched together have the same Cluster value.

The following is what you could interpret from the results:

The first cluster shows the two related companies which have similar name (87% matching score). The name is similar enough that the difference in name may be due to human error during data entry.
The second cluster shows the two companies that have the same address although they are entered as different names (one name in Chinese and another in English) -- the two customer records appears to be duplicate.
The last cluster match records based on the 2 rules ("Match By Customer Name" and "Match By City State" -- the Rule column provides different color for different rule, you can click the Matching Rules tab at the bottom for the rule color legend). "Big Cheese, The" and "THE BIG CHEESE" appears to be related companies in a different city. The second one "To be filled later" appears to be a placeholder as the name may not available when first entered into the system. Later, a different person may enter the record as separate new record rather than updating an existing one and resulting in a duplicate.

Click Finish to publish your Knowledge Base.

B. Create and execute a DQS matching project

In the Data Quality Client home screen, under Data Quality Projects, click New Data Quality Project.
Name your new Project (e.g. MyCustomer Matching Project), select Knowledge Base created in the previous step (e.g. MyCustomerKB) and select Matching Activity

Note: Make sure to select the matching activity, once you click Next, you can't go back and change the activity, so you have to create a NEW project and start over.

In the Map screen:

Select data source, database, and table for your data (e.g. MyCustomers table and data generated from the script at the beginning of this article).

Note: in this example, we are using the same data set when we create the knowledge base, but it is not required. In more realistic example, you build the rule with a representative sample of your data set, then use the full data set for the project. You can reuse the same Knowledge Base for different data set by creating a different project.

Select columns to be matched and map to the domains in your knowledge base (you must map all domains used by all the rules to continue).
You should have your data source columns mapped as below. Click Next to continue.

In the Matching screen:

Click start to begin the matching process
Review the results and click next to continue.

In the Export screen

Select destination (e.g. SQL Server)
Select database name
Click the checkbox next to the Matching Results and specify table name

Click Export to continue

Note:

Optionally, you can also export survivorship results, for DQS to export only one record from the same cluster. You can specify how the record should be picked from the following option:

Pivot record : the first record in the cluster
Most complete and longest record
Most complete
Longest record

Unfortunately, DQS does not support the option to select record based on value in a field (such as updated date or the originating system). For scenario such as picking the most recent record or record from a trusted system, you have to export the entire matching results then implement your own logic for survivorship.

Review the results

Open SQL Server Management Studio and query the export table
Review the result. Use the CLUSTER_ID column to identify cluster of matched records.

In this blog article, I discussed how to create a Knowledge Base to define matching policy. You can then refer to the Knowledge Base to create Data Quality Projects to match your customer data. In this article, I use a simple example to walk through the most common functionalities. For more detailed explanation of DQS Matching, refer to the blog article wrote by my colleague, Gadi Peleg:

It is also recommended that you run a separate cleansing project on your data set before you run matching project. Although DQS matching process uses the same domain definition for the cleansing, it does not make the correction nor does it flag invalid values. In addition, DQS matching only use the internal domain definition and does not automatically send your data to external third party providers. You must explicitly run a cleansing project to send the data externally to the Reference Data Service providers. However, the Service Provider may include additional information about the data you send. For example, Dun & Bradstreet includes DunsNumber in the output. This may be another useful information to use as part of your matching rule. Refer to my previous article on Cleansing your Customer data using Dun & Bradstreet DQS Service for more information on how to use DQS to cleanse data using Reference Data Service.

Cleansing Customer Data Using Data Quality Services (DQS)

Welly.Lee — Sat, 15 Jun 2013 02:08:00 GMT

SQL Server 2012 Data Quality Services (DQS) enables you to cleanse data using your own Knowledge Base. In this blog article, I am going to show how to create a simple Knowledge Base which you can use to cleanse your customer data.

The steps for using DQS to cleanse data is as follows:

A. Create DQS Knowledge Base
B. Create a DQS project to cleanse your data using the Knowledge Base

For this example, I am using the following sample data - you can run the following script on your SQL Server database :

CREATE TABLE MyCustomers
(
       CustomerID INT,
       CustomerName NVARCHAR(255),
       City NVARCHAR(32),
       Province NVARCHAR(32),
       LastUpdate DATETIME
)

INSERT INTO MyCustomers
VALUES      (1, 'Consolidate Co Ltd', 'Miami', 'FL','2013-01-01'),
                     (2, 'Consolidation Company Ltd', 'New York', 'NY','2013-01-01'),
                     (3, N'什锦的件', 'LA', 'CA','2013-01-01'),
                     (4, 'Chop-suey Chinese', 'Los Angeles', 'CA', '2013-03-03'),
                     (5, 'Big Cheese, The', 'Redmond', 'WA', '2013-02-02'),
                     (6, 'THE BIG CHEESE', 'Chicago', 'Il','2013-02-02'),
                     (7, 'To Be Filled Later', 'Redmond', 'Wash.', '2013-01-01')

A. Create DQS Knowledge Base

Start Data Quality Client. For information about this step, refer to Run the Data Quality Client Application.
In the Data Quality Client home screen, under Knowledge Base Management, click New Knowledge Base.
Name your new Knowledge Base (e.g. MyCustomerKB), make sure Domain Management activity is selected and click Next

Click Create Domain Icon.

From the pop-up window, name your domain (e.g. CustomerName). As an option, you can select to change the format of the output to Capitalize.

Repeat the steps and create domains for City and State.
Select City domain and click on domain values tab. In this screen, you can enter all the list of valid and invalid values for the domain.

Click on Add new domain value icon. Enter a correct value (e.g. Los Angeles) then press enter.
Click on Add new domain value icon. Enter an invalid value for the city (e.g. United States), click the down arrow in the Type column and select the invalid type (yellow triangle) then press enter. (There are 3 supported type: correct, invalid, and error; invalid suggests that the value is not valid for the given domain, but may be valid in a different domain. For example, United States is not a valid value for city domain but is a valid value for country domain; error suggests that the value is globally incorrect). For invalid or error value, you can optionally specify correct to value for DQS to automatically correct. Otherwise, records with invalid or error value will be flag as invalid during cleansing.
Click on Add new domain value icon. Enter a synonym value for the city (e.g. LA) then press enter. Highlight both "LA" and "Los Angeles" values, right click then select Set as Synonyms.

Note: by default, DQS include DQS_NULL as valid value for the domain, you can change the type to invalid if you would like the record with missing value to be flagged

Update the leading value by right clicking Los Angeles and select Set as Leading during cleansing.

The final city domain values should look as follows:

Select State domain and click on domain values tab. In this step, we will import the values from a spreadsheet.

Obtain the list of US states, its standard abbreviation and postal abbreviation from here. Copy the first 3 columns into excel and save as csv file.
Click the down arrow next to import values icon on the state domain management and select import valid values from excel. From the pop-up window, browse to the location of the csv file (make sure to select file type as csv) and update Use First row as header checkbox according to your file (if you do not include header when you prepare the file, then you can leave the default option unchecked.

You should see the State domain values screen populated as below - notice that when you import list of values with more than one column, DQS automatically set the first column as leading value and the subsequent column value as its synonyms.

Select CustomerName domain and click on Term-Based Relations tab. DQS allows you to define terms within your domain value and standardize them into standard term. For example, business name often include abbreviation such as "corp", etc. When there are variation for the use of the terms (e.g. one record may use abbreviation such as "Microsoft Corp" while another record may have values for "Microsoft Corporation"), you can use DQS to standardize the use of terms within your domain values. In this step, we will define 2 term based relations for our CustomerName domain:

Click Add new relation icon
Add "co" as value and "Company" as correct to. Press enter to continue.
Add "ltd" as value and "Limited" as correct to.
You should see the CompanyName Term-Based Relations screen to be populated as below. Click finish to continue.

Click Publish Knowledge Base button to continue.

Creating your own DQS Knowledge Base sometimes requires a lot of effort. For things such as address cleansing, phone number cleansing, creating your own complete list of all valid and invalid values can be a huge effort. DQS supports integration with third party service provider to cleanse your data through DQS. Refer to my blog article on how to cleanse Customer Data using Dun & Bradstreet for more information.

B. Create a DQS project to cleanse your data using the Knowledge Base

In the Data Quality Client home screen, under Data Quality Projects, click New Data Quality Project.
Name your new Project (e.g. MyCustomer Cleansing Project), make sure you select Knowledge Base created in the previous step (e.g. MyCustomerKB) then click Next to continue.
In the Map screen:

Select data source, database, and table for your data (e.g. MyCustomers table and data generated from the script at the beginning of this article)
Select columns to be cleansed and map to the domains in your knowledge base
You should have your datasource columns mapped as below. Click Next to continue.

In the Cleanse screen, click Start to begin the process. Click Next when the process finishes.
In the Manage and View results screen:

Select City domain and click Corrected tab. You should see "LA" corrected to "Los Angeles"

Select City domain and click New tab. You should see the list of city not defined in the knowledge base domain value definition listed here. Click Approve all terms icon to continue (Refer to this msdn article on how do import all approved project values into DQS knowledge base). The approved values are now appear under Correct tab.

Select CustomerName domain and click New tab. You should see Customer Names are updated to use standardized terms. Click Next to continue.

In the Manage and View results screen, you can export the results to SQL Server table or Excel.

In this blog article, I discussed how to create a Knowledge Base to cleanse customer data, including use of domain values and term based relations. You can then refer to the Knowledge Base to create Data Quality Projects to cleanse your data. You can use the same knowledge base to perform cleansing on many Data Quality Projects. You can also automate the cleansing using SQL Server 2012 Integration Services. Matt Mason wrote a nice article : Overview of DQS Transform that describes the SSIS DQS Cleansing transform.

You may also notice that there appears to be duplicate records in the sample data I used. In the next article, I will describe on how to enhance your knowledge base by adding matching policy and identify duplicate and related records in your dataset.

Cleansing your Customer Data using Dun & Bradstreet DQS service

Welly.Lee — Tue, 11 Jun 2013 16:39:00 GMT

Starting today you can use SQL Server Data Quality Service (DQS) to cleanse data using Dun & Bradstreet (D&B) extensive business information data. This is done using DQS Reference Data Service (RDS) functionality. You can subscribe to the D&B Company Cleanse & Match for Microsoft SQL Server Data Quality Service from Windows Azure Marketplace, then connect to the service from your DQS client tool or SSIS DQS Cleansing transform task to cleanse your data.

The following are the step to use the D&B for your DQS data cleansing:

A. Subscribe to D&B Reference Data Service from Windows Azure MarketPlace

B. Configure DQS to use Reference Data Service

C. Create DQS knowledge base and attach to D&B Reference Data Service

D. Cleanse Data using D&B Reference Data Service from Data Quality Client

E. Automate data cleansing using SSIS DQS Cleansing transform task

A. Subscribe to D&B Reference Data Service from Windows Azure MarketPlace

Create Windows Azure Marketplace account (if you have previously create an account with Windows Azure Marketplace, you can skip this step and login with your account)
Navigate to Data > Data Quality Services > Company Cleanse & Match for Microsoft SQL Server Data Quality Services and subscribe. For trial subscription, please contact DNB_MS_Partnership_CoreTeam@DNB.com for a promotion code.
Navigate to My Account and copy Primary Account Key information

B. Configure DQS to use Reference Data Service

Start Data Quality Client. For information about this step, refer to Run the Data Quality Client Application.
In the Data Quality Client home screen, under Administration, click Configuration.
In the Reference Data tab, under the Network Settings area, type appropriate values in the Proxy Server and Port boxes if you or your organization uses proxy server to connect to the Internet.
Specify the Marketplace primary account key (See Step A3 above) in the DataMarket Account ID box, and click the Validate DataMarket Account ID icon to validate the account key.
Click close to go back to the main menu.

Note that you may want to extend the DQS RDS timeout setting. Refer to this article for the instruction.

Reference : Configure DQS to use Reference Data (msdn)

C. Create DQS knowledge base and attach to D&B Reference Data Service

From Data Quality Client main menu, click New Knowledge Base
Specify Name for your knowledge base (e.g. DNBCleansing), make sure "Domain Management" is selected as the activity and click Next to continue
Click on create domain icon (see the screenshot below) and create the following domains (you can use default properties for this example; refer to Create a Domain for more information about creating DQS Knowledge Base domain)

CompanyName
State
Country
AddressLine (optional)
City (optional)
Zip (optional)
ZipPlus4 (optional)
DunsNumber (optional)
OrderReasonCode (required only if you are cleansing non US company)

Create a composite domain by clicking on create composite domain icon (see the screenshot below), specify name (e.g. FullCompanyInformation) and select all domains you created in Step C3. The final configuration should look like below:

Make sure the composite domain you created in step 4 is selected in the domain list and click Reference Data tab

Click Browse and from the Online Reference Data Provider Catalog pop-up window, select D&B Cleanse and Match and map the schema to your domain (from step C3 above). If you are cleansing international company (which require "OrderReasonCode") or you want to include more domains, then click Create More Schema icon (see screenshot below), select additional schema, and map to your domains. Make sure that all domains in your composite domain (step C4) are mapped to a schema. The final mapping should look like below, click OK to continue.

Click Finish publish your knowledge base.

Reference : DQS Knowledge Bases and Domains (msdn)

D. Cleanse Data using D&B Reference Data Service from Data Quality Client

Start Data Quality Client. For information about this step, refer to Run the Data Quality Client Application.
In the Data Quality Client home screen, click New data quality project
In the New Data Quality Project screen:

In the Name box, type a name for the new data quality project (e.g. New Customer Data Cleansing)
In the Use Knowledge base list, click to select a knowledge base created in Step C (e.g. DNBCleansing)
Ensure that Cleansing is selected as activity
Click Next

In the Map screen:

Select data source. For testing purpose, I used the following file I created using Excel and save the file to C:\temp\NewCustomerData.xls (if you are using x64 machine, you may need to save the file as type "Excel 97-2003 Workbook". If you are cleansing international data, you need to include a column for "order reason code" and populate the column with value of "1" (make sure to set the excel column as string data type as you can only map data with the same type as your domain data type definition).

Select the input file, specify worksheet, and map the column to the domains created in Step C3 - the final mapping should looks like below and click Next to continue.

In the Cleanse screen: click Start button, click Next to continue
In the Manage and View Results you can review the results. Refer to Cleanse Data Using Reference Data (external) knowledge for more information on reviewing results. Note that D&B also provides additional information about your data. You can review this information on the last step. For example, the below screenshot shows additional information such as address and DUNS number added to the valid company.

Reference: Cleanse Data Using Reference Data (External) Knowledge (msdn)

E. Automate data cleansing using SSIS DQS Cleansing transform task

Create a new SQL Server Integration Services project using SQL Server 2012 Data Tool
Configure your data source
Select DQS Cleansing transform task from Data Flow toolbox
Configure the DQS Cleansing transform task (for more information, refer to Overview of the DQS Cleansing Transform)

Connect to Data Quality Server which host the Knowledge Base (created in Step C).
Map the column in your data source to the Knowledge Base domain (created in Step C3)

Configure the data destination
Execute SSIS

In this blog article, I describe using SQL Server Data Quality Service (DQS) to connect to 3rd party Service Provider such as Dun & Bradstreet to cleanse customer data. Alternatively, you can also create your own Knowledge Base to cleanse data. In the next article, I describe how to create your own DQS Knowledge Base for data cleansing.

Extending timeout setting for Reference Data Services (RDS)

Welly.Lee — Wed, 10 Apr 2013 22:21:00 GMT

DQS, by default, uses connection timeout of 1 minutes when connecting to external Reference Data Services (RDS) provider. You can modify the timeout setting, using the following steps:

Run the following query on SQL Server where DQS is installed:

SELECT [ID]

,[SERVICE]

,[NAME]

,[UPDATE_TIME]

,[DESCRIPTION]

,[VALUE]

,[REVISION]

FROM [DQS_MAIN].[dbo].[A_CONFIGURATION]

where id=916

Click on the value cell (XML) and copy the XML output

Change the ConnectionTimeout parameter (default 60000 or 1 minute) and update the record (Tips: Make sure to update DQS_MAIN database before you make the update), for example:

BEFORE

update [DQS_MAIN].[dbo].[A_CONFIGURATION]

set value = '<configuration name="ReferenceData">

<param name="CacheEnabled" value="1" />

<param name="CacheCleanupIntervalMinutes" value="1440" />

<param name="CacheInvalidationTimeHours" value="24" />

<param name="CacheSizeEntries" value="1000000" />

<param name="BatchQuerySizeEntries" value="100" />

<param name="QueryMinConfidence" value="0.4" />

<param name="QueryMaxSuggestions" value="5" />

<param name="ProxyAddress" value="" />

<param name="ConnectionTimeout" value="60000" />

<param name="DataMarketCatalogEntryPoint" value="services/my/Datasets?$filter=Category%20eq%20''Data Quality Services''" />

<param name="MetadataEntryPoint" value="/$metadata" />

<param name="RetryErrorCodes" value="504" />

<param name="RetryReduceFactor" value="5" />

</configuration>'

where id=916

AFTER

update [DQS_MAIN].[dbo].[A_CONFIGURATION]

set value = '<configuration name="ReferenceData">

<param name="CacheEnabled" value="1" />

<param name="CacheCleanupIntervalMinutes" value="1440" />

<param name="CacheInvalidationTimeHours" value="24" />

<param name="CacheSizeEntries" value="1000000" />

<param name="BatchQuerySizeEntries" value="100" />

<param name="QueryMinConfidence" value="0.4" />

<param name="QueryMaxSuggestions" value="5" />

<param name="ProxyAddress" value="" />

<param name="ConnectionTimeout" value="300000" />

<param name="DataMarketCatalogEntryPoint" value="services/my/Datasets?$filter=Category%20eq%20''Data Quality Services''" />

<param name="MetadataEntryPoint" value="/$metadata" />

<param name="RetryErrorCodes" value="504" />

<param name="RetryReduceFactor" value="5" />

</configuration>'

where id=916

Note: make sure to qualify single quote(‘) for DatamarketCatalogEntryPoint with two single quote (‘’)

Restart sql server

Languages supported by DQS for Cleansing and Matching

Kumar Vivek — Thu, 14 Mar 2013 07:39:00 GMT

Data Quality Services (DQS) internally stores the domain values in a knowledge base in the Unicode format, and uses the trigram algorithm, which is language agnostic, to compare the domain values with your source data for the cleansing and matching operations. Therefore, you can practically use DQS to cleanse and match data in all the languages that are supported by the Windows operating system.

OK! But, why do you have limited set of language options while creating a string domain?

While creating a string domain in DQS, one can specify the language for the domain.

The Language drop-down list displays a limited set of languages, and this selection is only applicable for the Speller feature in DQS. The Speller feature works only for those languages that are listed in the Language drop-down list.

However, if you have values in a non-listed language (for example, Greek, Chinese, and so on), you must select Other from the Language drop-down list.

NOTE: Selecting Other from the Language drop-down list disables the Speller feature for the domain.

Does collation setting impact the cleansing/matching in DQS?

The collation setting determines the rules for comparing data in SQL Server. Although DQS stores all values in the Unicode format, the collation setting does influence the comparison rules. For example, the characters that are considered to be different in a collation setting might be considered the same in another collation setting. Therefore, you should choose a different collation setting, other than the default collation setting, while installing DQS only if you are completely aware of the collation comparison rules, and are sure about using the same comparison rules in DQS for cleansing and matching.

However, if you are not sure about the comparison rules in a collation, you must install DQS with the default server collation, and things should work fine for you. For more information about installing DQS and specifying collation settings, see here.

Thanks,
DQS Team

Upgrading DQS to SQL Server 2012 Service Pack 1 (SP1) Release

Kumar Vivek — Thu, 29 Nov 2012 08:34:00 GMT

This post provides information about the Data Quality Services (DQS) improvements in the SQL Server 2012 Service Pack 1 (SP1) release, and step-by-step instructions to upgrade your current installation of DQS to the SP1 release. The steps to upgrade from the SQL Server 2012 RTM or any of the cumulative update (CU) releases to the SP1 release are the same.

List of DQS bugs fixed in the SQL Server 2012 SP1 release

For detailed information about the DQS bugs fixed in the SP1 release, see the article at http://support.microsoft.com/kb/2674319, and search for "DQS" or "data quality".

Before You Upgrade

Upgrading DQS to the SP1 release involves upgrading the schema of DQS databases as well. Ensure that you back up your DQS databases before you upgrade to the SP1 release. Although, the DQS installer reminds you about backing up your databases during the process, it does not back up the databases as part of the process. You can manually perform a full backup of your DQS databases by running the following example script in SQL Server Management Studio:

USE MASTER;
GO
BACKUP DATABASE DQS_MAIN TO DISK='C:\mybackups\DQS_Main_fullbackup.bak';
BACKUP DATABASE DQS_PROJECTS TO DISK='C:\mybackups\DQS_Projects_fullbackup.bak';
BACKUP DATABASE DQS_STAGING_DATA TO DISK='C:\mybackups\DQS_Staging_fullbackup.bak';
GO

For more information about backing up and restoring DQS databases, see http://msdn.microsoft.com/en-us/library/hh213068(SQL.110).aspx.

How To Upgrade

Download the appropriate SQL Server 2012 SP1 installer file (32-bit or 64-bit) from http://www.microsoft.com/en-us/download/details.aspx?id=35575.
On the SQL Server computer, logon using an administrator account that is also a member of the sysadmin role in the corresponding database engine instance of SQL Server 2012 where DQS is installed.
Double-click the downloaded file to launch the update process on your SQL Server computer.
On the License Terms page, accept the terms, and click Next.
On the Select Features page, ensure that all the features for your instance are visible. If you have multiple instances, you can select an instance that you want to update. Shared Features are always updated since there is only one copy to be shared by all instances. Click Next.
The installer checks for files in use to help avoid unnecessary reboots, and warns you if an in-use file is detected so you can manually kill those applications. If you don’t kill the apps prompted during the check, the installer might prompt you for a reboot at the end of the update process.
Click Update to begin the update process. This might take a while to complete (~15-20 minutes) depending on the installed features in your SQL Server instance. After the update process completes successfully, a success message is displayed. Click Close to close the installer page.
We will now upgrade the DQS database schemas. To do so, start Command Prompt, and change your directory to the location where DQSInstaller.exe is available. If you installed the default instance of SQL Server, the DQSInstaller.exe file will be available at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn:
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
At the command prompt, type the following command, and press ENTER:
dqsinstaller.exe –upgrade

NOTE: The DQS installer checks for any pending restarts on your computer owing to any other updates that have been recently installed on your computer. If any pending restarts are found, a message appears to notify you about the same, and you can choose to continue or abort the installation by pressing Y or N respectively. We recommend that if there are any pending restarts, you must abort the DQS schema upgrade process, restart your computer, and then perform the schema upgrade by starting from step 8.
The installer prompts you to back up the DQS databases before proceeding. Also, during the database schema upgrade process, current DQS activity will be terminated, and all the DQS users will be disconnected from the Data Quality Server.

Since you have already backed up your databases, type Y or Yes, and then press ENTER to continue with the upgrade.
A completion message is displayed after the successful upgrade of the DQS databases schema.

NOTE: If Data Quality Client is installed on a different computer, you will have to run the SP1 installer file on each computer where Data Quality Client is installed to upgrade it by following steps 2-7 earlier.

Verifying the Upgrade

To verify that you have successfully upgraded your DQS installation, you can do either of the following:

Use Data Quality Client to logon to Data Quality Server, click the Help icon in the top-right corner of the Data Quality Client home screen, and then click About SQL Server Data Quality Services. The version dialog box appears that should display the version of server and client as 11.0.3000.0:

You can check the current assembly version in the DQS_MAIN and DQS_PROJECTS databases by querying the A_DB_VERSION table in each database. To do so, run the following query using SQL Server Management Studio:
SELECT * FROM DQS_MAIN.dbo.A_DB_VERSION WHERE STATUS=2;
SELECT * FROM DQS_PROJECTS.dbo.A_DB_VERSION WHERE STATUS=2;

Each upgrade step will show an entry, and the date of the upgrade. The maximum VERSION_ID and ASSEMBLY_VERSION on the latest date is the current version. In this case, it should be 1600 and 11.0.3000.0 respectively. Sample output:

ID	UPGRADE_DATE	VERSION_ID	ASSEMBLY_VERSION	USER_NAME	STATUS	ERROR
1000	2012-04-09 16:27:18.680	1200	11.0.2316.0	DOMAIN\UserName	2
1001	2012-04-09 16:27:25.083	1400	11.0.2316.0	DOMAIN\UserName	2
1002	2012-11-27 17:26:43.180	1600	11.0.3000.0	DOMAIN\UserName	2

Having trouble upgrading DQS to SP1?

Post your query on the DQS Forum or contact Microsoft Support.

Thanks,
- DQS Team

Pattern Matching in DQS Domain Rules

Kumar Vivek — Mon, 08 Oct 2012 16:43:00 GMT

This post provides you information on how to use the pattern matching rules in DQS while creating domain rules. These are the four pattern matching rules in DQS:

For a pattern matching rule:

Any letter (A…Z) can be used as a pattern for any letter; case insensitive
Any digit (0…9) can be used as a pattern for any digit
Any special character, except a letter or a digit, can be used as a pattern for itself
Brackets, [], define optional matching

Example 1:

Consider the following pattern matching rule to represent Employee Code:

This rule implies that the Employee Code will contain three parts: any three letters followed by a colon (:), which is again followed by any four digits.

In this case, following are some valid values as per the rule:

And, following are some invalid values as per the rule:

Example 2:

Consider the following pattern matching rule for an Employee Code that shows optional matching:

This rule implies that the Employee Code will contain three parts: any three letters followed by a hyphen (-), which is again followed by any four or five digits.

In this case, following are some valid values as per the rule:

And the following are some invalid values as per the rule:

Thanks,
DQS Team

Excessive database growth issue fixed in DQS in the CU2 Release for SQL Server 2012

Kumar Vivek — Tue, 03 Jul 2012 13:55:00 GMT

The Cumulative Update 2 (CU2) release for SQL Server 2012 is out, and it fixes a DQS issue where the DQS_PROJECTS database use to grow excessively during the knowledge discovery or the cleansing activity. For detailed information about this issue, see http://support.microsoft.com/kb/2685743.

You can download and install CU2 release from here: http://support.microsoft.com/kb/2703275.

Note: Before installing the CU2 release, ensure that you back up your DQS databases. For more information, see http://msdn.microsoft.com/en-us/library/hh213040.

To update your existing SQL Server installation with the CU2 release, do the following:

Visit http://support.microsoft.com/kb/2703275, and click Get Hotfix Now at the top of the page.
In the next page, select the hotfix to download, type in your email address where you want the hotfix link to be sent, and then click Request Hotfix.
Check your email for the hotfix link, and click the link to download the hotfix.
After downloding the hotfix file, double-click the file to extract the CU2 installer file to a folder on your computer.
Double-click the CU2 installer file to launch the update process on your SQL Server computer.
In the License Terms page, accept the terms, and click Next.
On the Select Features page, ensure that all the features for your instance are visible. If you have multiple instances, you can select an instance that you want to update. Shared Features are always updated since there is only one copy to be shared by all instances. Click Next.
The installer checks for files in use to help avoid unnecessary reboots, and warns you if an in-use file is detected so you can manually kill those applications. If you don’t kill the apps prompted during the check, the installer might prompt you for a reboot at the end of the update process.
Click Next to begin the update process. This might take a while to complete (~10-20 mins). After the update process completes successfully, a success message is displayed. Click Close to close the installer page.
Start Command Prompt.
At the command prompt, change your directory to the location where DQSInstaller.exe is available. If you installed the default instance of SQL Server, the DQSInstaller.exe file will be available at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn:
cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
At the command prompt, type the following command, and press ENTER:
dqsinstaller.exe -upgrade
The installer prompts you for backing up the DQS databases before proceeding. Type Y or Yes and press ENTER to continue with the upgrade.
A completion message is displayed after successful upgrade of the DQS databases schema.

Credits: Jason Howell for helping with the detailed steps and the screenshot.

- DQS Team

DQS supported in a SQL Server 2012 cluster

Kumar Vivek — Mon, 25 Jun 2012 12:00:05 GMT

The SQL Server 2012 Cumulative Update 1 (CU1) release enables support for DQS as part of a clustered SQL Server 2012 setup.

Ensure that you install the CU1 release as mentioned under the Resolution section of the following KB article to enable cluster support for DQS: http://support.microsoft.com/kb/2674817.

You can download the CU1 package from here: http://support.microsoft.com/kb/2679368.

NOTE: When you run the SQL Server 2012 setup, you are prompted for the "Scan for Product Updates" step. However, this step can find and download only Microsoft General Distribution Release (GDR) and service pack updates. The CU1 package is not listed. Therefore, you must manually download the CU1 package from the location mentioned earlier, and point the SQL Server setup to the package for the installation.

- DQS Team

DQS on the Twelve Days of SQL Server 2012

Matthew Roche [MSFT] — Wed, 18 Apr 2012 21:12:28 GMT

Call to Action: When you’re done reading this article and checking out the resources it references, please take a moment to comment and let the DQS team know what additional resources you would like provided. If there’s a topic you would like covered in a blog post or webcast or demo, we want to know about it. We can’t promise we’ll be able to create everything that’s requested, but if you don’t ask, odds are you won’t receive.

This morning the Data Quality Services team hosted as session as part of the ongoing “Twelve Days of SQL Server 2012” webcast series. Senior Program Manager Matthew Roche ran the session, while team members Howie Kroehl, Gadi Peleg, Welly Lee, Cim Ryan and Matt Masson hung out in the Q&A area answering attendee questions. We ended up having just over 200 attendees for the session, and the feedback was overwhelmingly positive. If you’re interested in viewing the recorded session on-demand, please take a look at the Twelve Days of SQL Server 2012 event web site – the recording should be online within 24 to 48 hours.

While the recording includes the session content, it does not include the questions asked (and answers provided) during the live session. For your reference, here is the Q&A transcript, slightly formatted for ease of reading:

Question: Will Microsoft ever do at source profiling?
Private Answer: Can you clarify what you mean by "at source profiling"?
Question: At source Profiling = going to the source DB2 system and looking at the data instead of moving the data to the SQL Server in chunks to profile it
Answer: You're right that profiling the data in non-SQL Server systems isn't really feasible using DQS or SSIS, directly, in SQL Server 2012; the data does need to be staged in SQL Server, first. As for the future, all we can say here is that Microsoft hasn't publicly announced capabilities along those lines.
Question: If i can get the right drivers to set up a linked server for the AS400 db i should be able to profile it correct?
Answer: Correct

Question: What is the cleansing speed?
Answer: http://blogs.msdn.com/b/dqs/archive/2012/04/17/significant-performance-enhancements-in-dqs-with-the-cumulative-update-1-cu1-release-for-sql-server-2012.aspx
Answer: The table in the blog post above describes the performance of DQS in different activities comparing the RTM version to the latest improvements in CU1 (cumulative update).

Question: Can this be integrated with SSIS so as to make it a part of my process of loading the data?
Answer: Yes, there is a DQS Cleansing Transform for use in the SSIS Data Flow.

Question: Do you have to have Windows Azure to use Data Quality Services?
Answer: No, DQS is not a cloud product in SQL Server 2012. The RDS capabilities are useful on top of the capabilities offered in the box.

Question: This is amazing !!! I love this Demo
Answer: Thanks!

Question: Can data in xml source be used as source data for DQS?
Answer: Unfortunately no, you can use Excel, CSV, SQL Table/view or linked server to connect to any other source.

Question: do the azure RDS have a fee?
Answer: Yes, you can subscribe to these services and pay according to the plan you select: <https://datamarket.azure.com/browse/Data>
Question: Are the RDS's used with no fee? Is it included in the DQS license?
Answer: Most RDS do have additional fees, although some provide cleansing free of charge. For more information, see https://datamarket.azure.com/

Question: did I hear this SSIS integration is not now in 2012?
Answer: The DQS cleansing component for SSIS is in SQL Server 2012

Question: Can you post the link to DQS online tutorial please?
Answer: http://technet.microsoft.com/en-us/sqlserver/hh780961

Question: Can I use local SQL Server data as a reference data set (i.e. a lookup table as a reference data set)?
Answer: DQS does not support lookup matching however you can import or discover your data into your domains in the knowledge base and use it to cleans your data via SSIS or project in DQS.

Question: Is MDS the tool to manage the data between the systems and DQS for cleansing and matching?
Answer: To "manage", yes. To transfer, use SSIS.

In the final slide of the session we included links to additional DQS resources online. One of them is the DQS blog (which you have already found) while the other were for the “DQS Movies” video series and the DQS forums. Here are the URLs for these resources and a few more:

DQS “How Do I?” videos: http://msdn.microsoft.com/en-us/sqlserver/hh323832.aspx
DQS Forums: http://social.msdn.microsoft.com/Forums/en-us/sqldataqualityservices/threads
DQS resources page: http://msdn.microsoft.com/en-us/sqlserver/hh780961.aspx
DQS on the TechNet Wiki: http://social.technet.microsoft.com/wiki/contents/articles/4385.data-quality-services-dqs.aspx

Please let us know what you think (both about the session and about DQS in general) and what additional resources you’d like to see us create. We can’t wait to hear from you!