数据仓库建模与ETL的实践技巧.ppt

《数据仓库建模与ETL的实践技巧.ppt》由会员分享，可在线阅读，更多相关《数据仓库建模与ETL的实践技巧.ppt（57页珍藏版）》请在三一办公上搜索。

1、BI，数据仓库（DW）建模与ETL的实践技巧,作者：MALONE日期：2009-02-20,主要内容(一),一、什么是BI（Business Intelligence）(一)、什么是BI的定义？(二)、BI的诞生？(三)、基本技术(四)、数据处理(五)、数据展现(六)、常用的BI 厂商和产品,主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基

2、本情况？几种情况的利弊？(五)、创建事实表,主要内容(三),四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍,主要内容(一),一、什么是BI（Business Intelligence）(一)、什么是BI的

3、定义？(二)、BI的诞生？(三)、基本技术(四)、数据处理(五)、数据展现(六)、常用的BI 厂商和产品,一、什么是BI（Business Intelligence）,(一)、什么是BI（Business Intelligence）a、什么是BI的定义？BI是Business Intelligence的英文缩写，中文解释为商务智能，用来帮助企业更好地利用数据提高决策质量的技术集合，是从大量的数据中钻取信息与知识的过程。简单讲就是业务、数据、数据价值应用的过程。用图解的方式可以理解为下图：,这样不难看出，传统的交易系统完成的是Business到Data的过程，而BI要做的事情是在Data的基础上

4、，让Data产生价值，这个产生价值的过程就是Business Intelligence analyse的过程。,一、什么是BI（Business Intelligence）,b、如何实现Business Intelligence analyse的过程 从技术角度来说，是一个复杂的技术集合，它包含ETL、DW、OLAP、DM等多环节，基本过程可用下图描述。,上图流程，简单的说就是把交易系统已经发生过的数据，通过ETL工具抽取到主题明确的数据仓库中，OLAP后生成Cube或报表，透过Portal展现给用户，用户利用这些经过分类（Classification）、聚集（Clustering）、描述和可

5、视化（Description and Visualization）的数据，支持业务决策。,一、什么是BI（Business Intelligence）,说明：BI不能产生决策，而是利用BI过程处理后的数据来支持决策。哪么BI所谓的智能到底是什么呢？（理清这个概念，有助于对BI的应用。）BI最终展现给用户的信息就是报表或图视，但它不同于传统的静态报表或图视，它颠覆了传统报表或图视的提供与阅读的方式，产生的数据集合就象玩具“魔方”一样，可以任意快速的旋转组合报表或图视，有力的保障了用户分析数据时操作的简单性、报表或图视直观性及思维的连惯性。,实例:c、商业智能(BI)在电信行业所处的商业作用和价值

6、？(什么是电信BI)BI应用在电信行业又称作电信经营分析系统。随着竞争的加剧和数据业务的发展，电信业BI市场将进入一个高速发展的时期。在电信市场竞争愈演愈烈的背景下，各个运营商已经从业务为中心向以服务为中心转变，原来赖以竞争的手段如价格战、行业垄断优势、促销策略等以无法适应新形势的需要。为了保住客户资源，运营商需要一套业务分析支持系统，以从自身市场数据中获得能够真正反映企业运营状况的有效信息，从而为市场经营决策提供科学支持，商业智能(BI)所处的商业作用和价值显而易见。,主要内容(一),一、什么是BI（Business Intelligence）(一)、什么是BI的定义？(二)、BI的诞生？(

7、三)、基本技术(四)、数据处理(五)、数据展现(六)、常用的BI 厂商和产品,一、什么是BI（Business Intelligence）,(二)、BI的诞生？典型的案例有超市，便利店“尿片和啤酒”的故事,(三)、基本技术 a、BI(Business Intelligence)是一种运用了数据仓库、在线分析和数据挖掘等技术来处理和分析数据的崭新技术，目的:是为企业决策者提供决策支持。b、哪么BI技术涉及了哪些方面呢？其核心技术中ETL、DW及OLAP。或者说是“数据处理技术”与“数据展现技术”更加容易理解。c、为什么要在操作型数据库和 OLAP 之间加一层“数据仓库”呢？花空间买时间(资源与效

8、能的问题)说一千道一万都计算机资源与效能惹的祸，操作型数据库以快速响应业务为主要目标，而OLAP的时候要占用大量的硬件资源，在OLAP的时候，业务操作很难快速响应，无法保证业务的顺利进行，从业务、数据、数据的价值的逻辑来看，没有业务就谈不上OLAP；零星分散的数据一般存在有多个应用，对应多个业务操作型数据库，访问效能极其低下。最高效的方法：就是将数据先整合到数据仓库中，而 由OLAP应用统一从数据仓库里取数，以解决快速响应业务与OLAP的矛盾。,主要内容(一),一、什么是BI（Business Intelligence）(一)、什么是BI的定义？(二)、BI的诞生？(三)、基本技术(四)、数据

9、处理(五)、数据展现(六)、常用的BI 厂商和产品,一、什么是BI（Business Intelligence）,上图流程，简单的说就是把交易系统已经发生过的数据，通过ETL工具抽取到主题明确的数据仓库中，OLAP后生成Cube或报表，透过Portal展现给用户，用户利用这些经过分类（Classification）、聚集（Clustering）、描述和可视化（Description and Visualization）的数据，支持业务决策。,DB2、Informix,Oracle,Sysbase,其他数据结构类型,一、什么是BI（Business Intelligence）,(四)、数据处理(

10、1)、ODS 层的作用？1)、在业务系统和数据仓库之间的数据过渡离层。如果业务数据来源比较复杂，一般采用构造ODS的方法来实现收集当前需要处理的数据。如下述数据来源：a、业务数据库种类繁多。业务交易系统使用了不同种的数据库，如DB2、Informix、Oracle、SQL server、文本等。b、不同的应用系统、不同的地理位置。c、订阅数据源。d、批量还原非传统数据库数据。.等等。用于存放从业务系统直接抽取出来的数据，这些数据从数据结构、数据之间的逻辑关系上都与业务系统基本保持一致。2)、保存当前或接近当前的细节数据，以供查询或ETL检错使用。3)、数据存储周期性。ODS中存储的数据都是临时

11、的，每次ETL之前都要清空ODS中存储的数据。(2)、ETL(Extract Transform Load)操作型业务数据库（DB）到数据仓库（DW）的过程称之为ETL，它实现数据的抽取，转换及装载工作。抽取：将数据从各种原始的业务系统中读取出来。转换：按照预先设计好的规则将抽取得数据进行转换、清洗，以及处理一些冗余、歧义的数据，使本来异构的数据格式能统一起来。装载：将转换完的数据按计划增量或全部的导入到数据仓库中。在技术上主要涉及增量、转换、调度和监控等几个方面的处理。(3)、DW,(Data Warehouse)数据仓库的官方定义是一个面向主题的(Subject Oriented)、集成的

12、(Integrate)、相对稳定的(Non-Volatile)、反映历史变化(Time Variant)的数据集合，用于支持管理决策。(4)、OLAP（On-Line Analytical Processing）即联机分析处理，是BI的一种全新的数据封装方式，直接产物是报表或Cube，是使分析人员、管理人员或执行人员能够从多角度对信息进行快速、一致、交互地存取,从而获得对数据的更深入了解的一类软件技术。,主要内容(一),一、什么是BI（Business Intelligence）(一)、什么是BI的定义？(二)、BI的诞生？(三)、基本技术(四)、数据处理(五)、数据展现(六)、常用的BI 厂

13、商和产品,一、什么是BI（Business Intelligence）,上图流程，简单的说就是把交易系统已经发生过的数据，通过ETL工具抽取到主题明确的数据仓库中，OLAP后生成Cube或报表，透过Portal展现给用户，用户利用这些经过分类（Classification）、聚集（Clustering）、描述和可视化（Description and Visualization）的数据，支持业务决策。,DB2、Informix,Oracle,Sysbase,其他数据结构类型,一、什么是BI（Business Intelligence）,(五)、数据展现 数据查询是最简单的 BI 应用，输出报表是

14、BI最直接的产物，根据数据连接，加工过程及用途，应用模式大致可以分为四种：格式报表；在线分析；数据可视化；数据挖掘。1、格式报表：带格式的数据集合，如：交叉表等。2、在线分析：多维数据集合，如：Cube等。3、数据可视化：信息以尽可能多的形式展现出来，目的是使决策者通过图形这种直观的表现方式迅速获得信息中蕴藏的知识，如柱图，仪表盘等。4、数据挖掘：从大量的数据中，抽取出潜在的、有价值的知识（模型或规则）的过程。(六)、常用的BI 厂商和产品ETL：Informatica,SQL Server Analysis ServerDW：IBM DB2，Oracle，NCR Teradata，Sybas

15、e IQ等等；OLAP：Business Objects，Hyperion，Cognos，MicroStrategy，IBM Data Mining：IBM，SAS，SPSS 现在很多的数据库提供商都开始绑定BI的开发组件到自己的数据库产品中，他们都瞄准了这其中的肥肉，磨拳檫掌，各论长短。,一、什么是BI（Business Intelligence）,上图流程，简单的说就是把交易系统已经发生过的数据，通过ETL工具抽取到主题明确的数据仓库中，OLAP后生成Cube或报表，透过Portal展现给用户，用户利用这些经过分类（Classification）、聚集（Clustering）、描述和可视化

16、（Description and Visualization）的数据，支持业务决策。,DB2、Informix,Oracle,Sysbase,其他数据结构类型,Informatica,SQL Server Analysis Server,IBM DB2,Oracle,NCR Teradata,Business Objects,Hyperion,Cognos,MicroStrategy,主要内容(一)总结,一、什么是BI（Business Intelligence）(一)、什么是BI的定义？(二)、BI的诞生？(三)、基本技术(四)、数据处理(五)、数据展现(六)、常用的BI 厂商和产品,主要内

17、容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,一、数据仓库的架构,(一)、什么是数据仓库？数据仓库(Data Warehouse DW)是为了便于多维分析和多角度展现而将数据按特定的模式进行存储所建立起来的关系型数据库，它的数据基于OLTP源系统。数据仓库中的数据是细节的、集成的、面向主题的，

18、以OLAP系统的分析需求为目的。四个基本特征:DW的数据是面向主题的DW的数据是集成的DW的数据是非易失的(相对稳定的)DW的数据是随时间不断变化的(反映历史变化(Time Variant),主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,(二)、数据仓库OLAP和OLTP数据库的区别?,

19、主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,(三)、数据仓库的架构模型有哪些？,1、星型架构图1.1(某零售分析系统)星型架构的中间为事实表，四周为维度表，类似星星2、雪花型架构图1.2(某分析系统)雪花型架构的中间为事实表，两边的维度表可以再有其关联子表，从而表达了清晰的维度层次关系

20、。,3、两者比较？从OLAP系统的分析需求和ETL的处理效率两方面来考虑：星型结构聚合快，分析效率高;而雪花型结构明确，便于与OLTP系统交互。因此，在实际项目中，我们将综合运用星型架构与雪花型架构来设计数据仓库。,(三)、数据仓库的架构模型有哪些？,主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创

21、建事实表,三、构建企业级数据仓库五步法,(一)、确定主题 即确定数据分析或前端展现的主题。(以上海烟草集团零售分析系统为例，23家有限公司进销存情况)例如：我们希望分析某月某有限公司某卷烟销售情况，这就是一个主题。主题要体现出某一方面的各分析角度(维度)和统计数值型数据(量度)之间的关系，确定主题时要综合考虑。统计数值型数据(量度)存在于中间的事实表;分析角度是各个维度;我们将通过维度的组合，来考察量度。那么，“某年某月某一地区的卷烟销售情况”这样一个主题，就要求我们通过时间，地区和卷烟三个维度的组合，来考察销售情况这个量度。从而，不同的主题来源于数据仓库中的不同子集，我们可以称之为数据集市。

22、数据集市体现了数据仓库某一方面的信息，多个数据集市构成了数据仓库。,(二)、确定量度 在确定了主题以后，我们将考虑要分析的技术指标，诸如:年销售额之类。它们一般为数值型数据。我们或者将该数据汇总，或者将该数据取次数、独立次数或取最大最小值等，这样的数据称为量度。量度是要统计的指标，必须事先选择恰当，基于不同的量度可以进行复杂关键性能指标(KPI)等的设计和计算。,三、构建企业级数据仓库五步法,主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三

23、)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,(三)、确定事实数据粒度 在确定了量度之后，我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置到最小。例如：假设目前的数据最小记录到包卷烟，即数据库中记录了每包卷烟的交易额。那么，如果我们可以确认，在将来的分析需求中，卷烟只需要精确到包就可以的话，我们就可以在ETL处理过程中，按包为单位来汇总进销存数据，此时，数据仓库中量度的粒度就是“包”;反过来，如果我们不能确认

24、将来的分析需求在卷烟上是否需要精确到包，那么，我们就需要遵循“最小粒度原则”，在数据仓库的事实表中保留每一支的进销存数据，以便日后对“支”进行分析。举例:卷烟零售分析系统中，需求变更要求以万支为单位分析卷烟进销存情况。附加:DDS层 销售，库存及进货事实表结构 和 卷烟单位代码表,三、构建企业级数据仓库五步法,三、构建企业级数据仓库五步法,三、构建企业级数据仓库五步法,主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四

25、)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,三、构建企业级数据仓库五步法,(四)、确定维度 1、如何理解维度？及维度的层次(Hierarchy)和级别(Level)a、维度是指分析的各个角度。例如我们希望按照时间，或者按照地区，或者按照卷烟名称进行分析，那么这里的时间、地区、卷烟就是相应的维度。基于不同的维度，我们可以看到各量度的汇总情况，也可以基于所有的维度进行交叉分析。b、维度的层次(Hierarchy)和级别(Level)层次:时间维表结构 为例，年-半年-季度-月-半月-旬-周-日级别:卷烟维表结构

26、为例，卷烟价格分类一类(如:中华，熊猫)、二类(如:红双喜等)、三类、四类、五类(如:女士烟)、雪茄烟等等 2、什么是缓慢变化维度？维度表随时间变化的问题，我们称其为缓慢变化维度。以客户维表结构为例，每月所属有限公司发生变化，客户代码，客户标识，源客户标识，客户全称，分公司编号,分公司名称,历史标识(T/F)等变化,三、构建企业级数据仓库五步法,客户维表结构,三、构建企业级数据仓库五步法,3、缓慢变化维度 几种基本情况？几种情况的利弊？(1)、缓慢变化维度第一种类型：(UPDATE)-覆盖历史数据需要修改。这种情况下，我们使用UPDATE方法来修改维度表中的数据。例如：分公司编号=I0，分公司

27、名称=闸北,后来发现该客户所属区域已经发生迁移，从闸北区迁移到了宝山区，处理方法:在ETL处理时，直接修改维度表中分公司编号=Q0，分公司名称=宝山(2)、缓慢变化维度第二种类型：(UPDATE/INSERT)-分历史选择历史数据保留，新增数据也要保留。这时，要将原数据更新，将新数据插入.例如：分公司编号=I0，分公司名称=闸北,后来发现该客户所属区域已经发生迁移，从闸北区迁移到了宝山区，处理方法:在ETL处理时，先将原先的客户代码，客户标识，源客户标识，客户全称，分公司编号,分公司名称,历史标识 修改,然后插入新信息。UPDATE:客户代码 不变，客户标识 不变，源客户标识=客户标识，客户全

28、称=*客户全称，分公司编号 不变,分公司名称 不变,历史标识=F INSERT:客户代码=新客户代码，客户标识=新客户标识，源客户标识=update的客户标识，客户全称=客户全称，分公司编号=Q0，分公司名称=宝山,历史标识=T。(3)、缓慢变化维度第三种类型：-交替实体新增数据维度成员改变了属性。例如：某一维度成员新加入了一列，该列在历史数据中不能基于它浏览，而在目前数据和将来数据中可以按照它浏览，那么此时我们需要改变维度表属性，即加入新的字段列。那么，我们将使用存储过程或程序生成新的维度属性，在后续的数据中将基于新的属性进行查看。,主要内容(二),二、数据仓库的架构(一)、什么是数据仓库？

29、(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,三、构建企业级数据仓库五步法,(五)、创建事实表(1)、了解 在确定好事实数据和维度后，我们将考虑加载事实表。事实表装有企业的度量数据，度量以及事实表才是最终用户真正想要看的内容，维表是事实表数据的入口，事实只有通过维度解释才会变得有意义。(2)、如何创建？做法:将原始表(OLTP)与维度表进行关联

30、，生成事实表.注意点:在关联时有为空的数据时(数据源脏)，需要使用外连接，连接后我们将各维度的代理键取出放于事实表中，事实表除了各维度代理键外，还有各量度数据，这将来自原始表，事实表中将存在维度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原则”，即要求事实表数据条数尽量多(粒度最小)，而描述性信息尽量少。事实数据表是数据仓库的核心，需要精心维护，在JOIN后将得到事实数据表，一般记录条数都比较大，我们需要为其设置复合主键和索引，以实现数据的完整性和基于数据仓库的查询性能优化。事实数据表与维度表一起放于数据仓库中，如果前端需要连接数据仓库进行查询，我们还需要建立一些相关的中间汇总表或物化

31、视图，以方便查询.举例:附加:HDS层，到 DDS层 销售实事表结构,三、构建企业级数据仓库五步法,三、构建企业级数据仓库五步法,HDS层表结构,三、构建企业级数据仓库五步法,DDS层表结构,主要内容(二)总结,二、数据仓库的架构(一)、什么是数据仓库？(二)、数据仓库OLAP和OLTP数据库的区别?(三)、数据仓库的架构模型有哪些？三、构建企业级数据仓库五步法(一)、确定主题(二)、确定量度(三)、确定事实数据粒度(四)、确定维度如何理解维度？及维度的层次和级别?什么是缓慢变化维度？缓慢变化维度 几种基本情况？几种情况的利弊？(五)、创建事实表,主要内容(三),四、什么是ETL(ETL中的关

32、键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍,四、什么是ETL(ETL中的关键技术),(零)、什么是ETL a、在数据仓库的构建中，ETL贯穿于项目始终，它是整个数据仓库的生命线，包括了数据清洗、整合、转换、加载等各个过程。如果说数

33、据仓库是一座大厦，那么ETL就是大厦的根基。ETL抽取整合数据的好坏直接影响到最终的结果展现。所以ETL在整个数据仓库项目中起着十分关键的作用，必须摆到十分重要的位置。b、ETL是数据抽取(Extract)、转换(Transform)、加载(Load)的简写，它是指：将OLTP系统中的数据抽取出来，并将不同数据源的数据进行转换和整合，得出一致性的数据，然后加载到数据仓库中。c、简而言之，通过ETL，我们可以基于源系统中的数据来生成数据仓库。ETL为我们搭建了OLTP系统和OLAP系统之间的桥梁。如下图:,主要内容(三),四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)

34、增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍,四、什么是ETL(ETL中的关键技术),(一)、数据抽取 数据抽取是从数据源中抽取数据的过程。实际应用中，数据源较多采用的是关系数据库。(1)全量抽取 全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数据

35、库中抽取出来，并转换成自己的ETL工具可以识别的格式。全量抽取比较简单。例如:数据源表HDS.SYC_TSL00_DAYHIS(日结数据表),主要内容(三),四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍

36、,四、什么是ETL(ETL中的关键技术),(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器(不谈)在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除。触发器方式优缺点:优点:是数据抽取的性能较高.缺点:是要求业务表建立触发器，对业务系统有一定的影响。b.时间戳(谈)它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段

37、的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，即表的其它字段的数据发生改变时，自动更新时间戳字段的值。有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单，但对业务系统也有很大的倾入性(加入额外的时间戳字段)，特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作。另外，无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。附加:数据源表HDS.SYC_TSL00_DAYHIS(日结数据表)表结构。loaddate:加载日期。

38、交易日期V_MONTH 参考:HDS 4 TABLE.xls c.全表比对(不谈)d.日志对比(不谈),四、什么是ETL(ETL中的关键技术),四、什么是ETL(ETL中的关键技术),主要内容(三),四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量

39、装载工具或api(四)、主流ETL工具介绍,四、什么是ETL(ETL中的关键技术),(二)、数据转换和加工从数据源中抽取的数据不一定完全满足目的库的要求，例如数据格式的不一致、数据输入错误、数据不完整等等，因此有必要对抽取出的数据进行数据转换和加工。数据的转换和加工可以在ETL引擎中进行，也可以在数据抽取过程中利用关系数据库的特性同时进行。(1)ETL引擎中的数据转换和加工ETL引擎中一般以组件化的方式实现数据转换。常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等。这些组件如同一条流水线上的一道道工序，它们是可插拔的，且可以任意组

40、装，各组件之间通过数据总线共享数据。例如：附加:数据源表HDS.SYC_TSL00_DAYHIS(日结数据表)中 ITEMCD=10310101 软中华-数据替换HDS.TSL00_DAYHIS 日结数据表 中 CIGCD=310101 软中华-数据替换进销存单位 包 转 条-数据计算HDS-DDS 的进销存数据-数据拆分(2)在数据库中进行数据加工关系数据库本身已经提供了强大的SQL、函数来支持数据的加工，如在SQL查询语句中添加where条件进行过滤，查询中重命名字段名与目的表进行映射，substr函数，等等。下面是一个SQL查询的例子。SELECT COMPCD,SUBSTR(ITEMC

41、D,2,6)AS CIGCD FROM HDS.SYC_TSL00_DAYHIS WHERE LOADDATE=CURRENT DATE;,四、什么是ETL(ETL中的关键技术),主要内容(三),四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载

42、工具或api(四)、主流ETL工具介绍,四、什么是ETL(ETL中的关键技术),(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍InformaticaNCR TeradataCognos,HyperionOracle warehouse builder、Oracle Data Integrator,主要内容(三)总结,四、什么是ETL(ETL中的关键技术)(一)、数据抽取(1)全量抽取(2)增量抽取 增量数据抽取中常用的捕获变化数据的方法:a.触发器b.时间戳c.全表比对d.日志对比(二)、数据转换和加工(1)ETL引擎中的数据转换和加工(2)在数据库中进行数据加工(三)、数据装载(1)直接SQL语句进行insert、update、delete操作(2)采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api(四)、主流ETL工具介绍,谢谢！,