数据仓库和管理信息系统中ETL的建设-毕业设计论文

DW/MIS中ETL的建设本科毕业论文(科研训练、毕业设计)题 目：数据仓库和管理信息系统中 ETL的建设姓 名：学 院：软件学院系：专 业：软件工程年 级：学 号：指导教师（校内）： 职称：副教授指导教师（校外）： 职称：工程师年 月 日数据仓库和管理信息系统中ETL的建设摘要 数据仓库和管理信息系统（DW&MIS）对建设银行的生存和发展具有战略意义及现实意义。数据仓库（DW）的建设能够为银行提供规模庞大且规范统一的数据，并支持更先进和更复杂的管理分析与运算工作；基于数据仓库的管理信息系统（MIS）建设是在数据仓库和各相关应用软件（包括数据加工架构下的应用软件等）的支援下，为管理者提供各类信息管理和分析工作的执行平台。 DW&MIS项目所建设的内容将涵盖资产负债管理(ALM)、客户关系管理(CRM)、财务/绩效管理(F/PM)、风险管理(RM)等四大管理主题的业务架构建设、跨管理主题以及特定主题的数据架构建设和技术架构建设三大方面。 ETL功能是提供数据仓库信息内容的前期工作，一般在一个数据仓库系统中，要占据60%左右的工作量。由此可见ETL在整个DW/MIS项目中的重要性。本论文对ETL的总体架构，ETL的处理流程，ETL是如何实施的，以及各种ETL工具等进行分析和研究。关键词 抽取 转换 加载 操作数据存储 联机事务处理 Construction of ETL in DW/MISABSTRACT DW/MIS is very useful about CCBs survival and development.The construction of the data warehouse (DW) can offer normal and unified data in large scale for bank, support more advanced and more complicated management analysis and operation to work. The management information system on the basis of the data warehouse (MIS) bases on data warehouse and every relevant support of application software (including application software under the data process framework ,etc), offer the execution platforms of all kinds of information management and analytical work to administrator. The content of construction of DW&MIS project contains 4 operation framework of management theme such as the asset-liabilities managing (ALM), customers relation managing (CRM), the financial affairs / the performance managing (F/PM), risk management (RM),etc and 3 aspect such as data framework building over management theme and special theme, technology framework building.The function of ETL supports the forepart work of information of data warehouse. Commonly in a data warehouse system, ETL accounts for 60 percent of all workload. So that ETL is very important in the DW/MIS project. This thesis will analyse and research the overall framework of ETL, the process flow of ETL, how ETL implemented, and various kinds of ETL tools, etc.Keywords Extract Transform Load ODS OLTP目录第一章 引言51.1 编写目的51.2 项目背景51.3 项目主要目标5第二章 项目总体描述72.1 基本设计思想72.2 ETL概念和数据仓库关系72.3 ETL的工作过程以及实施步骤82.4 结合单一主题实践选择的ETL工具92.5 数据源的确认以及数据标准的制定92.6 数据的抽取92.7 数据的转换112.8 数据的装载122.9 数据的更新132.10 ETL方案的选择应考虑的问题132.11 ETL实施案例(信贷管理单一主题)14第三章 系统架构203.1 系统整体逻辑结构203.1.1 DW&MIS整体架构203.1.2 ETL体系结构213.2 ETL模块逻辑结构223.3 ETL模块处理流程223.4 ETL功能模块实施243.4.1 源数据抽取243.4.2 数据传输253.4.3 数据转换253.4.4 目标数据表的刷新273.5 ETL实施建议28第四章 运行环境304.1 软件环境30第五章 ETL实施中的关键因素345.1 数据质量的控制345.2 元数据的管理355.3 流程的统一调度355.4 监控预警36第六章 数据安全性386.1 数据传输安全386.2 数据存储安全386.3 数据访问安全38第七章 结束语与感想39致谢语40第一章 引言1.1 编写目的此论文编写的目的是对本学期在中国建设银行厦门开发中心所做的毕业设计进行总结，并向学院做一个汇报。1.2 项目背景系统名称：中国建设银行数据仓库和管理信息系统，简称为DW&MIS项目提出部门：中国建设银行总行信息中心项目开发部门（单位）：中国建设银行总行厦门开发中心系统运行部门：中国建设银行总行运行中心系统直接用户：中国建设银行总行各主要业务部门、一级分行的管理者与信息提供者系统间接用户：使用该系统数据的其他系统用户建设银行要成为国际一流的商业银行，必须借助信息技术形成后发优势，而数据仓库和管理信息系统（DW&MIS）对其生存和发展具有战略意义及现实意义。数据仓库（DW）的建设能够为银行提供规模庞大且规范统一的数据，并支持更先进和更复杂的管理分析与运算工作；基于数据仓库的管理信息系统（MIS）建设是在数据仓库和各相关应用软件（包括数据加工架构下的应用软件等）的支援下，为管理者提供各类信息管理和分析工作的执行平台。DW&MIS项目所建设的内容将涵盖资产负债管理(ALM)、客户关系管理(CRM)、财务/绩效管理(F/PM)、风险管理(RM)等四大管理主题的业务架构建设、跨管理主题以及特定主题的数据架构建设和技术架构建设三大方面。其作为解决全行信息共享、数据一致性、数据质量和管理信息系统集成等问题的重要支持，为建设银行实现信息资源的统一管理和高效运用，实现集约化、科学化管理奠定基础。1.3 项目主要目标建设银行数据仓库和管理信息系统项目整体目标如下：l 建立企业级的数据仓库基础设施，开发基于数据仓库的管理信息系统解决跨部门、跨业务、跨时间和跨信息平台的复杂的信息整合问题，支持复杂的信息检索及在线访问并可处理海量数据，建立全行范围综合信息资源的集中管理与运用支持平台。l 为建设银行的管理科学化提供基础信息以规范的形式整合全行的信息资源，建立全行范围的单一的管理数据视图，全面反映建行的风险管理、财务与绩效管理、资产负债管理和客户关系管理的状况，强化数据视图在全行范围的一致有效和充分共享，为上述四大主题的管理科学化提供基础信息，并支持管理能力的不断提升。l 遵循统一协调的项目实施原则“统一规划、统一数据标准、统一数据模型、统一技术标准，分步实施开发”。第二章 项目总体描述2.1 基本设计思想在软件工程中，提倡低耦合和高内聚，就是要强调模块化，将单一的功能放在单一的模块中，通过接口来通讯。在ETL设计中，同样也需要这样。如果能够将大数据切分成若干适度小数据，例如按照营业机构切分。那么，每次ETL处理都能保证在相对不长的时间内，相对不容易出现异常中断的情况下，运行完毕，这对于流程测试、数据检验都有很大的帮助。另外如果要降低ETL任务间的耦合，提高一个任务的内聚，还必须让单个任务只处理单一数据，否则，如果在一个任务中去装载两个不甚相干的表，这就引起一个问题，当重装其中一个表时，另一个表也会重装，这无疑是一种浪费。可能存在的另一个问题是，如果任务设计为装载多个表，而且他们的依赖关系非常强，在装载过程中，一旦有个表数据装载出现错误，就往往会导致需要重新装载所有的相关表，因为它们都是在同一个任务中处理的。当然，分任务单独装载表的话，如果某个表被其它多个表所关联，那么在装载其它表时，该表就会被多次读入数据，还就需要做一次性能的评估，如果这不是性能瓶颈的话，还是应该采用低耦合的设计。另外，如果ETL工具支持这种“热”数据，可以将数据预先读入内存，以供多个任务使用，这会是解决此问题的一个办法。对于ETL低耦合和高内聚的设计，有两点基本原则：1、对于ETL的性能，不是求最好的性能，而是求可以接受的性能。2、对于ETL任务，只要考虑单一装载的原子功能，不去过多考虑装载调度功能，让流程控制去调度。2.2 ETL概念和数据仓库关系ETL分别代表数据的获取、数据传递和数据存储的三个过程。具体内容是从源系统抽取数据，.转换成特定格式和结构，以提供数据仓库存储需要的所有功能和过程，最后将转换后的数据迁移到数据仓库中E 即抽取 extractive ; T 即传输、转换、清洗 transmission ;L 即加载、调度 load ；由于各个源系统的差异性，ETL功能改造了源系统中的相关数据，将他们变成有用的信息存储在数据仓库中，没有这些功能，就没有数据仓库中的战略信息。如果没有对源数据进行正确的抽取，清洗和整合，作为数据仓库中枢功能的查询处理就不能进行。ETL功能是提供数据仓库信息内容的前期工作，一般在一个数据仓库系统中，要占据60%左右的工作量。由于建行是由大量操作型系统支持的，运行在不同的计算机平台上，所以数据抽取是个很大的挑战，数据转换和装载也会同等困难。ETL在整个数据仓库流程中的位置，如图2-1。图2-1 ETL在整个数据仓库流程中的位置2.3 ETL的工作过程以及实施步骤1、 首先讨论客户需求，确定分析系统范围和主题；2、 根据需求建立业务概念模型；3、 根据业务概念模型建立业务逻辑模型；4、 制定统一的数据标准和映射抽取关系5、 根据业务逻辑模型建立数据模型；6、 进行ETL抽取、转换；7、 加载进目标存储区；图2-2是一个完整的ETL过程图事实表的ETL维度表的ETL为所有的数据装载编写规程组织数据缓存区域和检测工具为聚集表制定计划决定数据转换和清洗规则建立全面的数据抽取规则准备从源到目标数据元素的数据映像关系决定所有的数据源，包括内部和外部决定数据仓库中需要的所有的目标数据图2-2 数据仓库ETL过程参考2.4 结合单一主题实践选择的ETL工具ETL设计工具：Sagent Design Studio，用于设计实施数据抽取计划。ETL调度工具：Sagent Automation，根据特定条件设定管理数据抽取计划实施。ETL管理工具：Sagent Admin， 实现包括ETL 全过程在内的元数据管理ETL 实 现：Sagent Data Movement Server， 提供ETL服务。元数据管理工具： Sagent Admin，管理用户对元数据的访问权限、对数据流中各个组成部分的访问和管理权限等。通过对数据流访问权限的控制，可以方便地对报表和OLAP的访问进行控制，从而满足所有对OLAP 访问控制的功能。元数据存储： SQL Server2000。2.5 数据源的确认以及数据标准的制定单一主题所需的数据虽然只存在于一个数据源系统DCC中，但是存在于多个表中。因此必须要将这些表关联起来才能得到所需的信息。数据映射规则以及数据标准需要总行的速赢项目完成；数据仓库第一期主要实现资产负债主题，数据尽可能从已经集中的DCC里面取。2.6 数据的抽取1、数据抽取方法方法一：在数据仓库服务器上建一个临时表，将所需的字段值先抽取到该表中，然后进行标准化和汇总装载到数据仓库中。方法二：用复杂的SQL语句将多表关联选取相应的字段转载到数据仓库中。2、抽取方法的分析对于方法一，可以提高数据抽取的效率，抽取方法和语句也比较简单。但是需要在数据仓库服务器上开辟另外的存储空间，如果数据量大，需要的存储空间也大。同时在数据仓库服务器上进行标准化和汇总，增加数据仓库服务器的负担，可能会影响到它的效率。对于方法二，在抽取过程中进行了一些转换，可以直接装载数据到数据仓库中，数据仓库服务器不需要额外的空间来存储临时表和对数据进行标准化处理。但是数据抽取过程很复杂，SQL会很繁琐，而且涉及到多表关联，效率会比较低。对于数据源分布在多个系统中这种方法是行不通的。3、抽取方法的比较综合考虑各种方法，方法一可实施程度高。初始装载时，按照所涉及到的源系统的表结构选取需要的字段建立新的表。第一次装载时，采用静态数据捕获，将源数据表的数据全部抽取到临时表中。通过编程或工具对临时表中的数据进行转换，并且进行对数据按照数据仓库的要求进行标准化处理，设置默认值。然后装载到数据仓库中。4、日常数据抽取方法（1）通过对交易日志中对哪些记录进行了update、insert、delete操作，将这些记录抽取到数据仓库中，但是这种方法需要源系统的数据库系统有交易日志可查，另外对交易日志的格式需要编写专门的程序来进行处理。源系统是Informix数据库；（2）通过在源系统的数据库中设置触发器，每当有update、insert、delete操作时时触发一个事件将发生改变的记录抽取到临时存储区中，但是这样对源系统有一些影响；（3）将今天的抽取和昨天的抽取进行比较，找出它们之间的变化，将有update、insert、delete操作的记录抽取到临时表中，这种方法不太适合有大量数据的系统，因为文件太大，不容易进行比较找出变化的记录；（4）通过时间戳来进行数据的抽取，这种方法要求源系统中的数据表中含有日期和时间标记的字段。每次源系统进行update、insert、delete操作时都有时间和日期记录。在每次抽取后在这次抽取的时间和日期作一个时间标记，下次抽取时只需抽取在时间标记后进行update、insert、delete操作的记录。这种方法适合改动比较少的数据表；(5)通过在源程序写入所有update、insert、delete操作，抽取程序捕获源数据变化的文件。这种方法需要修改源程序，需要对它进行维护。一般而言，不会采取这种方法；各种数据抽取方式的适合情况（表示不允许），如表2-1。数据量大没有修改源数据表的权限没有时间戳没有有交易日志不允许修改源系统的应用程序静态数据捕获交易日志捕获数据库触发器捕获在源程序中捕获时间戳捕获文件比较捕获表2-1各种数据抽取方式的适合情况针对单一主题而言，源系统的特点：数据量小，涉及的数据表少，有操作数据库进行的权限。因此采用在源系统的数据表中建立触发器的方式，如果该表中有更新（insert、update、delete）操作，将有变化的记录抽取到临时存储区。2.7 数据的转换对于数据转换主要从以下方面进行转换：（1）格式修正。包括数据类型和单个字段的变化。例如，类型在各种源系统中定义不一致，需要对它们进行标准化；（2）字段的解码。一些用1和2来表示男性和女性，另一个系统用M和F来表示。一些难以理解的表示方法如BJ、SH等。数据仓库需要用户可以理解的值，因此需要对这些值进行解码；（3）计算值和导出值。一些字段在导入数据仓库时需要进行计算。主要是事实表的数据；（4）单个字段的分离。例如有些系统中将地址和名称写在一个字段里，在数据仓库中为了分析的方便需要将这些信息分离成地址和名称二个字段；（5）信息的合并。例如信贷的发生额在DCC中，而客户信息在CMIS中，如果要得到完整的信息，就必须将二个系统的数据进行合并；（6）特征集合转化。例如源系统使用大型机，它的文本格式采用EBCDIC格式，而数据仓库基于微机系统，采用ASCII格式，这样需要进行特征集合转化；（7）度量单位的转化。例如国内的贷款使用人民币，而涉外的贷款会使用到美元，装入到数据仓库是必须要统一货币类型；（8）日期/时间转化。这与操作系统和数据库管理系统设置有关，如在Informix中11/10/2004表示2004年10月11日，而在SQL Server中可能表示2004年11月10日；（9）汇总。例如由于粒度需要，在转入数据仓库时一天的发放贷款数据汇总，这样需要对一天的贷款发生额进行汇总；（10）键的重新构造。例如在源系统中是以机构编号作为主键，如果在机构维度表中继续采用机构编号作为主键，在一个机构的机构编号发生变化时，就没有办法判断这二个机构编号是属于同一个机构。因此我们采用机构维度表自己生成的键作为主键，机构编号变化时只要加上一个指向新编号的指针即可；以上是数据转换要考虑的方面，针对单一主题，在数据仓库服务器上编写相应的SQL语句或者编写相应的程序进行转换。2.8 数据的装载1、数据装载有四种方式：装载、追加、破坏性合并、建设性合并。（1）装载：将数据准备区的数据装载到数据仓库中，如果数据仓库中原来有数据，则先全部清除后，在进行装载。（2）追加：将数据准备区的数据追加到数据仓库中，当已输入记录与已存在记录重复时，要定义如何处理这条记录的副本，可以作为副本增加进去，也可以将其丢弃。（3）破坏性合并：如果数据仓库中不存在数据准备区的数据相同的键值，就将数据准备区的数据加入到数据仓库中。如果已存在某个某个键值，则将该键值的内容进行更新。（4）建设性合并：将数据准备区的数据加入到数据仓库中。如果已存在某个某个键值，则将新加入的记录作一个标记，表示是对旧记录的替代。2、初始装载一次对整个数据仓库进行装载，可以将数据装载分为几个在装载，每个子装载可以采用上述四种方式的任何一种。对一个表的初始装载的第一次允许，可以采用装载的方法，以后的过程可以采用追加。3、增量装载一般采用建设性合并。对于任何历史性观察不重要的目标表，如一个维度表的一条记录变化意味着纠正自己的错误，旧记录被新纪录代替，采用破坏性合并。4、完全刷新这种类型应用于周期性地重写整体数据仓库。与初始装载不同的是在装入数据时必须将原来的数据删除。可以采用装载和追加的方法。2.9 数据的更新如果当更新的记录数达到1525时，更新的所需的成本会高于刷新，否则，刷新的成本高于刷新。1、维度表的更新对维度表的修改有三种类型：改正错误、保存历史数据、暂时的修改(1) 改正错误： 如客户维度表中姓名输错了，输入正确姓名后，旧的姓名没有必要保留，用新的姓名进行替代。(2) 保存历史数据：如客户的婚姻状态发生改变，需要重新一个分配键值，记录该客户新的信息，并将原来的记录指向这条新的记录。(3) 暂时的修改：对于保存历史数据的修改中，如果要对跨时期进行分析。如客户2004年10月1日搬迁了地址，要对客户在2004年9月1日至2004年11月1日之间的贷款进行分析，这样需要在键值不变的情况下，增加客户新的地址，并修改它的有效日期。对单一主题的每一个维度进行分析，采用哪种方式进行修改。对时间维属性的描述与源系统没有关系。对其它维度表，如果是属于输入错误，可以进行改正错误。如果是属于像客户地址搬迁，信用等级的改变需要保存历史数据。如果有特殊的分析要求，如进行客户在多个信用等级的阶段的贷款总额，则需要进行暂时的修改。2、事实表的更新事实表的增量抽取是通过捕获数据库的交易日志，事实表的数据装载应用并行处理技术。2.10 ETL方案的选择应考虑的问题 ETL解决方案包括数据抽取（E）、数据传输、转换与清洗（T）、数据加载、调度（L），毋庸置言，ETL系统将贯穿整个数据仓库系统的全过程。 ETL方案的选择应考虑以下方面： 1、 数据操作效率；（1） 是否支持复杂的数据操作；（2） 是否支持多任务并行操作；（3） 是否符合系统对数据处理时间窗口的要求。2、 数据操作时间周期；（1） 是否支持各种数据处理时间周期的混合操作；（2） 是否支持数据的小批量持续加载；（3） 是否支持数据的大批量定时加载。3、 定制的灵活性；（1） 是否支持数据依赖的建立；（2） 是否支持数据流的建立；（3） 是否支持操作定时启动；（4） 是否可扩展；（5） 开发环境是什么，开发是否简单、灵活。2.11 ETL实施案例(信贷管理单一主题)1、 系统架构 图2-3 系统架构图元数据服务器主要实现系统数据抽取管理工作， 并且保存应用系统中所使用的元数据。涉及的软件：ERWIN、Sagent DesignStudio、INFORMIX-OnLine Version 7.23.UC6、SqlServer2000数据集市（DataMart） 服务器采用ROLAP技术， 在关系型数据库上建立、多模型保存多维数据数据。涉及的软件： INFORMIX-OnLine Version 7.23.UC6、SqlServer2000WEB服务器为整个系统中的WEB 应用提供WWW服务。涉及的软件： Windows XP ServerPC Client 数据集市的设计、管理和调度可在一台机器上实现多个功能。也可以将不同的功能分布到不同的机器。涉及的软件： ERWIN、Sagent DesignStudio PC Client 即席查询通过Client/Server 方式对数据仓库中的数据进行OLAP 分析， 综合统计。涉及的软件： BO2、 目标定位(1) 信贷业务分析 目前信贷资产在商业银行总资产中占据主要地位，商业银行的盈利很大一部分来自于利差收入，通过在现有信贷业务系统数据的基础上，构造信贷信息数据仓库，建立信贷决策支持系统，可以辅助银行中高级经营管理者进行经营决策，提高银行经营、运作的科学性及预见性，降低银行信贷风险，提高信贷获利能力，建立辅助信贷决策模型。以效益为核心，从行业、客户、产品、机构等多个口径对辖内信贷资产的历史数据进行流动性分析、安全性分析、盈利性分析，并能预测其将来的发展趋势。(2) 信贷客户分析 以客户为中心，分别对客户自身的经营状况以及与商业银行的信贷关系从各种角度进行分析。分析客户对银行的贡献程度，分析客户与银行间的关系，使银行能更有效的做出决策。(3) 信贷品种分析 主要对商业银行贷款的不同种类进行利润的贡献度分析，并结合客户信息，针对不同的客户群设计和推销不同的金融服务和金融产品，使产品的设计和开发真正做到以市场为导向，以客户为中心。 (4) 机构分析 对各发放贷款机构进行评测，分析各机构的工作情况及工作效率。各机构间进行比较分析，分析成功与失败的原因，帮助决策着更好的制定策略。3、 功能结构 图2-4 功能结构4、 E-R模型图2-5 E-R模型5、 事实表、维表设计(1) 信贷管理事实表信贷情况表（主键 LONG，时间外键 LONG，帐户外键 LONG，客户外键 LONG，币别外键 INTEGER，机构外键 INTEGER，贷种外键 INTEGER，额度 DECIMAL(16，2)，贷款总额 DECIMAL(16，2)，已还本金 DECIMAL(16，2)，贷款余额 DECIMAL(16，2)，应收利息 DECIMAL(16，2)，催收利息 DECIMAL(16，2)，逾期金额 DECIMAL(16，2)，本月发放金额 DECIMAL(16，2)，本月回收本金 DECIMAL(16，2)，呆账金额 DECIMAL(16，2)，呆滞金额 DECIMAL(16，2)）(2) 信贷管理维度表时间维度表（主键 LONG，月份id INTEGER，月份中文名 CHAR8，月份英文简称 CHAR8，季度 CHAR10，季度编码 CHAR4，半年 CHAR8，半年编码 CHAR4，年份 CHAR6）客户信息维度表（主键 LONG，客户编号 CHAR22，单位名称 CHAR50，注册城市 CHAR50，注册省 CHAR30，注册国家 CHAR30，单位中文简称 CHAR20，法人客户编号 CHAR20，税务登记编号 CHAR20，纳税人规模 CHAR1，营业执照号 CHAR30，经营种类 CHAR30，行业别 CHAR10，所属系统 CHAR10，经济性质 CHAR20，上级主管部门 CHAR20，贷款证号 CHAR21，注册资金DECIMAL(16，2)，实收资本DECIMAL(16，2)，企业规模 CHAR14，财务联系人 CHAR20，企业信用等级 CHAR10，企业资质等级 CHAR4，外币余额限制DECIMAL(16，2)，批准贷款额度INTEGER，批准年度 CHAR6，批准文号 CHAR14，资金主管部门 CHAR30，主导产品 CHAR50，营业执照有效期 CHAR12，开户许可证号 CHAR15，贷款额度最大编号 CHAR6，注册资金币别 CHAR20，CC客户编号 CHAR12，信用卡额度编号 CHAR6）币别维度表（主键 INTEGER，币别编码 CHAR4，币名 CHAR30，英文编号 CHAR10，国家 CHAR20，洲 CHAR10，国家性质 CHAR20）机构维度表（主键 INTEGER，机构号 CHAR12，机构名称 CHAR40，机构级别编码 CHAR4，机构级别 CHAR12，所属二级分行 CHAR30，所属地区 CHAR20）账户维度表（主键 INTEGER，账号CHAR40，开户日期CHAR20，开户行编码 CHAR12，开户行名称 CHAR40，帐户类型编码 CHAR8，帐户类型名称 CHAR10，单位名称 CHAR40，担保人 CHAR20，帐户状态 CHAR8，起贷日期CHAR20，到期日期 CHAR20）贷种维度表（主键 INTEGER，贷种编码 CHAR10，贷种名称 CHAR20，科目编码 CHAR8，科目级别 CHAR8，科目名称 CHAR20，贷款大类编码 CHAR8，贷款大类名称 CHAR20）6、 源数据表 表名中文说明更新规则相关的维CICIFCI1单位客户信息档覆盖客户维CMMSCNTC国籍档覆盖客户维CMMSCBTP行业别档覆盖客户维CMMSCBLS所属系统档覆盖客户维CMMSCCNC经济性质档覆盖客户维CMMSCMND上级主管部门档覆盖客户维CMMSCCRG信用等级档覆盖客户维CMMSCQLC资质等级档覆盖客户维CMMSCFPD资金主管部门档覆盖客户维CMMSCCUD币别档覆盖客户维、币别维CMMSCCRC币别名称档覆盖币别维CMBCTBCT营业单位主档覆盖客户维、机构维、账户维DKZLGXB贷款种类关系表覆盖账户维、贷种维LNLNSLNS放款主档覆盖账户维LNLNSUPY放款利息明细档新增事实维LNLNSPLN分期放款计划档新增事实维LNLNSDUE分期还款计划档新增事实维LNLNSPMT贷款本金变动异动档新增事实维LNLNSJR1放款帐卡档 覆盖事实维LNLNSJR2放款帐卡档 覆盖事实维第三章 系统架构3.1 系统整体逻辑结构3.1.1 DW&MIS整体架构图3-1 DW&MIS整体架构3.1.2 ETL体系结构图3-2 ETL体系结构Design manager 提供一个图形化的映射环境，让开发者定义从源到目标的映射关系、转换、处理流程。设计过程的各对象的逻辑定义存储在一个元数据资料库中。Meta data management 提供一个关于ETL设计和运行处理等相关定义、管理信息的元数据资料库。ETL引擎在运行时和其它应用都可参考此资料库中的元数据。Extract 通过接口提取源数据，例如ODBC、专用数据库接口和平面文件提取器，并参照元数据来决定数据的提取及其提取方式。Transform 开发者将提取的数据，按照业务需要转换为目标数据结构，并实现汇总。Load 加载经转换和汇总的数据到目标数据仓库中，可实现SQL或批量加载。Transport services 利用网络协议或文件协议，在源和目标系统之间移动数据，利用内存在ETL处理的各组件中移动数据。Administration and operation 可让管理员基于事件和时间进行调度、运行、监测ETL作业、管理错误信息、从失败中恢复和调节从源系统的输出。3.2 ETL模块逻辑结构图3-3 ETL模块逻辑结构3.3 ETL模块处理流程图3-4 ETL模块处理流程(一)图3-5 ETL模块处理流程(二)3.4 ETL功能模块实施3.4.1 源数据抽取源数据主要来自于如下多个异构数据源：DCC系统、个贷管理系统、信贷管理系统、外汇资金系统、国内信用证系统、委托住房金融系统、证券业务系统、人力资源系统、国际卡系统、个人理财系统、CallCenter系统、重客服务系统、网络银行系统、抵债资产系统、费用分解系统、总账信息管理系统、固定资产系统。运行平台主要有：SUN Solaris、HP-UX、IBM AIX、Sco Unix、Windows等。数据库环境主要有：DB2、Informix、Oracle、Sybase、SQLServer等。生成文件时,可对大数据量表,先生成多个文件,以某项分组的功能去实现(例:主键的键值作值域分组;hash分组;平均分组等),这样就.为以后的ETL处理作了一定基础工作。对数据的汇总处理不管是在数据库上作，还是在ETL工具上做，内部都是要先排序，再汇总的。生成文件的数据尽可能做到是经过排序的有序数据，因为排过序的数据，作汇总时无论在数据库或ETL工具上处理都会加快。而数据库本身的索引、存储空间等技术使查找、排序、汇总的操作优化，是任何etl工具都不可能达到的高效，所以，尽可能在数据库端就利用高效的排序算法完成卸出数据的有序化工作。可考虑如下实施步骤： 1) 建立系统的连接； 2) 读取数据提取规则参数表，确定提取的源表； 3) 根据源表、提取规则、最后一次提取的时间戳或流水号生成提取动态SQL语句； 4) 查询出的数据根据输出文件拆分规则生成相应的文件（包含汇总记录），如果当天数据已全部生成，生成汇总记录文件； 5) 将生成的文件转移到公共文件夹中（ETL工具获取数据文件的目录），同时复制一份到备份数据目录。3.4.2 数据传输数据传输是通过网络负责把各远程源系统的数据文件FTP到ETL工具的文件接收区中，可考虑通过传输中间件实现。可考虑如下实施步骤： 1) 初始化参数表，如间隔时间、远程源系统数据文件的存放位置、ETL工具端数据文件存放路径，并连接到远程源系统主机上； 2) 取远程源系统数据文件列表，将其取至本地； 3) 根据远程源系统数据文件列表，生成取数据文件的脚本，运行此脚本； 4) 完成取数据操作后，生成取回数据的列表，根据此列表生产删除远程源系统数据文件的脚本，运行此脚本； 5) 将取回的数据文件移到ETL工具的指定文件接收区中； 6) 继续监控。3.4.3 数据转换数据转换是将源数据变为目标数据的关键环节，通常会遇到的转换要求有：字段映射；映射的自动匹配；字段的拆分；多字段的混合运算；跨异构数据库的关联；自定义函数；多数据类型支持；复杂条件过滤；支持脏读；数据的批量装载；时间类型的转换；对各种码表的支持；环境变量是否可以动态修改；去重复记录；抽取断点；记录间合并或计算；记录拆分；抽取的字段是否可以动态修改；行、列变换；排序；统计；度量衡等常用的转换函数；代理主键的生成；调试功能；抽取远程数据；增量抽取的处理；制造样品数据；在转换过程中是否支持数据比较的功能；数据预览；性能监控；数据清洗及标准化；按行，按列的分组聚合等。考虑到降低源数据系统的负荷以及ETL工具的运行机制，数据转换工作建议在数据加载时进行。 清洗主要是针对系统的各个环节可能出现的数据二义性、重复、不完整、违反业务规则等问题，可以考虑通过试抽取，将有问题的纪录先剔除出来，根据实际情况调整相应的清洗操作。 对数据文件进行处理，读入一个文件可能会被分配到三个文件当中，即合法数据文件、异常数据文件、初次汇总文件。合法数据文件是没有违反规则的数据，异常数据文件是在清洗过程中发现不合乎规则的记录。异常数据文件跟合法数据文件的和应该是输入文件。初次汇总文件是经过初步汇总的文件，该汇总过程只依赖于输入文件内部，不跟外部其它文件关联。数据转换可考虑如下实施步骤： 1) 初始化参数表，如间隔时间、本地文件路径等，程序启动时或配置参数更改时（定时判断配置参数更改标志位）加载各类参数配置表； 2) 实时扫描ETL工具的文件接收区，如有新文件出现，根据文件名，调用该文件的相应处理模块； 3) 根据对应文件的清洗规则对数据文件进行行扫描，异常数据未知参数、文件格式错误（包含整个文件、行记录）生成异常数据文件，合法数据生成合法数据文件； 4) 在进行数据的行扫描时，同时调用转换、汇总模块，生成文件各自对应的汇总数据； 5) 程序根据生成的不同数据文件，生成最后汇总数据，同源数据文件各自的汇总数据累计和进行对比，如出现不一致，提供系统预警。对于数据清洗的过程，可考虑如下实施步骤：1) 元素化：将非标准的数据，统一格式化成结构数据。2) 标准化：将数据标准化，根据字典消除不一致的缩写等等。3) 校验：对标准化的数据进行一致性校验，即在内容上修改错误。4) 匹配：在其它记录中寻找相似的记录，发现重复异常。5) 消除重复记录：根据匹配结果进行处理，可以删除部分记录或者多个记录合并为一个更完整信息的记录。6) 档案化：将前5个步骤的结果写入元数据存储中心。这样可以更好地进行后续的清理过程，使得用户容易理解数据库以及更好地进行切片、切块等操作。另外，还可以通过范围检验、枚举清单和相关业务逻辑检验三种方法来进行检验和处理：1) 范围检验：检验一个字段中的数据是否落在预期范围之内，一般如日期范围、年龄范围或地区范围等。2) 枚举清单：对照数据字段可接受值的清单，以确定检验字段是否需要处理。3) 相关业务逻辑检验：让有业务经验的人员结合实际的业务逻辑，在保证数据有效性的前提下，决定对无法清洗的数据的取舍。 3.4.4 目标数据表的刷新目标表的加载策略决定了加载数据的频度。一些目标表是不需要更新的，而一些目标表在每日夜晚加载，一些可能每周、每月加载。制定目标表的刷新策略需要ETL 工具的支持，工具对于更新的时间点或周期必须能够灵活处理。在以下三种策略中，所有的数据都依据时间戳等要素与现存数据进行比较。1) 维表加载可考虑如下实施步骤：(1) 根据目标表的格式和自然键将输入的数据组织成记录；(2) 确定目标表的数据与源数据表之间的记录映射规则；(3) 抽取有效记录中的代理键、自然健和重要的改变列；(4) 使用输入的记录，对抽取的维表做一个查找操作，如果找不到与输入记录相同的记录，那么建立一条新记录并指派一个新的代理键，并将记录插入目标表中。若找到，则对重要的改变列进行更新；(5) 加载和更新维表记录。2) 事实表加载事实表的加载是后续数据分析的基础。所有的事实表都应该始终增加数据。事实表的粒度决定了数据加载的复杂程度，它不是简单的数据拷贝，必须首先将每个事实表与各自的维表键结合。每个与事实表相关的源数据表必须有足够的信息来查找维表中自然键，验证事实记录的完整性。3) 聚集表加载加载聚集表则不用担心数据的完整性、一致性，因为事实表包含的所有必须的数据可以形成聚集表。可以考虑如下实施步骤：(1) 完全替换聚集表中的变化的数据记录；（详细级）(2) 插入一条汇总数据。（概括级）3.5 ETL实施建议 根据业界的经验和教训，在ETL实施过程中，还有以下要点值得引起注意：l 尽可能利用数据中转区对生产数据进行预处理。保证了集成与加载的高效性。l ETL的过程应该是主动“拉取”，而不是从内部“推送”，其可控性将大为增强。 l ETL实施之前，应制定流程化的配置管理和标准协议。l 关键数据标准至关重要。DW&MIS面临的最大挑战是当接收数据时各源系统数据的异构性和低质量，例如：DCC系统按照客户编号和账号管理数据，总账信息管理系统按照指标号进行帐目数字管理，国内信用证系统则按照信用证编号管理。而ETL需要对这三个系统进行集成以获得对客户的全面视角。这一过程需要复杂的匹配规则、客户编号、名称/地址等字段的正常化与标准化。所以ETL在处理过程，必须定义一个关键数据标准，在此基础上，制定相应的数据接口标准。l 在ETL实施中，必然是ETL工具和手工编码相结合，这两种方式各有优劣，可从以下几个方面进行比较：ETL工具手工编码灵活性比较灵活 最灵活难易程度相对容易要求一定技术水平管理和维护容易较难性能和效率较高取决于编写者水平开发周期较短较长工作量中等较重价格较高相对较低第四章 运行环境4.1 软件环境1) 主要ETL工具的架构和原理l Oracle公司的Oracle9i WarehouseBuilder(3.0)图4-1 主要ETL工具的架构和原理工作原理：Oracle Warehouse Builder(OWB)基础架构分为两部分设计环境和运行环境。设计环境处理元数据，运行环境处理物理数据。元数据环境的核心是元数据知识库（Repository）和设计工具，数据处理部分的核心是运行环境（Runtime Environment）和数据库。OWB的元数据存储在Oracle数据库中，通过OWB 客户端可以设计关系数据库模型、多维模型、ETL过程和最终用户工具环境，并提供对元数据的生命周期管理。一旦用户完成ETL过程的逻辑设计，OWB能够自动生成对应数据库对象的SQL代码，该代码可以被发布到文件系统或数据库中。ETL通过OEM（Oracle Enterprise Manager）执行发布到数据库中的代码来实现，如果涉及到数据库外的调用则分别运行在各自的环境中，如SAP的抽取代码在SAP环境中运行。OWB的任务计划和流程控制是通过与其集成的OEM和OWF（Oracle Workflow）来实现。l Ascential公司的DataStage(XE)图4-2 Ascential公司的DataStage(XE)工作原理：DataStage 企业版(DataStage XE Parallel Extender)使用了C/S架构。用户通过各个客户端工具访问DataStage企业版的开发、部署和维护功能，这些工具包括：Designer：用来建立和编辑DataStage 作业和表的定义。Designer 中的“Job Sequencer”控制作业的执行，作业成功完成（或失败）的条件；Administrator：用来执行管理任务，如建立DataStage 用户、建立和删除工程并且建立清洗标准；Manager：用来编辑管理用户工程的DataStage 资料库；Director：用来验证、