SQL Server 2005 数据转换服务

　　简介
　　由于拼写错误、截断、缺少或插入的标记、空字段、意外的缩略语和其他不规则问题，实际的数据是“有问题”的。 因此，在数据仓库项目中，很大一部分的时间和金钱都花费在了提取、转换和加载 （ETL） 阶段。 在 ETL 阶段，新数据被清理、标准化，并使其与现有数据一致。 在 Microsoft SQL Server？2005 中可用的模糊查找和模糊分组转换，有助于使 ETL 过程在遇到若干种在实际数据中观测到的常见错误时更易复原。 它们解决一般的匹配和分组问题，而无需特定于域的规则和脚本的专家集合。 通过为您的域自定义模糊查找和模糊分组，您可以利用数据转换服务 （Data Transformation Services，DTS） 设计器内的通用数据清理算法，并避免创建复杂的自定义规则和代码。
　　
　　模糊查找使得您能够将输入记录与引用表中的无错的、标准化的记录匹配。 匹配过程对于在输入记录中存在的错误有复原功能。模糊查找返回最相近的匹配并指出匹配的质量。 例如，由于输入数据中的录入错误或其他错误，在一次新的销售交易中输入的客户信息（名称和地址）可能与包含所有当前客户的客户引用表中的任何记录都不完全匹配。即使不存在完全匹配，模糊查找也会从客户引用表返回最佳匹配记录，并提供度量值以表明匹配质量。
　　
　　模糊分组使您能够标识一个表中的记录的组 — 在这个表中每个组都可能对应相同的实际实体。 分组对在实际数据中观测到的常见错误有复原功能，因为每组中的记录可能彼此不相同但彼此很相似。 例如，对于将一个客户引用表中描述每个实际客户的所有记录归类到一起，模糊分组是很有用的。
　　
　　模糊查找和模糊分组为复杂的、常遇到的数据清理问题提供易用的解决方案。 尽管它们与现有的诸如 soundex、基于规则的系统、基于编辑距离的系统及全文搜索等现有方法有一些联系，但是模糊查找和模糊分组有一些优势：
　　
　　模糊查找和模糊分组使用一个自定义的、考虑编辑距离（例如，“hits”与“bit”的距离为 2）、标记数、标记顺序以及相对频率的独立于域的距离函数。 结果，与全文搜索相比，模糊查找和模糊分组获得的辨别力要精细得多，因为它们捕获了更详细的数据结构。
　　
　　由于它们完全是标记驱动的，模糊查找和模糊分组不像 soundex 那样有依赖于语言的组件。
　　
　　因为它们不只使用编辑距离，模糊查找和模糊分组不容易被变换误导，而且与只使用编辑距离的方法相比，能够检测出更高级的模式。
　　
　　模糊查找和模糊分组紧密集成在 DTS 中，这使它们对 SQL Server 2005 的 ETL 任务来说易于使用，而且无需或只需很少的自定义编程。
　　
　　下面的部分提供了使用并了解模糊查找和模糊分组的分步指南，并且包括了这些转换的一些实现和性能方面的内容，这对用户来说很有用。 本文意在通过更详细地解释模糊查找和模糊分组的某些方面来补充在线书籍。 有关更多关于选项和配置参数方面的信息，请参阅在线书籍项。 这些项包括的信息有：列宽、层次结构、标记处理选项以及其他有用的参数，这些参数提供一些方法，以加入可用来提高某些方案的准确性的域知识。
　　
　　模糊查找入门
　　模糊查找可以通过使用损坏的或不完整的字符串关键字查找大型表中的数据。 例如，如果您想要按名称和地址查找客户信息，您可以使用模糊查找来查找这些信息，即使您的输入与您的引用表中所存储的记录并不完全匹配。 用于模糊查找的最简单的包是由包含一个源、一个模糊查找转换和一个目标的单个 DTS 数据流任务组成（图 1）。
　　　
　　图 1. 最简单的模糊查找包
　　
　　要构造最简单的模糊查找包：1.打开 DTS 设计器。
　　
　　2.创建一个新的 ETL 项目，添加一个新包，单击 Data Flow 选项卡，然后接受 add a data flow 项选项。
　　
　　3.在数据流图上，从 Toolbox 拖动 OLE DB 源和目标转换，然后通过使用一个模糊查找的实例连接它们。
　　
　　4.通过选择一个连接和包含有问题的数据的输入表，将 OLE DB 源指向您的新数据。 您的数据必须包含一些字符串列。
　　
　　5.双击 Fuzzy Lookup 打开自定义用户界面 （UI）。 从 Reference table name 下拉菜单选择您希望转换的连接和表，指向已经存储的引用数据。
　　
　　6.在 Columns 选项卡上，将您想要比较的项从 Available Input Columns（来自 OLE DB 源）拖动到 Available Lookup Columns（来自引用表）。 例如，您可能希望将输入中的 StreetAddress 与引用表中的 Address 相比较。
　　
　　7.为 Available Lookup Columns 中的所有项选择复选框，然后单击 OK.
　　
　　8.将 OLE DB 目标指向您可以为其编写新表的连接，然后单击 New. 接受默认创建语句，现在您已经准备好运行模糊查找了。
　　
　　9.要运行您刚刚创建的包，在“解决方案资源管理器”窗口中鼠标右击其名称，然后选择 Execute.
　　
　　DTS 设计器运行此包，并提供关于管道的详细的可视反馈。 取决于引用数据的大小，您可能会注意到在容错索引 （Error-Tolerant Index，ETI） 创建时的延迟。 ETI 是模糊查找在运行时使用的主数据结构。
　　
　　在 ETI 创建后，所有输入行都被处理然后结果被写入到目标。 通过显示由每个组件处理的行数，DTS 设计器为您提供关于管道进度的反馈。 您也可以通过鼠标右击模糊查找和 OLE DB 目标之间的连接器将一个 DataViewer 放置在管道上。 这允许您实时看到那些模糊查找与您的输入行匹配的行。 除了匹配元组，模糊查找还输出可信度和相似性百分比。 有关更多关于可信度和相似性百分比的信息，请参阅本文后面的解释结果。
　　
　　最有效地使用模糊查找
　　运行模糊查找的主要步骤是创建 ETI、执行查找和检查输出。 下列部分提供了关于这些步骤的每一步的详细信息。
　　
　　了解容错索引
　　模糊查找通过索引在引用数据和引用行 ID 中出现的标记创建 ETI. 如果您将 ETI 存储在了服务器上，您可以通过从中选择一些行来查看其内容。 每个行由一个索引标记和包含该标记的引用行 ID 序列组成。 在地址示例中，如果您的引用数据包含 13831 N.E. 8th St，ETI 将包含 13831、N、E、8th 和St. 的标记项。 以下是 ETI 如何随引用数据而增长： 在引用表中有越多的唯一标记和越多的行，ETI 中就会有越多的项和越长的列表。 有关更多关于 ETI 的大小如何随引用数据而增长的信息，请参阅本文后面的了解性能。 标记化过程是通过模糊查找自定义属性 delimiter string 控制的。 例如，如果您想要索引 N.E.，而不是 N 和 E，则请将句点从分隔符列表删除。 结果是 N.E. 作为一个单独的标记在 ETI 中显示，而且会在运行时作为一个单元被查找。 由于分隔符的全局应用，如 First.Avenue 也作为一个单独的标记显示。
　　
　　由于 ETI 的构造成本因引用数据大小的增长而变得更加昂贵，模糊查找提供一个选项，可以将 ETI 存储在服务器上，日后可以重新使用。 这个选项使您能够避免在每次运行模糊查找时都重新创建一个 ETI. 如果您的 ETI 会花费太多的时间而不能每次运行都重建，考虑创建一次而在接下来的运行中对其进行重用。 要做到这一点，在 Reference Table 选项卡上选择 Store new index，然后指定一个表名称。
　　
　　注 ETI 可能会变得相当巨大，所以规划服务器空间可能是必要的。 在最坏的情况下，ETI 可能会是引用表的索引行中的数据大小的两倍。
　　
　　如果您想要存储 ETI 但是引用数据不时地更改，您还可以启用 Maintain stored index. 这个功能在您的 ETI 上安装一个触发器，它检测对基础引用数据的修改。 只要这样的修改发生，此触发器将相应的更改传递到 ETI，从而使其保持为最新。 如果您不安装表维护，对您的引用表所做的更改将在没有警告的情况下使任何关联的 ETI 无效。
　　
　　注 表维护功能在 Beta 2 版中不可用。
　　
　　在运行时发生了什么
　　在运行时，模糊查找使用 ETI 查找其输入的最佳匹配。 在确定最佳匹配时，最重要的参数是 MinSimilarity 阀值。 您可以通过使用模糊查找UI 来设置这个自定义属性。 引用元组只有在其与输入足够相似时才会被返回。 因此，如果您设置了一个很高的相似性要求，模糊查找考虑的候选也会较少，而且结果可能是不返回任何匹配。 如果您将 MinSimilarity 设置得低，模糊查找将考虑更多的候选，而更有可能找到一个匹配，但搜索可能会用去更长的时间。
　　
　　匹配条件包括：
　　为匹配给出的引用元组而需要对输入元组做的标记或字符插入、删除、替换以及重新排序的数量。 例如，输入 122 First Lane 很可能被认为比输入 22 N.E. 1st Ln & Leary Way 更接近引用 122 First Ln.
　　
　　来自引用表的标记频率。 非常频繁的标记通常被认为几乎不会提供对匹配有用的信息。 相对稀少的标记被认为是它们在其中出现的行的特性。
　　
　　设置正确的阀值取决于您的应用程序和数据的性质。 如果您要求一个在您的输入和引用之间的相近的匹配，您应该考虑为 MinSimilarity 设置一个大值，如 0.95. 如果您在进行一个研究性的项目，您可能会对检查弱匹配与相近匹配一样感兴趣，那么您应该将 MinSimilarity 设置为一个较低的值，如 0.1. 并没有可以用于确定这个范围的固定规则，所以建议您对数据设置进行试验。 查看几次运行的输出可以供设置最优值考虑。 例如，您执行第一次运行使用的阀值为 0.1. 您观测到一个特定的输入与一个相似性为 0.2 的特定的输出匹配。 如果对于您的应用程序来说此元组过于不相似（详细信息请参阅解释结果），第二次运行您可以将 MinSimilarity 设置为 0.3，从而排除与其过于不相似的匹配。 在一个小的测