ETL 文档

×××BI项目之ETL文档

×××项目组

2004-12-19

×××BI项目之ETL文档

目 录

一、ETL之概述

1、ETL是数据仓库建构/应用中的核心过程

2、ETL的体系结构

3、ETL的设计原则

二、×××数据仓库建构中的ETL：分析与设计

1、面临的问题

2、明确需求：需要哪些数据？

3、分析数据源：从何处获取数据？能获取怎样的数据？

4、基于数据源及数据仓库模型，建立从源到目标的映射模型：如何获取数据？

5、元数据库模型的建构

三、×××数据仓库建构中的ETL：开发/实现

1、元数据驱动下的ETL基本实现

2、关于与OA及×××传输工具接口的额外实现 3、元数据的配置、维护与管理

×××BI项目之ETL文档

一、ETL概述

1、ETL是数据仓库建构/应用中的核心过程

数据源具有多样性和可变性

数据仓库系统是在业务系统的基础上发展而来的，其内部存储的数据来自于事务处理的业务系统和外部数据源。因企业的业务系统是在不同时期、不同背景、面对不同应用、不同开发商等各种客观前提下建立的，其数据结构、存储平台、系统平台均存在很大的异构性。这导致企业内各源数据缺少统一的标准，因而其数据难以转化为有用的信息，原始数据的不一致性导致决策时其可信度的降低。

此外，随着企业的不断发展，既有的业务系统、业务流程以及相关的信息结构都可能会发生变化，这种变化将直接影响到后端数据仓库系统中的数据更新。如何有效的维护这种变化，尽量控制数据仓库刷新操作的成本，也是数据仓库建构中极为重要的一个问题。

ETL的核心功能定位

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

针对数据源的多样性和可变性，ETL通过对从数据源到目标数据仓库间的映射规则进行元数据级别上的建模，使得整个抽取、转换、装载过程在元数据驱动下能完全自动调度执行，同时也便于维护和扩展。

同时，因分析的需要，数据仓库中的数据要求是面向主题的，具有集成性、一致性和时间性，而所有这些都是在详细分析各种数据源，真正理解数据的业务含义基础上通过ETL过程来实现的。

×××BI项目之ETL文档

2、ETL的体系结构

简单的讲，ETL就是抽取、转换和装载，同时提供数据质量的管理，并且贯穿整个商务智能解决方案的全过程，完成整个系统的数据处理、调度、监控及元数据管理等。 其体系结构如下图所示：

Databases & filesTarget adaptersMeta dataimport/exportLoadAdministration &operations servicesTransformTransport servicesExtractDesignmanagerSource adaptersRuntimemeta dataservicesMeta datarepositoryDatabases& filesLegacyapplicationsETL Processing Framework

· Design manager: 提供一个图形化的映射环境，让开发者定义从源到目标的映射关系、

转换、处理流程。设计过程的各对象的逻辑定义存储在一个元数据资料库中。 · Meta data repository: 提供一个关于ETL设计和运行处理等相关定义、管理信息的元数据资料库。ETL引擎在运行时和其它应用都可参考此资料库中的元数据。 · Extract: 通过接口提取源数据，例如:ODBC、专用数据库接口和平面文件提取器。参照元数据来决定提取何处的数据和怎样提取。

· Transform: 开发者将提取的数据按照业务需要转换为目标数据结构，并实现汇总。 · Load: 加载经转换和汇总的数据到目标数据仓库中，可实现SQL或批量加载。 · Transport services: 利用网络协议(TCP/IP等)或文件协议(FTP等)，在源和目标系统之间移动数据，利用内存(Data Caches 等)在ETL处理各组件中移动数据。

· Administration and operation:可让管理员基于事件和时间进行调度、运行、监测ETL作业、管理错误信息、从失败中恢复和调节从源系统的输出。

×××BI项目之ETL文档

3、ETL的设计原则

由于企业在自身的发展及信息化建设过程中，业务种类、业务流程以及相伴随的信息结构将不断变化，这样就会导致多种平台的出现或多个信息系统同时或更替使用，使得数据分散存储，这样多种数据源（包括数据库管理系统）的现象也会出现，而且随着企业的继续发展，它们还可能继续变化。因此，如何对企业现有信息架构进行有效描述以驱动ETL的自动调度执行，以及如何维护与管理这种架构描述以应对未来一段时间内可能发生的变化，就显得至关重要了，而这方面的工作往往是通过设计元数据来完成的，它也是ETL设计过程所要解决的主要问题。

ETL的设计原则表现在如下几个方面：  元数据的定义：抽象与具体相结合的原则

元数据是关于数据的数据，它立足于一个较高的抽象层次对原始数据及其相关特性进行描述，以便这些原始数据的使用者只要查看这些元数据就能知道如何使用或操作这些原始数据。这样，原始数据的使用者就不必绑定于特定的原始数据，而是分离开来，依靠元数据驱动，从而达到集成各种不同数据源的目的。这也是抽象带来的好处。但应注意把握抽象的度，根据特定情况在某些方面适当具体化，以便 在实际应用中提高性能，同时尽量避免对原始数据使用者的修改维护。  元数据的格式或描述语言：开放性与可移植性的原则

一般，用XML等作为元数据的表现形式，以对其他数据进行描述，从而便于元数据间的交换，但对XML的解析会有额外的性能消耗，因此也可根据具体情况，选择其他方式来作为元数据的载体。

 元数据的变化：可维护性与可扩展性的原则

首先，这里元数据的变化是指元数据内容的变化，它反映着原始数据结构的变化。这样，由于原始数据的使用者已同原始数据分离，从而通过改变元数据的内容就避免了对原始数据使用者的改变，使得整个过程可配置，方便维护。  元数据驱动的ETL过程：可跟踪性原则

应采用日志文件记录抽取过程所作的操作，便于诊断、跟踪数据抽取过程，保证抽取数据的准确、抽取过程的优化。  与其他工具或系统的可集成性原则

应留有一定的接口，便于其他工具或系统调用。

×××BI项目之ETL文档

二、×××数据仓库建构中的ETL：分析与设计

1、面临的问题

项目启动之前，经初步了解，×××集团及各分公司的信息系统建设状况所呈现的问题表现在如下几个方面：

（1）、信息系统建设、使用方面

除八家子公司外，集团所属其他分公司根本还未用上合适的信息系统； 八家子公司尽管都已上了ERP系统，但使用程度参差不齐，大都仅使用了部分模块或子系统；

（2）、基础资料方面

物料编码不统一，物料信息不齐全； 科目编码不一致； （3）、数据分析需求方面

集团各部门对数据的分析需求各不相同。

基于BI项目的特点及×××集团的实际情况，我们确定了如下的ETL设计过程，使得整个项目进展有条不紊、衔接有序。

2、明确需求：需要哪些数据？

由于各业务系统中有许多数据是专为操作型业务服务和使用的，并不为分析所用，也不一定具有分析的价值，因此并非业务系统中的所有数据都需要作ETL，而是按需提取，并使这一过程可配置可扩展，这样也使得整个ETL过程在可行性及性能上得到了保证。 按需提取，需从何来？严格来讲，它应该仅仅来自于数据仓库之所需。因此，数据仓库模型的建构就必须首先执行。为此，我们在实践中总结出了如下针对BI项目的富有特色的需求调研过程：

（1）、初步的需求调研：集团各部门需要看到什么？需要作哪些分析？大致分析到怎样的细节程度？等等。

这期间也可进行初步的数据源状况的调查分析，以为后一阶段详细深入的数据源分析作铺垫，但因缺乏明确的目标，不可能深入进行。

（2）、根据初步的展现需求，设计图形化的展现DEMO，“秀”给各部门负责人/领到看，征求修改意见及建议，推动需求向纵深发展；

（3）、总结各部门分析、展现之所需，形成原始的需求分析文档；

（4）、基于需求并高于需求，建构数据仓库模型，从而形成数据源分析的主导、ETL过程的目标及前端展现的发源地。

×××BI项目之ETL文档

在调研过程中，要特别注意把握好关于数据的如下几方面需求：

 数据需求：需要哪些种类的数据？哪些种类的数据是基础的，具有原子性，哪

些种类的数据可以通过其他数据计算出来？原子数据的覆盖面要广些，便于针对可变的需求进行扩展。  分析维度需求：对每一种数据，需要从哪些角度去分析、展现？应用发散性思

维考虑得尽可能多些；  分析粒度需求：对每种数据的每一个分析维度，沿维的层次结构分析到何种级

别、粒度？ 经过这样的调研后，我们对需求数据作了总结，归纳为如下几大类型：

（1）、出现在几种财务报表中的财务数据，与特定公司相关，而往往与产品无关，常要求月度或以上级别的数据，直接按科目从总帐模块取得，比如资产、负债等； （2）、业务数据，与公司及产品相关，要求明细级数据（每天甚至小时），最好从各业务模块（如销售、采购等）所用的业务单据中取得，比如销售、采购、生产、库存、产品成本构成等；

（3）、财务指标类数据，与特定公司相关，往往与产品无关，月度或以上级别的数据，常通过计算得到。

有了需求和目标，数据源分析的深入和细化就成为了可能。

3、分析数据源：从何处获取数据？能获取怎样的数据？

需求能否得到满足，还取决于数据源的状况。在前一阶段对集团各部门及其子公司进行需求调研所获得的数据需求基础上，对各子公司的数据源进行详细的分析就成为必要了。为此，我们将每个子公司的最新帐套收集上来，并以需求数据作为参照对象，以抽取需求数据作为目标，对每个帐套作了如下分析：

（1）、需要怎样的数据？这些数据的分析维度有哪些？沿各维的分析粒度如何？

（2）、各子公司的业务系统如何？数据如何存储（Excel？Access？MS SQL SERVER？Oracle？等等）？

（3）、如果用的是×××公司ERP系统，那么版本如何？已上了哪些模块或子系统？ （4）、所需数据能否从数据源获得？是否有可能从数据源的多处获得？更具体的，是从哪些表的哪些字段获得？是否需要作必要的转换？

（5）、抽取的数据能否满足维度分析的要求？如果不能满足，那么如何弥补？ （6）、抽取的数据能否达到所需要的分析粒度？如果不能满足，该如何弥补？

（7）、抽取数据的装载目标如何？为填充数据仓库事实表，还需要借助数据源中的哪些其他表的其他数据？

在对每个子公司数据源进行详细分析的基础上，我们发现有如下基本问题需要解决：

×××BI项目之ETL文档

（1）、数据的抽取源选择及数据的一致性问题

由于各子公司的×××公司ERP系统版本多样，且仅上了部分模块或子系统，这就导致同样的数据在不同的子公司将可能从不同的地方或多个地方取到； （2）、科目的标准化及科目的对应问题

因各子公司帐套的科目设置各不相同，而某些财务数据必须通过科目取得，这就需要解决科目的标准化问题。 （3）、物料信息问题

有的子公司直接将物料信息设置进了科目，需要建立科目－物料的对应关系。 （4）、数据的分析粒度问题

由于某些子公司仅上了总帐等财务模块，而没有上业务模块，这就使得明细级的交易数据无法被记录到系统中，从而使某些数据的分析粒度达不到要求。 经分析商讨，我们确定了如下关于取数的解决方案：

如果该子公司上了销售、采购、生产、库存或成本等模块，即系统记录下了明细级的交易数据，那么有关销售、采购、生产、库存或成本等类型的数据一律从相应模块所涉及的表中获取，即可满足分析维度和分析粒度的要求。否则，如果未上相应模块，那么就按科目从总帐模块对应的科目余额表及科目数量金额表中取得，此时分析维度将依赖于科目所挂的核算项目种类，而分析粒度则只能到月。如果仍无法取到，则只能考虑从WEB界面接口手工录入或从相关Excel表导入。

各子公司详细的数据获取方案参见表。

有了对源和目标的分析，那么就可以进入关于ETL部分最关键也最为重要的一步：映射模型的建构。

4、基于数据源及数据仓库模型，建立从源到目标的映射模型：如何获取数据？

在需求分析及数据源分析的基础上，我们已得到了数据源与数据仓库目标间映射模型的轮廓，但还需进一步细化到各字段以及某些字段间的转换处理等。

由于针对各子公司的数据获取方式有较大的差异，而且对每一种数据，在每个子公司中都存在多个可能的来源（这是需要同各子公司进行确认的）。因此，如果直接构建映射模型，会显得较为复杂，而且结构混乱，难以维护和扩展。为使最终构建出的映射模型尽量简化，层次尽可能清晰，我们采用分而治之的方法，对需求数据按各种方式进行了分类，以便进行合理的设计。

首先将数据按分析主题分为六类：销售、采购、库存、生产、成本、财务数据。 其中，财务数据又可分为财务报表数据和财务指标数据。 其次，按数据来源或数据获取方式可分为三类：

×××BI项目之ETL文档

从各业务模块所包含的相应单据中获取； 从总帐财务模块按科目获取； 从外部手工补录；

与上述两类相应的，数据又可按分析上的时间粒度分为两类： 业务日期粒度级别的数据：来自于业务单据；

财务结算月度级别的数据：来自于财务科目余额表或科目数量金额表；

应该说，对数据的后两种分类是因为当前×××集团各子公司的信息系统建设状况参差不齐所引起的，将随着集团及各子公司的发展而变化。

最后，出于上述部分数据同预算比较的需要，还有部分预算数据需要手工录入。

为此，基于上述分类及可扩展可维护的原则，我们在设计映射模型的时候也采用了分而治之的思想，同时为方便前端分析展现的设计，我们也对数据仓库结构作了相应调整。 映射模型的总体描述如下表所示。

×××BI项目之ETL文档

主题 数据 销售量 销售额 销售单价 销售 销售成本 发货量 退货量 退货额 采购 采购金额价格/量 源 业务 销售发票 目标 销售事实表 销售月度事实表 发货事实表 发货月度事实表 退货事实表 退货月度事实表 采购事实表 采购月度事实表 即时库存快照表 库存月度事实表 库存月度事实表 生产事实表 生产月度事实表 成本月度事实表 成本明细月度事实表 生产-成本月份事实表 科目余额事实表 财务指标事实表 财务 主营业务收入科目 业务 财务 业务 财务 业务 财务 业务 财务 业务 销售出库单 主营业务成本科目 或 产品/商品科目 退货单（销售出入库单或采购单） 某科目（依子公司不同） 外购发票 原材料借方发生额/量 存货表 存货余额表 原材料/产成品/商品科目 产品入库单 （库存） 库存价值 原材料 库存量 产成品 产值 产量 正品率 得率 料工费及其构成 生产 财务 产品科目 成本 业务 财务 财务 成本计算单 生产成本科目 相关科目 财务报表数据 财务指标数据 从财务报表数据计算得到 每月水电煤能源构成 每月由各子公司上报得到

相应各子公司的各种类型数据的具体映射规则请见附件。

×××BI项目之ETL文档

5、元数据库模型的建构

有了映射模型，似乎就可考虑ETL过程的实现了。其实不然！

如果此时就考虑实现ETL过程，那就相当于将相关元数据信息直接蕴涵到了实现过程，这将不利于后续的维护和扩展。因此有必要基于映射模型及整个ETL的运作过程，设计专门的元数据库，以便在驱动ETL过程的同时，统一管理、维护这些元数据。

基于上述对需求数据的各种分类以及×××集团及其各子公司数据源的实际情况，我们设计的元数据将主要包含如下几类： （1）、编码对应规则方面的元数据

这包括：

科目对应规则（所有一级科目及二级费用科目；部分其他二级科目对应），处理各子公司间科目设置的不一致性问题；

科目-物料对应规则（需从财务按科目取数，但物料信息被设置为科目的子公司），处理物料信息的统一、集成和一致性问题； （2）、映射规则方面的元数据

这包括：

从客户基础资料表中取客户信息的元数据； 从供应商基础资料表中取供应商信息的元数据； 从财务按科目取财务报表类数据的元数据；

从财务按科目分别取销售、采购、库存、生产、成本类数据的元数据； 从各类业务单据中分别取销售、采购、库存、生产、成本类数据的元数据；

从OA系统中的各类表分别取合同采购、预算以及手工录入的上述各主题数据的元数据；

从外部Excel表中取帐龄、现金流量等数据的元数据； （3）、增量抽取方面的元数据

记录上次抽取的终止位置，以便下次抽取时从该点继续。它可能直接被包含在映射规则元数据表中。

（4）、元数据的维护管理与工作流调度规则方面的元数据

这包括：

子公司系统配置、数据源信息及其相关映射表信息方面的元数据； ETL过程工作流调度信息元数据；

基于ETL的设计原则以及上述映射模型，我们设计的元数据库模型如文档所示。

×××BI项目之ETL文档

另外，由于从数据源到数据仓库基本上都采用的是MS SQL SERVER 2000数据库管理系统，因此，我们也将用MS SQL SERVER 2000来实现元数据库。 详细元数据表结构及其描述见附件。

×××BI项目之ETL文档

三、开发/实现

1、元数据驱动下的ETL基本实现

在需求数据及数据源分析的基础上，我们已经完成了元数据库模型的设计，而要真正实现数据的抽取、转换、装载过程，还需作相应的编程实现，这也正是本节的主题。 （1）、元数据的配置

针对各数据源，按元数据库模型设计的基本要求将数据源信息、映射规则信息、编码对

应规则等等装入到元数据库。 （2）、实现方式选择

鉴于×××集团的实际情况，可选用三种方式来实现元数据驱动ETL的编程： A：通过DTS，并借助ActiveX脚本的编程来实现； B：完全通过VB应用来实现数据的抽取、转换、装载； C：DTS与VB的结合。

方式A：基于图形GUI界面，方便快速，易于管理维护，但功能及灵活性上可能会受

到影响；

方式B：功能强大，实现灵活，但开发工作量较大，项目进度会受到影响，并且日后的维护成本会较大；

方式C：一般情况下，应是一种较好的选择，但对性能会有一定的影响。 综合考虑上述各种因素及客户方的成员构成，拟采用方式A进行开发。

×××BI项目之ETL文档

（3）、ETL基本流程及简述

数据源（数据采集端）元数据数据仓库DTS（抽取维度数据）DTS（抽取事实数据）汇总到数据集市定时调度执行ETL 过 程 简 图

增量抽取维度数据

这里的维度数据包括：主营业务科目、客户、供应商、仓库、物料、车间部门等维度数据。 其中，主营业务科目维度数据是直接将ERP账套的科目表的主营业务科目部分平面化即可。而其他维度数据则从基础资料表中按不同的基础资料类别取得，其中的ERP内码设为增量标志。相关数据源及目标信息、增量标志值均存放于元数据表中。

将基本事实数据增量装载到分段区表

将年月值作为所有事实数据抽取的增量标志。

这里的基本事实数据类型包括：销售、采购、库存、生产成本、出入库（从而发货、生产入库、退货、采购入库等）、科目余额（财务类数据）等。

由于不涉及到清洗、转换及与维度表的关联，以及增量标志的运用，因此装载到分段区表的过程是快速高效并无误的。

对分段区数据进行必要的清洗、转换

借助元数据表中配置的各子公司的特定抽取、转换要求，修改分段区表中的数据，并关联到相应的维度表。

将清洗、转换后的分段区数据装载到数据仓库

由于不再需作任何修改，装载到数据仓库的过程是快速高效无误的。

构建数据集市

对抽取到数据仓库中的基本事实数据，按分析的（性能、功能方面）要求，汇总到若干数据集市，这一过程由于有元数据的配置，也是增量进行的。

×××BI项目之ETL文档

（4）、DTS开发及其调用层次结构

根据项目计划，目前需要从集团下属的八个子公司中抽取各种数据到集团的数据仓库中供分析监控系统采用。

这里就涉及到一个问题：是针对各子公司分别进行DTS的开发，还是站在一个整体统一的高度寻求共性兼顾个性循环处理？前者的特点在于简单，不需要较多的元数据，也不需要考虑多个子公司的不同情形所带来的复杂性，但可移植性差，重用度不高，且过多的DTS也会带来维护、管理的较大工作量（一个子公司约需要10个左右的DTS，八个子公司就是80个左右，而且这个数字还会随着子公司的增加而不断膨胀！）。后者的特点在于基于各子公司的共性并兼顾个性，由元数据统一驱动循环进行抽取。尽管初期的元数据提炼、分析、设计较为复杂并费时，但一旦设计好就事半功倍，可移植、可重用、可扩展。

为此，通过对各子公司的整体分析及抽象，并充分考虑到某些子公司业务的特殊性，我们开发设计了如下40个DTS。

DTS名称 ETL_A_Whole DTS功能描述 全局DTS。整个ETL过程由该DTS启动，它也是唯一的自动调度对象。 ETL_A_Whole_1 维度抽取DTS调度点。由它开始启动维度数据抽取的DTS。 ETL_A_Whole_2 基本事实抽取DTS调度点。由它开始启动基本事实数据抽取的DTS。 ETL_A_Whole_3 数据集市抽取DTS调度点。由它开始启动汇总到数据集市的DTS。 ETL_Dimension_Item 它将分别启动如下DTS： Extract_Dimension_Customer Extract_Dimension_Dept Extract_Dimension_Material Extract_Dimension_Stock Extract_Dimension_Supplier ETL_Dimension_MainSale 主营业务收入科目结构抽取DTS。这主要是为了方便管理者按帐套中所设定的主营业务科目结构浏览、分析相关数据。 ETL_AA_Aggregation 一般性数据集市，包括月度汇总、基本财务、业务数据的本年各期、上年同期 上了财务总账模块的子公司 维度数据增量抽取DTS。其中包括了 客户、供应商、车间部门、仓库及物料维度的抽取。 适用范围 ×××BI项目之ETL文档

及预算数据的集中地。 ETL_AA_FinanceQuota ETL_AAA_Panorama 财务指标类数据集市。 企业状况月报数据集市，对ETL_AA_Aggregation中所产生的结果作必要的进一步汇总。 ETL_AAAA_UpdateETLFlag 修改增量抽取标志DTS。这使得每次 只抽取部分数据，保证了抽取的性能及速度。考虑到各种因素（比如财务过帐）所带来的帐套中数据的滞后性，这里一般回溯三个月进行数据的抽取。 ETL_Fact_AccountAge 它同时也将执行如下DTS： Extract_Fact_AccountAge ETL_Fact_CostFromBill 它同时也将执行如下DTS： Extract_Fact_CostFromBill ETL_Fact_CostFromBill_Gross 它同时也将执行如下DTS： Extract_Fact_CostFromBill_Gross ETL_Fact_FromStockBill 它同时也将执行如下DTS： Extract_Fact_FromStockBill ETL_Fact_PurchaseFromBill 它同时也将执行如下DTS： Extract_Fact_PurchaseFromBill ETL_Fact_SaleFromBill 它同时也将执行如下DTS： Extract_Fact_SaleFromBill ETL_Fact_SaleHuiLong 它同时也将执行如下DTS： Extract_Fact_SaleHuilong 销售回笼数据抽取DTS。 应收应付子系统 销售发票数据抽取DTS。 物流模块 采购发票数据抽取DTS。 物流模块 出入库数据抽取DTS。它包括了出库（发货）、生产入库、退货、采购实际入库等数据的抽取。 物流模块 产品成本构成明细DTS。从一系列与成本计算相关的表中抽取产品的成本构成明细数据。 上了成本模块 产品成本构成DTS。从成本计算单中抽取产品的成本构成数据。 上了成本模块 账龄分析DTS。通过读取账龄维度表中的账龄段的设置，从而灵活、动态地进行账龄分析。 上了应收、应付子系统。 ×××BI项目之ETL文档

ETL_Fact_StockFromBill 它同时也将执行如下DTS： Extract_Fact_StockFromBill ETL_Fact_StockSnapshot 它同时也将执行如下DTS： Extract_Fact_StockSnapshot ETL_Fact_FinanceAccount 它同时也将执行如下DTS： Extract_Fact_FinanceAccount Extract_Fact_FinanceAccount_Lianggou Extract_Fact_FinanceAccount_Youzhi Extract_Fact_TurnInOut_Init Extract_Fact_TurnInOut_Lianggou Extract_Fact_TurnInOut_Youzhi

轮进轮出数据抽取DTS。用于粮食购销及油脂公司。 总账模块 财务报表类数据及从财务科目抽取业务数据的DTS。其中，Extract_Fact_FinanceAccount_Lianggou专用于粮食购销；Extract_Fact_FinanceAccount_Youzhi专用于油脂公司 总账模块 库存快照抽取DTS。从存货表中抽取。 仓存子系统 库存数据抽取DTS。从存货余额表中抽取。 仓存子系统 ×××BI项目之ETL文档

DTS调用层次结构图如下：

×××BI项目之ETL文档

2、关于与OA及×××传输工具接口的额外实现

（1）、与×××OA系统的接口

由于如下两个原因，×××BI系统需要与×××OA系统进行数据交流： A、BI系统需要的若干子公司的部分数据需从OA系统录入；

B、各子公司新增物料的若干物料属性需子公司用户在OA端补录或确认；

为此，针对A，由于我们采用了元数据驱动ETL的方式，故只需要作适当额外的配置，就可解决；针对B，我们对现有ETL的物料维度数据抽取的DTS作了一定的扩展，并同OA系统共享一张用于交换物料信息数据的表，从而实现整个自动抽取过程。

新增物料分段区表

功能描述

名称 代码 注释 新增物料分段区表 T_BI2OA_AddMaterial 说明： 该表用于同OA方交换新增物料信息，存放于OA方数据库。其工作过程如下： BI方：在ETL抽取过程中发现有新增物料时，将新增物料信息写入该表，并置修改标志位为0（表示未修改）；同时将标志位为1的记录信息更新到数据仓库表，并从该表删除这样的记录； OA方：从该表中选取修改标志位为0的记录信息，提示用户修改物料的分类信息并更新回该表，置标志位为1； 物料类型包括： 产成品、原材料、原材料-产成品、包装物、其他类等； 物料明细标志字段：大类、中类、小类、明细； 是否定牌产品： 若是，则为定牌名称；否则为空； 是否大宗原料： 1－是；0－否；-1－非原料 修改标志位： 1－已修改完成；0－正在修改中；-1－还未开始修改

详细字段信息参见分段区模型。

×××BI项目之ETL文档

（2）、与×××传输工具服务器的接口

由于各种原因（如网络故障、子公司的人为关机、服务器自身的问题等），×××传输工具服务器上所接受的各子公司账套可能未能正确恢复而导致部分账套不能存取或使用，这会直接波及到ETL过程顺利执行。为了不至于因某个或某几个账套的问题而导致整个ETL过程的失败，我们在真正启动ETL过程之前，先执行一个用于测试账套可连接性的DTS，然后仅对可连接的账套执行ETL过程，这既维护了抽取数据的完整性，又提高了ETL过程的鲁棒性。

包名：TestConnection

功能：循环遍历表T_Ref_Database（子公司及其账套对应表）中的数据库连接，将连接状态（1为可连接，0为不可连接）保存，并用于后续的ETL过程。

说明：初始时，置所有数据库为可连接状态，然后进入循环，对每一数据库账套测试可连接性，若连接成功，则直接转下次循环；否则，修改该数据库账套的可连接状态，再转下次循环。

×××BI项目之ETL文档

3、元数据的配置、维护与管理

正如前文所述，随着企业的发展变化，业务流程从而信息流程及信息结构都会发生变化，这些变化就会导致数据源的变化，因而必须在元数据库中通过元数据来反映这种变化。 （1）、对元数据库模型（即元数据库中的各张表、表结构、字段及其描述）的理解；

理解是维护与管理的基础。 详细信息可参见元数据模型。 （2）、元数据的配置与维护

一般而言，最关键的元数据信息是关于数据源、数据目标、增量抽取标志值等信息。由于我们的数据源几乎都是ERP账套，因此可大大简化元数据模型，许多信息就不必要配置了。 下面便对相关配置信息作简要说明。

元数据表 必填字段： T_Map_AccountAge T_Map_Contract T_Map_Cost T_Map_Purchase T_Map_Sale T_Map_SaleHuiLong T_Map_Stock T_Map_StockInOut FOrganization_id：组织机构内码 FSServer：数据源服务器ip FDServer：数据仓库服务器ip FSDatabase：数据源账套名 FDDatabase：数据仓库账套名 FETLFlag_Year：ETL起始年 FETLFlag_Month：ETL起始月 FMonth_id：ETL起始月id 必填字段： FOrganization_id：组织机构内码 FSServer：数据源服务器ip FDServer：数据仓库服务器ip T_Map_Dimension FSDatabase：数据源账套名 FDDatabase：数据仓库账套名 FdimensionType：维度类型 （ FETLFlag： 初始配置时需要输入，为数据仓库相关维度表中的ERP内码值中之最大值，之后将由ETL过程自动更新。 说明 备注 FETLFlag_Yea FETLFlag_Month FMonth_id 初始配置时需要输入，之后将由ETL过程自动更新。 ×××BI项目之ETL文档 限于如下5个值： customer/supplier/material/stock/department ） FETLFlag：增量抽取标志 T_Map_FinanceAccount 必填字段： FOrganization_id：组织机构内码 FSServer：数据源服务器ip FDServer：数据仓库服务器ip FSDatabase：数据源账套名 FDDatabase：数据仓库账套名 FETLFlag_Year：ETL起始年 FETLFlag_Month：ETL起始月 FMonth_id：ETL起始月id FSMainSaleNumber： 主营业务收入一级科目代码 用于抽取主营业务收入； FSMainCostNumber： 主营业务成本一级科目代码 FSSLFeeNumber： 营业费用一级科目代码 用于抽取营业费用及其明细； FSMGFeeNumber： 管理费用一级科目代码 用于抽取管理费用及其明细； FSFAFeeNumber： 财务费用一级科目代码 用于抽取财务费用及其明细； FSARNumber： 应收账款一级科目代码 ×××BI项目之ETL文档 用于分析性抽取应收账款； FSAPNumber： 应付账款一级科目代码 用于分析性抽取应付账款； FSProfitNumber： 未分配利润科目代码； FSPARNumber： 预收账款一级科目代码 用于分析性抽取预收账款； FSPAPNumber 预收账款一级科目代码 用于分析性抽取预付账款； 可选填字段： FSIsToSale：如果为1，则从主营业务收入科目下抽取销售数据到月度销售事实表； FSIsToCost：如果为1，则从主营业务成本科目下抽取销售成本数据到月度发货事实表； FSMaterialNumber：原材料科目代码；如果填写，则从该科目下抽取月度采购、月度库存数据到相应事实表； FSProductNumber：产成品科目代码；如果填写，则从该科目下抽取月度发货、月度库存、月度生产数据到相应事实表； FSGoodsNumber：商品科目代码；如果填写，则从该科目下抽取月度采购、月度发货、月度库存数据到相应事实表； FSPackageNumber：包装物科目代码；如果填写，则从该科目下抽取月度采购、月度发货、月度库存数据到相应事实表； FSShortLoanNumber：短期贷款科目代码；如果填写，则从该科目下抽取银行名称； FSLongLoanNumber：长期贷款科目代码；如果填写，则从该科目下抽取银行名称； FSCostNumber：生产成本科目代码；如果填×××BI项目之ETL文档

写，则从该科目下抽取产品的生产成本到生产成本事实表； FSYuTiNumber，FSDaiTanNumber：预提、待摊费用科目代码；如果填写，则从该科目下分析性抽取预提、待摊费用； FSAROtherNumber、FSAPOtherNumber：其他应收、其他应付科目代码；如果填写，则从该科目下分析性抽取其他应收、其他应付科目代码； FSBuTieNumberS、FSBuTieNumberD：针对粮食购销公司中补贴收入的特殊处理，其他公司不填。 T_Ref_Account 必填字段： FOrganization_id：组织机构内码 FaccountNumber：ERP科目代码 FaccountName：ERP科目名称 FstdAccountNumber：标准科目代码 FstdAccountName：标准科目名称 T_Ref_Account-Material 必填字段： FOrganization_id：组织机构内码 FaccountNumber：ERP科目代码 FaccountName：ERP科目名称 FmaterialID：维度表中物料内码 FmaterialName：维度表中物料名称 FmaterialGranurity：粒度（明细、大类、中类、小类） T_Ref_GroupLGF 必填字段： FOrganization_id：组织机构内码 FisAccount：是否从科目中取成本项目 Ftype：值为“成本” FLGFFlag：料、工、费分组标志； FcostItemNumber：若从成本模块取，则为成本项目代码；若从生产成本科目下取，则为 ×××BI项目之ETL文档 成本项目科目代码 FcostItemName：成本项目名称； 可选填字段（当从生产成本科目下取时）： FparentNumber：父科目代码 FparentName：父科目名称 FsonNumber：子科目代码 FcostItemDetail：明细子科目名称 T_Ref_DataType 必填字段： fdatatype：数据类型值，从数据集市表T_Aggregation_HistoryBudget中的fdatatype字段取值中选择 T_Ref_Database 必填字段： FOrganization_id：组织机构内码 FSServer：数据源服务器ip FDServer：数据仓库服务器ip FSDatabase：数据源账套名 FDDatabase：数据仓库账套名 FSIsAvailable： 当前该账套是否可连接，由ETL过程自动填写。 ×××BI项目之ETL文档

附录：

元数据模型-数据表清单

名称 引用_科目-物料对应表 代码 T_Ref_Account-Material 该表的使用时机： 需要取销售、采购、生产、成本、库存等数据信息； 但是对应子公司没有上相应模块从而没有相应业务单据； 故需从财务按科目取 引用_科目对应表 T_Ref_Account 该表主要用于从数据源按财务科目抽取财务数据到数据仓库中科目余额事实表时作参考用，它将各子公司的科目与集团的标准科目建立了对应关系，使得数据仓库中的科目余额事实表与集团标准科目维表正确建立关联。 该表包含的对应关系有： 一级科目对应， 二级费用科目对应， 其他业务收入/支出明细科目对应， 未分配利润科目对应。 事实映射_财务数据表 T_Map_FinanceAccount 存放按财务科目抽取财务数据时所需要的信息。包括： 子公司、帐套名、源表、目标表、源/目标间的字段对应、其他相关表/字段等信息。 核算项目详情横表： F1－客户 F2－部门 F3－职员 F4－物料或商品 F8－供应商 核算项目详情纵表： fdetailid, fitemid, fitemclassid; 增量抽取标志：年度、期间； 余额表中大于等于该值者被抽取； 注释 ×××BI项目之ETL文档

事实映射_采购 T_Map_Purchase 从业务单据（外购发票）取采购数据。 增量抽取标志：年，月 事实映射_产品成本 T_Map_Cost 包含这样的元数据信息，它使得DTS知道从何处怎样取得各产成品的成本构成信息。 成本项目信息及料、工、费的组成。 增量抽取标志：年，月 事实映射_库存 T_Map_Stock 包含元数据信息，以便DTS获取信息来抽取库存数据信息。 从存货表、存货余额表取数。包括： 子公司、帐套、即时库存表及其中的字段、存货余额表及其中的字段、物料等。 增量抽取标志字段：年，月 事实映射_销售 T_Map_Sale 从业务单据（销售发票）取采购数据。 增量抽取标志：年，月 引用_料工费分组 T_Ref_GroupLGF 按不同的公司定义产成品的成本结构：料、工、费的构成。 其中： 是否按科目FIsAccount:标明是从财务科目定义还是从成本计算单（以及成本对象、成本项目表）的角度来定义料、工、费的组成。 父科目代码：这里指产成品科目代码； 子科目代码：这里指成本项目的科目代码。 料工费标志：“料”，“工”，“费” 维度映射 T_Map_Dimension 包含账套连接等信息； 要增量抽取的维度类型； 增量抽取标志：维度所属基础资料在原ERP系统基础资料表中的内码值。 Excel导入_路径映射表 T_Link_ExcelPath 存放各子公司的报表Excel文件的相关信息，包括报表类型、Excel存放路径等。 ×××BI项目之ETL文档

事实映射_出入库发退货生产 T_Map_StockInOut 从业务单据（出入库单）取发、退货数据。 增量抽取标志：年，月 ICStockBill中的FTranType的取值： 2－产成品入库（生产入库）； 21－销售出库单（销售出库）－蓝字：frob=1；此时用于抽取发货数据 21－销售退货单（退货）－红字：frob= -1；此时用于抽取退货数据 事实映射_销售回笼 从业务单据（类型为销售回款的收款单）取销售回笼数据。 其中，在新收款单t_rp_NewReceiveBill中，famount、freceiveAmount 1、标明收款类型的字段，在新旧系统不同，譬如福新和海狮就不同； 2、当为退款（由字段fpre标志）时，famount为负值，而freceiveAmount总为正值。 3、是否应收应付： frp= 1－应收； frp= 0－应付； 增量抽取标志：年，月 事实映射_采购合同 事实映射_账龄分析 引用_企业状况月报数据类型表 引用_子公司帐套信息表 T_Ref_Database T_Map_PurchaseContract 增量抽取标志：年，月 T_Map_AccountAge T_Ref_DataType 增量标志：年 用于动态配置企业状况月报数据集市中所应包含的数据类型 包含各子公司及其账套信息，以及ETL时是否可连接等信息。 T_Map_SaleHuiLong