一、什么是 ETL
ETLETL是数据抽取(EXTRACT)、转换(TRANSFORM)、加载(LOAD )的简写,它是将OLTP系统中的数据经过抽取,并将不同数据源的数据进行转换、整合,得出一致性的数据,然后加载到 数据仓库中。简而言之ETL是完成从 OLTP系统到OLAP系统的过程(图一:PIC1.JPG)。
二、数据仓库的架构
数据仓库(DATA WAREHOUSE \ DW)是基于OLTP系统的数据源,为了便于多维分析和 多角度展现将其数据按特定的模式进行存储而建立的关系型数据库,它不同于多维数据库,数据仓库中的数据是细节的,集成的,数据仓库是面向主题的,是以 OLAP系统为分析目的。它包括星型架构(图二:PIC2.JPG)与雪花型架构(图三:PIC3.JPG),其中星型架构中间为事实表,四周为维度表, 类似星星;雪花型架构中间为事实表,两边的维度表可以再有其关联子表,而在星型中只允许一张表作为维度表与事实表关联,雪花型一维度可以有多张表,而星型不可以。考虑到效率时,星型聚合快,效率高,不过雪花型结构明确,便于与OLTP系统交互。在实际项目中,我们将综合运用星型架构与雪花型架构。
三、ETL构建企业级数据仓库五步法的流程
(一)、确定主题
即确定数据分析或前端展现的某一方面的分析主题,例如我们分析某年某月某一地区的啤酒销售情况,就是一个主题。主题要体现某一方面的各分析角度(维度)和统计数值型数据(量度),确定主题时要综合考虑,一个主题在数据仓库中即为一个数据集市,数据集市体现了某一方面的信息,多个数据集市构成了数据仓库。
(二)、确定量度
在确定了主题以后,我们将考虑要分析的技术指标,诸如年销售额此类,一般为数值型数据,或者将该数据汇总,或者将该数据取次数,独立次数或取最大最小值等,这样的数据称之为量度。量度是要统计的指标,必须事先选择恰当,基于不同的量度可以进行复杂关键性能指标(KPI)等的计算。
(三)、确定事实数据粒度
在确定了量度之后我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况,考虑到量度的聚合程度不同,我们将采用“最小粒度原则”,即将量度的粒度设置 到最小,例如我们将按照时间对销售额进行汇总,目前的数据最小记录到天,即数据库中记录了每天的交易额,那么我们不能在ETL时将数据进行按月或年汇总, 需要保持到天,以便于后续对天进行分析。而且我们不必担心数据量和数据没有提前汇总带来的问题,因为在后续的建立CUBE时已经将数据提前汇总了。
(四)、确定维度
维度是要分析的各个角度,例如我们希望按照时间,或者按照地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度,基于不同的维度我们可以看到各量度的汇总情况,我们可以基于所有的维度进行交叉分析。这里我们首先要确定维度的层次(HIERARCHY)和级别(LEVEL)(图 四:PIC4.JPG),维度的层次是指该维度的所有级别,包括各级别的属性;维度的级别是指该维度下的成员,例如当建立地区维度时我们将地区维度作为一个级别,层次为省、市、县三层,考虑到维度表要包含尽量多的信息,所以建立维度时要符合“矮胖原则”,即维度表要尽量宽,尽量包含所有的描述性信息,而不 是统计性的数据信息。
还有一种常见的情况,就是父子型维度(图五:PIC5.JPG),该维度一般用于非叶子节点含有成员等情况,例如公司员工的维度,在统计员工的工资时,部 门主管的工资不能等于下属成员工资的简单相加,必须对该主管的工资单独统计,然后该主管部门的工资等于下属员工工资加部门主管的工资,那么在建立员工维度时,我们需要将员工维度建立成父子型维度,这样在统计时,主管的工资会自动加上,避免了都是叶子节点才有数据的情况。
另外,在建立维度表时要充分使用代理键,代理键是数值型的ID号码,好处是代理键唯一标识了每一维度成员信息,便于区分,更重要的是在聚合时由于数值型匹 配,JOIN效率高,便于聚合,而且代理键对缓慢变化维度有更重要的意义,它起到了标识历史数据与新数据的作用,在原数据主键相同的情况下,代理键起到了对新数据与历史数据非常重要的标识作用。
有时我们也会遇到维度缓慢变化的情况,比如增加了新的产品,或者产品的ID号码修改了,或者产品增加了一个新的属性,此时某一维度的成员会随着新的数据的加入而增加新的维度成员,这样我们要考虑到缓慢变化维度的处理,对于缓慢变化维度,有三种情况:
1、缓慢变化维度第一种类型:历史数据需要修改。这样新来的数据要改写历史数据,这时我们要使用UPDATE,例如产品的ID号码为123,后来发现ID 号码错误了,需要改写成456,那么在修改好的新数据插入时,维度表中原来的ID号码会相应改为456,这样在维度加载时要使用第一种类型,做法是完全更 改。
2、缓慢变化维度第二种类型:历史数据保留,新增数据也要保留。这时要将原数据更新,将新数据插入,需要使用UPDATE / INSERT,比如某一员工2005年在A部门,2006年时他调到了B部门。那么在统计2005年的数据时就应该将该员工定位到A部门;而在统计 2006年数据时就应该定位到B部门,然后再有新的数据插入时,将按照新部门(B部门)进行处理,这样我们的做法是将该维度成员列表加入标识列,将历史的 数据标识为“过期”,将目前的数据标识为“当前的”。另一种方法是将该维度打上时间戳,即将历史数据生效的时间段作为它的一个属性,在与原始表匹配生成事实表时将按照时间段进行关联,这样的好处是该维度成员生效时间明确。
3、缓慢变化维度第三种类型:新增数据维度成员改变了属性。例如某一维度成员新加入了一列,该列在历史数据中不能基于它浏览,而在目前数据和将来数据中可以按照它浏览,那么此时我们需要改变维度表属性,即加入新的列,那么我们将使用存储过程或程序生成新的维度属性,在后续的数据中将基于新的属性进行查看。
(五)、创建事实表
在确定好事实数据和维度后,我们将考虑加载事实表。
在公司的大量数据堆积如山时,我们想看看里面究竟是什么,结果发现里面是一笔笔生产记录,一笔笔交易记录… 那么这些记录是我们将要建立的事实表的原始数据,即关于某一主题的事实记录表。
我们的做法是将原始表与维度表进行关联,生成事实表(图六:PIC6.JPG)。注意在关联时有为空的数据时(数据源脏),需要使用外连接,连接后我们将 各维度的代理键取出放于事实表中,事实表除了各维度代理键外,还有各量度数据,这将来自原始表,事实表中将存在维度代理键和各量度,而不应该存在描述性信息,即符合“瘦高原则”,即要求事实表数据条数尽量多(粒度最小),而描述性信息尽量少。
如果考虑到扩展,可以将事实表加一唯一标识列,以为了以后扩展将该事实作为雪花型维度,不过不需要时一般建议不用这样做。
事实数据表是数据仓库的核心,需要精心维护,在JOIN后将得到事实数据表,一般记录条数都比较大,我们需要为其设置复合主键和索引,以为了数据的完整性和基于数据仓库的查询性能优化,事实数据表与维度表一起放于数据仓库中,如果前端需要连接数据仓库进行查询,我们还需要建立一些相关的中间汇总表或物化视图,以方便查询。
三、ETL中高级技巧的运用
(一)、准备区的运用
在构建数据仓库时,如果数据源位于一服务器上,数据仓库在另一服务器端,考虑到数据源SERVER端访问频繁,并且数据量大,需要不断更新,所以可以建立准备区数据库(图七:PIC7.JPG)。先将数据抽取到准备区中,然后基于准备区中的数据进行处理,这样处理的好处是防止了在原OLTP系统中中频繁访问,进行数据运算或排序等操作。例如我们可以按照天将数据抽取到准备区中,基于数据准备区,我们将进行数据的转换,整合,将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表,一些转换中间表和临时表以及ETL日志表等。
(二)、时间戳的运用
时间维度对于某一事实主题来说十分重要,因为不同的时间有不同的统计数据信息,那么按照时间记录的信息将发挥很重要的作用。在ETL中,时间戳有其特殊的 作用,在上面提到的缓慢变化维度中,我们可以使用时间戳标识维度成员;在记录数据库和数据仓库的操作时,我们也将使用时间戳标识信息,例如在进行数据抽取时,我们将按照时间戳对OLTP系统中的数据进行抽取,比如在午夜0:00取前一天的数据,我们将按照OLTP系统中的时间戳取GETDATE到 GETDATE减一天,这样得到前一天数据。
(三)、日志表的运用
在对数据进行处理时,难免会发生数据处理错误,产生出错信息,那么我们如何获得出错信息并及时修正呢? 方法是我们使用一张或多张LOG日志表,将出错信息记录下来,在日志表中我们将记录每次抽取的条数,处理成功的条数,处理失败的条数,处理失败的数据,处理时间等等,这样当数据发生错误时,我们很容易发现问题所在,然后对出错的数据进行修正或重新处理。
(四)、使用调度
在对数据仓库进行增量更新时必须使用调度(图八:PIC8.JPG),即对事实数据表进行增量更新处理,在使用调度前要考虑到事实数据量,需要多长时间更 新一次,比如希望按天进行查看,那么我们最好按天进行抽取,如果数据量不大,可以按照月或半年对数据进行更新,如果有缓慢变化维度情况,调度时需要考虑到维度表更新情况,在更新事实数据表之前要先更新维度表。
调度是数据仓库的关键环节,要考虑缜密,在ETL的流程搭建好后,要定期对其运行,所以调度是执行ETL流程的关键步骤,每一次调度除了写入LOG日志表 的数据处理信息外,还要使用发送EMAIL或报警信息等,这样也方便的技术人员对ETL流程的把握,增强了安全性和数据处理的准确性。
四、总结
ETL构建数据仓库需要简单的五步,掌握了这五步的方法我们将构建一个强大的数据仓库,不过每一步都有很深的需要研究与挖掘,尤其在实际项目中,我们要综合考虑,例如如果数据源的脏数据很多,在搭建数据仓库之前我们首先要进行数据清洗,以剔除掉不需要的信息和脏数据。
总之,ETL是数据仓库的核心,掌握了ETL构建数据仓库的五步法,就掌握了搭建数据仓库的根本方法。不过,我们不能教条,基于不同的项目,我们还将要进行具体分析,如父子型维度和缓慢变化维度的运用等。在数据仓库构建中,ETL关系到整个项目的数据质量,所以马虎不得,必须将其摆到重要位置,将ETL这一大厦根基筑牢!
- 浏览: 134349 次
- 性别:
- 来自: 杭州
文章分类
- 全部博客 (84)
- Java (30)
- C/C++ (3)
- Linux (1)
- 数据库 (19)
- 搜索引擎 (1)
- 数据结构 (2)
- 杂七杂八 (4)
- WEB前端 (6)
- Spring (2)
- JavaScript (9)
- DHTMLX (3)
- 正则表达式 (1)
- JQuery (1)
- Report (2)
- PDF (2)
- Word (0)
- Excel (0)
- CSV (0)
- Test & QA (1)
- BI (Business Intelligence) (10)
- SQL (18)
- SAP Business Object (0)
- DataStage (3)
- CI (Continuous Integration) (1)
- Algorith (3)
- Portal (1)
- Struts (1)
- Oracle (9)
- MS SQL Server (3)
- Teradata (3)
- MySql (1)
- Data Warehouse (5)
- ETL (8)
发表评论
-
shell执行oracle存储过程,获得存储过程返回值【转】
2013-05-08 14:53 4915一个小的etl调度,需要将存储过程执行情况进行返回并控制其后 ... -
SQL server 与Oracle开发比较【转】
2013-04-03 22:03 1644SQL server 与Oracle开发比较 本文档主 ... -
ETL中缓慢变化维度的处理方式
2013-03-20 20:36 1718ETL中缓慢变化维度的处理方式 缓慢变化维处 ... -
SQL Server中的行列转换问题
2013-03-04 14:16 1406SQL Server中的行列转换问题 -
TERADATA解决锁表的方法
2013-02-04 16:16 2450TERADATA的锁表问题一直很头疼,经常早上过来发现任务 ... -
Teradata数据库SQL命令
2013-01-30 15:11 1420HELP 帮助用户了解数据库中各种对象的结构 SHOW 帮 ... -
ETL学习心得:探求数据仓库关键环节ETL的本质【转】
2012-12-12 14:44 4703ETL学习心得:探求数 ... -
浅析多维体系结构(总线架构), 一致性维度和一致性事实
2012-11-26 23:27 1855在Kimball的维度建模的数据仓库中,关于多维体系结构 ... -
数据仓库架构:星形模型PK雪花形模型
2012-11-23 16:24 1517星形模型(Star Schema)和雪花模型(Snowflak ... -
ETL的一些概念和问题(转)
2012-11-21 11:30 11151. What is a logical data mappi ... -
Teradata数据库中的求字段值的长度函数(即对应SQL中的Length()函数)
2012-06-28 12:57 3565公司的新项目用的是Teradata数据库,之前从未用过这个,所 ... -
用 IBM WebSphere DataStage 进行数据整合:第 3 部分: 实时数据整合
2012-05-15 14:08 974【转载】用 IBM WebSphere DataSta ... -
用 IBM WebSphere DataStage 进行数据整合:第 2 部分
2012-05-15 14:05 1062【转载】用 IBM WebSphere DataSta ... -
用 IBM WebSphere DataStage 进行数据整合:第 1 部分
2012-05-15 14:02 950【转载】用 IBM WebSphere DataSta ... -
(转载)SQL函数说明大全
2012-04-06 15:25 1896觉得该作者总结的很好,所以就转载过来了,想看原文的朋友请点击此 ... -
SQL Server CONVERT() 函数
2012-01-31 15:42 777SQL Server CONVERT() 函数 ... -
inner join on, left join on, right join on
2012-01-29 12:14 932inner join on, left join ... -
SELECT INTO FROM 与 INSERT INTO SELECT
2011-12-06 15:49 13091.INSERT INTO SELECT语句 ... -
常用DDL,DML,DCL SQL语句大全
2011-11-26 20:33 1206下列语句部分是Mssql语句,不可以在access中使用。 ... -
如何在S2SH中, 由.hbm.xml文件和POJO类生成数据库表
2011-05-26 13:55 953首先要在spring.xml里面配置sessionfactor ...
相关推荐
ETL工具算法构建企业级数据仓库五步法是指通过五个步骤来构建企业级数据仓库,即确定主题、设计数据仓库架构、构建ETL流程、构建数据仓库和维护数据仓库。 第一步:确定主题。确定主题是构建数据仓库的第一步,它是...
构建企业数据仓库五步法 构建企业数据仓库五步法是企业数据仓库的核心步骤,旨在帮助企业快速构建自己的数据仓库。该方法分为五个步骤:确定主题、确定量度、确定事实数据粒度、确定维度和确定事实表结构。 确定...
ETL构建企业级数据仓库五步法是指通过五个步骤来构建一个企业级的数据仓库。下面将对每一个步骤进行详细的解释: (一)、确定主题 确定主题是ETL构建数据仓库的第一步骤。在这个步骤中,需要确定数据仓库的主题域...
### ETL构建数据仓库 #### 一、ETL的概念及其重要性 ETL(Extract-Transform-Load)是数据抽取(Extract)、...通过以上五个步骤,我们可以系统地构建出一个高效、准确的企业级数据仓库,为企业提供有力的数据支持。
ETL构建企业级数据仓库五步法.docx ETL高级教程.docx 三大主流ETL工具选型.docx 什么是ETL.docx 商业智能 通过SSIS设计ETL来将Oracle,DB2,Sybase等数据源的数据定期导入到数据仓库.docx 商务智能(BI)的四大关键技术-...
总结来说,这份报告揭示了如何通过构建一个全面的ETL平台,利用元数据驱动和数据质量校验,提高数据仓库的ETL效率和数据质量。平台的模块化设计和灵活的架构确保了系统的可扩展性和适应性,为数据仓库的持续优化提供...
是构建数据仓库的重要一环,用户从数据源抽取出所需的数据,经过数据清洗,最终按照预先定义好的数据仓库模型,将数据加载到数据仓库中去; 2. 常用的ETL工具:主要有三大主流工具,分别是Ascential公司的Datastage...
《Spark+ClickHouse实战企业级数据仓库视频教程19章全》是一套全面解析大数据处理与分析的课程,旨在帮助学员掌握如何利用Spark和ClickHouse构建高效、高性能的企业级数据仓库。课程涵盖从基础概念到高级应用,通过...
企业级ETL(Extract, Transform, Load,即数据提取、转换和加载)的构建是企业数据仓库和商务智能项目中的重要组成部分。ETL过程涉及到从不同的源系统抽取数据,对数据进行清洗、转换和集成处理,然后加载到目标数据...
在构建数据仓库的过程中,ETL(Extract/Transform/Load,抽取/转换/加载)和元数据管理是两项核心活动,它们确保数据能够被高效地整合和利用。 ETL过程是数据仓库中的关键步骤,它涉及数据从多个源抽取出来、转换成...
假设一个零售企业希望通过构建数据仓库来整合其销售、库存、客户等多个业务系统的数据,以支持高层决策。在这一过程中,ETL的具体实施步骤可能包括: 1. **数据抽取**: 首先识别所有相关业务系统的数据源,包括POS...
构建企业级数据仓库的五步法包括: 1. **确定主题**:这是定义数据仓库分析范围的第一步,主题应反映分析的角度和量度。例如,分析啤酒销售,主题可能是“某年某月某一地区的啤酒销售情况”。每个主题对应一个数据...
### 数据仓库与ETL工具箱概念 在企业信息化过程中,数据仓库作为核心组件,承担着存储、处理和提供决策支持数据的职责。ETL(抽取、转换、加载...理解数据仓库的数据模型和ETL处理流程对于成功构建数据仓库至关重要。
7.ETL构建企业级数据仓库五步法 8.ETL增量抽取 9.ETL增量抽取方式 10.三大主流ETL工具选型 11.商务智能(BI)的四大关键技术-ETL(抽取(Extract)、转换(Transform)和加载(Load)) 12.商业智能 通过SSIS设计ETL来将Oracle...
本文描述了成功建立企业级... 着重介绍了ETL构建企业级数据仓 库的五步法,在对数据仓库分析、设计、建模方面提出了完备的解决方案和实用的高级技巧,尤其对数据 仓库模型搭建以及ETL处理有非常好的指导意义和使用价值
结合【描述】和【标签】,我们可以理解ETL在构建数据仓库以及商业智能系统中的作用,它是数据流入数据仓库之前的必经之路,确保数据可以被后续分析所使用。数据仓库和ETL的结合,使得企业能够更有效地管理和分析数据...
构建一个高效、实用的企业级数据仓库需要经历以下五个主要步骤: 1. **确定主题**:首先需要明确数据仓库的目的和目标,即要解决的具体问题。每个主题都代表了一组相关的分析视角和关键绩效指标(KPIs)。例如,...