《BI项目中ETL设计与思考》由会员分享，可在线阅读，更多相关《BI项目中ETL设计与思考（36页珍藏版）》请在金锄头文库上搜索。
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是将将业务系统的的数据经过抽抽取、清洗转转换之后加载载到数据仓库库的过程，目目的是将企业业中的分散、零零乱、标准不不统一的数据据整合到一起起，为企业的的决策提供分分析的依据ETL是BBI项目最重重要的一个环环节，通常情情况下ETLL会花掉整个个项目的1/3
13、的时间，ETTL设计的好好坏直接关接接到BI项目的成成败。ETLL也是一个长长期的过程，只只有不断的发发现问题并解解决问题，才才能使ETLL运行效率更更高，为项目目后期开发提提供准确的数数据。ETL的设设计分三部分分：数据抽取取、数据的清清洗转换、数数据的加载。在在设计ETLL的时候也是是从这三部分分出发。数据据的抽取是从从各个不同的的数据源抽取取到ODS中(这个过程也也可以做一些些数据的清洗洗和转换)，在抽取的的过程中需要要挑选不同的的抽取方法，尽尽可能的提高高ETL的运行行效率。ETTL三个部分分中，花费时时间最长的是是T(清洗、转转换)的部分，一一般情况下这这部分工作量量是整个ETTL的
14、2/3。数据据的加载一般般在数据清洗洗完了之后直直接写入DWW中去。ETL的实实现有多种方方法，常用的的有三种，第第一种是借助助ETL工具如如Oraclle的OWB、SQL sserverr 20000的DTS、SQL SServerr2005的的SSIS服务务、informmatic等等实现，第二二种是SQLL方式实现，第第三种是ETTL工具和SQLL相结合。前前两种方法各各有优缺点，借借助工具可以以快速的建立立起ETL工程，屏屏蔽复杂的编编码任务，提提高速度，降降低难度，但但是欠缺灵活活性。SQLL的方法优点点是灵活，提提高ETL运行效效率，但是编编码复杂，对对技术要求比比较高。第三三种是
15、综合了了前面二种的的优点，极大大的提高ETTL的开发速速度和效率。数据的抽取取数据的抽取取需要在调研研阶段做大量量工作，首先先要搞清楚以以下几个问题题：数据是从从几个业务系系统中来?各个业务系系统的数据库库服务器运行什什么DBMSS?是否存在手手工数据，手手工数据量有有多大?是否存在非非结构化的数数据?等等类似问问题，当收集集完这些信息息之后才可以以进行数据抽抽取的设计。1、与存放放DW的数据库系统相相同的数据源源处理方法这一类数源源在设计比较较容易，一般般情况下，DDBMS(包括SQLSServerr，Oraclle)都会提提供数据库链接功功能，在DWW数据库服务器器和原业务系系统之间建立立直接的链接接关系就可以以写Seleect 语句句直接访问。2、与DWW数据库系统不不同的数据源源的处理方法法。这一类数据据源一般情况况下也可以通通过ODBCC的方式建立立数据库链接接，如SQLL Servver和Oraclle之间。如如果不能建立立数据库链接，可可以有两种方方式完成，一一种是通过工工具将源数据据导出成.ttxt或者是是.xls文件件，然后再将将这些源系统统文件导入到到ODS中。另另外一种方法法通过程序接接口来完成。3、对于文文件类型数据据源(.txxt,，xls)，可可以培训业务务人员利用数数据库工具将将这些数据导导入到指定的的数据库，然然后从指定的的数据库抽取。或或者可以借
