_数据仓库中ETL技术的探讨与实践

　　第22卷第11期??????2005年11月????计算机应用与软件

　　ComputerApplicationsandSoftwareVol??22,No.11

　　Nov.2005

　　数据仓库中ETL技术的探讨与实践

　　王克龙??王??玲??王平立??宋??斌

　　(南京理工大学计算机科学与技术系??江苏南京210094)

　　摘??要????支持数据抽取、转换、清洗和装载的工具集对任何数据仓库工程都是一个必不可少的关键部件。本文重点探讨了ETL

　　技术以及ETL工具的选择原则,并结合具体实例详细介绍了ETL过程的设计和实现方法。关键词????数据仓库??ETL??数据抽取??数据转换??数据清洗??数据装载

　　RESEARCHONTECHNOLOGYOFETLINDATAWAREHOUSEANDITSPRACTICE

　　WangKelong??WangLing??WangPingli??SongBin

　　(DepartmentofComputerScienceandTechnology,NUST,NanjingJiangsu210094,China)

　　Abstract????Thesetoftoolsthatsupportsdataextract,transform,cleansing,andloadingisakeycomponentofthedatawarehousearchitec-ture.ThispaperdiscussesETLtechnologyandtheprincipleofselectingtheETLtoolsandintroducesthedesignandimplementationmethodofETLprocessindetai.l

　　Keywords????Datawarehouse??ETL??Dataextract??Datatransform??Datacleansing??Dataloading

　　1??引??言

　　近年来,由于计算机技术的不断普及和科技进步为大量数据的应用提供了基础,数据库技术的应用和发展为人们积累了

　　大量的日常业务数据,这些数据对于企业来说无异于一个巨大的宝库,蕴藏着大量的对企业管理决策有用的信息。但如何使这些数据转换成有用的信息,为人们生活和社会发展的各方面提供正确的决策,传统的数据库系统已无法满足需求。于是人们尝试对数据库中的数据进行再加工,形成一个综合的、面向分析的环境,以更好地支持决策分析。正是在这种需求下,一种新的信息处理技术??????数据仓库(DataWarehouse,DW)技术应运而生。

　　数据仓库作为一个集成的信息库,把数据从各个信息源中提取出来后,按照数据仓库所用的公共数据模型进行相应变换,并与仓库中现有数据集成在一起。在仓库中,由于数据模型和语法等方面的差异已经消除,数据可以被直接访问,因此查询和分析处理都很快。最重要的是,访问仓库中的数据不需要占用信息源的系统资源,访问仓库中的数据不会像直接访问信息源那样增加开销。

　　对于创建数据仓库及其后续工作,如联机分析、数据挖掘等,都需要数据的正确性、一致性、完整性和可靠性,而目前的现存操作型系统中的数据尚存在很多的问题,容易造成脏数据。

　　因此,在数据仓库构建过程中,工作量最繁重、问题最多的就是从业务数据库向数据仓库抽取、转换、集成数据的过程。数据采集(ETL)工具是用来从不同种类和形式的操作型系统中抽取、转换、集成数据,将其存储到数据仓库,并对数据的质量进行2??ETL技术介绍

　　2??1??ETL相关概念

　　ETL,即数据抽取、转换、清洗、装载的过程,是构建数据仓库最重要的步骤之一。用户从数据源抽取出所需的数据,经过数据清洗,最终按照预先定义好的数据仓库模型,将数据加载到数据仓库中去。如图1所示。

　　图1??ETL处理示意图

　　2??2??ETL步骤详细介绍

　　创建数据仓库最重要的一个步骤是将数据从各种操作型数据库系统中抽取出来,排除数据中的数据缺陷,完成一系列转换,最后将数据加载到数据仓库。这个过程非常繁杂,是整个数据仓库建设过程中工作量最大的一部分。下面将详细介绍它们的设计细节。

　　(1)数据抽取??并不是源数据库的所有细节数据对于数据仓库的主题域都是有用的,必须根据已确定主题的需要,从原有操作型数据库中抽取相关数据到数据仓库。一般在设计数据抽取时要考虑以下几个方面:

　　??源数据库和目标数据库各自的数据库格式是否一致?

　　收稿日期:2004-03-03。王克龙,硕士生,主研领域:数据库技术,

　　企业信息化。

　　??

　　第11期??????

　　王克龙等:数据仓库中ETL技术的探讨与实践??31

　　??从源数据库中要访问哪些文件和表?

　　??从源数据库中可以提取哪些字段,抽取记录的条件是什么?

　　??目标数据库中的表结构是什么?

　　(2)数据转换??数据仓库中的数据往往来自一个或多个异构的数据库系统,这些数据源之间往往存在着不一致的问题,如不一致的字段长度、不一致的赋值等。数据不一致会严重影响数据仓库的数据质量。数据转换就是处理这些不一致性的过程。

　　数据转换一般包括两个方面的内容。一方面是数据名称及格式的统一,如统一的命名、统一的数据格式、统一的计量单位等等。另一方面,数据仓库中存在着源数据库中可能不存在的数据,因此需要创建新的数据逻辑视图并进行以下的转换:

　　??把一个字段的各个部分隔成两个或多个字段。

　　??把一个记录的两个或多个字段组合成一个字段。??把来自多个记录的字段结合成一个记录。

　　(3)数据清洗??数据质量是决定信息价值的关键因素。高质量的信息导致高质量的决策,而低质量的信息将导致低劣的决策。数据仓库是分析决策的基础,所以数据仓库中数据的准确性是非常重要的。

　　传统数据库中的数据大都存在错误,这是活生生的事实。尽管数据仓库项目能把注意中心放在数据质量问题和引导未来的进一步改进上,但数据仓库开发者却并不能改变数据仓库的基础??????历史数据的质量。因此,有必要对进入数据仓库的数据进行全面检查并使他们尽可能无差错。这一过程就称作数据清洗。

　　数据清洗应该处理许多可能存在的错误类型。这些类型包括数据源中丢失数据和有错误数据,还包括两个或多个数据源里的不一致数据和冲突数据,所有这些都必须处理。有时如果系统不能自动处理,还必须借助于手工操作来完成。

　　(4)数据装载??这一步所进行的工作是运行以上的处理步骤,将数据装入到数据仓库。主要的工作包括确定数据装入的次序、载入初始数据等。

　　订单出发,各部门围绕客户的订单严格按企业规定进行运作。目前该企业已经建立起了MIS系统,并积累了大量的业务数

　　据。管理层希望通过对这些业务数据的分析,更好地了解企业的生产运作情况,帮助管理层进行决策。

　　根据以上需求建立企业数据仓库,在经过总体需求分析后,建立了数据仓库的逻辑模型和物理模型,基本确定了数据仓库中事实表和维表的结构。下面的工作就是将原MIS系统中的相关业务数据转移到数据仓库的事实表和维表中。主要包括:确定数据源、指定数据目的地以及操纵和转换从数据源到数据目的地的数据。

　　在各大厂商提出的数据仓库解决方案中都提供了ETL工具,在众多产品中,普遍认为DTS是系统最易使用、扩展性最好、编程效率最高的数据抽取工具。

　　??DTS可以自动或交互地从多个异构数据源向数据仓库装入数据。

　　??DTS支持快速的非记录的块拷贝程序向SQLServer数据库插入数据。这是目前为止将大量的数据移动到SQLServer表中最快的方法。

　　??

　　DTS基于OLEDB接口能够在关系数据源、非关系数据

　　源以及ODBC数据源之间进行转移数据。

　　??DTS支持使用VBScript或JavaScript等脚本语言创建自定义的转换脚本。也允许使用编程语言(如VisualBasic或VisualC++)编写自定义的组件,能够在转换中对数据进行各种操作。

　　??DTS同SQLServer2000结合紧密,可以自动调度导入或操作任务,也可以使用SQL代理服务来进行调度。

　　因此在本系统中选用Microsoft的DTS作为ETL工具。DTS主要的功能有导入和导出数据、变换数据和传送数据库对象。DTS允许在一个过程中完成导入、导出和变换数据。这个过程的定义可以保存在包当中。DTS包含三种类型对象:连接对象、任务对象和步骤对象。连接对象定义数据源的连接,即与转换的源和目标的连接;任务对象定义了包中的动作,例如执行SQL语句、拷贝一个表的内容或执行一段脚本;步骤对象定义任务对象的执行的顺序。

　　定义包有三种方式:使用DTS设计器(DTSDesigner)、DTS导入和导出向导、DTS编程接口。本系统采用DTS设计器定义包。包可以三种方式保存:基于COM的文件,MSSQLServer的msdb数据库,作为外部VisualBasic文件。在本系统中我们用SQL语言和VBScript脚本语言对加载过程进行编程控制,以正确完成加载任务。对所定义的包保存在msdb数据库中。

　　DTS组件在定义数据源和目的连接以后,可以在两者之间进行数据转换。这是数据转移的主要阶段。DTS既可以复制整个表和视图,又可以复制特定SQL语句返回的数据,还可以针对源和目的都是SQL数据库时,复制所有数据库对象和数据。

　　对事实表的转换任务如下:由于事实表的主键来自三个维表和原合同表,度量来自原操作型数据的合同表和合同细则表,所以用SQL查询语句,把事实表所需字段合并起来:

　　selectLPMart.dbo.timedim.时间序号,????????LPMart.dbo.customerdim.客户序号,????????LPMart.dbo.product

　　dim.产品序号,

　　????????[order].htnoas??合同编号??,

　　2??3??ETL工具选择原则

　　目前已有众多商用数据仓库产品走向市场。IBM、Oracle、Sybase、CA、NCR、SAS、Microsoft等公司已相继推出了各自的数

　　据仓库解决方案,它们的ETL工具也各有其优势和不足。在选择ETL工具时必须遵守以下原则:

　　??支持多种数据源,如DBMS、电子表格、平面文件。??支持多种平台,支持多种数据库。

　　??具有规范的数据访问接口。

　　??具有灵活的可编程性和调用外部程序的功能。??工具生成的代码必须是在开发环境中可维护的。

　　??能只抽取满足指定条件的数据和源数据的指定部分。??能在抽取过程中进行数据类型转换和字符集转换并能计算生成衍生的字段。

　　??具有直观的视图、灵活的配置,能自动调用以定期实现管理工作。

　　3??应用实例

　　某外贸公司是一个比较典型的订货型生产企业,生产并出,(页)

　　??

　　??78??????

　　计算机应用与软件2005年

　　[4]W3C.ExtensiblestylesheetLanguage(XSL)[EB/OL].http:??www.w3.

　　org/TR/2001/REC-xs-l20011015/xslspecRX.pd,fMay2002.

　　[5]SubrahmanyamAllamaraju.ProfessionalJavaServerProgrammingJ2EE

　　Edition,闻道工作室,机械工业出版社,2001,9??

　　[6]DeanHelman,Mode-lView-Controller.http:??http://www.wendangwang.com/mvc-pattern.

　　htm,lMay1998.

　　[7]SteveBurbeck-ApplicationprogramminginSmalltalk-80(TM),Howto

　　useMode-lView-Controller(MVC).http:??st-www.cs.uiuc.edu/users/smarch/st-docs/mvc.htm,l1992.

　　小类??12??。所以在此要通过ActiveX脚本对数据进行一些编程的转换才能实现。对产品小类字段的编程转换如下:

　　****************************从产品编号源列提取出产品小类目的列(VisualBasic转换脚本)****************************FunctionMain()??Dimsr,,ij

　　??Sr=DTSSource(??产品编号??)????

　　i=InStr(DTSSource(??产品编号??),??-??)j=i+1i=i+1

　　(上接第31页)

　　????????[order].dhslas??订货数量??,????????[order].jeas??金额??,

　　??????round(([order].up-LPMart.dbo.product

　　[order].dhs,l2)as??利润??

　　fromcontractinnerjoin[order]oncontract.htno=[order].htnoinnerjoinLPMart.dbo.timedimoncontract.hdate=LPMart.dbo.timeinnerjoinLPMart.dbo.customerdim

　　oncontract.gno=LPMart.dbo.customerdim.客户编号innerjoinLPMart.dbo.productdimon[order].comno=LPMart.dbo.product

　　dim.产品编号dim.合同日期

　　dim.总成本)*

　　??do????

　　??loopuntilIsNumeric(Mid(sr,,i1))

　　??DTSDestination(??产品小类??)=Mid(sr,,ji-j+2)

　　??DTSDestination(??产品小类??)=Mid(DTSSource(??产品编号??),??????????InStr(DTSSource(??产品编号??),??-??)+1,2)??Main=DTSTransformStatOKEndFunction

　　由于事实表的时间序号、产品序号、客户序号数据依据相关

　　的维表,根据关系规则,数据导入要先导入维表,然后再导入事实表,整个工作流的执行步骤如图4所示。

　　查询语句编写完成并分析有效以后,需要对目标数据库的表进行选择,在目的选项卡中选择salesfact事实表即可。最后是对应字段的映射处理,在转换选项卡中只要进行字段一对一的复制即可,如图2

　　。

　　图4??转换工作流程

　　执行这个定义好的DTS转换任务,数据将按照设定的步骤和规则导入数据仓库的维表和事实表中,从而完成了数据仓库的数据转载任务。同时还可以设置DTS包,将原操作型数据库中新增和更改过的数据定期自动地更新到数据仓库中。

　　4??结束语

　　图2??事实表??转换??选项

　　各维表的转换过程与此类似,只是在进行产品维表的转换时,由于产品维表中的产品编号、产品大类、产品小类字段都来自于原操作型数据库产品表的产品编号字段,如图3

　　。

　　数据抽取、转换、清洗和装载是创建数据仓库最重要的阶

　　段,它涉及数据仓库数据质量是否满足要求以及能否有效进行决策分析。数据仓库以传统操作型数据库为主要数据源,通过ETL工具采集数据并装入数据仓库。一个为企业决策者提供快捷、准确、全面、有价值信息的数据仓库系统必须以高效的ETL作为基础。

　　参考文献

　　[1]W.H.Inmon,Buildingthedatabridge,thetencriticalsuccessfactorsof

　　buildingadatawarehouse.DataBaseProgrammingDesign.1992(11):70~73.

　　[2]W.H.Inmon等著,王志海等译,数据仓库??第二版,北京:机械工业

　　出版社,2000??

　　[3]张宁、贾自艳、史忠植,??数据仓库中ETL技术的研究??,《计算机工

　　程与应用》,2002(24):213~216.

　　图3??产品维表??转换??选项卡

　　[4]朱焱,??浅谈数据抽取净化和转换工具??,《计算机应用》,2000(4)??[5]飞思科技产品研发中心编著,SQLServer2000OLAP服务设计与应

　　用,北京:,2002??

　　产品大类、产品小类都是从产品编号的数据值中拆分出来

　　??分为:类??AL??,产品