[译] 使用Using Data Quality Services (DQS) 清理用户数据

时间:2021-10-26 02:10:46

SQL Server 2012 Data Quality Services (DQS)  允许你使用自己的知识库来清洗数据. 在本文中我会展示一个简单示例.

使用DQS清理步骤如下:

A. 建立DQS 知识库
B. 建立DQS项目然后根据知识库清理数据

在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. 建立DQS 知识库

1. 先运行SQL Server 2014 Data Quality Server Installer 安装DQS

注: DQS只能安装在企业版或者商业智能版的SQL 上

[译] 使用Using Data Quality Services (DQS) 清理用户数据

安装完毕以后引擎新增的DQS_MAIN数据库,其中新增了三个Role,如果你要让其他用户访问DQS,那么必须赋予他们其中一个Role才可以访问DQS

[译] 使用Using Data Quality Services (DQS) 清理用户数据

2.装好以后启动 Data Quality Client.

[译] 使用Using Data Quality Services (DQS) 清理用户数据

3. 点击 New Knowledge Base.  Name字段输入 MyCustomerKB, 确定 Domain Management已经被选中,然后点击 Next

[译] 使用Using Data Quality Services (DQS) 清理用户数据

4. 点击 Create Domain 图标.

[译] 使用Using Data Quality Services (DQS) 清理用户数据

5. 弹出框里面 Domain Name写 CustomerName .  还有一个可选项  Format Output to . 可以选成 Capitalize.

[译] 使用Using Data Quality Services (DQS) 清理用户数据

6. 重复相同的步骤创建 City 和 State域(暂且叫域吧,不知道官方翻译成什么) .

[译] 使用Using Data Quality Services (DQS) 清理用户数据

7. 选择 City 域, 点击 domain values 标签. 在这个界面,你可以输入所有有效值或者无效值.

        • 点击 Add new domain value 图标.输入一个正确值, 比如Los Angeles,然后回车. 
        • 点击 Add new domain value 图标.输入一个无效值, 比如United States, 点击 Type 列的下拉框,然后选择黄色感叹号(yellow triangle, 无效值)  (这里有三个类型. 正确,无效和错误; 无效 (invalid) 表示该值在本域中无效,但是可能在另外一个域中有效. 例如 United States 不是city 域的有效值,但是它是country的有效值 ; 错误(error)则表示完全不正确). 你可以指定正确的值来修正无效或者错误值. 否则这些值会在清理过程中被标记为无效 (invalid).
        • 点击 Add new domain value 图标. 输入一个同义词LA,然后按回车. 选中 "LA" 和 "Los Angeles" 右击然后选择设置成同义词 (Set as Synonyms).

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    注意:默认情况下, DQS 包含一个 DQS_NULL 有效值, 你可以把它的类型改为无效, 比如你想标记缺失值.

    接着改变主导值,右击Los Angeles并选择 Set as Leading during .

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

      • city域最终显示如下:

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

     

    8. 选择 State 域 ,点击 domain values 标签. 在这个步骤.我们用通过表格导入值.

    • http://www.stateabbreviations.us/获取米国洲的缩写列表. 拷贝前三列到excel然后保存为 csv 文件.
    • 点击import values 图标的下拉尖头,选择 import valid values from excel. ..balabala….

      [译] 使用Using Data Quality Services (DQS) 清理用户数据

        • 导入后结果如下. DQS自动把第一列作为主导值,后面的列作为同义词.  (这个DQS 一上来就让我看到Bug…我用的是SQL 2014 . 比如说有一行数据MarylandMd.MD    其中Maryland 并没有成为Md. 和MD的主导值)

      [译] 使用Using Data Quality Services (DQS) 清理用户数据

    9. 选择 CustomerName 域,然后点击Term-Based Relations 标签. DQS允许你域值里面定义术语,并使他们标准化. 例如公司名称常包括 “corp”,当出现他的变种(比如 "Microsoft Corp" 或者 "Microsoft Corporation")的时候你可以使用DQS 标准化域值中的术语,在这个步骤中,我们将定义两个术语:

        • 点击 Add new relation 图标
        • 添加”co”值,并设置正确值为"Company" 回车继续.
        • 添加 "ltd" 值,并设置正确值为"Limited" . 
        • 完成后显示如下,然后点击Finish 按钮 .

      [译] 使用Using Data Quality Services (DQS) 清理用户数据

    10. 点击 Publish 按钮 button继续.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    • 创建自己的DQS知识库有时候需要花费大量精力. 比如地址清理,电话号码清理,建立完整的有效和无效值会花费大量时间. DQS 支持第三方知识库来清理你的数据. 可以查看这篇文章 : how to cleanse Customer Data using Dun & Bradstreet .

    •  

    • B. 创建一个DQS项目,使用知识库来清理你的数据

    1. 再 Data Quality Client 首页, Data Quality Projects下面,点击 New Data Quality Project.

    2. 项目名称  MyCustomer,并且选择你之前创造的知识库.然后点击Next继续.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    3. 映射界面:

            选择数据源,数据库以及表. (我们之前创建的 MyCustomers 表)

            进行列与域的关联. 入下图,点击Next继续.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    4. 在清理界面,点击 Start 开始处理程序,完成后点击Next .

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    5.  Manage and View results 界面:

    选择 City 域并点击 Corrected 标签. 你会看大"LA" 被纠正为"Los Angeles"

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    选择 City 域并点击 New 标签. 你会发现没有知识库中定义的域值清单.点击 Approve all terms 图标继续 (细节可以看下这篇文章 msdn article ). The approved values are now appear under Correct tab.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

      选择 CustomerName 域并且点击New标签. 你会看到两个客户名已经通过标准数据更新.点击Next 继续.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    6. 在Manage and View results 界面, 你可以把结果导出到SQL Server 或 Excel.

    [译] 使用Using Data Quality Services (DQS) 清理用户数据

    在本文中,我介绍了如何建立一个知识库来清理用户数据, 包括使用域值和基本的术语关系. 然后你可以创建数据质量项目用你创建的知识库清理数据. 你可以在多个项目中使用相同的知识库. 同样的你也可以使用SSIS 自动处理. 可以参考 Matt Mason 写的文章: Overview of DQS Transform ,介绍 SSIS DQS Cleansing transform的使用方法

    你可能注意到我用的样本数据有些重复记录. 在下一篇文章,我会介绍如何使用匹配策略和重复数据检测来增强你的知识库.

     

    原文地址: http://blogs.msdn.com/b/dqs/archive/2013/06/14/cleansing-customer-data-using-data-quality-services-dqs.aspx