ETL 文档.docx

1、ETL 文档BI项目之ETL文档项目组 2004-12-19 目 录一、ETL之概述1、ETL是数据仓库建构/应用中的核心过程2、ETL的体系结构3、ETL的设计原则二、数据仓库建构中的ETL：分析与设计1、面临的问题2、明确需求：需要哪些数据？3、分析数据源：从何处获取数据？能获取怎样的数据？4、基于数据源及数据仓库模型，建立从源到目标的映射模型：如何获取数据？5、元数据库模型的建构三、数据仓库建构中的ETL：开发/实现1、元数据驱动下的ETL基本实现2、关于与OA及传输工具接口的额外实现3、元数据的配置、维护与管理一、ETL概述1、ETL是数据仓库建构/应用中的核心过程数据源具有多样性和可

2、变性数据仓库系统是在业务系统的基础上发展而来的，其内部存储的数据来自于事务处理的业务系统和外部数据源。因企业的业务系统是在不同时期、不同背景、面对不同应用、不同开发商等各种客观前提下建立的，其数据结构、存储平台、系统平台均存在很大的异构性。这导致企业内各源数据缺少统一的标准，因而其数据难以转化为有用的信息，原始数据的不一致性导致决策时其可信度的降低。 此外，随着企业的不断发展，既有的业务系统、业务流程以及相关的信息结构都可能会发生变化，这种变化将直接影响到后端数据仓库系统中的数据更新。如何有效的维护这种变化，尽量控制数据仓库刷新操作的成本，也是数据仓库建构中极为重要的一个问题。ETL的核心功能

3、定位ETL是建构企业数据仓库（Data Warehousing，即DW）从而实现商务智能（Business Intelligence，即BI）的核心和灵魂，它按照统一的规则集成数据并提高数据的价值，是负责完成数据从数据源向目标数据仓库转化的过程，是实施数据仓库的重要步骤。如果说数据仓库的模型设计是一座大厦的设计蓝图，数据是砖瓦的话，那么ETL就是建设大厦的过程。在整个项目中最难的部分是用户需求分析和模型设计，而ETL规则设计和实施则是工作量最大的，其工作量要占整个项目的60%-80%，这是国内外从众多实践中得到的普遍共识。针对数据源的多样性和可变性，ETL通过对从数据源到目标数据仓库间的映射规

4、则进行元数据级别上的建模，使得整个抽取、转换、装载过程在元数据驱动下能完全自动调度执行，同时也便于维护和扩展。同时，因分析的需要，数据仓库中的数据要求是面向主题的，具有集成性、一致性和时间性，而所有这些都是在详细分析各种数据源，真正理解数据的业务含义基础上通过ETL过程来实现的。2、ETL的体系结构简单的讲，ETL就是抽取、转换和装载，同时提供数据质量的管理，并且贯穿整个商务智能解决方案的全过程，完成整个系统的数据处理、调度、监控及元数据管理等。其体系结构如下图所示： Design manager: 提供一个图形化的映射环境，让开发者定义从源到目标的映射关系、转换、处理流程。设计过程的各对象的

5、逻辑定义存储在一个元数据资料库中。 Meta data repository: 提供一个关于ETL设计和运行处理等相关定义、管理信息的元数据资料库。ETL引擎在运行时和其它应用都可参考此资料库中的元数据。 Extract: 通过接口提取源数据，例如:ODBC、专用数据库接口和平面文件提取器。参照元数据来决定提取何处的数据和怎样提取。 Transform: 开发者将提取的数据按照业务需要转换为目标数据结构，并实现汇总。 Load: 加载经转换和汇总的数据到目标数据仓库中，可实现SQL或批量加载。 Transport services: 利用网络协议(TCP/IP等)或文件协议(FTP等)，在源和

6、目标系统之间移动数据，利用内存(Data Caches 等)在ETL处理各组件中移动数据。 Administration and operation:可让管理员基于事件和时间进行调度、运行、监测ETL作业、管理错误信息、从失败中恢复和调节从源系统的输出。3、ETL的设计原则由于企业在自身的发展及信息化建设过程中，业务种类、业务流程以及相伴随的信息结构将不断变化，这样就会导致多种平台的出现或多个信息系统同时或更替使用，使得数据分散存储，这样多种数据源（包括数据库管理系统）的现象也会出现，而且随着企业的继续发展，它们还可能继续变化。因此，如何对企业现有信息架构进行有效描述以驱动ETL的自动调度执行

7、，以及如何维护与管理这种架构描述以应对未来一段时间内可能发生的变化，就显得至关重要了，而这方面的工作往往是通过设计元数据来完成的，它也是ETL设计过程所要解决的主要问题。ETL的设计原则表现在如下几个方面： 元数据的定义：抽象与具体相结合的原则元数据是关于数据的数据，它立足于一个较高的抽象层次对原始数据及其相关特性进行描述，以便这些原始数据的使用者只要查看这些元数据就能知道如何使用或操作这些原始数据。这样，原始数据的使用者就不必绑定于特定的原始数据，而是分离开来，依靠元数据驱动，从而达到集成各种不同数据源的目的。这也是抽象带来的好处。但应注意把握抽象的度，根据特定情况在某些方面适当具体化，以便

8、在实际应用中提高性能，同时尽量避免对原始数据使用者的修改维护。 元数据的格式或描述语言：开放性与可移植性的原则一般，用XML等作为元数据的表现形式，以对其他数据进行描述，从而便于元数据间的交换，但对XML的解析会有额外的性能消耗，因此也可根据具体情况，选择其他方式来作为元数据的载体。 元数据的变化：可维护性与可扩展性的原则首先，这里元数据的变化是指元数据内容的变化，它反映着原始数据结构的变化。这样，由于原始数据的使用者已同原始数据分离，从而通过改变元数据的内容就避免了对原始数据使用者的改变，使得整个过程可配置，方便维护。 元数据驱动的ETL过程：可跟踪性原则应采用日志文件记录抽取过程所作的操作

9、，便于诊断、跟踪数据抽取过程，保证抽取数据的准确、抽取过程的优化。 与其他工具或系统的可集成性原则应留有一定的接口，便于其他工具或系统调用。二、数据仓库建构中的ETL：分析与设计1、面临的问题项目启动之前，经初步了解，集团及各分公司的信息系统建设状况所呈现的问题表现在如下几个方面：（1）、信息系统建设、使用方面除八家子公司外，集团所属其他分公司根本还未用上合适的信息系统；八家子公司尽管都已上了ERP系统，但使用程度参差不齐，大都仅使用了部分模块或子系统；（2）、基础资料方面物料编码不统一，物料信息不齐全；科目编码不一致；（3）、数据分析需求方面集团各部门对数据的分析需求各不相同。基于BI项目的

10、特点及集团的实际情况，我们确定了如下的ETL设计过程，使得整个项目进展有条不紊、衔接有序。2、明确需求：需要哪些数据？由于各业务系统中有许多数据是专为操作型业务服务和使用的，并不为分析所用，也不一定具有分析的价值，因此并非业务系统中的所有数据都需要作ETL，而是按需提取，并使这一过程可配置可扩展，这样也使得整个ETL过程在可行性及性能上得到了保证。按需提取，需从何来？严格来讲，它应该仅仅来自于数据仓库之所需。因此，数据仓库模型的建构就必须首先执行。为此，我们在实践中总结出了如下针对BI项目的富有特色的需求调研过程：（1）、初步的需求调研：集团各部门需要看到什么？需要作哪些分析？大致分析到怎样的

11、细节程度？等等。这期间也可进行初步的数据源状况的调查分析，以为后一阶段详细深入的数据源分析作铺垫，但因缺乏明确的目标，不可能深入进行。（2）、根据初步的展现需求，设计图形化的展现DEMO，“秀”给各部门负责人/领到看，征求修改意见及建议，推动需求向纵深发展；（3）、总结各部门分析、展现之所需，形成原始的需求分析文档；（4）、基于需求并高于需求，建构数据仓库模型，从而形成数据源分析的主导、ETL过程的目标及前端展现的发源地。在调研过程中，要特别注意把握好关于数据的如下几方面需求： 数据需求：需要哪些种类的数据？哪些种类的数据是基础的，具有原子性，哪些种类的数据可以通过其他数据计算出来？原子数据的

12、覆盖面要广些，便于针对可变的需求进行扩展。 分析维度需求：对每一种数据，需要从哪些角度去分析、展现？应用发散性思维考虑得尽可能多些； 分析粒度需求：对每种数据的每一个分析维度，沿维的层次结构分析到何种级别、粒度？经过这样的调研后，我们对需求数据作了总结，归纳为如下几大类型：（1）、出现在几种财务报表中的财务数据，与特定公司相关，而往往与产品无关，常要求月度或以上级别的数据，直接按科目从总帐模块取得，比如资产、负债等；（2）、业务数据，与公司及产品相关，要求明细级数据（每天甚至小时），最好从各业务模块（如销售、采购等）所用的业务单据中取得，比如销售、采购、生产、库存、产品成本构成等；（3）、财务

13、指标类数据，与特定公司相关，往往与产品无关，月度或以上级别的数据，常通过计算得到。有了需求和目标，数据源分析的深入和细化就成为了可能。3、分析数据源：从何处获取数据？能获取怎样的数据？需求能否得到满足，还取决于数据源的状况。在前一阶段对集团各部门及其子公司进行需求调研所获得的数据需求基础上，对各子公司的数据源进行详细的分析就成为必要了。为此，我们将每个子公司的最新帐套收集上来，并以需求数据作为参照对象，以抽取需求数据作为目标，对每个帐套作了如下分析：（1）、需要怎样的数据？这些数据的分析维度有哪些？沿各维的分析粒度如何？（2）、各子公司的业务系统如何？数据如何存储（Excel？Access？M

14、S SQL SERVER？Oracle？等等）？（3）、如果用的是公司ERP系统，那么版本如何？已上了哪些模块或子系统？（4）、所需数据能否从数据源获得？是否有可能从数据源的多处获得？更具体的，是从哪些表的哪些字段获得？是否需要作必要的转换？（5）、抽取的数据能否满足维度分析的要求？如果不能满足，那么如何弥补？（6）、抽取的数据能否达到所需要的分析粒度？如果不能满足，该如何弥补？（7）、抽取数据的装载目标如何？为填充数据仓库事实表，还需要借助数据源中的哪些其他表的其他数据？在对每个子公司数据源进行详细分析的基础上，我们发现有如下基本问题需要解决：（1）、数据的抽取源选择及数据的一致性问题由于各

15、子公司的公司ERP系统版本多样，且仅上了部分模块或子系统，这就导致同样的数据在不同的子公司将可能从不同的地方或多个地方取到；（2）、科目的标准化及科目的对应问题因各子公司帐套的科目设置各不相同，而某些财务数据必须通过科目取得，这就需要解决科目的标准化问题。（3）、物料信息问题有的子公司直接将物料信息设置进了科目，需要建立科目物料的对应关系。（4）、数据的分析粒度问题由于某些子公司仅上了总帐等财务模块，而没有上业务模块，这就使得明细级的交易数据无法被记录到系统中，从而使某些数据的分析粒度达不到要求。经分析商讨，我们确定了如下关于取数的解决方案：如果该子公司上了销售、采购、生产、库存或成本等模块，

16、即系统记录下了明细级的交易数据，那么有关销售、采购、生产、库存或成本等类型的数据一律从相应模块所涉及的表中获取，即可满足分析维度和分析粒度的要求。否则，如果未上相应模块，那么就按科目从总帐模块对应的科目余额表及科目数量金额表中取得，此时分析维度将依赖于科目所挂的核算项目种类，而分析粒度则只能到月。如果仍无法取到，则只能考虑从WEB界面接口手工录入或从相关Excel表导入。各子公司详细的数据获取方案参见表。有了对源和目标的分析，那么就可以进入关于ETL部分最关键也最为重要的一步：映射模型的建构。4、基于数据源及数据仓库模型，建立从源到目标的映射模型：如何获取数据？在需求分析及数据源分析的基础上，

17、我们已得到了数据源与数据仓库目标间映射模型的轮廓，但还需进一步细化到各字段以及某些字段间的转换处理等。由于针对各子公司的数据获取方式有较大的差异，而且对每一种数据，在每个子公司中都存在多个可能的来源（这是需要同各子公司进行确认的）。因此，如果直接构建映射模型，会显得较为复杂，而且结构混乱，难以维护和扩展。为使最终构建出的映射模型尽量简化，层次尽可能清晰，我们采用分而治之的方法，对需求数据按各种方式进行了分类，以便进行合理的设计。首先将数据按分析主题分为六类：销售、采购、库存、生产、成本、财务数据。其中，财务数据又可分为财务报表数据和财务指标数据。其次，按数据来源或数据获取方式可分为三类：从各业

18、务模块所包含的相应单据中获取；从总帐财务模块按科目获取；从外部手工补录；与上述两类相应的，数据又可按分析上的时间粒度分为两类：业务日期粒度级别的数据：来自于业务单据；财务结算月度级别的数据：来自于财务科目余额表或科目数量金额表；应该说，对数据的后两种分类是因为当前集团各子公司的信息系统建设状况参差不齐所引起的，将随着集团及各子公司的发展而变化。最后，出于上述部分数据同预算比较的需要，还有部分预算数据需要手工录入。为此，基于上述分类及可扩展可维护的原则，我们在设计映射模型的时候也采用了分而治之的思想，同时为方便前端分析展现的设计，我们也对数据仓库结构作了相应调整。映射模型的总体描述如下表所示。主

19、题数据源目标销售销售量销售额销售单价业务销售发票销售事实表财务主营业务收入科目销售月度事实表销售成本发货量业务销售出库单发货事实表财务主营业务成本科目 或产品/商品科目发货月度事实表退货量退货额业务退货单（销售出入库单或采购单）退货事实表财务某科目（依子公司不同）退货月度事实表采购采购金额价格/量业务外购发票采购事实表财务原材料借方发生额/量采购月度事实表（库存）原材料产成品库存价值库存量业务存货表存货余额表即时库存快照表库存月度事实表财务原材料/产成品/商品科目库存月度事实表生产产值产量正品率得率业务产品入库单生产事实表财务产品科目生产月度事实表成本料工费及其构成业务成本计算单成本月度事实表

20、成本明细月度事实表财务生产成本科目生产-成本月份事实表财务报表数据财务相关科目科目余额事实表财务指标数据从财务报表数据计算得到 财务指标事实表每月水电煤能源构成每月由各子公司上报得到相应各子公司的各种类型数据的具体映射规则请见附件。5、元数据库模型的建构有了映射模型，似乎就可考虑ETL过程的实现了。其实不然！如果此时就考虑实现ETL过程，那就相当于将相关元数据信息直接蕴涵到了实现过程，这将不利于后续的维护和扩展。因此有必要基于映射模型及整个ETL的运作过程，设计专门的元数据库，以便在驱动ETL过程的同时，统一管理、维护这些元数据。基于上述对需求数据的各种分类以及集团及其各子公司数据源的实际情况

21、，我们设计的元数据将主要包含如下几类：（1）、编码对应规则方面的元数据这包括：科目对应规则（所有一级科目及二级费用科目；部分其他二级科目对应），处理各子公司间科目设置的不一致性问题；科目-物料对应规则（需从财务按科目取数，但物料信息被设置为科目的子公司），处理物料信息的统一、集成和一致性问题；（2）、映射规则方面的元数据这包括：从客户基础资料表中取客户信息的元数据；从供应商基础资料表中取供应商信息的元数据；从财务按科目取财务报表类数据的元数据；从财务按科目分别取销售、采购、库存、生产、成本类数据的元数据；从各类业务单据中分别取销售、采购、库存、生产、成本类数据的元数据；从OA系统中的各类表分别

22、取合同采购、预算以及手工录入的上述各主题数据的元数据；从外部Excel表中取帐龄、现金流量等数据的元数据；（3）、增量抽取方面的元数据记录上次抽取的终止位置，以便下次抽取时从该点继续。它可能直接被包含在映射规则元数据表中。（4）、元数据的维护管理与工作流调度规则方面的元数据这包括：子公司系统配置、数据源信息及其相关映射表信息方面的元数据；ETL过程工作流调度信息元数据； 基于ETL的设计原则以及上述映射模型，我们设计的元数据库模型如文档所示。另外，由于从数据源到数据仓库基本上都采用的是MS SQL SERVER 2000数据库管理系统，因此，我们也将用MS SQL SERVER 2000来实现

23、元数据库。详细元数据表结构及其描述见附件。三、开发/实现1、元数据驱动下的ETL基本实现在需求数据及数据源分析的基础上，我们已经完成了元数据库模型的设计，而要真正实现数据的抽取、转换、装载过程，还需作相应的编程实现，这也正是本节的主题。（1）、元数据的配置 针对各数据源，按元数据库模型设计的基本要求将数据源信息、映射规则信息、编码对应规则等等装入到元数据库。（2）、实现方式选择 鉴于集团的实际情况，可选用三种方式来实现元数据驱动ETL的编程： A：通过DTS，并借助ActiveX脚本的编程来实现； B：完全通过VB应用来实现数据的抽取、转换、装载； C：DTS与VB的结合。 方式A：基于图形G

24、UI界面，方便快速，易于管理维护，但功能及灵活性上可能会受到影响；方式B：功能强大，实现灵活，但开发工作量较大，项目进度会受到影响，并且日后的维护成本会较大； 方式C：一般情况下，应是一种较好的选择，但对性能会有一定的影响。 综合考虑上述各种因素及客户方的成员构成，拟采用方式A进行开发。（3）、ETL基本流程及简述增量抽取维度数据这里的维度数据包括：主营业务科目、客户、供应商、仓库、物料、车间部门等维度数据。其中，主营业务科目维度数据是直接将ERP账套的科目表的主营业务科目部分平面化即可。而其他维度数据则从基础资料表中按不同的基础资料类别取得，其中的ERP内码设为增量标志。相关数据源及目标信息

25、、增量标志值均存放于元数据表中。将基本事实数据增量装载到分段区表将年月值作为所有事实数据抽取的增量标志。这里的基本事实数据类型包括：销售、采购、库存、生产成本、出入库（从而发货、生产入库、退货、采购入库等）、科目余额（财务类数据）等。由于不涉及到清洗、转换及与维度表的关联，以及增量标志的运用，因此装载到分段区表的过程是快速高效并无误的。对分段区数据进行必要的清洗、转换借助元数据表中配置的各子公司的特定抽取、转换要求，修改分段区表中的数据，并关联到相应的维度表。将清洗、转换后的分段区数据装载到数据仓库由于不再需作任何修改，装载到数据仓库的过程是快速高效无误的。构建数据集市对抽取到数据仓库中的基本

26、事实数据，按分析的（性能、功能方面）要求，汇总到若干数据集市，这一过程由于有元数据的配置，也是增量进行的。（4）、DTS开发及其调用层次结构根据项目计划，目前需要从集团下属的八个子公司中抽取各种数据到集团的数据仓库中供分析监控系统采用。这里就涉及到一个问题：是针对各子公司分别进行DTS的开发，还是站在一个整体统一的高度寻求共性兼顾个性循环处理？前者的特点在于简单，不需要较多的元数据，也不需要考虑多个子公司的不同情形所带来的复杂性，但可移植性差，重用度不高，且过多的DTS也会带来维护、管理的较大工作量（一个子公司约需要10个左右的DTS，八个子公司就是80个左右，而且这个数字还会随着子公司的增加

27、而不断膨胀！）。后者的特点在于基于各子公司的共性并兼顾个性，由元数据统一驱动循环进行抽取。尽管初期的元数据提炼、分析、设计较为复杂并费时，但一旦设计好就事半功倍，可移植、可重用、可扩展。为此，通过对各子公司的整体分析及抽象，并充分考虑到某些子公司业务的特殊性，我们开发设计了如下40个DTS。DTS名称DTS功能描述适用范围ETL_A_Whole全局DTS。整个ETL过程由该DTS启动，它也是唯一的自动调度对象。ETL_A_Whole_1维度抽取DTS调度点。由它开始启动维度数据抽取的DTS。ETL_A_Whole_2基本事实抽取DTS调度点。由它开始启动基本事实数据抽取的DTS。ETL_A_W

28、hole_3数据集市抽取DTS调度点。由它开始启动汇总到数据集市的DTS。ETL_Dimension_Item它将分别启动如下DTS：Extract_Dimension_CustomerExtract_Dimension_Dept Extract_Dimension_MaterialExtract_Dimension_Stock Extract_Dimension_Supplier维度数据增量抽取DTS。其中包括了客户、供应商、车间部门、仓库及物料维度的抽取。ETL_Dimension_MainSale主营业务收入科目结构抽取DTS。这主要是为了方便管理者按帐套中所设定的主营业务科目结构浏览、

29、分析相关数据。上了财务总账模块的子公司ETL_AA_Aggregation一般性数据集市，包括月度汇总、基本财务、业务数据的本年各期、上年同期及预算数据的集中地。ETL_AA_FinanceQuota财务指标类数据集市。ETL_AAA_Panorama企业状况月报数据集市，对ETL_AA_Aggregation中所产生的结果作必要的进一步汇总。ETL_AAAA_UpdateETLFlag修改增量抽取标志DTS。这使得每次只抽取部分数据，保证了抽取的性能及速度。考虑到各种因素（比如财务过帐）所带来的帐套中数据的滞后性，这里一般回溯三个月进行数据的抽取。ETL_Fact_AccountAge它同时

30、也将执行如下DTS：Extract_Fact_AccountAge账龄分析DTS。通过读取账龄维度表中的账龄段的设置，从而灵活、动态地进行账龄分析。上了应收、应付子系统。ETL_Fact_CostFromBill它同时也将执行如下DTS：Extract_Fact_CostFromBill产品成本构成DTS。从成本计算单中抽取产品的成本构成数据。上了成本模块ETL_Fact_CostFromBill_Gross它同时也将执行如下DTS：Extract_Fact_CostFromBill_Gross 产品成本构成明细DTS。从一系列与成本计算相关的表中抽取产品的成本构成明细数据。上了成本模块ETL_Fact_FromStockBill它同时也将执行如下DTS：Extract_Fact_FromStockBill出入库数据抽取DTS。它包括了出库（发货）、生产入库、退货、采购实际入库等数据的抽取。物流模块ETL_Fact_PurchaseFromBill它同时也将执行如下DTS：Extract_Fact_PurchaseFromBill采购发票数据抽取DTS。物流模块ETL_Fact_SaleFromBill它同时也将执行如下DTS：Extract_Fact_SaleFromBill销售发票数据抽取DTS。