论文部分内容阅读
摘 要: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.
[4] 沙笑笑,等.DTS工具在建立数据仓库过程中的应用[J].科技创新导报,2008,10:26.
关键词: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.
[4] 沙笑笑,等.DTS工具在建立数据仓库过程中的应用[J].科技创新导报,2008,10:26.