用dq和SSIS实现数据清洗和验证自动化，第1部分

我一直在做一些SQL Server数据质量服务(dq)的工作，为盐湖城的SQL星期六做准备(http://www.sqlsaturday.com/349/eventhome.aspx ).  我的代码演示包括使用MelissaData地址检查来验证地址. 

地址数据是许多主数据管理或数据质量项目背后的经典驱动因素之一.  把账单寄错地址是, 毕竟, 纸张和邮资的浪费，以及你不会得到报酬的保证.  很难想象一个组织不需要使用邮寄地址.  即使是纯粹的互联网或社交媒体公司——Goggle、Twitter等. -需要发送账单和跟踪员工地址.  保持地址数据的质量是每个企业都需要参与的一个持续的过程, 所以我将清理并验证在Adventure 工作s样本数据库中找到的地址. 

dq优势

dq对于清理地址数据的任务有几个优势.  使用参考数据功能，它很容易与MelissaData集成.  dq客户端对用户非常友好，因此业务规则可以由业务本身维护.  在过去，即使是最简单的维护任务也需要IT运行一些脚本.  如果有人拼错了“哈肯萨克”, “业务需要输入某种类型的服务请求，一些IT工作人员需要创建一个更新语句——纠正一个简单的拼写错误需要一个漫长的过程. 

dq在SSIS中很容易实现自动化.  只需在数据流任务和最新规则中创建dq转换, 由企业使用dq客户端创建, 是否应用于数据. 

开始

要使用dq，您需要同时安装服务器和客户端.  这是一个简单的、向导驱动的安装. 详情请参阅 http://msdn.microsoft.com/en-us/library/gg492277.aspx.  服务器已安装在数据库服务器上.  服务器安装完成后，您应该有三个新的数据库, dq_MAIN, dq_PROJECTS和dq_STAGING_DATA.   

那些喜欢细节的人可能想要打开这些，深入研究单个的对象.  对于我们其他人来说，只需确保这些数据库有一个定期的备份和维护计划.  这些数据库存储组织中所有dq工作的所有规则和设置.  在灾难发生时，大量的工作和没有正式记录的知识可能会丢失. 

为了进行地址验证，您需要注册一个Azure帐户.  一旦你有这个帐户去商店和“购买”MelissaData地址检查, 但如果你每月处理少于1000个地址，这是一项免费服务.

使用dq进行地址清理

对于这个示例，我从Adventure 工作s示例数据库中获取了25条记录.  在过去，我从未真正检查过这些数据的质量.  它只是我用来演示或学习技术的比特.  作为一个示例数据库，我希望这是非常干净的数据.  我发现了一些问题, 比如不需要的地址, 事实上, 存在, 我想了一下就知道了.  毕竟，大多数人都不希望他们的真实地址出现在如此广泛使用的示例数据库中.  我没有预料到的地名拼写错误, 然而, 这是一个比预期更好的演示源. 

仔细看一下这个示例数据，注意几个项目.  而大多数的地址是在美国或加拿大, 我故意写了个德国地址.  MelissaData地址验证只处理美国和加拿大地址，因此这个记录将无法验证.  类似地，“公司办公室，El Segundo, CA”显然是不完整的. 你还应该注意到不完整的加拿大邮政编码，以及庞鲁日镇(Pont-Rouge)的拼写错误, 魁北克- " o "和" n "被转置成" Pnot-Rouge ".

建立知识库

dq中的知识库是一组应用于干净数据的规则.  知识库的基本元素是“领域”。, 应用于特定数据元素的规则集合.  对于源表，我们将围绕address1设置域, address2, 城市, 州或省, 邮政编码和国家字段. 

这里需要强调的是，dq客户端是业务的工具, 特别是数据管理员, 应该使用, 不是工作人员.  IT knows technology; the business knows the data.  dq客户端允许企业创建规则来清理和标准化数据.

当数据管理员打开dq客户机时，他们将看到知识库管理部分, 数据质量项目和管理.  第一步是创建一个知识库，即一组规则，用于确保数据质量. 

当我们自动化dq转换时，我们将需要封装一组规则来清理数据, 临时存储清理后的数据, 然后将数据发送到MelissaData进行验证是一个单独的步骤.  自动数据验证步骤以当前状态发送数据，而不应用任何转换, 比如纠正错别字.  简单地发送我们知道是错误的数据进行验证将是一种浪费, 因此，我们的第一步将是创建一个知识库，在将数据发送到MelissaData之前清理数据.  可以考虑在洗涤前先浸泡脏衣服.  Create a new knowledge base and assign it a name; here I have named it “Clean for Melissa”.  为每个相关字段创建一个域，如下所示.

正如早期提到的, 我们知道我们在城市字段中有一个打印错误, 因此，我们可以为城市域创建域规则来纠正这个拼写错误.  数据管理员可以通过几种方式来实现这一点.  首先，管理人员可以简单地引入要清理的数据样本，并手工检查它.  一旦发现一个无效的条目，他们就会标记它，并在“Domain Values”选项卡中输入正确的值. 

或者，管理员可以使用“基于术语的关系”选项卡来设置转换. 

在此步骤中创建的知识库将在MelissaData验证数据之前清理数据.  为了使清理数据和验证数据的过程自动化, 数据管理员将需要创建第二个, 为验证分离知识库.  在dq转换步骤中，将发送数据进行验证，因为它存在于源系统中，而不应用任何更正.  因此, 我们需要先清理数据，然后再验证它, 将每组规则封装在单独的知识库中.  保存并发布生成的知识库. 

数据管理员现在创建一个单独的知识库.  这里我将其命名为“MelissaData Demo”.  如下所示, 数据管理员再次为每个实体创建域, 但也会创建复合域.  而域是规则的集合, 复合域可以看作是集合的集合.  复合域支持跨所有域应用的规则.   在这里，我创建了“AddressComposit”，并将所有单独的域添加到复合域. 

一旦将域添加到组合中, 然后，我们使用reference data标签设置MelissaData地址检查以进行验证. 

然后，数据管理员将每个单独的域关联到MelissaData RDS模式, 如下所示.

同样，保存并发布知识库.

结论

请注意，到目前为止，IT还没有参与其中.  数据管理员已经创建了一组用于持续应用于数据的规则.  每次数据管理员都会修改其中一个域并发布结果, 更新后的规则会自动应用到任何dq项目或dq转换任务为SSIS.  在过去，创建这些规则并保持它们的更新是一个永无止境的项目.  这让用户感到沮丧, 因为如果不进行代码更改和测试，他们甚至无法实现最简单的规则更改.  旧的流程，在最好的情况下，在IT资源方面是昂贵的，并且对IT工作者来说真的很无聊. 

在这个新过程中，工作以一种更符合逻辑的方式进行划分.  了解数据的人创建了数据应该遵守的规则.  这些人知道，Pnot-Rouge不是魁北克的一个城镇，而Pont-Rouge是.  在第二部分, 我将着眼于自动化这一过程的技术工作——IT专业人员的工作.