教学管理数据仓库中ETL的实现

教学管理数据仓库中ETL的实现（精选5篇）

篇1：教学管理数据仓库中ETL的实现

龙源期刊网 http:// 教学管理数据仓库中ETL的实现

作者：占小忆

来源：《科技创新导报》2011年第16期

摘 要:ETL 工具从异构数据源抽取数据,并将数据清洗,规范化后装载到数据仓库。文章从前期的数据理解阶段入手,分别讨论了数据的抽取、清洗转换、装载等不同阶段需要考虑的设计问题及相应的解决方案。提出了以数据理解为根基,以清洗转换为中心的设计思想,并给出成绩管理模块的具体实施步骤。

关键词:ETL数据仓库数据抽取数据转换数据加载

中图分类号:TP311.13 文献标识码:A 文章编号:1674-098X(2011)06(a)-0017-02 1 ETL的具体实现

ETL具有以下两个主要特点:①数据同步;②数据的成批操作。数据仓库中的数据来源于教师、学生资料、学生考试成绩等等,其中一些数据存储在SQLServer、Foxpro等数据库中,还有一些以文本、word和excel方式存储于文件中,这些数据是异构数据,需要进一步处理后,才能加载到数据仓库中。本系统运用SQL Server2000提供的DTS(数据转换服务)工具,实现从不同的数据源中转换数据以创建数据仓库。1.1 数据抽取

源数据库的所有细节数据对于数据仓库的主题域并不是都有用的,必须根据已确定主题的需要,从原有操作型数据库中抽取相关数据到数据仓库。一般在设计数据抽取时要考虑以下几个方面:源数据库和目标数据库各自的数据库格式是否一致？从源数据库中要访问哪些文件和表？从源数据库中可以提取哪些字段,抽取记录的条件是什么？目标数据库中的表结构是什么？应当按照什么时间间隔来重复抽取表,定期更新数据仓库等？大型数据抽取工作可有专门的数据处理工具来完成。如果有少量数据格式,也可有专业人员编写抽取程序来完成数据抽取工作。

1.2 数据转换

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

(1)统一数据名称及格式。由于不同数据源数据明明及定义没有统一的标准,因此在源数据载入数据仓库之前必须对各个数据源的数据名称及格式进行统一。要处理的内容如下:大小写字母和文本全部转换为统一格式;从定点的十进制数据到浮点式二进制数据的格式数值数据均

龙源期刊网 http:// 须转换为一致类型;统一书写格式。如常见的日期格式(DD/MM/YY,MM/DD/YY,YY/MM/DD等)必须被转换为同样的形式。

(2)创建新的数据逻辑视图。数据仓库中存在着源数据库可能不存在的数据,比如学生成绩的平均分,通过人数等,因此还需要进行一下转换:把一个字段的各个部分隔成两个或多个字段;把一个记录的两个或多个字段组合成一个字段;把来自多个记录的字段结合成一个记录;增加一个新字段用来存储汇总记录;为了多维分析的方便,在导入数据时也常通过Case语句和Convert函数来进行简单的数据转换。其他设计复杂的转换需要单独编写转换函数来实现。1.3 数据清洗

数据清洗的任务实际上就是过滤不符合要求的数据,将过滤的结果交给业务主管部门,由业务单位确认应该过滤掉或是修正之后再进行抽取。不符合要求的数据主要是有以下几种:数据源中丢失数据、数据源中有错误数据、两个或多个数据源中的数据不一致或发生冲突。(1)对于数据的遗漏值和不规范值的处理,例如如生源地区,学生在网上自主录入的字段,有些学生不遵守录入规则,导致该字段出现空值和不规则值。对于这一类数据,可以利用系统的数据筛选功能将空值和不规则的值筛选出来加以手工修正。

(2)对于数据杂质和不一致的数据应视情况区别对待,不能一律删除。例如学期成绩,应当查询该生当前学期每门课程的成绩,从而来计算学期平均成绩,如果该生当前学期有部分课程成绩为空,则认定该生缺考以零值计算这些课程;如果全部课程成绩都为空的话,则认定该学生学业发生变更,直接删除这些数据。

(3)实现数据一致性,如:汇总后的学生信息表和学生成绩表中学生人数不同,即一张表中的学生记录在另一张表中没有对应学生的数据,这将对日后的数据分析产生相当大的影响。为了两张表所描述的学生统一起来,查询并删除这些记录。1.4 数据汇总

源数据库中的细节数据进入数据仓库后,还需要将这些数据在各种层次结构上进行汇总。例如,教学管理数据仓库中存储的细节数据时每个学生每门课考试的考试成绩,由于时间维为学期、学年两个层次。教师要对大面积的基础课的学生成绩作趋势分析时,可能要获取每门基础课程的每学期、每学年的各个年级,各个学生的成绩值时,就必须分别在时间维的学期、学年这两个层次结构上对细节数据进行汇总。为了提高数据仓库的查询效率,我们往往要将这些汇总数据存储到数据仓库中。根据汇总级别不同可分为轻度汇总数据和高度汇总数据。1.5 数据加载

数据加载就是将从源应用系统中抽取、转换后的数据加载到数据仓库系统中。教学管理数据仓库中,主要采用以下几种方法加载数据:(1)数据结构相匹配的SQL Server关系表,用SQL

龙源期刊网 http:// insert语句加载;(2)存储于异构数据源的数据,如FoxPro关系数据库,excel文件等,可以通过SQL Server的DTS来实现加载。(3)对需要调整的数据,经程序重整后转变为固定格式的文本文件,再导入数据仓库。(4)对少量的数据,利用手工录入。

分析数据装载进数据仓库中以后,还需要验证事实表与相关维表的引用完整性,确保所有事实表中的记录都与维表中的适当记录相关。但维表中的每条记录不一定要与事实表中的数据相关。

ETL工具选择原则

目前已有众多厂商推出数据仓库产品。IBM、Sybase、Oracle、CA、SAS、NCR、Microsoft等公司已相继推出了自己的数据仓库解决方案,它们的ETL工具也都各有其优势和不足。在选择ETL工具时我们必须遵守以下原则:可以支持多种平台,支持多种数据库;可以支持多种数据源,如 DBMS、电子表格、平面文件;具有规范的数据访问接口;工具生成的代码必须是在开发环境中可维护的;具有灵活的可编程性和调用外部程序的功能;能只抽取满足指定条件的数据和源数据的指定部分;具有直观的视图、灵活的配置,能自动调用以定期实现管理工作;能在抽取过程中进行数据类型转换和字符集转换并能计算生成衍生的字段。3 教学管理系统中ETL的实现

本教学管理系统的主题主要有三个方面:学生成绩管理、学生就业管理、教师科研管理。根据以上需求建立教学管理数据仓库,在经过总体需求分析后,建立了数据仓库的逻辑模型和物理模型,基本确定了数据仓库中事实表和维表的结构。下面的工作就是将原 MIS系统中的相关数据转移到数据仓库的事实表和维表中。主要包括:确定数据源、指定数据目的地以及操纵和转换从数据源到数据目的地的数据。现在各大厂商提出的数据仓库的解决方案中都提供了 ETL工具,在众多产品中,一致认为 DTS是系统最易使用、扩展性最好、编程效率最高的数据抽取工具。

(1)DTS可以自动或交互地从多个异构数据源向数据仓库装入数据;(2)DTS支持快速的非记录的块拷贝程序向 SQL Server数据库插入数据。这是目前为止将大量的数据移动到SQL Server表中最快的方法;(3)DTS基于OLE DB接口能够在关系数据源、非关系数据源以及ODBC数据源之间进行转移数据;(4)DTS支持使用VBScript或 JavaScript等脚本语言创建自定义的转换脚本。也允许使用编程语言(如Visual Basic或Visual C++)编写自定义的组件,能够在转换中对数据进行各种操作;(5)DTS同SQL Server 2000结合紧密,可以自动调度导入或操作任务,也可以使用SQL代理服务来进行调度。

因此在本系统中选用 Microsoft的 DTS作为ET L工具。DTS主要的功能有导入和导出数据、变换数据和传送数据库对象。

龙源期刊网 http:// DTS允许在一个过程中完成导入、导出和变换数据。这个过程的定义可以保存在包当中。DTS包含三种类型对象:连接对象、任务对象和步骤对象。连接对象定义数据源的连接,即与转换的源和目标的连接;任务对象定义了包中的动作,例如执行 SQL语句、拷贝一个表的内容或执行一段脚本;步骤对象定义任务对象的执行的顺序。定义包有三种方式:使用 DTS设计器(DTS Designer)、DTS导入和导出向导、DTS编程接口。DTS设计器定义包。包可以三种方式保存:基于COM的文件、MS SQL Server的msdb数据库、作为外部Visual Basic文件。在本系统中我们用SQL语言和VBScript脚本语言对加载过程进行编程控制,以正确完成加载任务。对所定义的包保存在msdb数据库中。DTS组件在定义数据源和目的连接以后,可以在两者之间进行数据转换。这是数据转移的主要阶段。DTS既可以复制整个表和视图,又可以复制特定SQL语句返回的数据,还可以针对源和目的都是 SQL数据库时,复制所有数据库对象和数据。对事实表的转换任务如下:由于事实表的字段全部来自原管理系统中的成绩表,只是字段名称不同,所以用SQL查询语句即可:select ts.学号,ts.教师编号as教师号,ts.课程编号as课程号,ts.成绩,ts.考试时间 from教学成绩表ts 查询语句编写完成并分析有效以后,需要对目标数据库的表进行选择,在目的选项卡中单击“创建”按钮,填入事实表名scores fact和新增字段“学期编号”即可完成后如图1 最后是对应字段的映射处理,在转换选项卡中只要进行字段一对一的复制即可(除学期编号字段外),如图2。

由于“学期编号”字段在源成绩表中只有考试时间,所以需要把考试时间按照一定的规则转换成“学期编号”,在此选择“VB Script Language”语言将源脚本替换为如下脚本。Function Main()if year(DTSSource(“考试时间”))=2006 and month(DTSSource(“考试时间”))>=9 then DTSDestination(“学期编号”)=“2006200701” end if if year(DTSSource(“考试时间”))=2007 and month(DTSSource(“考试时间”))DTSDestination(“学期编号”)=“2006200702” end if if year(DTSSource(“考试时间”))=2007 and month(DTSSource(“考试时间”))>=9 then DTSDestination(“学期编号”)=“2007200801” end if

龙源期刊网 http:// if year(DTSSource(“考试时间”))=2008 and month(DTSSource(“考试时间”))DTSDestination(“学期编号”)=“2007200802” end if if year(DTSSource(“考试时间”))=2008 and month(DTSSource(“考试时间”))>=9 then DTSDestination(“学期编号”)=“2008200901” end if if year(DTSSource(“考试时间”))=2009 and month(DTSSource(“考试时间”))DTSDestination(“学期编号”)=“2008200902” end if Main=DTSTransformStat_OK End Function 各维表的转换过程与此类似,只是在进行学期维表的转换时,由于学期维表中的“学年”“学期”字段都来自于原操作型数据库成绩表的“考试时间”字段,转换方法同“学期编号”字段通过ActiveX脚本对数据进行一些编程的转换才能实现,在此就不叙述了。

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

ETL是数据仓库开发项目的关键部分,也是一个长期的过程,同时这部分的工作直接关系数据仓库中数据的质量,从而影响到决策分析的结果和质量。在ETL过程中的每一步都会发现大量的问题,有些可以直接解决,有些则需要回溯到前一个甚至几个过程。通常情况下,每次对 ETL过程的修改都需要重新运行整个ETL过程并对结果进行验证。这样一来,开发整个ETL过程的所需的时间必然很长。因此,只有认真、仔细地设计ETL过程中的每一步,尽量使得 ETL过程每一步的运行效率更高、结果更准确同时更容易修改,才能有效保证整个项目的最终成功。

参考文献

龙源期刊网 http:// [1] W.H.I nmon,Building the data bridge,the ten critical success fact ors ofbuilding a data warehouse.DataBase Pr ogramming&Design.1992(11):70～73.[2] W.H.I nmon等著,王志海等译,数据仓库1第二版[M],北京:机械工业出版社, 20001.[3] 张宁、贾自艳、史忠植,“数据仓库中ET L技术的研究”[J],计算机工程与应用,2002(24):213～216.[4] 沙笑笑,等.DTS工具在建立数据仓库过程中的应用[J].科技创新导报,2008,10:26.

篇2：教学管理数据仓库中ETL的实现

其实ETL过程就是数据流动的过程，从不同的数据源流向不同的目标数据。但在数据仓库中，ETL有几个特点，一是数据同步，它不是一次性倒完数据就拉到，它是经常性的活动，按照固定周期运行的，甚至现在还有人提出了实时ETL的概念。二是数据量，一般都是巨大的，值得你将数据流动的过程拆分成E、T和L。

现在有很多成熟的工具提供ETL功能，例如datastage、powermart等，且不说他们的好坏。从应用角度来说，ETL的过程其实不是非常复杂，这些工具给数据仓库工程带来和很大的便利性，特别是开发的便利和维护的便利。但另一方面，开发人员容易迷失在这些工具中。举个例子，VB是一种非常简单的语言并且也是非常易用的编程工具，上手特别快，但是真正VB的高手有多少？微软设计的产品通常有个原则是“将使用者当作傻瓜”，在这个原则下，微软的东西确实非常好用，但是对于开发者，如果你自己也将自己当作傻瓜，那就真的傻了。ETL工具也是一样，这些工具为我们提供图形化界面，让我们将主要的精力放在规则上，以期提高开发效率。从使用效果来说，确实使用这些工具能够非常快速地构建一个job来处理某个数据，不过从整体来看，并不见得他的整体效率会高多少。问题主要不是出在工具上，而是在设计、开发人员上。他们迷失在工具中，没有去探求ETL的本质。

可以说这些工具应用了这么长时间，在这么多项目、环境中应用，它必然有它成功之处，它必定体现了ETL的本质。如果我们不透过表面这些工具的简单使用去看它背后蕴涵的思想，最终我们作出来的东西也就是一个个独立的job，将他们整合起来仍然有巨大的工作量。大家都知道“理论与实践相结合”，如果在一个领域有所超越，必须要在理论水平上达到一定的高度

探求ETL本质之一

ETL的过程就是数据流动的过程，从不同异构数据源流向统一的目标数据。其间，数据的抽取、清洗、转换和装载形成串行或并行的过程。ETL的核心还是在于T这个过程，也就是转换，而抽取和装载一般可以作为转换的输入和输出，或者，它们作为一个单独的部件，其复杂度没有转换部件高。和OLTP系统中不同，那里充满这单条记录的insert、update和select等操作，ETL过程一般都是批量操作，例如它的装载多采用批量装载工具，一般都是DBMS系统自身附带的工具，例如Oracle SQLLoader和DB2的autoloader等。

ETL本身有一些特点，在一些工具中都有体现，下面以datastage和powermart举例来说。

1、静态的ETL单元和动态的ETL单元实例；一次转换指明了某种格式的数据如何格式化成另一种格式的数据，对于数据源的物理形式在设计时可以不用指定，它可以在运行时，当这个ETL单元创建一个实例时才指定。对于静态和动态的ETL单元，Datastage没有严格区分，它的一个Job就是实现这个功能，在早期版本，一个Job同时不能运行两次，所以一个Job相当于一个实例，在后期版本，它支持multiple instances，而且还不是默认选项。Powermart中将这两个概念加以区分，静态的叫做Mapping，动态运行时叫做Session。

2、ETL元数据；元数据是描述数据的数据，他的含义非常广泛，这里仅指ETL的元数据。主要包括每次转换前后的数据结构和转换的规则。ETL元数据还包括形式参数的管理，形式参数的ETL单元定义的参数，相对还有实参，它是运行时指定的参数，实参不在元数据管理范围之内。

3、数据流程的控制；要有可视化的流程编辑工具，提供流程定义和流程监控功能。流程调度的最小单位是ETL单元实例，ETL单元是不能在细分的ETL过程，当然这由开发者来控制，例如可以将抽取、转换放在一个ETL单元中，那样这个抽取和转换只能同时运行，而如果将他们分作两个单元，可以分别运行，这有利于错误恢复操作。当然，ETL单元究竟应该细分到什么程度应该依据具体应用来看，目前还没有找到很好的细分策略。比如，我们可以规定将装载一个表的功能作为一个ETL单元，但是不可否认，这样的ETL单元之间会有很多共同的操作，例如两个单元共用一个Hash表，要将这个Hash表装入内存两次。

4、转换规则的定义方法；提供函数集提供常用规则方法，提供规则定义语言描述规则。

5、对数据的快速索引；一般都是利用Hash技术，将参照关系表提前装入内存，在转换时查找这个hash表。Datastage中有Hash文件技术，Powermart也有类似的Lookup功能。

探求ETL本质之二（分类）

昨在IT-Director上阅读一篇报告，关于ETL产品分类的。一般来说，我们眼中的ETL工具都是价格昂贵，能够处理海量数据的家伙，但是这是其中的一种。它可以分成4种，针对不同的需求，主要是从转换规则的复杂度和数据量大小来看。它们包括

1、交互式运行环境，你可以指定数据源、目标数据，指定规则，立马ETL。这种交互式的操作无疑非常方便，但是只能适合小数据量和复杂度不高的ETL过程，因为一旦规则复杂了，可能需要语言级的描述，不能简简单单拖拖拽拽就可以的。还有数据量的问题，这种交互式必然建立在解释型语言基础上，另外他的灵活性必然要牺牲一定的性能为代价。所以如果要处理海量数据的话，每次读取一条记录，每次对规则进行解释执行，每次在写入一条记录，这对性能影响是非常大的。

2、专门编码型的，它提供了一个基于某种语言的程序框架，你可以不必将编程精力放在一些周边的功能上，例如读文件功能、写http://rad.17luntan.com/ClickPortal/W...&alliedsiteid=0“);” onmouseout=“isShowAds = false;isShowAds2 = false;”>数据库的功能，而将精力主要放在规则的实现上面。这种近似手工代码的性能肯定是没话说，除非你的编程技巧不过关（这也是不可忽视的因素之一）。对于处理大数据量，处理复杂转换逻辑，这种方式的ETL实现是非常直观的。

3、代码生成器型的，它就像是一个ETL代码生成器，提供简单的图形化界面操作，让你拖拖拽拽将转换规则都设定好，其实他的后台都是生成基于某种语言的程序，要运行这个ETL过程，必须要编译才行。Datastage就是类似这样的产品，设计好的job必须要编译，这避免了每次转换的解释执行，但是不知道它生成的中间语言是什么。以前我设计的ETL工具大挪移其实也是归属于这一类，它提供了界面让用户编写规则，最后生成C++语言，编译后即可运行。这类工具的特点就是要在界面上下狠功夫，必须让用户轻松定义一个ETL过程，提供丰富的插件来完成读、写和转换函数。大挪移在这方面就太弱了，规则必须手写，而且要写成标准c++语法，这未免还是有点难为最终用户了，还不如做成一个专业编码型的产品呢。另外一点，这类工具必须提供面向专家应用的功能，因为它不可能考虑到所有的转换规则和所有的读写，一方面提供插件接口来让第三方编写特定的插件，另一方面还有提供特定语言来实现高级功能。例如Datastage提供一种类Basic的语言，不过他的Job的脚本化实现好像就做的不太好，只能手工绘制job，而不能编程实现Job。

4、最后还有一种类型叫做数据集线器，顾名思义，他就是像Hub一样地工作。将这种类型分出来和上面几种分类在标准上有所差异，上面三种更多指ETL实现的方法，此类主要从数据处理角度。目前有一些产品属于EAI（Enterprise Application Integration），它的数据集成主要是一种准实时性。所以这类产品就像Hub一样，不断接收各种异构数据源来的数据，经过处理，在实施发送到不同的目标数据中去。

虽然，这些类看似各又千秋，特别在BI项目中，面对海量数据的ETL时，中间两种的选择就开始了，在选择过程中，必须要考虑到开发效率、维护方面、性能、学习曲线、人员技能等各方面因素，当然还有最重要也是最现实的因素就是客户的意象。

探求ETL本质之三（转换）

ETL探求之一中提到，ETL过程最复杂的部分就是T，这个转换过程，T过程究竟有哪些类型呢？

一、宏观输入输出

从对数据源的整个宏观处理分，看看一个ETL过程的输入输出，可以分成下面几类：

1、大小交，这种处理在数据清洗过程是常见了，例如从数据源到ODS阶段，如果数据仓库采用维度建模，而且维度基本采用代理键的话，必然存在代码到此键值的转换。如果用SQL实现，必然需要将一个大表和一堆小表都Join起来，当然如果使用ETL工具的话，一般都是先将小表读入内存中再处理。这种情况，输出数据的粒度和大表一样。

2、大大交，大表和大表之间关联也是一个重要的课题，当然其中要有一个主表，在逻辑上，应当是主表Left Join辅表。大表之间的关联存在最大的问题就是性能和稳定性，对于海量数据来说，必须有优化的方法来处理他们的关联，另外，对于大数据的处理无疑会占用太多的系统资源，出错的几率非常大，如何做到有效错误恢复也是个问题。对于这种情况，我们建议还是尽量将大表拆分成适度的稍小一点的表，形成大小交的类型。这类情况的输出数据粒度和主表一样。

3、站着进来，躺着出去。事务系统中为了提高系统灵活性和扩展性，很多信息放在代码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。大家对Decode肯定不陌生，这是进行宽表化常见的手段之一。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄表粗一些，就粗在那个代码字段上。

4、聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本身其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，聚集算法的优化仍是探究的一个课题。例如是直接使用SQL的Group by，还是先排序，在处理。

二、微观规则

从数据的转换的微观细节分，可以分成下面的几个基本类型，当然还有一些复杂的组合情况，例如先运算，在参照转换的规则，这种基于基本类型组合的情况就不在此列了。ETL的规则是依赖目标数据的，目标数据有多少字段，就有多少条规则。

1、直接映射，原来是什么就是什么，原封不动照搬过来，对这样的规则，如果数据源字段和目标字段长度或精度不符，需要特别注意看是否真的可以直接映射还是需要做一些简单运算。

2、字段运算，数据源的一个或多个字段进行数学运算得到的目标字段，这种规则一般对数值型字段而言。

3、参照转换，在转换中通常要用数据源的一个或多个字段作为Key，去一个关联数组中去搜索特定值，而且应该只能得到唯一值。这个关联数组使用Hash算法实现是比较合适也是最常见的，在整个ETL开始之前，它就装入内存，对性能提高的帮助非常大。

4、字符串处理，从数据源某个字符串字段中经常可以获取特定信息，例如身份证号。而且，经常会有数值型值以字符串形式体现。对字符串的操作通常有类型转换、字符串截取等。但是由于字符类型字段的随意性也造成了脏数据的隐患，所以在处理这种规则的时候，一定要加上异常处理。

5、空值判断，对于空值的处理是数据仓库中一个常见问题，是将它作为脏数据还是作为特定一种维成员？这恐怕还要看应用的情况，也是需要进一步探求的。但是无论怎样，对于可能有NULL值的字段，不要采用“直接映射”的规则类型，必须对空值进行判断，目前我们的建议是将它转换成特定的值。

6、日期转换，在数据仓库中日期值一般都会有特定的，不同于日期类型值的表示方法，例如使用8位整型20040801表示日期。而在数据源中，这种字段基本都是日期类型的，所以对于这样的规则，需要一些共通函数来处理将日期转换为8位日期值、6位月份值等。

7、日期运算，基于日期，我们通常会计算日差、月差、时长等。一般数据库提供的日期运算函数都是基于日期型的，而在数据仓库中采用特定类型来表示日期的话，必须有一套自己的日期运算函数集。

8、聚集运算，对于事实表中的度量字段，他们通常是通过数据源一个或多个字段运用聚集函数得来的，这些聚集函数为SQL标准中，包括sum,count,avg,min,max。

9、既定取值，这种规则和以上各种类型规则的差别就在于它不依赖于数据源字段，对目标字段取一个固定的或是依赖系统的值。

探求ETL本质之四（数据质量）

“不要绝对的数据准确，但要知道为什么不准确。”

这是我们在构建BI系统是对数据准确性的要求。确实，对绝对的数据准确谁也没有把握，不仅是系统集成商，包括客户也是无法确定。准确的东西需要一个标准，但首先要保证这个标准是准确的，至少现在还没有这样一个标准。客户会提出一个相对标准，例如将你的OLAP数据结果和报表结果对比。虽然这是一种不太公平的比较，你也只好认了吧。

首先在数据源那里，已经很难保证数据质量了，这一点也是事实。在这一层有哪些可能原因导致数据质量问题？可以分为下面几类：

1、数据格式错误，例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样处理大数据量的数据源系统，他们通常会舍弃一些数据库自身的检查机制，例如字段约束等。他们尽可能将数据检查在入库前保证，但是这一点是很难确保的。这类情况诸如身份证号码、手机号、非日期类型的日期字段等。

2、数据一致性，同样，数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID，在例如有些代码在代码表中找不到等。

3、业务逻辑的合理性，这一点很难说对与错。通常，数据源系统的设计并不是非常严谨，例如让用户开户日期晚于用户销户日期都是有可能发生的，一个用户表中存在多个用户ID也是有可能发生的。对这种情况，有什么办法吗？

构建一个BI系统，要做到完全理解数据源系统根本就是不可能的。特别是数据源系统在交付后，有更多维护人员的即兴发挥，那更是要花大量的时间去寻找原因。以前曾经争辩过设计人员对规则描述的问题，有人提出要在ETL开始之前务必将所有的规则弄得一清二楚。我并不同意这样的意见，倒是认为在ETL过程要有处理这些质量有问题数据的保证。一定要正面这些脏数据，是丢弃还是处理，无法逃避。如果没有质量保证，那么在这个过程中，错误会逐渐放大，抛开数据源质量问题，我们再来看看ETL过程中哪些因素对数据准确性产生重大影响。

1、规则描述错误。上面提到对设计人员对数据源系统理解的不充分，导致规则理解错误，这是一方面。另一方面，是规则的描述，如果无二义性地描述规则也是要探求的一个课题。规则是依附于目标字段的，在探求之三中，提到规则的分类。但是规则总不能总是用文字描述，必须有严格的数学表达方式。我甚至想过，如果设计人员能够使用某种规则语言来描述，那么我们的ETL单元就可以自动生成、同步，省去很多手工操作了。

2、ETL开发错误。即时规则很明确，ETL开发的过程中也会发生一些错误，例如逻辑错误、书写错误等。例如对于一个分段值，开区间闭区间是需要指定的，但是常常开发人员没注意，一个大于等于号写成大于号就导致数据错误。

3、人为处理错误。在整体ETL流程没有完成之前，为了图省事，通常会手工运行ETL过程，这其中一个重大的问题就是你不会按照正常流程去运行了，而是按照自己的理解去运行，发生的错误可能是误删了数据、重复装载数据等。

探求ETL本质之五（质量保证）

上回提到ETL数据质量问题，这是无法根治的，只能采取特定的手段去尽量避免，而且必须要定义出度量方法来衡量数据的质量是好还是坏。对于数据源的质量，客户对此应该更加关心，如果在这个源头不能保证比较干净的数据，那么后面的分析功能的可信度也都成问题。数据源系统也在不断进化过程中，客户的操作也在逐渐规范中，BI系统也同样如此。本文探讨一下对数据源质量和ETL处理质量的应对方法。

如何应对数据源的质量问题？记得在onteldatastage列表中也讨论过一个话题－“-1的处理”，在数据仓库模型维表中，通常有一条-1记录，表示“未知”，这个未知含义可广了，任何可能出错的数据，NULL数据甚至是规则没有涵盖到的数据，都转成-1。这是一种处理脏数据的方法，但这也是一种掩盖事实的方法。就好像写一个函数FileOpen(filename)，返回一个错误码，当然，你可以只返回一种错误码，如-1，但这是一种不好的设计，对于调用者来说，他需要依据这个错误码进行某些判断，例如是文件不存在，还是读取权限不够，都有相应的处理逻辑。数据仓库中也是一样，所以，建议将不同的数据质量类型处理结果分别转换成不同的值，譬如，在转换后，-1表示参照不上，-2表示NULL数据等。不过这仅仅对付了上回提到的第一类错误，数据格式错误。对于数据一致性和业务逻辑合理性问题，这仍有待探求。但这里有一个原则就是“必须在数据仓库中反应数据源的质量”。

对于ETL过程中产生的质量问题，必须有保障手段。从以往的经验看，没有保障手段给实施人员带来麻烦重重。实施人员对于反复装载数据一定不会陌生，甚至是最后数据留到最后的Cube，才发现了第一步ETL其实已经错了。这个保障手段就是数据验证机制，当然，它的目的是能够在ETL过程中监控数据质量，产生报警。这个模块要将实施人员当作是最终用户，可以说他们是数据验证机制的直接收益者。

首先，必须有一个对质量的度量方法，什么是高质什么是低质，不能靠感官感觉，但这却是在没有度量方法条件下通常的做法。那经营分析系统来说，联通总部曾提出测试规范，这其实就是一种度量方法，例如指标的误差范围不能高于5%等，对系统本身来说其实必须要有这样的度量方法，先不要说这个度量方法是否科学。对于ETL数据处理质量，他的度量方法应该比联通总部测试规范定义的方法更要严格，因为他更多将BI系统看作一个黑盒子，从数据源到展现的数据误差允许一定的误差。而ETL数据处理质量度量是一种白盒的度量，要注重每一步过程。因此理论上，要求输入输出的指标应该完全一致。但是我们必须正面完全一致只是理想，对于有误差的数据，必须找到原因。

在质量度量方法的前提下，就可以建立一个数据验证框架。此框架依据总量、分量数据稽核方法，该方法在高的《数据仓库中的数据稽核技术》一文中已经指出。作为补充，下面提出几点功能上的建议：

1、提供前端。将开发实施人员当作用户，同样也要为之提供友好的用户界面。《稽核技术》一文中指出测试报告的形式，这种形式还是要依赖人为判断，在一堆数据中去找规律。到不如用OLAP的方式提供界面，不光是加上测试统计出来的指标结果，并且配合度量方法的计算。例如误差率，对于误差率为大于0的指标，就要好好查一下原因了。

2、提供框架。数据验证不是一次性工作，而是每次ETL过程中都必须做的。因此，必须有一个框架，自动化验证过程，并提供扩展手段，让实施人员能够增加验证范围。有了这样一个框架，其实它起到规范化操作的作用，开发实施人员可以将主要精力放在验证脚本的编写上，而不必过多关注验证如何融合到流程中，如何展现等工作。为此，要设计一套表，类似于DM表，每次验证结果数据都记录其中，并且自动触发多维分析的数据装载、发布等。这样，实施人员可以在每次装载，甚至在流程过程中就可以观察数据的误差率。特别是，如果数据仓库的模型能够统一起来，甚至数据验证脚本都可以确定下来，剩下的就是规范流程了。

3、规范流程。上回提到有一种ETL数据质量问题是由于人工处理导致的，其中最主要原因还是流程不规范。开发实施人员运行单独一个ETL单元是很方便的，虽然以前曾建议一个ETL单元必须是“可重入”的，这能够解决误删数据，重复装载数据问题。但要记住数据验证也是在流程当中，要让数据验证能够日常运作，就不要让实施者感觉到他的存在。总的来说，规范流程是提高实施效率的关键工作，这也是以后要继续探求的。

探求ETL本质之六（元数据漫谈）

对于元数据（Metadata）的定义到目前为止没有什么特别精彩的，这个概念非常广，一般都是这样定义，“元数据是描述数据的数据（Data about Data）”，这造成一种递归定义，就像问小强住在哪里，答，在旺财隔壁。按照这样的定义，元数据所描述的数据是什么呢？还是元数据。这样就可能有元元元...元数据。我还听说过一种对元数据，如果说数据是一抽屉档案，那么元数据就是分类标签。那它和索引有什么区别？

元数据体现是一种抽象，哲学家从古至今都在抽象这个世界，力图找到世界的本质。抽象不是一层关系，它是一种逐步由具体到一般的过程。例如我->男人->人->哺乳动物->生物这就是一个抽象过程，你要是在软件业混会发现这个例子很常见，面向对象方法就是这样一种抽象过程。它对世界中的事物、过程进行抽象，使用面向对象方法，构建一套对象模型。同样在面向对象方法中，类是对象的抽象，接口又是对类的抽象。因此，我认为可以将“元”和“抽象”换一下，叫抽象数据是不是好理解一些。常听到这样的话，“xx领导的讲话高屋建瓴，给我们后面的工作指引的清晰的方向”，这个成语“高屋建瓴”，站在10楼往下到水，居高临下，能砸死人，这是指站在一定的高度看待事物，这个一定的高度就是指他有够“元”。在设计模式中，强调要对接口编程，就是说你不要处理这类对象和那类对象的交互，而要处理这个接口和那个接口的交互，先别管他们内部是怎么干的。

元数据存在的意义也在于此，虽然上面说了一通都撤到哲学上去，但这个词必须还是要结合软件设计中看，我不知道在别的领域是不是存在Metadata这样的叫法，虽然我相信别的领域必然有类似的东东。元数据的存在就是要做到在更高抽象一层设计软件。这肯定有好处，什么灵活性啊，扩展性啊，可维护性啊，都能得到提高，而且架构清晰，只是弯弯太多，要是从下往上看，太复杂了。很早以前，我曾看过backorifice的代码，我靠，一个简单的功能，从这个类转到父类，又转到父类，很不理解，为什么一个简单的功能不在一个类的方法中实现就拉到了呢？现在想想，还真不能这样，这虽然使代码容易看懂了，但是结构确实混乱的，那他只能干现在的事，如果有什么功能扩展，这些代码就废了。

我从98年刚工作时就开始接触元数据的概念，当时叫做元数据驱动的系统架构，后来在QiDSS中也用到这个概念构建QiNavigator，但是现在觉得元数据也没啥，不就是建一堆表描述界面的元素，再利用这些数据自动生成界面吗。到了数据仓库系统中，这个概念更强了，是数据仓库中一个重要的部分。但是至今，我还是认为这个概念过于玄乎，看不到实际的东西，市面上有一些元数据管理的东西，但是从应用情况就得知，用的不多。之所以玄乎，就是因为抽象层次没有分清楚，关键就是对于元数据的分类（这种分类就是一种抽象过程）和元数据的使用。你可以将元数据抽象成0和1，但是那样对你的业务有用吗？必须还得抽象到适合的程度，最后问题还是“度”。

篇3：教学管理数据仓库中ETL的实现

发端于美国的次贷危机正在一步步演化，逐渐扩大为一场全球性的金融经济危机。同时也引发了金融行业对信贷风险的关注，随着巴塞尔II协议的出台，更加迫切要求各个金融企业有全面的风险信息化管理，从而能更好帮助管理层做出准确的决策。

而在早期的金融企业的信息化建设过程中，企业中各个部门的业务管理系统相对独立，这些业务管理系统经常是不同的软件集成服务商开发完成的，采用不同的软硬件架构平台，不同的技术实现方案、不同的数据定义规范。这样的管理系统存在有数据共享困难、数据汇总统一困难、信息趋势预测困难、企业网络硬件资源浪费等缺陷，所以整个企业急需一次大规模的数据整合，建立企业内部的数据仓库，将不同的业务管理系统进行融汇统一。

数据仓库是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non Volatile）、反映历史变化（Time Variant）的数据集合。数据仓库的主要作用从日常运营数据之中将对分析有用的数据分离出来，提供决策支持所需的信息。

但数据仓库系统的建设过程中，肯定会遇到这么一个问题，由于融合了多个业务管理系统，其系统中肯定存在有大量的脏乱数据，而造成这些脏乱数据的主要原因：

1、由于源业务管理系统自身问题而产生的数据冗余，包括无效记录、重复记录等；

2、多个源业务管理系统其业务规则变更的不统一，包括不同的计量单位、编码规则的失效等；

3、业务习惯原因，包括滥用习惯用词等。

如果不对这些脏乱数据进行处理，系统根本就不可能为决策分析系统提供任何支持。所以必须在数据仓库系统中进行数据清洗。由此，一个重要的字眼映入眼帘，这就是ETL。ETL处理贯穿了整个数据仓库逻辑架构，且其花耗的时间成本接近建造整个数据仓库的70%，我们现在用一个信贷数据仓库的逻辑架构图来分析下ETL的重要性及其所能解决的问题。

二、ETL所做的那些事

ETL是在数据集成整合过程中一个非常重要的步骤，它与工作流引擎和元数据管理体系相结合，对异构数据源的数据进行抽取（E x t r a c t）、转换清洗（T r a n s f o r m）、装载（L o a d）到数据仓库，目的是将多个源业务管理系统中的零乱、标准不统一、不规范的数据整合到目标数据仓库中，从而提供分析、预测和决策的依据。

在这个信贷数据仓库项目中主要有ODS源系统、仓库存储、应用三大模块组成。ODS源系统主要是负责对源系统数据的明显错误进行修复和过滤，之后将数据加载到仓库存储的临时数据区。在仓库存储方面，主要是根据ODS提供的数据进行汇总和分析加载，之后提供统计信息给应用区，之后通过应用区展示给使用客户。

ETL在整个数据加工过程中的重点在于“T”，即数据的清洗和转换。以下为数据清洗和转换所做的事情。

1、数据清洗

数据清洗的任务是过滤，将过滤不符合要求的数据后的结果加载到目标区域。并产生包括所有不符合要求的数据的拒绝文件，有相关客户经理进行核对，确认是否需要修复。其中不符合要求的数据主要是有冗余数据、数据缺失和错误数据三大问题。

(1) 冗余数据：冗余数据主要是业务系统长期累积的一个过程，此情况在历史拉链表中体现得特别明显，在信贷数据仓库中常见的有由于机构编码规则的不同，导致数据的不断累加。对于这一类数据只需要根据主键导出，经相关业务客户经理确认后进行删除修复。

(2) 数据缺失：这部分是由于源系统对字段不做必输要求，但这字段又是目标系统要求分析的重要指标，或者由于源业务系统某时点问题，导致的指标信息不全。对于这部分信息则要求返回给源业务系统，要求客户经理进行完善后重新加载。此部分信息亦称为“补录信息”。

(3) 错误数据：这一类错误常见于不同源业务管理系统由于平台、规范等不一致而导致的数据错误。如日期格式错误、字段长度不一致、字段类型不一致、指标结果越界、或者由于不同系统的字符集问题导致的乱码等。这类程序则要求在修复程序的同时重新加载数据进行修复。

数据清洗是一个不断反复的过程，经常由于某时点的数据质量问题或程序异常而引发错误，常见的处理方式为作业清洗完成之后，立即做常规数据检核，以保证每次数据加载的正确性。

2、数据转换

数据转换的任务主要根据既定的转换规则，对不同的源业务管理系统的数据进行整合，或在仓库内部做规则数据统计。

(1) 数据整合：这个主要是提供既定业务数据转换规则，常见于不同源业务管理系统的编码统一。

(2) 汇总统计：根据不同维度进行汇总统计，以期达到不同的分析应用。

三、结论

ETL是作为构建数据仓库一个非常重要的环节，负责将异构业务数据源中的数据如关系数据、平面数据文件等抽取到临时数据层后进行清洗转换后，最终加载到目标数据仓库中；或者是直接在数据仓库内部进行的不同维度的数据汇总统计，并加载维表统计区或应用数据集市中，成为联机分析处理、数据挖掘的基础。

摘要：随着金融信息化系统的不断完善, 建立全行的数据仓库势在必行, 由于之前业务管理系统的各类规范不同, 所以如果做好数据的清洗和转换, 对构建完善的数据仓库有着非常重要的作用。

关键词：ETL,数据仓库,数据清洗,数据转换

参考文献

[1]周根贵.数据仓库与数据挖掘[M].浙江大学出版社.2004

[2]杨辉.数据挖掘及其在商业银行中的应用[J].中国金融电脑.1998

[3]王珊.数据仓库技术与联机分析处理[M].科学出版社.1998

[4]王珊, 萨师煊.数据库系统概论 (第四版) [M].高等教育出版社.2006

篇4：教学管理数据仓库中ETL的实现

关键词:ETL数据仓库数据抽取数据转换数据加载

中图分类号:TP311.13文献标识码:A文章编号:1674-098X(2011)06(a)-0017-02

1 ETL的具体实现

ETL具有以下两个主要特点:①数据同步;②数据的成批操作。数据仓库中的数据来源于教师、学生资料、学生考试成绩等等,其中一些数据存储在SQLServer、Foxpro等数据库中,还有一些以文本、word和excel方式存储于文件中,这些数据是异构数据,需要进一步处理后,才能加载到数据仓库中。本系统运用SQL Server2000提供的DTS(数据转换服务)工具,实现从不同的数据源中转换数据以创建数据仓库。

1.1 数据抽取

源数据库的所有细节数据对于数据仓库的主题域并不是都有用的,必须根据已确定主题的需要,从原有操作型数据库中抽取相关数据到数据仓库。一般在设计数据抽取时要考虑以下几个方面:源数据库和目标数据库各自的数据库格式是否一致？从源数据库中要访问哪些文件和表？从源数据库中可以提取哪些字段,抽取记录的条件是什么？目标数据库中的表结构是什么？应当按照什么时间间隔来重复抽取表,定期更新数据仓库等？大型数据抽取工作可有专门的数据处理工具来完成。如果有少量数据格式,也可有专业人员编写抽取程序来完成数据抽取工作。

1.2 数据转换

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

(1)统一数据名称及格式。由于不同数据源数据明明及定义没有统一的标准,因此在源数据载入数据仓库之前必须对各个数据源的数据名称及格式进行统一。要处理的内容如下:大小写字母和文本全部转换为统一格式;从定点的十进制数据到浮点式二进制数据的格式数值数据均须转换为一致类型;统一书写格式。如常见的日期格式(DD/MM/YY,MM/DD/YY,YY/MM/DD等)必须被转换为同样的形式。

(2)创建新的数据逻辑视图。数据仓库中存在着源数据库可能不存在的数据,比如学生成绩的平均分,通过人数等,因此还需要进行一下转换:把一个字段的各个部分隔成两个或多个字段;把一个记录的两个或多个字段组合成一个字段;把来自多个记录的字段结合成一个记录;增加一个新字段用来存储汇总记录;为了多维分析的方便,在导入数据时也常通过Case语句和Convert函数来进行简单的数据转换。其他设计复杂的转换需要单独编写转换函数来实现。

1.3 数据清洗

数据清洗的任务实际上就是过滤不符合要求的数据,将过滤的结果交给业务主管部门,由业务单位确认应该过滤掉或是修正之后再进行抽取。不符合要求的数据主要是有以下几种:数据源中丢失数据、数据源中有错误数据、两个或多个数据源中的数据不一致或发生冲突。

(1)对于数据的遗漏值和不规范值的处理,例如如生源地区,学生在网上自主录入的字段,有些学生不遵守录入规则,导致该字段出现空值和不规则值。对于这一类数据,可以利用系统的数据筛选功能将空值和不规则的值筛选出来加以手工修正。

(2)對于数据杂质和不一致的数据应视情况区别对待,不能一律删除。例如学期成绩,应当查询该生当前学期每门课程的成绩,从而来计算学期平均成绩,如果该生当前学期有部分课程成绩为空,则认定该生缺考以零值计算这些课程;如果全部课程成绩都为空的话,则认定该学生学业发生变更,直接删除这些数据。

(3)实现数据一致性,如:汇总后的学生信息表和学生成绩表中学生人数不同,即一张表中的学生记录在另一张表中没有对应学生的数据,这将对日后的数据分析产生相当大的影响。为了两张表所描述的学生统一起来,查询并删除这些记录。

1.4 数据汇总

源数据库中的细节数据进入数据仓库后,还需要将这些数据在各种层次结构上进行汇总。例如,教学管理数据仓库中存储的细节数据时每个学生每门课考试的考试成绩,由于时间维为学期、学年两个层次。教师要对大面积的基础课的学生成绩作趋势分析时,可能要获取每门基础课程的每学期、每学年的各个年级,各个学生的成绩值时,就必须分别在时间维的学期、学年这两个层次结构上对细节数据进行汇总。为了提高数据仓库的查询效率,我们往往要将这些汇总数据存储到数据仓库中。根据汇总级别不同可分为轻度汇总数据和高度汇总数据。

1.5 数据加载

数据加载就是将从源应用系统中抽取、转换后的数据加载到数据仓库系统中。教学管理数据仓库中,主要采用以下几种方法加载数据:(1)数据结构相匹配的SQL Server关系表,用SQL insert语句加载;(2)存储于异构数据源的数据,如FoxPro关系数据库,excel文件等,可以通过SQL Server的DTS来实现加载。(3)对需要调整的数据,经程序重整后转变为固定格式的文本文件,再导入数据仓库。(4)对少量的数据,利用手工录入。

分析数据装载进数据仓库中以后,还需要验证事实表与相关维表的引用完整性,确保所有事实表中的记录都与维表中的适当记录相关。但维表中的每条记录不一定要与事实表中的数据相关。

2 ETL工具选择原则

目前已有众多厂商推出数据仓库产品。 IBM、Sybase、 Oracle、CA、 SAS、NCR、 Microsoft等公司已相继推出了自己的数据仓库解决方案,它们的ETL工具也都各有其优势和不足。在选择ETL工具时我们必须遵守以下原则:可以支持多种平台,支持多种数据库;可以支持多种数据源,如 DBMS、电子表格、平面文件;具有规范的数据访问接口;工具生成的代码必须是在开发环境中可维护的;具有灵活的可编程性和调用外部程序的功能;能只抽取满足指定条件的数据和源数据的指定部分;具有直观的视图、灵活的配置,能自动调用以定期实现管理工作;能在抽取过程中进行数据类型转换和字符集转换并能计算生成衍生的字段。

3 教学管理系统中ETL的实现

本教学管理系统的主题主要有三个方面:学生成绩管理、学生就业管理、教师科研管理。根据以上需求建立教学管理数据仓库,在经过总体需求分析后,建立了数据仓库的逻辑模型和物理模型,基本确定了数据仓库中事实表和维表的结构。下面的工作就是将原 MIS系统中的相关数据转移到数据仓库的事实表和维表中。主要包括:确定数据源、指定数据目的地以及操纵和转换从数据源到数据目的地的数据。现在各大厂商提出的数据仓库的解决方案中都提供了 ETL工具,在众多产品中,一致认为 DTS是系统最易使用、扩展性最好、编程效率最高的数据抽取工具。

(1)DTS可以自动或交互地从多个异构数据源向数据仓库装入数据;(2)DTS支持快速的非记录的块拷贝程序向 SQL Server数据库插入数据。这是目前为止将大量的数据移动到SQL Server表中最快的方法;(3)DTS基于OLE DB接口能够在关系数据源、 非关系数据源以及ODBC数据源之间进行转移数据;(4)DTS支持使用VBScript或 JavaScript等脚本语言创建自定义的转换脚本。也允许使用编程语言(如Visual Basic或Visual C++)编写自定义的组件,能够在转换中对数据进行各种操作;(5)DTS同SQL Server 2000结合紧密,可以自动调度导入或操作任务,也可以使用SQL代理服务来进行调度。

因此在本系统中选用 Microsoft的 DTS作为ET L工具。DTS主要的功能有导入和导出数据、变换数据和传送数据库对象。

DTS允许在一个过程中完成导入、导出和变换数据。这个过程的定义可以保存在包当中。DTS包含三种类型对象:连接对象、任务对象和步骤对象。连接对象定义数据源的连接,即与转换的源和目标的连接;任务对象定义了包中的动作,例如执行 SQL语句、拷贝一个表的内容或执行一段脚本;步骤对象定义任务对象的执行的顺序。定义包有三种方式:使用 DTS设计器(DTS Designer)、DTS导入和导出向导、DTS编程接口。DTS设计器定义包。包可以三种方式保存:基于COM的文件、MS SQL Server的msdb数据库、作为外部Visual Basic文件。在本系统中我们用SQL语言和VBScript脚本语言对加载过程进行编程控制,以正确完成加载任务。对所定义的包保存在msdb数据库中。DTS组件在定义数据源和目的连接以后,可以在两者之间进行数据转换。这是数据转移的主要阶段。DTS既可以复制整个表和视图,又可以复制特定SQL语句返回的数据,还可以针对源和目的都是 SQL数据库时,复制所有数据库对象和数据。对事实表的转换任务如下:由于事实表的字段全部来自原管理系统中的成绩表,只是字段名称不同,所以用SQL查询语句即可:select ts.学号,ts.教师编号as教师号,ts.课程编号as课程号,ts.成绩,ts.考试时间

from教学成绩表ts

查询语句编写完成并分析有效以后,需要对目标数据库的表进行选择,在目的选项卡中单击“创建”按钮,填入事实表名scores fact和新增字段“学期编号”即可完成后如图1

最后是对应字段的映射处理,在转换选项卡中只要进行字段一对一的复制即可(除学期编号字段外),如图2。

由于“学期编号”字段在源成绩表中只有考试时间,所以需要把考试时间按照一定的规则转换成“学期编号”,在此选择“VB Script Language”语言将源脚本替换为如下脚本。

Function Main()

if year(DTSSource("考试时间"))=2006 and month(DTSSource("考试时间"))>=9 then

DTSDestination("学期编号") ="2006200701"

end if

if year(DTSSource("考试时间"))=2007 and month(DTSSource("考试时间"))<=7 then

DTSDestination("学期编号") ="2006200702"

end if

if year(DTSSource("考试时间"))=2007 and month(DTSSource("考试时间"))>=9 then DTSDestination("学期编号") ="2007200801"

end if

if year(DTSSource("考试时间"))=2008 and month(DTSSource("考试时间"))<=7 then

DTSDestination("学期编号") ="2007200802"

end if

if year(DTSSource("考试时间"))=2008 and month(DTSSource("考试时间"))>=9 then

DTSDestination("学期编号") ="2008200901"

end if

if year(DTSSource("考试时间"))=2009 and month(DTSSource("考试时间"))<=7 then

DTSDestination("學期编号") ="2008200902"

end if

Main=DTSTransformStat_OK

End Function

各维表的转换过程与此类似,只是在进行学期维表的转换时,由于学期维表中的“学年”“学期”字段都来自于原操作型数据库成绩表的“考试时间”字段,转换方法同“学期编号”字段通过ActiveX脚本对数据进行一些编程的转换才能实现,在此就不叙述了。

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

4 结语

ETL是数据仓库开发项目的关键部分,也是一个长期的过程,同时这部分的工作直接关系数据仓库中数据的质量,从而影响到决策分析的结果和质量。在ETL过程中的每一步都会发现大量的问题,有些可以直接解决,有些则需要回溯到前一个甚至几个过程。通常情况下,每次对 ETL过程的修改都需要重新运行整个ETL过程并对结果进行验证。这样一来,开发整个ETL过程的所需的时间必然很长。因此,只有认真、仔细地设计ETL过程中的每一步,尽量使得 ETL过程每一步的运行效率更高、结果更准确同时更容易修改,才能有效保证整个项目的最终成功。

参考文献

[1]W.H.I nmon,Building the data bridge,the ten critical success fact ors ofbuilding a data warehouse.DataBase Pr ogramming&Design.1992(11):70～73.

[2]W.H.I nmon等著,王志海等译,数据仓库1第二版[M],北京:机械工业出版社, 20001.

[3]张宁、贾自艳、史忠植,“数据仓库中ET L技术的研究”[J],计算机工程与应用,2002(24):213～216.

篇5：教学管理数据仓库中ETL的实现

1 Teradata数据仓库的ETL模型设计

1.1 结合电信行业特征, 对ETL框架进行设计

具体设计ETL模型过程中, 必须对此模型涉及的应用领域特点予以全面的了解, 结合实况构建相应的模型。切实根据电信行业ETL框架的流程特性, 首先对数据进行合理的转换, 接下来获取相应的数据, 最后等待数据成功下载, 使用这样的步骤流程, 与电信行业中实行的ETL流程结构相符。具体的设计步骤是:首先, 所有类型的业务平台的源数据实际都会按照具体的抽取规范标准来抽取, 在形成一个统一的文件格式后, 具体储存于要求的FTP服务器目录中;其次, 开启FTP调度进程, 及时有效转换储存于要求的文件传输协议 (FTP) 中的接口文件, 除此之外, 还要通过文件传输协议 (FTP) 将接口文件传送至数据的抽取、清洗、转换、装载 (ETL服务器) 规定的目录中, 此目录属于一种分发目录, 接口文件经过一番转换后会变成一体化的AVL/CHK格式。实际当接口文件进入到数据的抽取、清洗、转换、装载 (ETL服务器) 后, 这个时候系统会启动某一数据分发的调度进程, 以此准确及时的分发ETL服务器内的所有接口文件, 让其进入到其它的加载目录中, 在接口文件送至加载目录中之后, ETL Automat on会将一个装载进程全面开启, 把存于此目录中的接口文件加载到数据仓库中。这与电信行业下的ETL框架流程特征相一致。

1.2 传统行业中的ETL框架

以往中, 行业实施的ETL设计流程太过简单化。常常在一台服务器上实施全部ETL流程, 实施的顺序是:先抽取源数据, 把实际抽取的数据做数据加载, 产生临时表1, 然后将临时表1中的数据予以一番清洗, 产生临时表2, 最后把临时表2中的数据进行转换, 再加载至数据仓库中。

1.3 电信行业和传统行业ETL比较

在电信行业特的ETL框架模型的优势具体体现在:首先, 根据所有平台中需进行加载的数据有着统一格式的文件形式, 保证了调度抽取的统一性。其次, 在接口文件还没加载到数据仓库前, 将其划分成了抽取、分发、加载这几个流程步骤。由于这三个流程步骤有着各自的控制程序, 所以, 能够对整体的ETL过程一目了然, 而且还能够及时发现存在的问题并采取有效措施加以改进与准确定位。最后, 把所有的ETL机制放置于各服务器中来调度处理, 使得诸多的原本繁琐的步骤实现了流程化, 对所有中间环节均一目了然。

2 Teradata数据仓库的ETL具体实施流程

2.1 ETL Automat on的无故障处理机制

1) 抽取、转换接口文件;将各业务平台中的接口文件放置于各类FTP的服务器目录中, 各接口文件在抽取结束后, 凡是其涉及的数据信息都要通过两种不同后缀名的文件来表示, 比如, *.AVL、*.CHK。*.AVL文件对此接口文件中涉及的全部信息进行了详细的记录, 但*.CHK文件仅仅记录下了*.AVL内的数据条数和数据大小情况, 主要是检查数据信息是否是精确无误的, 这样有利于保证ETL分发加载流程循序渐进发展。在ETL Automat on机制中, 从数据源内抽取的数据具有命令的脚本Interface_Extract.Pl会把源系统服务器中满足抽取要求的接口文件提取出来, 放入文件传输协议 (FTP) 服务器目录中, 在对接口文件提取时, 会先将文件传输协议 (FTP) 服务器IP和用户名及密码与相配套的源系统服务器全部综合在一起, 然后在接口文件的基础上对配置表Interface F leconf g进行合理提取, 再把与抽取规则相符的接口文件通过二进制传输模式抽取到FTP服务器内一些匹配的目录中, 同时在抽取完所有接口文件后, 系统会及时把此接口文件最后产生的抽取结果详细的记录在一个接口文件抽取日志表, 即Interface F le Extract log中;

2) ETL Automat on机制中接口文件的分发;系统将接口分发进程全面开启, 这种进程主要职责是分发和调度实际已转换好的.AVL和.CHK的接口文件。在此进程中主要围绕接口文件分发配置表Interface F leconf g对ETL服务器中分发目录内的接口文件进行扫描, 若发现了满足于相关提取要求的接口文件, 程序会及时按照.CHK文件对此接口文件需不需要分发操作进行准确判断, 同时在数据仓库中的两张表中分别登记分发记录、分发状态。所有接口文件不管分发成功还是分发失败, 系统都会把具体的分发状态输送至分发日志In terface D spatch log中。在结束分发操作后, 会将接口文件移送至ETL服务器内的装载目录中, 进行装载。将此分发环节添入到ETL Automat on机制中的主要目的是使数据装载到数据仓库后数据具有较高的质量。具备数据分发环节后, 能够及时的获悉存在的问题, 从而采取措施及时处理。

2.2 ETL Automat on的异常处理机制

从ETL流程角度上分析, 因源系统或者ETL流程自身问题的存在, 运行中往往会引起ETL过程的中断。对于这种情况, 应做好异常处理, 此中断现象在ETL任何环节中都会发生。在检查处理整个ETL流程环节时, 应从以下几方面进行:首先, 结合ETL状态记录表的信息获悉出现问题的环节。其次, 按照具体定位的某一环节, 及时收集记录此环节的系统详细日志, 并定位找出导致此类问题发生的主要原因。

3结论

综上所述可知, 设计了一台与电信行业特点相一致的ETL模型, 此模型能够把之前较为复杂的ETL划分为诸多的较为独立的处理单元, 对ETL过程一目了然。同时, 把本来要在数据仓库中实施的所有数据操作步骤全部拆分, 通过多台服务器来相应操作, 减轻了数据仓库的压力, 推动了数据仓库的有效执行, 数据得到及时传输。

摘要：Teradata是用于世界上最大的商用企业级数据库的关系数据库管理系统。ETL主要是用于描述将数据从来源端经过萃取、转置、加载到目的端的过程, ETL在数据仓库中使用较多, 但其对象并不局限于数据仓库。本文首先对Teradata数据仓库的ETL模型设计进行了研究, 其次阐述了Teradata数据仓库的ETL具体实施流程。

关键词：Teradata数据仓库,ETL,模型设计,流程实施

参考文献