Copyright 2007 宜昌艾福斯公司 ETL- Extract Transform Load宜昌艾福斯软件公司主讲：张超Copyright 2007 宜昌艾福斯公司 内容ETL基础 ETL概述 ETL体系结构 ETL实现步骤 OWB基础 概述 基本概念/基本元素 OWB功能简介 源目标对象定义 ETL设计 数据质量管理 ETL部署和执行 元数据管理 案例 Follow Me 总结Copyright 2007 宜昌艾福斯公司 ETL概述Copyright 2007 宜昌艾福斯公司 ETL概述 ETL(Extract-Transform-Load的缩写，即数据抽取 、转换、装载的过程) 是BI/DW（Business Intelligence）的核心和灵魂 ，能够按照统一的规则集成并提高数据的价值 是负责完成数据从数据源向目标数据仓库转化的 过程 是实施数据仓库的重要步骤。Copyright 2007 宜昌艾福斯公司 ETL体系结构Copyright 2007 宜昌艾福斯公司 ETL体系结构 Design manager 提供一个图形化的映射环境，让开 发者定义从源到目标的映射关系、转换、处理流程 。设计过程的各对象的逻辑定义存储在一个元数据 资料库中。 Meta data management 提供一个关于ETL设计和运 行处理等相关定义、管理信息的元数据资料库。 ETL引擎在运行时和其它应用都可参考此资料库中 的元数据。 Extract 通过接口提取源数据，例如 ODBC、专用 数据库接口和平面文件提取器，并参照元数据来决 定数据的提取及其提取方式。 Copyright 2007 宜昌艾福斯公司 ETL体系结构 Transform 开发者将提取的数据，按照业务需要转 换为目标数据结构，并实现汇总。 Load 加载经转换和汇总的数据到目标数据仓库中， 可实现SQL或批量加载。 Transport services 利用网络协议或文件协议，在源 和目标系统之间移动数据，利用内存在ETL处理的 各组件中移动数据。 Administration and operation 可让管理员基于事件 和时间进行调度、运行、监测ETL作业、管理错误 信息、从失败中恢复和调节从源系统的输出。Copyright 2007 宜昌艾福斯公司 ETL基本步骤Copyright 2007 宜昌艾福斯公司 ETL基本步骤 ODS区的数据采集 数据转换、清洗 数据加载 汇总层、CUBE加载Copyright 2007 宜昌艾福斯公司 ETL基本步骤Copyright 2007 宜昌艾福斯公司 ETL基本步骤第一步、ODS（ Operational Data Store-操作型数据存储 ）区的数据采集 主要作用： 为了尽量减少对业务系统的影响。 表结构可以不必和DW一致。根据具体业务需求和 数据量情况，将数据源的数据放入ODS有各种不 同的方法，比如Oracle的数据库链路，表复制， SQL*LOADER，Teradata的Fastload，Sysbase的 BCP等等。Copyright 2007 宜昌艾福斯公司 讨论比较项目 DW数据仓库 ODS DB应用系统 建设目的 决策支持 实时监控 业务操作 服务对象 企业管理层 业务管理层 生产层 存储周期 长期 短期 即时 处理频率 非实时 准实时 实时 主要功能 分析功能 事务处理，短时分 析 事务处理 技术实现 OLAP OLAP、OLTP OLTP 功能结构 集中 相对集中 分散 数据类型 明细数据,汇总数 据 明细数据 明细数据 数据容量 非常大 小 小Copyright 2007 宜昌艾福斯公司 ETL基本步骤需要解决的问题 数据的时间差异性问题 数据的平台多样性问题 数据的不稳定性问题 数据的依赖性问题Copyright 2007 宜昌艾福斯公司 ETL基本步骤第二步、数据转换和清洗将ODS中的数据，按照数据仓库中数据存储结构 进行合理的转换，转换步骤一般还要包含数据清洗 的过程。数据清洗主要是针对源数据库中出现二义 性、重复、不完整、违反业务或逻辑规则等问题的 数据数据进行统一的处理，一般包括如：NULL值 处理，日期格式转换，数据类型转换等等。在清洗 之前需要进行数据质量分析，以找出存在问题的数 据，否则数据清洗将无从谈起。数据装载是通过装 载工具或自行编写的SQL程序将抽取、转换后的结 果数据加载到目标数据库中。Copyright 2007 宜昌艾福斯公司 ETL基本步骤数数 据据 质质 量量 问问 题题Copyright 2007 宜昌艾福斯公司 ETL基本步骤 数据仓库中数据质量要求，包括格式、完整性要求 。 业务描述统一，对数据模型的不同版本融合、映 射为唯一版本。 信息描述规范、完整。Copyright 2007 宜昌艾福斯公司 ETL基本步骤 主要数据质量问题的清洗策略 主要问题 表现形式 产生原因 清洗策略Copyright 2007 宜昌艾福斯公司 讨论 主要数据质量问题的清洗策略 数据完整性问题 超出字典表范围 数据一致性问题 录入, 同步的问题Copyright 2007 宜昌艾福斯公司 ETL基本步骤第三步、数据加载将转换和清洗完的数据按照数据仓库的结构进 行数据加载。Copyright 2007 宜昌艾福斯公司 ETL基本步骤 针对数据现状，初始导入需要考虑的问题 如何解决时间差异性？ 如何解决平台差异性？ 如何适应数据的不稳定性？ 如何解决数据依赖性？ Copyright 2007 宜昌艾福斯公司 ETL基本步骤 数据刷新的策略要根据业务需求和应用系统的承受 能力和数据情况决定。主要需要考虑的一些问题： 如何解决时间差异性？ 如何解决平台差异性？ 如何适应数据的不稳定性？ 如何解决数据依赖性？ 如何减少对业务系统的影响？Copyright 2007 宜昌艾福斯公司 ETL基本步骤 不同的刷新任务类型，对业务系统的影响不同，刷 新任务有以下种归类特性： 刷新频率 刷新方式 数据加工方式并可针对各种异常情况做处理：回滚，重新装载 ，断点重新装载等等，还可在任务完成后（或失败 后）将日志以Email方式发给数据仓库管理人员。 Copyright 2007 宜昌艾福斯公司 ETL基本步骤第四步、汇总层和CUBE加载ODS加载进入数据仓库的数据只是底层详细层 数据，还需按定义的汇总规则进行汇总，生成数据 集市用的汇总表或CUBE。ETL流程是指完成每个 维表数据及事实表数据导入的顺序, 其包括两个部分 , 初始导入数据时的ETL流程, 及增量导入时的ETL 流程。Copyright 2007 宜昌艾福斯公司 ETL基本步骤 初始导入数据时的ETL流程 自动生成维的数据装载 手工维护维度装载 缓慢变化维表数据装载 事实表数据装载 聚合表初始生成Copyright 2007 宜昌艾福斯公司 ETL基本步骤 增量导入 缓慢变化维表数据装载 事实表数据装载阶段 数据汇总和聚合 作业调度和异常情况处理Copyright 2007 宜昌艾福斯公司 ETL基本步骤第五步、任务调度策略 驱动策略 前导Job驱动：只有满足另外一个JOB成功后， 自己才运行。 文件驱动：当下传的文件到达，并经过检验准确 后JOB才运行。 时间驱动：当到达某个时点时，Job便开始运行 。 事件驱动：如人工参与，导致JOB执行。 通知设计：重要信息（成功/失败）的通知Copyright 2007 宜昌艾福斯公司 ETL基本步骤 成功退出 分段提交方式，当分段提交的当次任务都正确完 成，即Job运行状态临时表中登记的作业状态全 部为完成时，退出ETL调度。 自动提交方式，当当期所有的任务都正确完成， 即Job运行状态表中登记的作业状态全部为完成 时，退出ETL调度。Copyright 2007 宜昌艾福斯公司 ETL基本步骤 失败退出 关键作业异常，关键作业运行异常时，影响剩下 的作业不能运行时，则退出ETL调度。 超过ETL时限，当超过预先设定的ETL?时限时 ，退出ETL调度。 数据库异常，当不能正常操作数据库时，退出 ETL调度。 操作系统异常，当发生操作系统异常，导致程序 不能正常运行，如文件系统异常导致读写文件错 时，需要退出ETL调度。Copyright 2007 宜昌艾福斯公司 ETL基本步骤 手工退出 需要人为干预ETL调度的时候，能以手工操作的 方式退出ETL调度。Copyright 2007 宜昌艾福斯公司 ETL工具 OWB Informatica Datastage 微软DTS等等Copyright 2007 宜昌艾福斯公司 阶段总结Copyright 2007 宜昌艾福斯公司 阶段总结为了能更好地实现ETL，建议用户在实施ETL过 程中应注意以下几点： 如果条件允许，可利用数据中转区对运营数 据进行预处理，保证集成与加载的高效性 如果ETL的过程是主动“拉取”，而不是从内 部“推送”，其可控性将大为增强 ETL之前应制定流程化的配置管理和标准协 议 关键数据标准至关重要Copyright 2007 宜昌艾福斯公司 OWB简介Copyright 2007 宜昌艾福斯公司 OWB概述 Oracle Warehouse Builder 是一个简单而又全面的数 据管理工具，能够管理数据的各个方面。它提供了 数据质量管理、数据审计、全面集成了关系模型和 维度模型，并提供了数据和元数据的全生命周期管 理。 支持的数据集成和管理内容包括： 从现有系统中迁移数据 从异构数据源集成数据 设计和管理企业所有的元数据 清洗数据并提供质量报告 提供标准的提取、移动和装载 （ETL）Copyright 2007 宜昌艾福斯公司 基本概念/基本元素Copyright 2007 宜昌艾福斯公司 基本概念/基本元素（一） Data Warehouse （数据仓库） Repository（知识库或资料档案库）Copyright 2007 宜昌艾福斯公司 基本概念/基本元素（二） Project（项目） Module（模块） 数据库存储（源、目标） 文件存储 其他对象集：进程、计划等等 Location（位置） Object（对象） 关系数据对象：表、索引、物化视图、分区等 维度数据对象：Dimension 维、Cube 立方 其他对象：映射、进程流、调度等等。Copyright 2007 宜昌艾福斯公司 基本概念/基本元素（三） Map（映射） ETL Job（作业） Calender（计划） Schedule（调度） Profile（数据概要文件） Process Flow（进程流）Copyright 2007 宜昌艾福斯公司 源目标对象定义Copyright 2007 宜昌艾福斯公司 源目标对象定义 定义 Oracle 数据对象 定义维度对象 定义平面文件和外部表 定义商业智能对象 导入数据定义 将 OWB 元数据与其他 BI 产品集成