文档章节

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

 张欢19933
发布于 2016/09/12 16:08
字数 4170
阅读 338
收藏 6

一、Data仓库的架构

  Data仓库(Data Warehouse DW)是为了便于多维分析和多角度展现而将Data按特定的模式进行存储所建立起来的关系型Datcbase,它的Data基于OLTP源Systam。Data仓库中的Data是细节的、集成的、面向主题的,以OLAPSystam的分析需求为目的。

  Data仓库的架构模型包括了星型架构与雪花型架构两种模式。星型架构的中间为事实表,四周为维度表,类似星星;而相比较而言,雪花型架构的中间为事实表,两边的维度表可以再有其关联子表,从而表达了清晰的维度层次关系。

  从OLAPSystam的分析需求和ETL的处理效率两方面来考虑:星型结构聚合快,分析效率高;而雪花型结构明确,便于与OLTPSystam交互。因此,在实际项目中,我们将综合运用星型架构与雪花型架构来设计Data仓库。

  那么,下面我们就来看一看,构建企业级Data仓库的流程。

  二、构建企业级Data仓库五步法

  (一)、确定主题

  即确定Data分析或前端展现的主题。例如:我们希望分析某年某月某一地区的啤酒销售情况,这就是一个主题。主题要体现出某一方面的各分析角度(维度)和统计数value型Data(量度)之间的关系,确定主题时要综合考虑。

  我们可以形象的将一个主题想象为一颗星星:统计数value型Data(量度)存在于星星中间的事实表;分析角度(维度)是星星的各个角;我们将通过维度的组合,来考察量度。那么,“某年某月某一地区的啤酒销售情况”这样一个主题,就要求我们通过时间和地区两个维度的组合,来考察销售情况这个量度。从而,不同的主题来源于Data仓库中的不同子集,我们可以称之为Data集市。Data集市体现了Data仓库某一方面的信息,多个Data集市构成了Data仓库。

  (二)、确定量度

  在确定了主题以后,我们将考虑要分析的技术指标,诸如年销售额之类。它们一般为数value型Data。我们或者将该Data汇总,或者将该Data取次数、独立次数或取最大最小value等,这样的Data称为量度。

  量度是要统计的指标,必须事先选择恰当,基于不同的量度可以进行复杂关键性能指标(KPI)等的设计和计算。

  (三)、确定事实Data粒度

  在确定了量度之后,我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况。考虑到量度的聚合程度不同,我们将采用“最小粒度原则”,即将量度的粒度设置到最小。

  例如:假设目前的Data最小记录到秒,即Datcbase中记录了每一秒的交易额。那么,如果我们可以确认,在将来的分析需求中,时间只需要精确到天就可以的话,我们就可以在ETL处理过程中,按天来汇总Data,此时,Data仓库中量度的粒度就是“天”;反过来,如果我们不能确认将来的分析需求在时间上是否需要精确到秒,那么,我们就需要遵循“最小粒度原则”,在Data仓库的事实表中保留每一秒的Data,以便日后对“秒”进行分析。

  在采用“最小粒度原则”的同时,我们不必担心海量Data所带来的汇总分析效率问题,因为在后续建立多维分析模型(CUBE)的时候,我们会对Data提前进行汇总,从而保障产生分析结果的效率。关于建立多维分析模型(CUBE)的相关问题,我们将在下期栏目中予以阐述。

  (四)、确定维度

  维度是指分析的各个角度。例如我们希望按照时间,或者按照地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度。基于不同的维度,我们可以看到各量度的汇总情况,也可以基于所有的维度进行交叉分析。

  这里我们首先要确定维度的层次(HierarChy)和级别(Level。我们在时间维度上,按照“年-季度-月”形成了一个层次,其中“年”、“季度”、“月”成为了这个层次的3个级别;同理,当我们建立产品维度时,我们可以将“产品大类-产品子类-产品”划为一个层次,其中包含“产品大类”、“产品子类”、“产品”三个级别。

  那么,我们分析中所用到的这些维度,在Data仓库中的存在形式是怎样的呢?

 我们可以将3个级别设置成一张Data表中的3个字段,比如时间维度;我们也可以使用三张表,分别保存产品大类、产品子类、产品三部分Data,比如产品维度。

  另外,value得一提的是,我们在建立维度表时要充分使用代理键。代理键是数value型的ID号码(例如每张表的第一个字段),它唯一标识了每一维度成员。更重要的是,在聚合时,数value型字段的匹配和比较,JOIN效率高,便于聚合。同时,代理键对缓慢变化维度有着重要的意义,在原Data主键相同的情况下,它起到了对新Data与历史Data的标识作用。

  在此,我们不妨谈一谈维度表随时间变化的问题,这是我们经常会遇到的情况,我们称其为缓慢变化维度。

  比如我们增加了新的产品,或者产品的ID号码修改了,或者产品增加了一个新的属性,此时,维度表就会被修改或者增加新的记录行。这样,我们在ETL的过程中,就要考虑到缓慢变化维度的处理。对于缓慢变化维度,有三种情况:

  1、缓慢变化维度第一种TYPE:

  历史Data需要修改。这种情况下,我们使用UPDATEmethod来修改维度表中的Data。例如:产品的ID号码为123,后来发现ID号码错了,需要改写成456,那么,我们就在ETL处理时,直接修改维度表中原来的ID号码为456。

  2、缓慢变化维度第二种TYPE:

  历史Data保留,新增Data也要保留。这时,要将原Data更新,将新Data插入,我们使用UPDATE / INSERT。比如:某一员工2005年在A部门,2006年时他调到了B部门。那么在统计2005年的Data时就应该将该员工定位到A部门;而在统计2006年Data时就应该定位到B部门,然后再有新的Data插入时,将按照新部门(B部门)进行处理,这样我们的做法是将该维度成员列表加入标识列,将历史的Data标识为“过期”,将目前的Data标识为“当前的”。另一种method是将该维度打上时间戳,即将历史Data生效的时间段作为它的一个属性,在与原始表匹配生成事实表时将按照时间段进行关联,这种method的好处是该维度成员生效时间明确。

  3、缓慢变化维度第三种TYPE:

  新增Data维度成员改变了属性。例如:某一维度成员新加入了一列,该列在历史Data中不能基于它浏览,而在目前Data和将来Data中可以按照它浏览,那么此时我们需要改变维度表属性,即加入新的字段列。那么,我们将使用存储过程或程式生成新的维度属性,在后续的Data中将基于新的属性进行查看。

  (五)、创建事实表

  在确定好事实Data和维度后,我们将考虑加载事实表。

  在公司的大量Data堆积如山时,我们想看看里面究竟是什么,结果发现里面是一笔笔生产记录,一笔笔交易记录… 那么这些记录是我们将要建立的事实表的原始Data,即关于某一主题的事实记录表。

  我们的做法是将原始表与维度表进行关联,生成事实表。注意在关联时有为空的Data时(Data源脏),需要使用外连接,连接后我们将各维度的代理键取出放于事实表中,事实表除了各维度代理键外,还有各量度Data,这将来自原始表,事实表中将存在维度代理键和各量度,而不应该存在描述性信息,即符合“瘦高原则”,即要求事实表Data条数尽量多(粒度最小),而描述性信息尽量少。

  如果考虑到扩展,可以将事实表加一唯一标识列,以为了以后扩展将该事实作为雪花型维度,不过不需要时一般建议不用这样做。

  事实Data表是Data仓库的核心,需要精心维护,在JOIN后将得到事实Data表,一般记录条数都比较大,我们需要为其设置复合主键和索引,以呈现Data的完整性和基于Data仓库的查询性能优化。事实Data表与维度表一起放于Data仓库中,如果前端需要连接Data仓库进行查询,我们还需要建立一些相关的中间汇总表或物化视,以方便查询。

  三、什么是ETL

  在Data仓库的构建中,ETL贯穿于项目始终,它是整个Data仓库的生命线,包括了Data清洗、整合、convert、加载等各个过程。如果说Data仓库是一座大厦,那么ETL就是大厦的根基。ETL抽取整合Data的好坏直接影响到最终的结果展现。所以ETL在整个Data仓库项目中起着十分关键的作用,必须摆到十分重要的位置。

  ETL是Data抽取(ExtraCt)、convert(Transform)、加载(Load )的简写,它是指:将OLTPSystam中的Data抽取出来,并将不同Data源的Data进行convert和整合,得出一致性的Data,然后加载到Data仓库中。

  那么,在这一convert过程中,我们就完成了对Data格式的更正、对Data字段的合并、以及新增指标的计算三项操作。类似地,我们也可以根据其他需求,完善Data仓库中的Data。

  简而言之,通过ETL,我们可以基于源Systam中的Data来生成Data仓库。ETL为我们搭建了OLTPSystam和OLAPSystam之间的桥梁。

  四、项目实践技巧

  (一)、准备区的运用

  在构建Data仓库时,如果Data源位于一台服务器上,Data仓库在另一台服务器端,考虑到Data源Server端访问频繁,并且Data量大,需要不断更新,所以可以建立准备区Datcbase。先将Data抽取到准备区中,然后基于准备区中的Data进行处理,这样处理的好处是防止了在原OLTPSystam中频繁访问,进行Data计算或排序等操作。

  例如我们可以按照天将Data抽取到准备区中,基于Data准备区,我们将进行Data的convert、整合、将不同Data源的Data进行一致性处理。Data准备区中将存在原始抽取表、convert中间表和临时表以及ETL日志表等。

  (二)、时间戳的运用

  时间维度对于某一事实主题来说十分重要,因为不同的时间有不同的统计Data信息,那么按照时间记录的信息将发挥很重要的作用。在ETL中,时间戳有其特殊的作用,在上面提到的缓慢变化维度中,我们可以使用时间戳标识维度成员;在记录Datcbase和Data仓库的操作时,我们也将使用时间戳标识信息。例如:在进行Data抽取时,我们将按照时间戳对OLTPSystam中的Data进行抽取,比如在午夜0:00取前一天的Data,我们将按照OLTPSystam中的时间戳取GETDATE到GETDATE减一天,这样得到前一天Data。

  (三)、日志表的运用

  在对Data进行处理时,难免会发生Data处理错误,产生出错信息,那么我们如何获得出错信息并及时修正呢? method是我们使用一张或多张Log日志表,将出错信息记录下来,在日志表中我们将记录每次抽取的条数、处理成功的条数、处理失败的条数、处理失败的Data、处理时间等等。这样,当Data发生错误时,我们很容易发现问题所在,然后对出错的Data进行修正或重新处理。

  (四)、使用调度

  在对Data仓库进行增量更新时必须使用调度,即对事实Data表进行增量更新处理。在使用调度前要考虑到事实Data量,确定需要多长时间更新一次。比如希望按天进行查看,那么我们最好按天进行抽取,如果Data量不大,可以按照月或半年对Data进行更新。如果有缓慢变化维度情况,调度时需要考虑到维度表更新情况,在更新事实Data表之前要先更新维度表。

  调度是Data仓库的关键环节,要考虑缜密。在ETL的流程搭建好后,要定期对其运行,所以调度是运行ETL流程的关键步骤。每一次调度除了写入Log日志表的Data处理信息外,还要使用发送Email或报警服务等,这样也方便的技术人员对ETL流程的把握,增强了安全性和Data处理的准确性。

  五、总结

  构建企业级Data仓库需要简单的五步,掌握了这五步的method,我们可以构建一个强大的Data仓库。然而,每一步都有很深的内容需要研究与挖掘,尤其在实际项目中,我们要综合考虑。例如:如果Data源的脏Data很多,在搭建Data仓库之前我们首先要进行Data清洗,以剔除掉不需要的信息和脏Data。

  ETL是OLTPSystam和OLAPSystam之间的桥梁,是Data从源Systam流入Data仓库的通道。在Data仓库的项目实施中,它关系到整个项目的Data质量,所以马虎不得,必须将其摆到重要位置,将Data仓库这一大厦的根基筑牢。


相关参考书推荐:

1、数据仓库工具箱(第3版)——维度建模权威指南数据管理书籍大数据应用与技术丛书维度建模技术教程

http://s.click.taobao.com/K1TNrXx

2、【正版】数据仓库(原书第4版)

http://s.click.taobao.com/q6HNrXx

3、数据仓库工具箱 第3版维度建模权威指南维度建模技术教程计算机教材 电子商务 客户关系管理清华大学出版社

http://s.click.taobao.com/AhLNrXx

4、正版】数据仓库应用指南数据仓库与商务智能最佳实践

http://s.click.taobao.com/mVCNrXx

5、社交网站的数据挖掘与分析(原书第2版)(老鼠书,第1版曾获2011年Jolt生产效率大奖)

http://s.click.taobao.com/S0HNrXx

6、大数据技术全解:基础、设计、开发与实践(三分虚拟技术,七分分布式管理,十二分杨巨龙 书店 数据挖掘、数据仓库书籍畅销书

http://s.click.taobao.com/fu4NrXx

7、数据仓库与数据挖掘教程 第2版清华出版社 

http://s.click.taobao.com/h97NrXx

8、数据仓库(原书第4版)(决战大数据时代!IT技术人员必备)

http://s.click.taobao.com/OY1NrXx

9、数据挖掘:概念与技术(原书第3版)

http://s.click.taobao.com/JMwbpXx

10、大数据 互联网大规模数据挖掘与分布式处理 第2版 大数据搜索引擎技术书籍 数据挖掘参考书籍 计算机教材畅销书籍

http://s.click.taobao.com/uVlbpXx

11、数据挖掘导论(完整版)/图灵计算机科学丛书 陈封能等著

http://s.click.taobao.com/zylbpXx

本文转载自:http://blog.sina.com.cn/s/blog_6111c0620100z2cd.html

下一篇: OLTP
粉丝 46
博文 538
码字总数 247200
作品 0
海淀
私信 提问
[北京]电商平台招聘数据仓库工程师

数据库工程师 岗位职责: 1、负责数据仓库建模、数据预处理子系统的设计和开发: 2、负责数据仓库应用产品设计和开发: 3、负责数据仓库ETL流程的优化及解决ETL相关技术问题: 任职要求: 1、...

刘恣意
2012/05/03
630
13
《数据仓库实践》

序言 2017 年初,我开始在简书上写关于数据仓库的系列博客,博客主题围绕大数据场景下数据仓库的理论和实践来展开,截止现在已有十篇左右。最初写作的时候主要是抱着学习和总结的态度,导致很...

dantezhao
2017/11/27
0
0
数据仓库经验老鸟分享零基础转行大数据的最佳自学之路

本人从08年从事数据仓库工程师转到目前大数据管理工作的,对数据仓库感情颇深,也曾一直苦于没有太好的学习资料,自行摸索。 我认为数据仓库更像一门管理学艺术,大多用的也是关系型数据库,...

董黎明
2018/06/22
118
0
创业公司做数据分析(六)数据仓库的建设

  作为系列文章的第六篇,本文将重点探讨数据处理层中数据仓库的建设。在第二篇运营数据系统一文,有提到早期的数据服务中存在不少问题,虽然在做运营Dashboard系统时,对后台数据服务进行...

zwgdft
2017/02/02
0
0
开源ETL工具在数据集成中的应用研究

大数据环境下,ETL负责将分散的、异构数据源中的数据如关系数据、平面文件数据等集成到数据仓库或数据集市的关键步骤,是后期联机分析处理、数据挖掘的基础环节。本课题以开源的ETL软件Kettl...

freeBryant24
2014/02/12
674
0

没有更多内容

加载失败,请刷新页面

加载更多

golang-字符串-地址分析

demo package mainimport "fmt"func main() {str := "map.baidu.com"fmt.Println(&str, str)str = str[0:5]fmt.Println(&str, str)str = "abc"fmt.Println(&s......

李琼涛
今天
4
0
Spring Boot WebFlux 增删改查完整实战 demo

03:WebFlux Web CRUD 实践 前言 上一篇基于功能性端点去创建一个简单服务,实现了 Hello 。这一篇用 Spring Boot WebFlux 的注解控制层技术创建一个 CRUD WebFlux 应用,让开发更方便。这里...

泥瓦匠BYSocket
今天
6
0
从0开始学FreeRTOS-(列表与列表项)-3

FreeRTOS列表&列表项的源码解读 第一次看列表与列表项的时候,感觉很像是链表,虽然我自己的链表也不太会,但是就是感觉很像。 在FreeRTOS中,列表与列表项使用得非常多,是FreeRTOS的一个数...

杰杰1号
今天
8
0
Java反射

Java 反射 反射是框架设计的灵魂(使用的前提条件:必须先得到代表的字节码的 Class,Class 类 用于表示.class 文件(字节码)) 一、反射的概述 定义:JAVA 反射机制是在运行状态中,对于任...

zzz1122334
今天
5
0
聊聊nacos的LocalConfigInfoProcessor

序 本文主要研究一下nacos的LocalConfigInfoProcessor LocalConfigInfoProcessor nacos-1.1.3/client/src/main/java/com/alibaba/nacos/client/config/impl/LocalConfigInfoProcessor.java p......

go4it
昨天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部