第三章 BI数据预处理

ETL概述
ETL概念 ETL的必要性 ETL的优势 制定ETL策略应考虑的问题
ETL体系结构
Business Intelligence
武汉大学信息管理学院 5
ETL概念
为了能有效的整合企业数据系统，保持数据的 一致性，并将数据统一地展现在客户面前，ETL解决 方案是用户唯一的选择。
Business Intelligence
数据清洗
数据清洗的任务:
过滤那些不符合要求的数据，将过滤的结果交给业务 主管部门，确认是否过滤掉还是由业务单位修正之后再进 行抽取。
不符合要求的数据主要是有不完整的数据、错误的数据、 重复的数据三大类。
Business Intelligence
武汉大学信息管理学院 30
(1)不完整的数据：
这一类数据主要是一些应该有的信息缺失。
Business Intelligence
武汉大学信息管理学院 12
ETL体系结构
它体现了主流ETL产品框架的主要组成部分。
Business Intelligence
武汉大学信息管理学院 13
Databases & files Target adapters Load Administration & operations services Transport services Extract Design manger Runtime Metadata services Metadata repository Meta data Import/export
Business Intelligence
武汉大学信息管理学院 35
数据加载
将经过转换和清洗的数据加载到数据仓库里面，即 入库。 主要加载技术： 1）使用数据仓库引擎厂商提供的数据加载工具 2）通过数据仓库引擎厂商提供的API编程
数据加载要考虑加载周期和数据追加策略两方面。
Business Intelligence
抽取方式、抽取时机、抽取周期
Business Intelligence
武汉大学信息管理学院 23
数据抽取
1、抽取方式： --小数据量的一些资源、系统管理数据、配置类数 据等，采用完全抽取方式。
--大数据量的如帐单、话单数据，按抽取周期采用
时间戳来增量抽取。
Business Intelligence
武汉大学信息管理学院 34
数据转换
对抽取的源数据根据数据仓库系统模型的要求，进行 数据的集成、拆分、汇总等处理，保证来自不同系统、 不同格式的数据的一致性和完整性。这些工作可以在 不同的过程中处理，视具体情况而定。 可以在数据抽取时转换 可以在数据加载时转换。 可以在进入数据仓库以后再进行数据处理。
Business Intelligence
武汉大学信息管理学院 39
2、数据质量下降的原因： 人工数据输入的错误 数据集成引入的错误 数据传输过程引入的错误
Business Intelligence
武汉大学信息管理学院 40
3、ETL工具的质量控制 一个好的ETL工具，应该能够从技术方面保证对数 据质量的自动检验，屏蔽掉繁琐的技术细节，使用 户可以把更多的精力花在业务逻辑的设计上。 在ETL清洗、转换中，对数据质量的控制可以从6方 面着手： --空值处理 --规范化数据格式 --拆分数据 --验证数据正确性 --数据替换 --建立ETL过程的主外键约束
对于这一类数据过滤出来，按缺失的内容分别写入不同 Excel文件向客户提交，要求在规定的时间内补全。补全后 才写入数据仓库。
Business Intelligence
武汉大学信息管理学院 31
(2)错误的数据：
这一类错误产生的原因是业务系统不够健全，在接收输入 后没有进行判断直接写入后台数据库造成的。
数据加载
更新追加：对于需要连续记录业务的状态变化， 根据当前的最新状态同历史状态数据进行对比的情 况采用更新追加。
在实施系统中，具体采用哪种方式，要综合考虑 效率、业务实现等因素。
Business Intelligence
武汉大学信息管理学院 38
ETL与数据质量
在ETL过程中，不仅涉及对数据格式和内容方面的 统一和集成，还涉及到对数据质量的控制。 1、质量问题具体表现： 数据格式错误 数据一致性问题 业务逻辑的合理性问题
武汉大学信息管理学院 36
数据加载
数据追加策略根据数据的抽取策略及业务规则确定， 一般有三种类型： 直接追加：每次加载时直接将数据追加到目标表 中。 全部覆盖：对抽取数据本身已包括了的当前数据 和所有历史数据，采用全部覆盖方式。
Business Intelligence
武汉大学信息管理学院 37
•是否存在手工数据，手工数据量有多大， •是否存在非结构化的数据等等， •当收集完这些信息之后才可以进行数据抽取的设计。
Business Intelligence
武汉大学信息管理学院 22
数据抽取
典型的数据抽取接口包括数据库接口和文件接口， 在数据抽取分析时需要对业务深刻理解，重点考虑 数据抽取的效率，以及对现有业务系统性能和安全 的影响，在抽取时应制定相应的策略：
Business Intelligence
武汉大学信息管理学院 26
• 对于与DW数据库系统不同的数据源
一般情况下也可以通过ODBC的方式建立数据库链接。 如果不能建立数据库链接，可以有两种方式完成: 一种是通过工具将源数据导出成.txt或者是.xls文 件，然后再将这些源系统文件导入到ODS中。 另外一种方法是通过程序接口来完成。
Business Intelligence
武汉大学信息管理学院 28
数据清洗
一般情况下，数据仓库分为ODS、DW两部分。
通常的做法是:
•从业务系统到ODS做清洗，将脏数据和不完整数据过滤掉，
•再从ODS到DW的过程中转换，进行一些业务规则的计算和聚
合。
Business Intelligence
武汉大学信息管理学院 29
部的导入到数据仓库中。
Business Intelligence
武汉大学信息管理学院 8
ETL必要性
（1）解决数据分散问题 （2）解决数据不清洁问题 （3）方便企业各部门构筑数据中心
集中 一致、全面和面 向决策的数据
Business Intelligence
武汉大学信息管理学院 9
ETL的优势
ETL工具的本质是数据转换器,它提供一种从源系 统到目标系统转换数据的方法. 1) ETL工具提供一种通用的解决方案,能够有效地应 用到数据仓库的建设过程中。
武汉大学信息管理学院 7
ETL概念
首先是‘抽取’：将数据从各种原始的业务系统中
读取出来，这是所有工作的前提。
ETL（Extract Transform Load）是抽取、转换、装载。
其次‘转换’：按照预先设计好的规则将抽取得数
据进行转换，使本来异构的数据格式能统一起来。
最后的‘装载’：将转换ቤተ መጻሕፍቲ ባይዱ的数据按计划增量或全
2) 能够支持多种数据源,并支持多种数据源接口。
3) 在数据转换方面可通过各种算法进行计算和转换, 具有灵活性和可扩展性. 4) 数据装载能够支持大多数据仓库存储产品的批数 据装载功能.
Business Intelligence
武汉大学信息管理学院 10
5)
能较好地将数据集成和数据清洗整合在一起.
Business Intelligence
武汉大学信息管理学院 32
(3)重复的数据：
对于这一类数据——特别是维表中会出现这种情况——将 重复数据记录的所有字段导出来，让客户确认并整理。
数据清洗是一个反复的过程，不可能在几天内完成，只有 不断的发现问题，解决问题。对于是否过滤，是否修正一 般要求客户确认，对于过滤掉的数据，写入Excel文件或者 将过滤数据写入数据表。
整个过程实现如图：
源数据存放区
数据准备区
数据集市
各 业 务 系 统
数据仓库
数据监查、抽取、预处理
数据转换、加载 数据转换、加载
步骤： 1)辨识与所研究主题相关的原始数据
2）开发数据抽取策略
3）将原始数据转换为目标规格 4）将原始数据加载到预定目标区域
Business Intelligence
武汉大学信息管理学院 20
在ETL过程中需要牢记两个重要问题： 1)引入的仅仅是能够在数据分析过程中发挥作用的
数据仓库数据。
2）数据仓库的设计者必须保证被引入数据仓库的 所有数据是完整和正确的。
Business Intelligence
武汉大学信息管理学院 21
数据抽取
这一部分需要在调研阶段做大量的工作:
•首先要搞清楚数据是从几个业务系统中来,各个业务系统 的数据库服务器运行什么DBMS,
Transform
Source adapters
Databases & files
Legacy applications
ETL过程
数据抽取
数据清洗 数据转换 数据加载 ETL与数据质量
ETL过程管理
Business Intelligence
武汉大学信息管理学院 18
ETL过程
Business Intelligence
武汉大学信息管理学院
2
Business Intelligence
武汉大学信息管理学院 3
BI 中 的 数 据 预 处 理

ETL概述
ETL过程
ETL系统的设计 ETL工具 元数据
Business Intelligence
武汉大学信息管理学院 4
这一类数据也要分类，对于类似于全角字符、数据前后有 不可见字符的问题，只能通过写SQL语句的方式找出来，然 后要求客户在业务系统修正之后抽取。
日期格式不正确的或者是日期越界的这一类错误会导致ETL 运行失败，这一类错误需要去业务系统数据库用SQL的方式 挑出来，交给业务主管部门要求限期修正，修正之后再抽 取。
大类
数据集成技术
细分类别
数据获取
关键技术
ETL
说明
数据预处理
数据仓库
描述性分析 信息分析技术 预测性分析 简单展示 知识展示技术 高级解释
数据仓库,数据集市 数据分析的基础
OLAP 数据挖掘 查询和报表 多维数据集 信息的一般分析 信息的深度分析 展示的一般技术
知识表示,知识解释, 展示的深度技术 知识可视化
Business Intelligence
武汉大学信息管理学院 33
数据转换
ETL三个部分中，花费时间最长的是‚T”(Transform， 清洗、转换)的部分，一般情况下这部分工作量是整 个ETL的2/3。 数据转换的任务:
主要进行不一致的数据转换、数据粒度的转换，以及 一些商务规则的计算。
Business Intelligence
Business Intelligence
武汉大学信息管理学院 27
• 对于文件类型数据源(.txt,.xls)，
可以培训业务人员利用数据库工具将这些数据导入 到指定的数据库，然后从指定的数据库中抽取。或 者可以借助工具实现，如SQLServer2005的SSIS服 务的平面数据源和平面目标等组件导入ODS中去。
武汉大学信息管理学院 24
2、抽取周期： 根据源数据的不同性质和实际的分析需求有所 不同。
3、抽取时机： 在相关业务系统空闲的时段执行。
Business Intelligence
武汉大学信息管理学院 25
4、抽取方式:
• 对于与存放DW的数据库系统相同的数据源 这一类数据源一般情况下，DBMS(SQLServer、 Oracle)都会提供数据库链接功能，在DW数据库服 务器和原业务系统之间建立直接的链接关系就可以 写select语句直接访问。
从整体角度来看，ETL主要作用在于:
屏蔽了复杂的业务逻辑，为各种基于数据仓库的
分析和应用提供了统一的数据接口。
Business Intelligence
武汉大学信息管理学院 11
制定ETL策略需要考虑的问题
ETL策略的制定必须考虑到 源系统、目标系统、业务规则等多方面的因素 1、不同的数据格式。 2、坏数据 3、不兼容的源系统和源系统的区别 4、提取与加载窗口。 5、业务规则。
武汉大学信息管理学院 6
ETL概念
ETL是BI/DW的核心和灵魂，是数据仓库的入口，是
负责完成数据从数据源向目标数据仓库转化的过程，
是实施数据仓库的重要步骤。 它为各种基于数据仓库的分析和应用提供了统一的 数据接口。 DW的数据不要求与OLTP保持实时的同步，因此ETL 可以定时进行。
Business Intelligence