BI项目中ETL设计与思考

1、ETL即数据抽取（Extract）、转换（Transform）、装载（Load）的过程，它是构建数据仓库的重要环节。 ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。ETL是BI项目重要的一个环节。通常情况下，在BI项目中ETL会花掉整个项目的1/3的时间,ETL设计的好坏直接关接到BI项目的成败。ETL的设计分三部分：数据抽取、数据的清洗转换、数据的加载。在设计ETL的时候我们也是从这三部分出发。数据的抽取是从各个不同的数据源抽取到ODS(OperationalDataStore，操作型数据

2、存储)中这个过程也可以做一些数据的清洗和转换)，在抽取的过程中需要挑选不同的抽取方法，尽可能的提高ETL的运行效率。ETL三个部分中，花费时间最长的是“T”(Transform，清洗、转换)的部分，一般情况下这部分工作量是整个ETL的2/3。数据的加载一般在数据清洗完了之后直接写入DW(DataWarehousing，数据仓库)中去。ETL的实实现有多种方方法，常用的的有三种。一一种是借助EETL工具(如Oraclle的OWB、SQLSeerver22000的DTS、SQLSeerver22005的SSIS服务务、Infoormatiic等)实现，一种种是SQL方式实实现，另外一一种是ETLL

3、工具和SQLL相结合。前前两种方法各各有各的优缺缺点，借助工工具可以快速速的建立起EETL工程，屏屏蔽了复杂的的编码任务，提提高了速度，降降低了难度，但但是缺少灵活活性。SQLL的方法优点点是灵活，提提高ETL运行效效率，但是编编码复杂，对对技术要求比比较高。第三三种是综合了了前面二种的的优点，会极极大地提高EETL的开发发速度和效率率。一、数据的的抽取这一部分需需要在调研阶阶段做大量的的工作，首先先要搞清楚数数据是从几个个业务系统中中来,各个业务系系统的数据库库服务器运行行什么DBMMS,是否存存在手工数据据，手工数据据量有多大，是是否存在非结结构化的数据据等等，当收收集完这些信信息之后才可

4、可以进行数据据抽取的设计计。1、对于与与存放DW的数据库库系统相同的的数据源处理理方法这一类数据据源在设计上上比较容易。一一般情况下，DBMS(SQLServer、Oracle)都会提供数据库链接功能，在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select语句直接访问。2、对于与与DW数据库系系统不同的数数据源的处理理方法对于这一类类数据源，一一般情况下也也可以通过OODBC的方方式建立数据据库链接如SQLSeerver和和Oraclle之间。如如果不能建立立数据库链接接，可以有两两种方式完成成，一种是通通过工具将源源数据导出成成.txt或者者是.xlss文件，然后后再将这些

5、源源系统文件导导入到ODSS中。另外一一种方法是通通过程序接口口来完成。3、对于文文件类型数据据源(.txxt,.xlls)，可以以培训业务人人员利用数据据库工具将这这些数据导入入到指定的数数据库，然后后从指定的数数据库中抽取取。或者还可可以借助工具具实现，如SSQLSerrver20005的SSIS服务务的平面数据据源和平面目目标等组件导导入ODS中去。4、增量更更新的问题对于数据量量大的系统，必必须考虑增量量抽取。一般般情况下，业业务系统会记记录业务发生生的时间，我我们可以用来来做增量的标标志,每次抽取之之前首先判断断ODS中记录录最大的时间间，然后根据据这个时间去去业务系统取取大于这个时

6、时间所有的记记录。利用业业务系统的时时间戳，一般般情况下，业业务系统没有有或者部分有有时间戳。 二、数数据的清洗转转换一般情况下下，数据仓库库分为ODSS、DW两部分。通通常的做法是是从业务系统统到ODS做清洗洗，将脏数据据和不完整数数据过滤掉，在在从ODS到DW的过程中中转换，进行行一些业务规规则的计算和和聚合。 1、数数据清洗数据清洗的的任务是过滤滤那些不符合合要求的数据据，将过滤的的结果交给业业务主管部门门，确认是否否过滤掉还是是由业务单位位修正之后再再进行抽取。不不符合要求的的数据主要是是有不完整的的数据、错误误的数据、重重复的数据三三大类。(1)不完完整的数据：这一类数据据主要是一些

7、些应该有的信信息缺失，如如供应商的名名称、分公司司的名称、客客户的区域信信息缺失、业业务系统中主主表与明细表表不能匹配等等。对于这一一类数据过滤滤出来，按缺缺失的内容分分别写入不同同Excell文件向客户户提交，要求求在规定的时时间内补全。补补全后才写入入数据仓库。(2)错误误的数据：这这一类错误产产生的原因是是业务系统不不够健全，在在接收输入后后没有进行判判断直接写入入后台数据库库造成的，比比如数值数据据输成全角数数字字符、字字符串数据后后面有一个回回车操作、日日期格式不正正确、日期越越界等。这一一类数据也要要分类，对于于类似于全角角字符、数据据前后有不可可见字符的问问题，只能通通过写SQL

8、L语句的方式式找出来，然然后要求客户户在业务系统统修正之后抽抽取。日期格格式不正确的的或者是日期期越界的这一一类错误会导导致ETL运行失失败，这一类类错误需要去去业务系统数数据库用SQQL的方式挑挑出来，交给给业务主管部部门要求限期期修正，修正正之后再抽取取。(3)重复复的数据：对对于这一类数数据特别是维维表中会出现现这种情况将重复数数据记录的所所有字段导出出来，让客户户确认并整理理。数据清洗是是一个反复的的过程，不可可能在几天内内完成，只有有不断的发现现问题，解决决问题。对于于是否过滤，是是否修正一般般要求客户确确认，对于过过滤掉的数据据，写入Exxcel文件件或者将过滤滤数据写入数数据表，

9、在EETL开发的的初期可以每每天向业务单单位发送过滤滤数据的邮件件，促使他们们尽快地修正正错误,同时也可以以做为将来验验证数据的依依据。数据清清洗需要注意意的是不要将将有用的数据据过滤掉，对对于每个过滤滤规则认真进进行验证，并并要用户确认认。2、数据转转换数据转换的的任务主要进进行不一致的的数据转换、数数据粒度的转转换，以及一一些商务规则则的计算。(1)不一一致数据转换换：这个过程程是一个整合合的过程，将将不同业务系系统的相同类类型的数据统统一，比如同同一个供应商商在结算系统统的编码是XXX00011,而在CRM中编码码是YY00001，这样样在抽取过来来之后统一转转换成一个编编码。(2)数据

10、据粒度的转换换：业务系统统一般存储非非常明细的数数据，而数据据仓库中数据据是用来分析析的，不需要要非常明细的的数据。一般般情况下，会会将业务系统统数据按照数数据仓库粒度度进行聚合。(3)商务务规则的计算算：不同的企企业有不同的的业务规则、不不同的数据指指标，这些指指标有的时候候不是简单的的加加减减就就能完成，这这个时候需要要在ETL中将这这些数据指标标计算好了之之后存储在数数据仓库中，以以供分析使用用。 三、EETL日志、警警告发送1、ETLL日志ETL日志志分为三类。一一类是执行过过程日志，这这一部分日志志是在ETLL执行过程中中每执行一步步的记录，记记录每次运行行每一步骤的的起始时间，影影

11、响了多少行行数据，流水水账形式。一一类是错误日日志，当某个个模块出错的的时候写错误误日志，记录录每次出错的的时间、出错错的模块以及及出错的信息息等。第三类类日志是总体体日志，只记记录ETL开始时时间、结束时时间是否成功功信息。如果果使用ETLL工具,ETLL工具会自动动产生一些日日志，这一类类日志也可以以作为ETLL日志的一部部分。记录日日志的目的是是随时可以知知道ETL运行情情况，如果出出错了，可以以知道哪里出出错。2、警告发发送如果ETLL出错了，不不仅要形成EETL出错日日志，而且要要向系统管理理员发送警告告。发送警告告的方式多种种，一般常用用的就是给系系统管理员发发送邮件，并并附上出错

12、的的信息，方便便管理员排查查错误。ETL是BBI项目的关关键部分，也也是一个长期期的过程，只只有不断的发发现问题并解解决问题，才才能使ETLL运行效率更更高，为BII项目后期开开发提供准确确的数据。数据抽取、清洗洗与转换 BBI项目中ETLL设计作者: 叶江, 出处:ITTPub,责任编辑: 叶江, 2007-055-14 113:39ETL是将将业务系统的的数据经过抽抽取、清洗转转换之后加载载到数据仓库库的过程，目目的是将企业业中的分散、零零乱、标准不不统一的数据据整合到一起起，为企业的的决策提供分分析的依据 CONTROL ShockwaveFlash.ShockwaveFlash.10

13、ETL是BBI项目最重重要的一个环环节，通常情情况下ETLL会花掉整个个项目的1/3的时间，ETTL设计的好好坏直接关接接到BI项目的成成败。ETLL也是一个长长期的过程，只只有不断的发发现问题并解解决问题，才才能使ETLL运行效率更更高，为项目目后期开发提提供准确的数数据。ETL的设设计分三部分分：数据抽取取、数据的清清洗转换、数数据的加载。在在设计ETLL的时候也是是从这三部分分出发。数据据的抽取是从从各个不同的的数据源抽取取到ODS中(这个过程也也可以做一些些数据的清洗洗和转换)，在抽取的的过程中需要要挑选不同的的抽取方法，尽尽可能的提高高ETL的运行行效率。ETTL三个部分分中，花费时

14、时间最长的是是T(清洗、转转换)的部分，一一般情况下这这部分工作量量是整个ETTL的2/3。数据据的加载一般般在数据清洗洗完了之后直直接写入DWW中去。ETL的实实现有多种方方法，常用的的有三种，第第一种是借助助ETL工具如如Oraclle的OWB、SQL sserverr 20000的DTS、SQL SServerr2005的的SSIS服务务、informmatic等等实现，第二二种是SQLL方式实现，第第三种是ETTL工具和SQLL相结合。前前两种方法各各有优缺点，借借助工具可以以快速的建立立起ETL工程，屏屏蔽复杂的编编码任务，提提高速度，降降低难度，但但是欠缺灵活活性。SQLL的方法优

15、点点是灵活，提提高ETL运行效效率，但是编编码复杂，对对技术要求比比较高。第三三种是综合了了前面二种的的优点，极大大的提高ETTL的开发速速度和效率。数据的抽取取数据的抽取取需要在调研研阶段做大量量工作，首先先要搞清楚以以下几个问题题：数据是从从几个业务系系统中来?各个业务系系统的 HYPERLINK / 数据库库 HYPERLINK / 服务器运行什什么DBMSS?是否存在手手工数据，手手工数据量有有多大?是否存在非非结构化的数数据?等等类似问问题，当收集集完这些信息息之后才可以以进行数据抽抽取的设计。1、与存放放DW的数据库系统相相同的数据源源处理方法这一类数源源在设计比较较容易，一般般情

16、况下，DDBMS(包括SQLSServerr，Oraclle)都会提提供数据库链接功功能，在DWW数据库服务器器和原业务系系统之间建立立直接的链接接关系就可以以写Seleect 语句句直接访问。2、与DWW数据库系统不不同的数据源源的处理方法法。这一类数据据源一般情况况下也可以通通过ODBCC的方式建立立数据库链接接，如SQLL Servver和Oraclle之间。如如果不能建立立数据库链接，可可以有两种方方式完成，一一种是通过工工具将源数据据导出成.ttxt或者是是.xls文件件，然后再将将这些源系统统文件导入到到ODS中。另另外一种方法法通过程序接接口来完成。3、对于文文件类型数据据源(.

17、txxt,，xls)，可可以培训业务务人员利用数数据库工具将将这些数据导导入到指定的的数据库，然然后从指定的的数据库抽取。或或者可以借助助工具实现，如如SQL SSERVERR 20055 的SSIS服务务的平面数据据源和平面目目标等组件导导入ODS中去。4、增量更更新问题对于数据量量大的系统，必必须考虑增量量抽取。一般般情况，业务务系统会记录录业务发生的的时间，可以以用作增量的的标志，每次次抽取之前首首先判断ODDS中记录最最大的时间，然然后根据这个个时间去业务务系统取大于于这个时间的的所有记录。利利用业务系统统的时间戳，一一般情况下，业业务系统没有有或者部分有有时间戳。数据的清洗洗转换一般

18、情况下下， HYPERLINK /datawarehouse/tt-database-dbck/ 数据仓库库分为ODS、DW两部分，通通常的做法是是从业务系统统到ODS做清洗洗，将脏数据据和不完整数数据过滤掉，再再从ODS到DW的过程中中转换，进行行一些业务规规则的计算和和聚合。共2页。 HYPERLINK /tips/7/7332007.shtml 1 HYPERLINK /tips/7/7332007_1.shtml 2 HYPERLINK /tips/7/7332007_1.shtml :1 HYPERLINK /tips/7/7332007_2.shtml 2 HYPERLINK /t

19、ips/7/7332007_2.shtml 下一页 1、数据清清洗数据清洗的的任务是过滤滤那些不符合合要求的数据据，将过滤的的结果交给业业务主管部门门，确认是否否过滤掉还是是由业务单位位修正之后再再进行抽取。不不符合要求的的数据主要是是有不完整的的数据、错误误的数据和重重复的数据三三大类。A、不完整整的数据，其其特征是是一一些应该有的的信息缺失，如如供应商的名名称，分公司司的名称，客客户的区域信信息缺失、业业务系统中主主表与明细表表不能匹配等等。需要将这这一类数据过过滤出来，按按缺失的内容容分别写入不不同 HYPERLINK /officejqfs/ Exceel文件向客客户提交，要要求在规定

20、的的时间内补全全。补全后才才写入 HYPERLINK /datawarehouse/tt-database-dbck/ 数据仓仓库。 B、错误的的数据，产生生原因是业务务系统不够健健全，在接收收输入后没有有进行判断直直接写入后台台 HYPERLINK / 数据库造成的的，比如数值值数据输成全全角数字字符符、字符串数数据后面有一一个回车、日日期格式不正正确、日期越越界等。这一一类数据也要要分类，对于于类似于全角角字符、数据据前后有不面面见字符的问问题只能写SSQL的方式式找出来，然然后要求客户户在业务系统统修正之后抽抽取;日期格式不不正确的或者者是日期越界界的这一类错错误会导致EETL运行失失败

21、，这一类类错误需要去去业务系统数数据库用SQL的方式式挑出来，交交给业务主管管部门要求限限期修正，修修正之后再抽抽取。 C、重复的的数据，特别别是维表中比比较常见，将将重复的数据据的记录所有有字段导出来来，让客户确确认并整理。数据清洗是是一个反复的的过程，不可可能在几天内内完成，只有有不断的发现现问题，解决决问题。对于于是否过滤、是是否修正一般般要求客户确确认;对于过滤掉掉的数据，写写入Exceel文件或者者将过滤数据据写入数据表表，在ETLL开发的初期期可以每天向向业务单位发发送过滤数据据的邮件，促促使他们尽快快的修正错误误，同时也可可以作为将来来验证数据的的依据。数据据清洗需要注注意的是不

22、要要将有用的数数据过滤掉了了，对于每个个过滤规则认认真进行验证证，并要用户户确认才行。2、数据转转换数据转换的的任务主要是是进行不一致致的数据转换换、数据粒度度的转换和一一些商务规则则的计算。A、不一致致数据转换，这这个过程是一一个整合的过过程，将不同同业务系统的的相同类型的的数据统一，比比如同一个供供应商在结算算系统的编码码是XX00001，而在在 HYPERLINK / CRM中编码码是YY00001，这样样在抽取过来来之后统一转转换成一个编编码。 B、数据粒粒度的转换，业业务系统一般般 HYPERLINK / 存储非常明细细的数据，而而数据仓库中中的数据是用用来分析的，不不需要非常明明细

23、的数据，一一般情况下，会会将业务系统统数据按照数数据仓库粒度度进行聚合。 C、商务规规则的计算，不不同的企业有有不同的业务务规则，不同同的数据指标标，这些指标标有的时候不不是简单的加加加减减就能能完成，这个个时候需要在在ETL中将这这些数据指标标计算好了之之后存储在数数据仓库中，供供分析使用。ETL日志志与警告发送送1、ETLL日志，记录录日志的目的的是随时可以以知道ETLL运行情况，如如果出错了，出出错在那里。ETL日志志分为三类。第第一类是执行行过程日志，是是在ETL执行过过程中每执行行一步的记录录，记录每次次运行每一步步骤的起始时时间，影响了了多少行数据据，流水账形形式。第二类类是错误日

24、志志，当某个模模块出错的时时候需要写错错误日志，记记录每次出错错的时间，出出错的模块以以及出错的信信息等。第三三类日志是总总体日志，只只记录ETLL开始时间，结结束时间是否否成功信息。如果使用EETL工具，工工具会自动产产生一些日志志，这一类日日志也可以作作为ETL日志的的一部分。2、警告发发送ETL出错错了，不仅要要写ETL出错日日志而且要向向系统管理员员发送警告，发发送警告的方方式有多种，常常用的就是给给系统管理员员发送邮件，并并附上出错的的信息，方便便管理员排查查错误。面向数据集成的的ETL技术研研究 HYPERLINK 开发者在线 BB 更新时间:2008-09-166作者： 来源:I

25、T专家网网本文关键词： HYPERLINK /files/list-0-0-136294-1-1.htm ETL HYPERLINK /files/list-0-0-144252-1-1.htm IT技术 HYPERLINK /files/list-0-0-168525-1-1.htm 数据库 随着企业信息息化建设的发发展，巨大的的投资为企业业建立了众多多的信息系统统，以帮助企企业进行内外外部业务的处处理和管理工工作。但是随随着信息系统统的增加，各各自孤立工作作的信息系统统将会造成大大量的冗余数数据和业务人人员的重复劳劳动。企业应应用集成(EEAI，Ennterprrise AApplicca

26、tionn Inteegratiion)应运运而生。EAAI通过建立立底层数据交交换平台来联联系横贯整个个企业的异构构系统、应用用、数据源等等，完成在企企业内部的EERP、CRRM、SCMM、数据库、数数据仓库，以以及其它重要要的内部系统统之间无缝地地共享和交换换数据的需要要。数据集成成是企业应用用集成的重要要环节，企业业实现数据集集成，可以使使更多的人更更充分地使用用已有数据资资源，减少资资料收集、数数据采集等重重复劳动和相相应费用。 但是，在实实施数据集成成的过程中，由由于不同用户户提供的数据据可能来自不不同的途径，其其数据内容、数数据格式和数数据质量千差差万别，有时时甚至会遇到到数据格式

27、不不能转换或数数据转换格式式后丢失信息息等棘手问题题，严重阻碍碍了数据在各各部门和各应应用系统中的的流动与共享享。因此，如如何对数据进进行有效的集集成管理已成成为增强企业业商业竞争力力的必然选择择。ETL是是实现数据集集成的主要技技术。ETL中三三个字母分别别代表的是EExtracct、Traansforrm、Loaad，即抽取取、转换、加加载。(1)数据抽取：从源数据源源系统抽取目目的数据源系系统需要的数数据;(2)数据转换：将从源数据据源获取的数数据按照业务务需求，转换换成目的数据据源要求的形形式，并对错错误、不一致致的数据进行行清洗和加工工。(3)数数据加载：将将转换后的数数据装载到目目

28、的数据源。ETL原本本是作为构建建数据仓库的的一个环节，负负责将分布的的、异构数据据源中的数据据如关系数据据、平面数据据文件等抽取取到临时中间间层后进行清清洗、转换、集集成，最后加加载到数据仓仓库或数据集集市中，成为为联机分析处处理、数据挖挖掘的基础。现现在也越来越越多地将ETTL应用于一一般信息系统统中数据的迁迁移、交换和和同步。一个个简单的ETTL体系结构构如图1.11所示。2.ETLL中的关键技技术ETL过程程中的主要环环节就是数据据抽取、数据据转换和加工工、数据装载载。为了实现现这些功能，各各个ETL工工具一般会进进行一些功能能上的扩充，例例如工作流、调调度引擎、规规则引擎、脚脚本支持

29、、统统计信息等。2.1 数数据抽取数据抽取是是从数据源中中抽取数据的的过程。实际际应用中，数数据源较多采采用的是关系系数据库。从从数据库中抽抽取数据一般般有以下几种种方式。(1)全量量抽取全量抽取类类似于数据迁迁移或数据复复制，它将数数据源中的表表或视图的数数据原封不动动的从数据库库中抽取出来来，并转换成成自己的ETTL工具可以以识别的格式式。全量抽取取比较简单。(2)增量量抽取增量抽取只只抽取自上次次抽取以来数数据库中要抽抽取的表中新新增或修改的的数据。在EETL使用过过程中。增量量抽取较全量量抽取应用更更广。如何捕捕获变化的数数据是增量抽抽取的关键。对对捕获方法一一般有两点要要求：准确性性

30、，能够将业业务系统中的的变化数据按按一定的频率率准确地捕获获到;性能，不不能对业务系系统造成太大大的压力，影影响现有业务务。目前增量量数据抽取中中常用的捕获获变化数据的的方法有：a.触发器器：在要抽取取的表上建立立需要的触发发器，一般要要建立插入、修修改、删除三三个触发器，每每当源表中的的数据发生变变化，就被相相应的触发器器将变化的数数据写入一个个临时表，抽抽取线程从临临时表中抽取取数据，临时时表中抽取过过的数据被标标记或删除。触触发器方式的的优点是数据据抽取的性能能较高，缺点点是要求业务务表建立触发发器，对业务务系统有一定定的影响。b.时间戳戳：它是一种种基于快照比比较的变化数数据捕获方式式

31、，在源表上上增加一个时时间戳字段，系系统中更新修修改表数据的的时候，同时时修改时间戳戳字段的值。当当进行数据抽抽取时，通过过比较系统时时间与时间戳戳字段的值来来决定抽取哪哪些数据。有有的数据库的的时间戳支持持自动更新，即即表的其它字字段的数据发发生改变时，自自动更新时间间戳字段的值值。有的数据据库不支持时时间戳的自动动更新，这就就要求业务系系统在更新业业务数据时，手手工更新时间间戳字段。同同触发器方式式一样，时间间戳方式的性性能也比较好好，数据抽取取相对清楚简简单，但对业业务系统也有有很大的倾入入性(加入额额外的时间戳戳字段)，特特别是对不支支持时间戳的的自动更新的的数据库，还还要求业务系系统

32、进行额外外的更新时间间戳操作。另另外，无法捕捕获对时间戳戳以前数据的的delette和upddate操作作,在数据准准确性上受到到了一定的限限制。c.全表比比对：典型的的全表比对的的方式是采用用MD5校验验码。ETLL工具事先为为要抽取的表表建立一个结结构类似的MMD5临时表表，该临时表表记录源表主主键以及根据据所有字段的的数据计算出出来的MD55校验码。每每次进行数据据抽取时，对对源表和MDD5临时表进进行MD5校校验码的比对对，从而决定定源表中的数数据是新增、修修改还是删除除，同时更新新MD5校验验码。MD55方式的优点点是对源系统统的倾入性较较小(仅需要要建立一个MMD5临时表表)，但缺

33、点点也是显而易易见的，与触触发器和时间间戳方式中的的主动通知不不同，MD55方式是被动动的进行全表表数据的比对对，性能较差差。当表中没没有主键或唯唯一列且含有有重复记录时时，MD5方方式的准确性性较差。d.日志对对比：通过分分析数据库自自身的日志来来判断变化的的数据。Orracle的的改变数据捕捕获(CDCC，Channged DData CCapturre)技术是是这方面的代代表。CDCC 特性是在在Oraclle9i数据据库中引入的的。CDC能能够帮助你识识别从上次抽抽取之后发生生变化的数据据。利用CDDC，在对源源表进行innsert、uupdatee或 dellete等操操作的同时就就

34、可以提取数数据，并且变变化的数据被被保存在数据据库的变化表表中。这样就就可以捕获发发生变化的数数据，然后利利用数据库视视图以一种可可控的方式提提供给目标系系统。CDCC体系结构基基于发布者/订阅者模型型。发布者捕捕捉变化数据据并提供给订订阅者。订阅阅者使用从发发布者那里获获得的变化数数据。通常，CCDC系统拥拥有一个发布布者和多个订订阅者。发布布者首先需要要识别捕获变变化数据所需需的源表。然然后，它捕捉捉变化的数据据并将其保存存在特别创建建的变化表中中。它还使订订阅者能够控控制对变化数数据的访问。订订阅者需要清清楚自己感兴兴趣的是哪些些变化数据。一一个订阅者可可能不会对发发布者发布的的所有数据

35、都都感兴趣。订订阅者需要创创建一个订阅阅者视图来访访问经发布者者授权可以访访问的变化数数据。CDCC分为同步模模式和异步模模式，同步模模式实时的捕捕获变化数据据并存储到变变化表中，发发布者与订阅阅都位于同一一数据库中。异异步模式则是是基于Oraacle的流流复制技术。ETL处理理的数据源除除了关系数据据库外，还可可能是文件，例例如txt文文件、exccel文件、xxml文件等等。对文件数数据的抽取一一般是进行全全量抽取，一一次抽取前可可保存文件的的时间戳或计计算文件的MMD5校验码码，下次抽取取时进行比对对，如果相同同则可忽略本本次抽取。2.2 数数据转换和加加工从数据源中中抽取的数据据不一定

36、完全全满足目的库库的要求，例例如数据格式式的不一致、数数据输入错误误、数据不完完整等等，因因此有必要对对抽取出的数数据进行数据据转换和加工工。数据的转换换和加工可以以在ETL引引擎中进行，也也可以在数据据抽取过程中中利用关系数数据库的特性性同时进行。(1)ETTL引擎中的的数据转换和和加工ETL引擎擎中一般以组组件化的方式式实现数据转转换。常用的的数据转换组组件有字段映映射、数据过过滤、数据清清洗、数据替替换、数据计计算、数据验验证、数据加加解密、数据据合并、数据据拆分等。这这些组件如同同一条流水线线上的一道道道工序，它们们是可插拔的的，且可以任任意组装，各各组件之间通通过数据总线线共享数据。

37、有些ETLL工具还提供供了脚本支持持，使得用户户可以以一种种编程的方式式定制数据的的转换和加工工行为。(2)在数数据库中进行行数据加工关系数据库库本身已经提提供了强大的的SQL、函函数来支持数数据的加工，如如在SQL查查询语句中添添加wherre条件进行行过滤，查询询中重命名字字段名与目的的表进行映射射，subsstr函数，ccase条件件判断等等。下下面是一个SSQL查询的的例子。select ID ass USERRID, ssubstrr(TITLLE, 1, 20) as TIITLE, case when REMARRK is null then eelse RREMARKK end

38、 as COONTENTT fromm TB_RREMARKK wherre ID 1000;相比在ETTL引擎中进进行数据转换换和加工，直直接在SQLL语句中进行行转换和加工工更加简单清清晰，性能更更高。对于SSQL语句无无法处理的可可以交由ETTL引擎处理理。 2.3 数数据装载将转换和加加工后的数据据装载到目的的库中通常是是ETL过程程的最后步骤骤。装载数据据的最佳方法法取决于所执执行操作的类类型以及需要要装入多少数数据。当目的的库是关系数数据库时，一一般来说有两两种装载方式式：(1)直接接SQL语句句进行inssert、uupdatee、deleete操作。(2)采用用批量装载方方法，

39、如bccp、bullk、关系数数据库特有的的批量装载工工具或apii。大多数情况况下会使用第第一种方法，因因为它们进行行了日志记录录并且是可恢恢复的。但是是，批量装载载操作易于使使用，并且在在装入大量数数据时效率较较高。使用哪哪种数据装载载方法取决于于业务系统的的需要。3.主流EETL工具ETL工具具从厂商来看看分为两种,一种是数据据库厂商自带带的ETL工工具,如Orracle warehhouse buildder、Orracle Data Integgratorr。另外一种种是第三方工工具提供商，如如Kettlle。开源世世界也有很多多的ETL工工具，功能各各异，强弱不不一。(1)Orra

40、cle Data Integgratorr(ODI)ODI前身身是Sunoopsis Activve Inttegrattion PPlatfoorm，在22006年底底被Oraccle收购，重重新命名为OOraclee Dataa Inteegratoor，主要定定位于在ETTL和数据集集成的场景里里使用。ODDI和Oraacle原来来的ETL工工具OWB相相比有一些显显著的特点，比比如和OWBB一样是ELLT架构，但但是比OWBB支持更多的的异构的数据据源，ODII提供了caall weeb serrvice的的机制，并且且ODI的接接口也可以暴暴露为webb servvice，从从而可以

41、和SSOA环境进进行交互。OODI能够检检测事件，一一个事件可以以触发ODII的一个接口口流程，从而而完成近乎实实时的数据集集成。ODI的主主要功能特点点有：a.使用CCDC作为变变更数据捕获获的捕获方式式。b.代理支支持并行处理理和负载均衡衡。c.完善的的权限控制、版版本管理功能能。d.支持数数据质量检查查，清洗和回回收脏数据。e.支持与与JMS消息息中间件集成成。f.支持WWeb Seervicee。(2)SQQL Serrver IIntegrrationn Servvices(SSIS)SSIS是是SQL SServerr 20055的新成员，在在SQL SServerr的早期版本本中

42、，其实就就已经有了它它的雏形，那那时的名称叫叫做数据转换换服务(DTTS)。在SSQL Seerver 2005的的前两个版本本SQL SServerr 7.0和和SQL SServerr 20000中，DTSS主要集中于于提取和加载载。通过使用用DTS，可可以从任何数数据源中提取取数据以及将将数据加载到到任何数据源源中。在SQQL Serrver 22005中，对对DTS进行行了重新设计计和改进形成成了SSISS。SSISS提供了数据据相关的控制制流、数据流流、日志、变变量、eveent、连接接管理等基础础设施。控制制流也称为工工作流或者任任务流，它更更像工作流，在在工作流中每每个组件都是是

43、一个任务。这这些任务是按按预定义的顺顺序执行的。在在任务流中可可能有分支。当当前任务的执执行结果决定定沿哪条分支支前进。数据据流是新的概概念。数据流流也称为流水水线，主要解解决数据转换换的问题。数数据流由一组组预定义的转转换操作组成成。数据流的的起点通常是是数据源(源源表);数据据流的终点通通常是数据的的目的地(目目标表)。可可以将数据流流的执行认为为是一个流水水线的过程，在在该过程中，每每一行数据都都是装配线中中需要处理的的零件，而每每一个转换都都是装配线中中的处理单元元。SSISS的体系结构构如图3.11所示。图3.1 SSIS体体系结构图4.ETLL工具的选择择在数据集成成中该如何选选择

44、ETL工工具呢?一般般来说需要考考虑以下几个个方面：(1)对平平台的支持程程度。(2)对数数据源的支持持程度。(3)抽取取和装载的性性能是不是较较高，且对业业务系统的性性能影响大不不大，倾入性性高不高。(4)数据据转换和加工工的功能强不不强。(5)是否否具有管理和和调度功能。(6)是否否具有良好的的集成性和开开放性。数据仓库建模与与ETL的实践践技巧 HYPERLINK 开发者在线 BB 更新时间:2008-09-166作者： 来源:本文关键词： HYPERLINK /files/list-0-0-144252-1-1.htm IT技术 HYPERLINK /files/list-0-0-13

45、6294-1-1.htm ETL HYPERLINK /files/list-0-0-69909-1-1.htm 数据仓库 一、数据仓库库的架构 数据仓库(Data Warehhouse DW)是为为了便于多维维分析和多角角度展现而将将数据按特定定的模式进行行存储所建立立起来的关系系型数据库，它它的数据基于于OLTP源源系统。数据据仓库中的数数据是细节的的、集成的、面面向主题的，以以OLAP系系统的分析需需求为目的。数据仓库的的架构模型包包括了星型架架构(图二：pic2.bmp)与与雪花型架构构(图三：ppic3.bbmp)两种种模式。如图图所示，星型型架构的中间间为事实表，四四周为维度表表，

46、类似星星星;而相比较较而言，雪花花型架构的中中间为事实表表，两边的维维度表可以再再有其关联子子表，从而表表达了清晰的的维度层次关关系。从OLAPP系统的分析析需求和ETTL的处理效效率两方面来来考虑：星型型结构聚合快快，分析效率率高;而雪花花型结构明确确，便于与OOLTP系统统交互。因此此，在实际项项目中，我们们将综合运用用星型架构与与雪花型架构构来设计数据据仓库。那么，下面面我们就来看看一看，构建建企业级数据据仓库的流程程。二、构建企企业级数据仓仓库五步法(一)、确确定主题即确定数据据分析或前端端展现的主题题。例如：我我们希望分析析某年某月某某一地区的啤啤酒销售情况况，这就是一一个主题。主主

47、题要体现出出某一方面的的各分析角度度(维度)和和统计数值型型数据(量度度)之间的关关系，确定主主题时要综合合考虑。我们可以形形象的将一个个主题想象为为一颗星星：统计数值型型数据(量度度)存在于星星星中间的事事实表;分析析角度(维度度)是星星的的各个角;我我们将通过维维度的组合，来来考察量度。那那么，“某年年某月某一地地区的啤酒销销售情况”这这样一个主题题，就要求我我们通过时间间和地区两个个维度的组合合，来考察销销售情况这个个量度。从而而，不同的主主题来源于数数据仓库中的的不同子集，我我们可以称之之为数据集市市。数据集市市体现了数据据仓库某一方方面的信息，多多个数据集市市构成了数据据仓库。(二)

48、、确确定量度在确定了主主题以后，我我们将考虑要要分析的技术术指标，诸如如年销售额之之类。它们一一般为数值型型数据。我们们或者将该数数据汇总，或或者将该数据据取次数、独独立次数或取取最大最小值值等，这样的的数据称为量量度。量度是要统统计的指标，必必须事先选择择恰当，基于于不同的量度度可以进行复复杂关键性能能指标(KPPI)等的设设计和计算。(三)、确确定事实数据据粒度在确定了量量度之后，我我们要考虑到到该量度的汇汇总情况和不不同维度下量量度的聚合情情况。考虑到到量度的聚合合程度不同，我我们将采用“最最小粒度原则则”，即将量量度的粒度设设置到最小。例如：假设设目前的数据据最小记录到到秒，即数据据库

49、中记录了了每一秒的交交易额。那么么，如果我们们可以确认，在在将来的分析析需求中，时时间只需要精精确到天就可可以的话，我我们就可以在在ETL处理理过程中，按按天来汇总数数据，此时，数数据仓库中量量度的粒度就就是“天”;反过来，如如果我们不能能确认将来的的分析需求在在时间上是否否需要精确到到秒，那么，我我们就需要遵遵循“最小粒粒度原则”，在在数据仓库的的事实表中保保留每一秒的的数据，以便便日后对“秒秒”进行分析析。在采用“最最小粒度原则则”的同时，我我们不必担心心海量数据所所带来的汇总总分析效率问问题，因为在在后续建立多多维分析模型型(CUBEE)的时候，我我们会对数据据提前进行汇汇总，从而保保障

50、产生分析析结果的效率率。关于建立立多维分析模模型(CUBBE)的相关关问题，我们们将在下期栏栏目中予以阐阐述。(四)、确确定维度维度是指分分析的各个角角度。例如我我们希望按照照时间，或者者按照地区，或或者按照产品品进行分析，那那么这里的时时间、地区、产产品就是相应应的维度。基基于不同的维维度，我们可可以看到各量量度的汇总情情况，也可以以基于所有的的维度进行交交叉分析。这里我们首首先要确定维维度的层次(Hieraarchy)和级别(LLevel)(图四：ppic4.bbmp)。如如图所示，我我们在时间维维度上，按照照“年-季度度-月”形成成了一个层次次，其中“年年”、“季度度”、“月”成成为了这

51、个层层次的3个级级别;同理，当当我们建立产产品维度时，我我们可以将“产产品大类-产产品子类-产产品”划为一一个层次，其其中包含“产产品大类”、“产产品子类”、“产产品”三个级级别。那么，我们们分析中所用用到的这些维维度，在数据据仓库中的存存在形式是怎怎样的呢? 我们可以将33个级别设置置成一张数据据表中的3个个字段，比如如时间维度;我们也可以以使用三张表表，分别保存存产品大类、产产品子类、产产品三部分数数据，比如产产品维度。(图五：piic5.bmmp) 另外，值得得一提的是，我我们在建立维维度表时要充充分使用代理理键。代理键键是数值型的的ID号码(例如图六中中每张表的第第一个字段)，它唯一标

52、标识了每一维维度成员。更更重要的是，在在聚合时，数数值型字段的的匹配和比较较，JOINN效率高，便便于聚合。同同时，代理键键对缓慢变化化维度有着重重要的意义，在在原数据主键键相同的情况况下，它起到到了对新数据据与历史数据据的标识作用用。在此，我们们不妨谈一谈谈维度表随时时间变化的问问题，这是我我们经常会遇遇到的情况，我我们称其为缓缓慢变化维度度。比如我们增增加了新的产产品，或者产产品的ID号号码修改了，或或者产品增加加了一个新的的属性，此时时，维度表就就会被修改或或者增加新的的记录行。这这样，我们在在ETL的过过程中，就要要考虑到缓慢慢变化维度的的处理。对于于缓慢变化维维度，有三种种情况：1、

53、缓慢变变化维度第一一种类型：历史数据需需要修改。这这种情况下，我我们使用UPPDATE方方法来修改维维度表中的数数据。例如：产品的IDD号码为1223，后来发发现ID号码码错了，需要要改写成4556，那么，我我们就在ETTL处理时，直直接修改维度度表中原来的的ID号码为为456。2、缓慢变变化维度第二二种类型：历史数据保保留，新增数数据也要保留留。这时，要要将原数据更更新，将新数数据插入，我我们使用UPPDATE / INSSERT。比比如：某一员员工20055年在A部门门，20066年时他调到到了B部门。那那么在统计22005年的的数据时就应应该将该员工工定位到A部部门;而在统统计20066

54、年数据时就就应该定位到到B部门，然然后再有新的的数据插入时时，将按照新新部门(B部部门)进行处处理，这样我我们的做法是是将该维度成成员列表加入入标识列，将将历史的数据据标识为“过过期”，将目目前的数据标标识为“当前前的”。另一一种方法是将将该维度打上上时间戳，即即将历史数据据生效的时间间段作为它的的一个属性，在在与原始表匹匹配生成事实实表时将按照照时间段进行行关联，这种种方法的好处处是该维度成成员生效时间间明确。3、缓慢变变化维度第三三种类型：新增数据维维度成员改变变了属性。例例如：某一维维度成员新加加入了一列，该该列在历史数数据中不能基基于它浏览，而而在目前数据据和将来数据据中可以按照照它浏

55、览，那那么此时我们们需要改变维维度表属性，即即加入新的字字段列。那么么，我们将使使用存储过程程或程序生成成新的维度属属性，在后续续的数据中将将基于新的属属性进行查看看。(五)、创创建事实表在确定好事事实数据和维维度后，我们们将考虑加载载事实表。在公司的大大量数据堆积积如山时，我我们想看看里里面究竟是什什么，结果发发现里面是一一笔笔生产记记录，一笔笔笔交易记录 那么这些些记录是我们们将要建立的的事实表的原原始数据，即即关于某一主主题的事实记记录表。我们的做法法是将原始表表与维度表进进行关联，生生成事实表(图六：piic6.bmmp)。注意意在关联时有有为空的数据据时(数据源源脏)，需要要使用外连

56、接接，连接后我我们将各维度度的代理键取取出放于事实实表中，事实实表除了各维维度代理键外外，还有各量量度数据，这这将来自原始始表，事实表表中将存在维维度代理键和和各量度，而而不应该存在在描述性信息息，即符合“瘦瘦高原则”，即即要求事实表表数据条数尽尽量多(粒度度最小)，而而描述性信息息尽量少。如果考虑到到扩展，可以以将事实表加加一唯一标识识列，以为了了以后扩展将将该事实作为为雪花型维度度，不过不需需要时一般建建议不用这样样做。事实数据表表是数据仓库库的核心，需需要精心维护护，在JOIIN后将得到到事实数据表表，一般记录录条数都比较较大，我们需需要为其设置置复合主键和和索引，以实实现数据的完完整性

57、和基于于数据仓库的的查询性能优优化。事实数数据表与维度度表一起放于于数据仓库中中，如果前端端需要连接数数据仓库进行行查询，我们们还需要建立立一些相关的的中间汇总表表或物化视图图，以方便查查询。三、什么是是ETL在数据仓库库的构建中，EETL贯穿于于项目始终，它它是整个数据据仓库的生命命线，包括了了数据清洗、整整合、转换、加加载等各个过过程。如果说说数据仓库是是一座大厦，那那么ETL就就是大厦的根根基。ETLL抽取整合数数据的好坏直直接影响到最最终的结果展展现。所以EETL在整个个数据仓库项项目中起着十十分关键的作作用，必须摆摆到十分重要要的位置。ETL是数数据抽取(EExtracct)、转换换(Trannsformm)、加载(Load )的简写，它它是指：将OOLTP系统统中的数据抽抽取出来，并并将不同数据据源的数据进进行转换和整整合，得出一一致性的数据据，然后加载载到数据仓库库中。例如：下图就向我我们展示了EETL的数据据转换效果。(图七：piic7.bmmp)那么，在这这一转换过程程中，我们就就完成了对数数据格式的更更正、对数据据字段的合并并、以及新增增指标的计算算三项操作。类类似地，我们们也可以根据据其他需求，完完善数据仓库库中的数据。简而言之，通通过ETL，我我们可以基于于源系统中的的数据来生成成数据