巧用SSIS实现数据的模糊对比关联分析
李晓亮(审计署武汉办)
【发布时间:2014年03月06日】
字号:【大】 【中】 【小】

利用多套数据开展对比关联分析时,关联条件是其中的关键,但由于数据来源不同,多套数据对同一内容文本的描述往往各异,有时还面临数据质量不高的挑战,而使用SQL语句时关联条件的设定无法实现模糊匹配,这种情况下审计人员往往只能人工对比,效率低下。
 SQL Server Integration Services(SSIS)是SQL Server 2005以上版本提供的数据集成解决方案平台,是一种ETL(提取、转换和加载)工具,具备统一、集成、可视的特点。在计算机数据分析实践中,SSIS中原本用于数据转换的模糊查找及相关功能,还可以用于实现模糊对比关联分析,实现方法也并不复杂,能较好解决以上难题。
典型案例:在项目审计中开展模糊关联分析
审计人员欲审查某地近年来的投资项目审批前是否都通过了环境影响评价,获取了近年来通过审批的投资项目明细信息(以下简称审批项目明细,主要字段包括项目名称、审批时间等)和项目环境影响评价明细(以下简称环评项目明细,字段同上),试图开展关联分析确定延伸审计重点。
正常情况下,使用审批项目明细表与环评项目明细表左连接即可,但作为关联条件的项目名称描述各异,且无分词规律可循,如审批项目名称“A城市圈环线高速公路C段”,环评项目名称则为“A城市圈环线高速公路B市C段”等,需要使用SSIS工具开展模糊关联分析。以下假定审批项目明细表和环评项目明细表均已导入SQL Server数据库中。
1、打开Microsoft SQL Server 2008(以该版本为例)菜单下的SQL Server Business Intelligence Development Studio:

即可进入集成开发环境首页,在首页选择创建项目,创建一个Integration Sevices项目:

点击确定后,Integration Sevices项目创建完毕,即进入SSIS的可视化编辑界面,这里已经自动创建了一个默认名为Package.dtsx的空SSIS包,后续可对其进行可视化编辑:

2、使用可视化工具编辑SSIS包,完成模糊关联分析设计。
(1)SSIS包的编辑首先要从控制流编辑开始,先在控制流中创建数据流,才能将可视化处理组件设计到数据流中。从左侧工具箱选择数据流任务组件后,向“控制流”选项卡下方编辑区域拖动来创建数据流任务:

(2)创建要被模糊关联的数据源。
双击上图编辑区域的“数据流任务”进入数据流任务设计。
从左侧工具箱拖动一个OLE DB源到数据流选项卡下的编辑区域:

双击新创建的“OLE DB源”,弹出OLE DB源编辑器界面,在界面中点击新建来创建一个OLE DB连接管理器,弹出以下对话框:

在上图界面点击新建,根据本机数据库实际情况进行设定(下图中数据库实例在本地,名为SQL2008,相应审批项目明细和环评项目明细表均在其temp数据库中)。

连续点击确定,回到OLE DB源编辑器界面,选择表为“审批项目明细表”(可理解此表为左连接中的左表),再在下图界面中点击确定完成回到主界面的数据流编辑区,将“OLE DB源”重命名为“审批项目明细”,完成数据源创建。

(3)设置审批项目与环评项目的模糊关联。
拖动工具箱下“模糊查找”至数据流编辑区,拖动“审批项目明细”数据源下方绿线与其连接:

双击“模糊查找”弹出模糊查找转换编辑器界面,在其中选择“环评项目明细”作为引用表(可理解此表为左连接中的右表):

在“列”选项卡中拖动设置模糊查找的模糊关联条件,并可选择引用表的输出字段(注意将环评引用表中的项目名称另起别名以免与审批项目名称字段名重复),完成后全部点击确定返回:

(4)设计结果数据生成目标。
SSIS本身是ETL工具,其数据处理的结果必定要输出到一定的数据流目标。拖动工具箱的数据流目标下的OLE DB目标(注意不是数据流源下的OLE DB源)至数据流编辑区,重命名为“模糊关联结果”,拖动模糊查找下的绿线与其连接:

双击“模糊关联结果”这个OLE DB目标继续设计:

在上图界面,表或视图的名称右侧点击新建来创建一张结果表,可以看到结果表建表语句已经自动生成(其中的[_Similarity]字段代表关联条件下的总体相似程度,其值为0到1,值越大相似程度越高;[_Confidence]字段是匹配的置信度;[_Similarity_项目名称]字段是关联条件中某一字段下的相似程度,此例中关联条件只有一个字段):

点击确定即回到OLE DB目标编辑器,在OLE DB目标编辑器界面左侧点击映射,可以看到模糊查找的结果将如何映射到结果表中:

点击确定完成模糊关联分析设计。
3、运行设计好的SSIS包,生成模糊对比分析的结果。
经过上述步骤,SSIS包已经设计完毕。在集成界面右方解决方案资源管理器处,右键点击Package.dstx执行包,可以看到执行的具体情况:

执行成功后,到相应数据库找到“模糊关联结果”表,即为分析结果,可用于确定延伸审计的重点项目。
小结:
本方法实现模糊对比关联分析较为便捷。这里的数据源表可视为SQL语句左连接中的左表,模糊查找中的引用表可视为左连接中的右表;而如果有更为复杂的分析需求,还可以进一步利用SSIS工具中的其他组件,如多播(复制为多份,同时与多张引用表模糊对比关联),条件性拆分(如将分析结果拆分为相似程度不同的多类结果),Excel源或目标等,读者可以自行使用SSIS的帮助。
需要指出的是,本方法的模糊查找是依赖于字串匹配的,优点是适用性广泛,但其缺点也主要有两点:一是无法实现语义上的匹配,如“沪”与“上海”无法匹配;二是对于有明确命名规则的字串,如包括省、市、县、街道等多要素的规则地址字段,将其进行文字分词再开展分析更为适宜,其效果也会更好。(李晓亮)
【关闭】    【打印】