第三章 数据仓库中的ETL和元数据
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1
第三章 数据仓库中的ETL和元数据
基本概念
2
ETL（Extract/Transform/Loading）是数据从 业务系统抽取转化到数据仓库的过程 Metadata Metadata可以理解成Data about data，关于 Data data 数据的数据；数据仓库的整合性是基于元数 据的统一
1.
2.
3.
如果熟悉直接写代码,那么项目效率更高
第二章 数据仓库中的ETL和元数据
31
ETL 元数据 外部数据
元数据
32
元数据的概念 元数据的内容与用途 元数据的分类 元数据的使用、常见问题、解决建议 元数据的标准化及维护与管理
什么是元数据
33
“数据太多，信息太少” 只有充分理解数据才能分析数据，因此元数对数据仓库尤 为重要。 元数据 关于数据仓库的数据 数据仓库建设过程中所产生的有关数据源定义、目标 定义、转换规则等相关的关键数据，同时元数据还包 含关于数据含义的商业信息，是整个数据仓库的核心.
ETL的关键名词
9
数据抽取：
只提取系统分析必需的那一部分数据
数据清洗
将错误的、不一致的数据在进入数据仓库之前予以更正或删除， 以免影响决策支持系统决策的正确性
数据转化
业务系统使用不同数据库上的产品，各种产品提供的数据类型不 同，数据格式不同
数据装载
将数据按照物理数据模型定义的表结构装入数据仓库，这些步骤 包括清空数据域、填充空格、有效性检查等
元数据的实际问题
41
人事
销售
数据采集 数据传输 数据转换 数据清洗
RDBMS
ODS/Staging Area
企业经营 分析
Statistics 业务 Clustering 管理
客户关系 管理 业务流程 分析 财务分析
库存
财务
外部系统
数据加载 数据汇总 数据模型
RDBMS 数据仓库 数据集市
Neural Nets Artificial Intelligence
矛盾
数据来源不同
大量、分散、不清洁
数据的分析、挖掘活动必须建立在一个数据清洁、 结构良好的数据仓库基础之上
ETL作用
6
解决数据分散问题
解决数据不清洁问题
方便企业各部门构筑数据集市
ETL
7
ETL的概念 关键名词 过程描述 实现办法 ETL的工具
ETL的概念
8
数据ETL是用来实现异构数据源的数据集成，即 完成数据的抓取/抽取（Capture/Extract）、清 洗 （ Scrub or data cleansing ） 、 转 换 （Transform）、装载与索引（Load and Index） Transform Load Index 等数据调和工作
书上的说法
23
数据提取 数据验证 数据清理 数据集成 数据聚集 将数据装入仓库目标表
数据ETL过程的实施要点
24
ETL过程是一个数据流动的过程，中间的“T” （转换）是关键 ； ETL工具的选择非常重要，运用合适的工具会事 半功倍 ； 如何保证数据质量？数据质量在一定程度上决 定了数据仓库的价值 。
)
捕获数据的方法
18
完全刷新
对移入DW的数据进行完全复制 经常变化
增量更新
捕获数据源中修改的数据 流水型增长、数据量大
捕获数据的方法
19
完全刷新
对移入DW的数据进行完全复制 经常变化
增量更新
捕获数据源中修改的数据 流水型增长、数据量大
对两者结合的数据先考虑增量更新，再考虑完 全刷新
增量更新的问题
整合的过程，将不同业务系统的相同类型的数据统一，
E.g., 供应商在结算系统的编码是XX0001 vs. 客户关系管理 中编码是YY0001
数据粒度的转换
将业务系统数据按照数据仓库粒度进行聚合。
商务规则的Biblioteka Baidu算
不同的企业有不同的业务规则、不同的数据指标,需要 在ETL中将这些数据指标计算好了之后存储在数据仓库 中，以供分析使用
元数据与数据是什么关系？
44
元数据也是数据，其本身也可以作为被描述的 对象 元数据可以出现在：
例子3
36
元数据的直观作用
37
信息的描述和分类可以实现格式化 从而为机器处理创造了可能 ETL是数据仓库从业务系统获得数据的必经之 路，元数据则是地图
数据字典与元数据
38
数据库的数据字典 数据仓库的元数据
数据库的数据字典
39
数据字典是数据库中各类数据描述的集合 (1) 数据项 (2) 数据结构 (3) 数据流 (4) 数据存储 (5) 处理过程
第三章 数据仓库中的ETL和元数据
3
ETL 元数据 外部数据
第三章 数据仓库中的ETL和元数据
需求/建模 需求 建模
ETL开发 开发
前端开发
数据仓库项目三部曲 数据仓库项目的三部曲
4
ETL作用
5
需求
企业管理者希望按需访问 存储各种格式的企业内部和企业外部数据：
经营数据、历史数据、现行数据、Internet数据、元数 据
股东
OLAP
Metadata
业务系统到数据仓库 ETL
(Meta data) 元数据组成 数据仓库 Target Database
数据展现工具 Front-End Tool
42
两者区别
43
数据仓库的元数据除对数据仓库中数据的描述 （数据仓库字典）外，还有以下三类元数据 ： (1) 关于数据源的元数据 (2) 关于抽取和转换的元数据 (3) 关于最终用户的元数据
具备数据仓库的部分特征和OLTP系统的部分特征，它是“面向主题的、集成的、 系统的部分特征，它是“面向主题的、集成的、 具备数据仓库的部分特征和 系统的部分特征 当前或接近当前的、不断变化的”数据。 当前或接近当前的、不断变化的”数据。
一般在带有ODS的系统体系结构中，ODS都设计为如下几个作用： 在业务系统和数据仓库之间形成一个隔离层 转移一部分业务系统细节查询的功能 完成数据仓库中不能完成的一些功能
例子2
35
IMDB本身也定义了一套元数据，用来描述每一部 电影
下面是它的一级元数据，每一级下面又列出了二级元数 据，总共加起来，可以从100多个方面刻画一部电影： Cast and Crew（演职人员）、Company Credits（相 关公司）、Basic Data（基本情况）、Plot & Quotes（ 情节和引语）、Fun Stuff（趣味信息）、Links to Other Sites（外部链接）、Box Office and Business（ 票房和商业开发）、Technical Info（技术信息）、 Literature（书面内容）、Other Data（其他信息）。
数据库
表名 KHCG SPQK ZGYJ ……
备注 客户采购商品表 商品情况表 职工业绩表 ……
Windows/Access C …… ……
数据源抽取规则表
15
表.列名 KHCG.CGSL KHCG.CGSL SPQK.SPID ……
过滤与连接 条件 < > ≠ ……
比较值 50000 500 ‘AB’ ……
将月/年/日的日期 格式转换成年/月/ 日格式 ……
客户采购日期
……
……
……
文件类型数据源（.txt,.xls）
17
人工实现：利用数据库工具将这些数据导入到 指定的数据库，然后从指定的数据库中抽取 工具实现：
如SQLServer2005的SSIS服务的平面数据源和平 面目标等组件导入ODS中去 (ODS（Operational Data Store）是数据仓库体系结构中的一个可选部分，ODS 体系结构中的一个可选部分， （ ） 数据仓库体系结构中的一个可选部分
ETL的实现办法
25
ETL工具
快速建立ETL过程，屏蔽复杂编码，降低难度 缺少灵活性
SQL方法实现
灵活，提高ETL运行效率 编码复杂，对技术要求比较高
ETL+SQL
综合前两种优点，提高ETL开发速度和效率
ETL工具
26
ETL工具分类 专业ETL厂商和产品
功能详尽、价格昂贵
整体方案提供商和产品
提供数据仓库存储、设计、展现工具，同时也提供 相应的ETL工具
ETL商业产品
27
ETL工具的选择
28
对平台的支持 对数据源的支持 数据转换功能 管理和调度功能 集成和开放性 对元数据的管理
问题
29
数据仓库厂商和数据仓库项目面试问题
使用ETL工具的优点是什么？与直接写代码开发区 别在哪里？
问题
30
数据仓库厂商和数据仓库项目面试问题
使用ETL工具的优点是什么？与直接写代码开发区别在 哪里？ ETL工具开发更规范，条理性更好，便于维护。可能 某些ETL工具效率比直接开发的存储过程的效率要查 一些。 使用ETL工具后，产生的文档更清晰，比直接开发的 文档易懂，即便是有开发着离职也不会出现断层。 这种工具更容易适应业务变更。
数据的ETL过程描述
10
抽取/抓取 清洗 集结地 (Staging Area) 拒绝数据的信息 转换
加载与索引
操作型 系统
有关拒绝数据的信息
EDW 或 ODS
数据抽取
11
准备工作：
数据是从几个业务系统中来？ 各个业务系统的数据库服务器运行什么DBMS？ 是否存在手工数据？ 手工数据量有多大？ 是否存在非结构化的数据？
例子1
34
每张数码照片都包含EXIF信息，就是用来描述数码 图片的元数据。按照Exif 2.1标准，其中主要包含 这样一些信息
Image Description 图像描述、来源. 指生成图像的工具 Artist 作者 有些相机可以输入使用者的名字 Make 生产者 指产品生产厂家 Model 型号 指设备型号 Orientation方向 有的相机支持，有的不支持 Software软件 显示固件Firmware版本 DateTime日期和时间 ……
如 SQLServer和Oracle之间 如果不能建立数据库链接
一种是通过工具将源数据导出成.txt或者是.xls文件，然 后再将这些源系统文件导入到ODS中。 另外一种方法是通过程序接口来完成
数据源抽取对象表
14
数据平台 Windows/SQL Server Windows/SQL Server A B
与存放DW的数据库系统相同的数据源
12
设计上比较容易 一般情况下，DBMS（SQLServer、Oracle） 都会提供数据库链接功能
在DW数据库服务器和原业务系统之间建立直接的 链接关系就可以写Select语句直接访问
与DW数据库系统不同的数据源
13
一般情况下可以通过ODBC的方式建立数据库 链接
复合条件 AND AND OR ……
备注 采购商品数量 小于50000 采购商品数量 大于500 商品前两位 非’AB’ ……
数据抽取的目标列与源列对应关系表
16
目标表.列
源表.列
转换公式
备注
SJCK_KHCG.KHZY
KHCG.KHZY
直接转换
客户职业
SJCK_KHCG.CGRQ
KHCG.CGRQ
元数据定义
40
数据的数据 (data about data) 结构化数据 (Structured data about data) 用于描述数据的内容（what）、覆盖范围（where, when）、质量、 管理方式、数据的所有者（who）、数据的提供方式（how）等信息， 是数据与数据用户之间的桥梁； 资源的信息 (Information about a resource) 编目信息 (Cataloguing information) 管理、控制信息（Administrative information) 是一组独立的关于资源的说明（metadata is a set of independent assertions about a resource ） data that defines and describes other data （ISO/IEC 111793:2003(E)）
20
一般情况下，业务系统会记录业务发生的时间， 我们可以用来做增量的标志,每次抽取之前首先 判断ODS中记录最大的时间，然后根据这个时 间去业务系统取大于这个时间所有的记录。 利用业务系统的时间戳，一般情况下，业务系 统没有或者部分有时间戳。
数据清洗
21
过滤不符合要求的数据，将过滤的结果交给业务主 管部门，确认是否过滤掉还是由业务单位修正之后 再进行抽取
不完整的数据：
如供应商的名称、客户的区域信息、业务系统中主表与明细 表不能匹配
错误的数据：业务系统不够健全，在接收输入后没有进 行判断直接写入DW
如数值数据输成全角数字字符、字符串数据后面有一个回车 操作、日期格式不正确
重复的数据：
将重复数据记录的所有字段导出来，让客户确认并整理
数据转换
22
不一致数据转换
第三章 数据仓库中的ETL和元数据
基本概念
2
ETL（Extract/Transform/Loading）是数据从 业务系统抽取转化到数据仓库的过程 Metadata Metadata可以理解成Data about data，关于 Data data 数据的数据；数据仓库的整合性是基于元数 据的统一
1.
2.
3.
如果熟悉直接写代码,那么项目效率更高
第二章 数据仓库中的ETL和元数据
31
ETL 元数据 外部数据
元数据
32
元数据的概念 元数据的内容与用途 元数据的分类 元数据的使用、常见问题、解决建议 元数据的标准化及维护与管理
什么是元数据
33
“数据太多，信息太少” 只有充分理解数据才能分析数据，因此元数对数据仓库尤 为重要。 元数据 关于数据仓库的数据 数据仓库建设过程中所产生的有关数据源定义、目标 定义、转换规则等相关的关键数据，同时元数据还包 含关于数据含义的商业信息，是整个数据仓库的核心.
ETL的关键名词
9
数据抽取：
只提取系统分析必需的那一部分数据
数据清洗
将错误的、不一致的数据在进入数据仓库之前予以更正或删除， 以免影响决策支持系统决策的正确性
数据转化
业务系统使用不同数据库上的产品，各种产品提供的数据类型不 同，数据格式不同
数据装载
将数据按照物理数据模型定义的表结构装入数据仓库，这些步骤 包括清空数据域、填充空格、有效性检查等
元数据的实际问题
41
人事
销售
数据采集 数据传输 数据转换 数据清洗
RDBMS
ODS/Staging Area
企业经营 分析
Statistics 业务 Clustering 管理
客户关系 管理 业务流程 分析 财务分析
库存
财务
外部系统
数据加载 数据汇总 数据模型
RDBMS 数据仓库 数据集市
Neural Nets Artificial Intelligence
矛盾
数据来源不同
大量、分散、不清洁
数据的分析、挖掘活动必须建立在一个数据清洁、 结构良好的数据仓库基础之上
ETL作用
6
解决数据分散问题
解决数据不清洁问题
方便企业各部门构筑数据集市
ETL
7
ETL的概念 关键名词 过程描述 实现办法 ETL的工具
ETL的概念
8
数据ETL是用来实现异构数据源的数据集成，即 完成数据的抓取/抽取（Capture/Extract）、清 洗 （ Scrub or data cleansing ） 、 转 换 （Transform）、装载与索引（Load and Index） Transform Load Index 等数据调和工作
书上的说法
23
数据提取 数据验证 数据清理 数据集成 数据聚集 将数据装入仓库目标表
数据ETL过程的实施要点
24
ETL过程是一个数据流动的过程，中间的“T” （转换）是关键 ； ETL工具的选择非常重要，运用合适的工具会事 半功倍 ； 如何保证数据质量？数据质量在一定程度上决 定了数据仓库的价值 。
)
捕获数据的方法
18
完全刷新
对移入DW的数据进行完全复制 经常变化
增量更新
捕获数据源中修改的数据 流水型增长、数据量大
捕获数据的方法
19
完全刷新
对移入DW的数据进行完全复制 经常变化
增量更新
捕获数据源中修改的数据 流水型增长、数据量大
对两者结合的数据先考虑增量更新，再考虑完 全刷新
增量更新的问题
整合的过程，将不同业务系统的相同类型的数据统一，
E.g., 供应商在结算系统的编码是XX0001 vs. 客户关系管理 中编码是YY0001
数据粒度的转换
将业务系统数据按照数据仓库粒度进行聚合。
商务规则的Biblioteka Baidu算
不同的企业有不同的业务规则、不同的数据指标,需要 在ETL中将这些数据指标计算好了之后存储在数据仓库 中，以供分析使用
元数据与数据是什么关系？
44
元数据也是数据，其本身也可以作为被描述的 对象 元数据可以出现在：
例子3
36
元数据的直观作用
37
信息的描述和分类可以实现格式化 从而为机器处理创造了可能 ETL是数据仓库从业务系统获得数据的必经之 路，元数据则是地图
数据字典与元数据
38
数据库的数据字典 数据仓库的元数据
数据库的数据字典
39
数据字典是数据库中各类数据描述的集合 (1) 数据项 (2) 数据结构 (3) 数据流 (4) 数据存储 (5) 处理过程
第三章 数据仓库中的ETL和元数据
3
ETL 元数据 外部数据
第三章 数据仓库中的ETL和元数据
需求/建模 需求 建模
ETL开发 开发
前端开发
数据仓库项目三部曲 数据仓库项目的三部曲
4
ETL作用
5
需求
企业管理者希望按需访问 存储各种格式的企业内部和企业外部数据：
经营数据、历史数据、现行数据、Internet数据、元数 据
股东
OLAP
Metadata
业务系统到数据仓库 ETL
(Meta data) 元数据组成 数据仓库 Target Database
数据展现工具 Front-End Tool
42
两者区别
43
数据仓库的元数据除对数据仓库中数据的描述 （数据仓库字典）外，还有以下三类元数据 ： (1) 关于数据源的元数据 (2) 关于抽取和转换的元数据 (3) 关于最终用户的元数据
具备数据仓库的部分特征和OLTP系统的部分特征，它是“面向主题的、集成的、 系统的部分特征，它是“面向主题的、集成的、 具备数据仓库的部分特征和 系统的部分特征 当前或接近当前的、不断变化的”数据。 当前或接近当前的、不断变化的”数据。
一般在带有ODS的系统体系结构中，ODS都设计为如下几个作用： 在业务系统和数据仓库之间形成一个隔离层 转移一部分业务系统细节查询的功能 完成数据仓库中不能完成的一些功能
例子2
35
IMDB本身也定义了一套元数据，用来描述每一部 电影
下面是它的一级元数据，每一级下面又列出了二级元数 据，总共加起来，可以从100多个方面刻画一部电影： Cast and Crew（演职人员）、Company Credits（相 关公司）、Basic Data（基本情况）、Plot & Quotes（ 情节和引语）、Fun Stuff（趣味信息）、Links to Other Sites（外部链接）、Box Office and Business（ 票房和商业开发）、Technical Info（技术信息）、 Literature（书面内容）、Other Data（其他信息）。
数据库
表名 KHCG SPQK ZGYJ ……
备注 客户采购商品表 商品情况表 职工业绩表 ……
Windows/Access C …… ……
数据源抽取规则表
15
表.列名 KHCG.CGSL KHCG.CGSL SPQK.SPID ……
过滤与连接 条件 < > ≠ ……
比较值 50000 500 ‘AB’ ……
将月/年/日的日期 格式转换成年/月/ 日格式 ……
客户采购日期
……
……
……
文件类型数据源（.txt,.xls）
17
人工实现：利用数据库工具将这些数据导入到 指定的数据库，然后从指定的数据库中抽取 工具实现：
如SQLServer2005的SSIS服务的平面数据源和平 面目标等组件导入ODS中去 (ODS（Operational Data Store）是数据仓库体系结构中的一个可选部分，ODS 体系结构中的一个可选部分， （ ） 数据仓库体系结构中的一个可选部分
ETL的实现办法
25
ETL工具
快速建立ETL过程，屏蔽复杂编码，降低难度 缺少灵活性
SQL方法实现
灵活，提高ETL运行效率 编码复杂，对技术要求比较高
ETL+SQL
综合前两种优点，提高ETL开发速度和效率
ETL工具
26
ETL工具分类 专业ETL厂商和产品
功能详尽、价格昂贵
整体方案提供商和产品
提供数据仓库存储、设计、展现工具，同时也提供 相应的ETL工具
ETL商业产品
27
ETL工具的选择
28
对平台的支持 对数据源的支持 数据转换功能 管理和调度功能 集成和开放性 对元数据的管理
问题
29
数据仓库厂商和数据仓库项目面试问题
使用ETL工具的优点是什么？与直接写代码开发区 别在哪里？
问题
30
数据仓库厂商和数据仓库项目面试问题
使用ETL工具的优点是什么？与直接写代码开发区别在 哪里？ ETL工具开发更规范，条理性更好，便于维护。可能 某些ETL工具效率比直接开发的存储过程的效率要查 一些。 使用ETL工具后，产生的文档更清晰，比直接开发的 文档易懂，即便是有开发着离职也不会出现断层。 这种工具更容易适应业务变更。
数据的ETL过程描述
10
抽取/抓取 清洗 集结地 (Staging Area) 拒绝数据的信息 转换
加载与索引
操作型 系统
有关拒绝数据的信息
EDW 或 ODS
数据抽取
11
准备工作：
数据是从几个业务系统中来？ 各个业务系统的数据库服务器运行什么DBMS？ 是否存在手工数据？ 手工数据量有多大？ 是否存在非结构化的数据？
例子1
34
每张数码照片都包含EXIF信息，就是用来描述数码 图片的元数据。按照Exif 2.1标准，其中主要包含 这样一些信息
Image Description 图像描述、来源. 指生成图像的工具 Artist 作者 有些相机可以输入使用者的名字 Make 生产者 指产品生产厂家 Model 型号 指设备型号 Orientation方向 有的相机支持，有的不支持 Software软件 显示固件Firmware版本 DateTime日期和时间 ……
如 SQLServer和Oracle之间 如果不能建立数据库链接
一种是通过工具将源数据导出成.txt或者是.xls文件，然 后再将这些源系统文件导入到ODS中。 另外一种方法是通过程序接口来完成
数据源抽取对象表
14
数据平台 Windows/SQL Server Windows/SQL Server A B
与存放DW的数据库系统相同的数据源
12
设计上比较容易 一般情况下，DBMS（SQLServer、Oracle） 都会提供数据库链接功能
在DW数据库服务器和原业务系统之间建立直接的 链接关系就可以写Select语句直接访问
与DW数据库系统不同的数据源
13
一般情况下可以通过ODBC的方式建立数据库 链接
复合条件 AND AND OR ……
备注 采购商品数量 小于50000 采购商品数量 大于500 商品前两位 非’AB’ ……
数据抽取的目标列与源列对应关系表
16
目标表.列
源表.列
转换公式
备注
SJCK_KHCG.KHZY
KHCG.KHZY
直接转换
客户职业
SJCK_KHCG.CGRQ
KHCG.CGRQ
元数据定义
40
数据的数据 (data about data) 结构化数据 (Structured data about data) 用于描述数据的内容（what）、覆盖范围（where, when）、质量、 管理方式、数据的所有者（who）、数据的提供方式（how）等信息， 是数据与数据用户之间的桥梁； 资源的信息 (Information about a resource) 编目信息 (Cataloguing information) 管理、控制信息（Administrative information) 是一组独立的关于资源的说明（metadata is a set of independent assertions about a resource ） data that defines and describes other data （ISO/IEC 111793:2003(E)）
20
一般情况下，业务系统会记录业务发生的时间， 我们可以用来做增量的标志,每次抽取之前首先 判断ODS中记录最大的时间，然后根据这个时 间去业务系统取大于这个时间所有的记录。 利用业务系统的时间戳，一般情况下，业务系 统没有或者部分有时间戳。
数据清洗
21
过滤不符合要求的数据，将过滤的结果交给业务主 管部门，确认是否过滤掉还是由业务单位修正之后 再进行抽取
不完整的数据：
如供应商的名称、客户的区域信息、业务系统中主表与明细 表不能匹配
错误的数据：业务系统不够健全，在接收输入后没有进 行判断直接写入DW
如数值数据输成全角数字字符、字符串数据后面有一个回车 操作、日期格式不正确
重复的数据：
将重复数据记录的所有字段导出来，让客户确认并整理
数据转换
22
不一致数据转换