万字详解ETL和数仓建模

ETL是数据抽取（Extract）、转换（Transform）、加载（Load ）的简写，它是将OLTP系统中的数据经过抽取，并将不同数据源的数据进行转换、整合，得出一致性的数据，然后加载到数据仓库中。简而言之ETL是完成从 OLTP系统到OLAP系统的过程

数据仓库（Data Warehouse DW）是基于OLTP系统的数据源，为了便于多维分析和 多角度展现将其数据按特定的模式进行存储而建立的关系型数据库，它不同于多维数据库，数据仓库中的数据是细节的，集成的，数据仓库是面向主题的，是以 OLAP系统为分析目的。它包括星型架构与雪花型架构，其中星型架构中间为事实表，四周为维度表， 类似星星；雪花型架构中间为事实表，两边的维度表可以再有其关联子表，而在星型中只允许一张表作为维度表与事实表关联，雪花型一维度可以有多张表，而星型 不可以。考虑到效率时，星型聚合快，效率高，不过雪花型结构明确，便于与OLTP系统交互。在实际项目中，我们将综合运用星型架构与雪花型架构。

即 确定数据分析或前端展现的某一方面的分析主题，例如我们分析某年某月某一地区的啤酒销售情况，就是一个主题。主题要体现某一方面的各分析角度（维度）和统 计数值型数据（量度），确定主题时要综合考虑，一个主题在数据仓库中即为一个数据集市，数据集市体现了某一方面的信息，多个数据集市构成了数据仓库。

在 确定了主题以后，我们将考虑要分析的技术指标，诸如年销售额此类，一般为数值型数据，或者将该数据汇总，或者将该数据取次数，独立次数或取最大最小值 等，这样的数据称之为量度。量度是要统计的指标，必须事先选择恰当，基于不同的量度可以进行复杂关键性能指标（KPI）等的计算。

在 确定了量度之后我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况，考虑到量度的聚合程度不同，我们将采用“最小粒度原则”，即将量度的粒度设置 到最小，例如我们将按照时间对销售额进行汇总，目前的数据最小记录到天，即数据库中记录了每天的交易额，那么我们不能在ETL时将数据进行按月或年汇总， 需要保持到天，以便于后续对天进行分析。而且我们不必担心数据量和数据没有提前汇总带来的问题，因为在后续的建立CUBE时已经将数据提前汇总了。

维 度是要分析的各个角度，例如我们希望按照时间，或者按照地区，或者按照产品进行分析，那么这里的时间、地区、产品就是相应的维度，基于不同的维度我们可 以看到各量度的汇总情况，我们可以基于所有的维度进行交叉分析。这里我们首先要确定维度的层次（Hierarchy）和级别（Level）（图 四：pic4jpg），维度的层次是指该维度的所有级别，包括各级别的属性；维度的级别是指该维度下的成员，例如当建立地区维度时我们将地区维度作为一 个级别，层次为省、市、县三层，考虑到维度表要包含尽量多的信息，所以建立维度时要符合“矮胖原则”，即维度表要尽量宽，尽量包含所有的描述性信息，而不 是统计性的数据信息。

还有一种常见的情况，就是父子型维度，该维度一般用于非叶子节点含有成员等情况，例如公司员工 的维度，在统计员工的工资时，部 门主管的工资不能等于下属成员工资的简单相加，必须对该主管的工资单独统计，然后该主管部门的工资等于下属员工工资加部门主管的工资，那么在建立员工维度 时，我们需要将员工维度建立成父子型维度，这样在统计时，主管的工资会自动加上，避免了都是叶子节点才有数据的情况。

另外，在建立维度表时要充 分使用代理键，代理键是数值型的ID号码，好处是代理键唯一标识了每一维度成员信息，便于区分，更重要的是在聚合时由于数值型匹 配，JOIN效率高，便于聚合，而且代理键对缓慢变化维度有更重要的意义，它起到了标识 历史 数据与新数据的作用，在原数据主键相同的情况下，代理键起到了 对新数据与 历史 数据非常重要的标识作用。

有时我们也会遇到维度缓慢变化的情况，比如增加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的属性，此时某一维度的成员会随着新的数据的加入而增加新的维度成员，这样我们要考虑到缓慢变化维度的处理，对于缓慢变化维度，有三种情况：

在确定好事实数据和维度后，我们将考虑加载事实表。

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

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

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

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

在构建数据仓库时，如果数据源位于一服务器上，数据仓库在另一 服务器端，考虑到数据源Server端访问频繁，并且数据量大，需要不断更新，所以可以建立准备区数据库（图七：pic7jpg）。先将数据抽取到准备 区中，然后基于准备区中的数据进行处理，这样处理的好处是防止了在原OLTP系统中中频繁访问，进行数据运算或排序等 *** 作。例如我们可以按照天将数据抽取 到准备区中，基于数据准备区，我们将进行数据的转换，整合，将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表，一些转换中间表和临时表以 及ETL日志表等。

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

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

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

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

ETL构建数据仓库需要简单的五步，掌握了这五步的方法我们将构建一个强大的数据仓库，不过每一步都有很深的需要研究与挖掘，尤其在实际项目中，我们要综合考虑，例如如果数据源的脏数据很多，在搭建数据仓库之前我们首先要进行数据清洗，以剔除掉不需要的信息和脏数据。

总之，ETL是数据仓库的核心，掌握了ETL构建数据仓库的五步法，就掌握了搭建数据仓库的根本方法。不过，我们不能教条，基于不同的项目，我们还将要进行 具体分析，如父子型维度和缓慢变化维度的运用等。在数据仓库构建中，ETL关系到整个项目的数据质量，所以马虎不得，必须将其摆到重要位置，将ETL这一 大厦根基筑牢。

如果ETL和SQL来说，肯定是SQL效率高的多。但是双方各有优势，先说ETL，ETL主要面向的是建立数据仓库来使用的。ETL更偏向数据清洗，多数据源数据整合，获取增量，转换加载到数据仓库所使用的工具。比如我有两个数据源，一个是数据库的表，另外一个是excel数据，而我需要合并这两个数据，通常这种东西在SQL语句中比较难实现。但是ETL却有很多现成的组件和驱动，几个组件就搞定了。还有比如跨服务器，并且服务器之间不能建立连接的数据源，比如我们公司系统分为一期和二期，存放的数据库是不同的，数据结构也不相同，数据库之间也不能建立连接，这种情况下，ETL就显得尤为重要和突出。通过固定的抽取，转换，加载到数据仓库中，即可很容易实现。

那么SQL呢？SQL事实上只是固定的脚本语言，但是执行效率高，速度快。不过灵活性不高，很难跨服务器整合数据。所以SQL更适合在固定数据库中执行大范围的查询和数据更改，由于脚本语言可以随便编写，所以在固定数据库中能够实现的功能就相当强大，不像ETL中功能只能受组件限制，组件有什么功能，才能实现什么功能。

所以具体我们在什么时候使用ETL和SQL就很明显了，当我们需要多数据源整合建立数据仓库，并进行数据分析的时候，我们使用ETL。如果是固定单一数据库的数据层次处理，我们就使用SQL。当然，ETL也是离不开SQL的。

主要有三大主流工具，分别是Ascential公司的Datastage、Informatica公司的Powercenter、NCR Teradata公司的ETL Automation还有其他开源工具，如PDI(Kettle)等。

DW系统以事实发生数据为基础，自产数据较少。

一个企业往往包含多个业务系统，均可能成为DW数据源。

业务系统数据质量良莠不齐，必须学会去伪存真。

业务系统数据纷繁复杂，要整合进数据模型。

源数据之间关系也纷繁复杂，源数据在加工进DW系统时，有些必须遵照一定的先后次序关系;

流水事件表：此类源表用于记录交易等动作的发生，在源系统中会新增、大部分不会修改和删除，少量表存在删除情况。如定期存款登记簿;

常规状态表：此类源表用于记录数据信息的状态。在源系统中会新增、修改，也存在删除的情况。如客户信息表;

代码参数表：此类源表用于记录源系统中使用到的数据代码和参数;

数据文件大多数以1天为固定的周期从源系统加载到数据仓库。数据文件包含增量，全量以及待删除的增量。

增量数据文件：数据文件的内容为数据表的增量信息，包含表内新增及修改的记录。

全量数据文件：数据文件的内容为数据表的全量信息，包含表内的所有数据。

带删除的增量：数据文件的内容为数据表的增量信息，包含表内新增、修改及删除的记录，通常删除的记录以字段DEL_IND='D'标识该记录。

可划分为： 历史 拉链算法、追加算法(事件表)、Upsert算法(主表)及全删全加算法(参数表);

历史 拉链：根据业务分析要求，对数据变化都要记录，需要基于日期的连续 历史 轨迹;

追加(事件表)：根据业务分析要求，对数据变化都要记录，不需要基于日期的连续 历史 轨迹;

Upsert(主表)：根据业务分析要求，对数据变化不需要都要记录，当前数据对 历史 数据有影响;

全删全加算法(参数表)：根据业务分析要求，对数据变化不需要都要记录，当前数据对 历史 数据无影响;

所谓拉链，就是记录 历史 ，记录一个事务从开始，一直到当前状态的所有变化信息(参数新增开始结束日期);

一般用于事件表，事件之间相对独立，不存在对 历史 信息进行更新;

是update和insert组合体，一般用于对 历史 信息变化不需要进行跟踪保留、只需其最新状态且数据量有一定规模的表，如客户资料表;

一般用于数据量不大的参数表，把 历史 数据全部删除，然后重新全量加载;

历史 拉链，Upsert,Append,全删全加;加载性能：全删全加，Append，Upsert， 历史 拉链;

APPEND算法，常规拉链算法，全量带删除拉链算法;

APPEND算法，MERGE算法，常规拉链算法，基于增量数据的删除拉链算法，基于全量数据的删除拉链算法，经济型常规拉链算法，经济型基于增量数据的删除拉链算法，经济型基于全量数据的删除拉链算法，PK_NOT_IN_APPEND算法，源日期字段自拉链算法;

此算法通常用于流水事件表，适合这类算法的源表在源系统中不会更新和删除，而只会发生一笔添加一笔，所以只需每天将交易日期为当日最新数据取过来直接附加到目标表即可，此类表在近源模型层的字段与技术缓冲层、源系统表基本上完全一致，不会额外增加物理化处理字段，使用时也与源系统表的查询方式相同;

此算法通常用于无删除 *** 作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不删除，所以需每天获取当日末最新数据(增量或全增量均可)，先找出真正的增量数据(新增和修改)，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链 *** 作(即END_DT关闭到当前业务日期)，然后再将最新的增量数据作为开链数据插入到目标表即可。

此类表再近源模型层比技术缓冲层、源系统的相应表额外增加两个物理化处理字段START_DT(开始日期)和END_DT(结束日期)，使用时需要先选定视觉日期，通过START_DT和END_DT去卡视觉日期，即START_DT'视觉日期';

此算法通常用于有删除 *** 作的常规状态类表，并且要求全量的数据文件，用以对比出删除增量;适合这类算法的源表在源系统中会新增，修改，删除，每天将当日末最新全量数据取过来外，分别找出真正的增量数据(新增，修改)和删除增量数据，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链 *** 作(即END_DT关闭到当前业务日期)，然后再将最新增量数据中真正的增量及删除数据作为开链数据插入到目标表即可，注意删除记录的删除标志DEL_IND会设置为‘D’;

此类表在近源模型层比技术缓冲层，源系统的相应表额外增加三个物理化处理字段START_DT(开始日期)，ENT_DT(结束日期)，DEL_IND(删除标准)。使用方式分两类：一时一般查询使用，此时需要先选定视角日期，通过START_DT和END_DT去卡视角日期，即START_DT‘视角日期’，同时加上条件DEL_IND 'D';另一种是下载或获取当日增量数据，此时就是需要START_DT'视角日期' 一个条件即可，不需要加DEL_IND 'D'的条件。

此算法通常用于流水事件表，适合这类算法的源表在源系统中不会更新和删除，而只会发生一笔添加一笔，所以只需每天将交易日期为当日的最新数据取过来直接附加到目标表即可;

通常建一张名为VT_NEW_编号的临时表，用于将各组当日最新数据转换加到VT_NEW_编号后，再一次附加到最终目标表;

此算法通常用于无删除 *** 作的常规状态表，一般是无需保留 历史 而只保留当前最新状态的表，适合这类算法的源表在源系统中会新增，修改，但不删除，所以需获取当日末最新数据(增量或全量均可)，用于MERGE IN或UPSERT目标表;为了效率及识别真正增量的要求，通常先识别出真正的增量数据(新增及修改数据)，然后再用这些真正的增量数据向目标表进行MERGE INTO *** 作;

通常建两张临时表，一个名为VT_NEW_编号，用于将各组当日最新数据转换加到VT_NEW_编号;另一张名为VT_INC_编号，将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入VT_INC_编号，然后再用VT_INC_编号对最终目标表进行MERGE INTO或UPSERT。

此算法通常用于无删除 *** 作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不删除，所以需每天获取当日末最新数据(增量或全增量均可)，先找出真正的增量数据(新增和修改)，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链 *** 作(即END_DT关闭到当前业务日期)，然后再将最新增量数据作为开链数据插入到目标表即可;

通常建两张临时表，一个名为VT_NEW_编号，用于将各组当日最新数据转换加到VT_NEW_编号;另一张名为VT_INC_编号，将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入VT_INC_编号，然后再将最终目标表的开链数据中的PK出现在VT_INT_编号中进行关链处理，然后将VT_INC_编号中的所有数据作为开链数据插入最终目标表即可。

此算法通常用于有删除 *** 作的常规状态表，并且要求删除数据是以DEL_IND='D'删除增量的形式提供;适合这类算法的源表再源系统中会新增、修改、删除，除每天获取当日末最新数据(增量或全量均可)外，还要获取当日删除的数据，根据找出的真正增量数据(新增和修改)以及删除增量数据，用它们将目标表中属性发生修改的开链数据(有效数据)进行关链 *** 作(即END_DT关闭到当前业务时间)，然后再将增量(不含删除数据)作为开链数据插入到目标表中即可;

通常建三张临时表，一个名为VT_NEW_编号，用于将各组当日最新数据 (不含删除数据)转换加载到VT_NEW_编号;第二张表名为VT_INC_编号，用VT_NEW_编号与目标表中的昨日的数据进行对比后找出真正的增量数据放入VT_INC_编号;第三张表名为VT_DEL_编号，将删除增量数据转换加载到VT_DEL_编号;最后再将最终目标表的开链数据中PK出现在VT_INC_编号或VT_DEL_编号中的进行关链处理，最后将VT_INC_编号中的所有数据作为开链数据插入最终目标表即可;

此算法通常用于有删除 *** 作的常规状态表，并且要求提供全量数据，用以对比出删除增量;适合这类算法的源表在源系统中会新增、修改、每天将当日末的最新全量数据取过来外，分别找出真正的增量数据(新增、修改)和删除增量数据，用它们将目标表中属性发生修改的开链数据(有效记录)进行关链 *** 作(即END_DT关闭到当前业务时间)，然后再将最新数据中真正的增量数据(不含删除数据)作为开链数据插入到目标表即可;

通常建两张临时表，一个名为VT_NEW_编号，用于将各组当日最新全量数据转换到VT_NEW_编号;另一张表名为VT_INC_编号,将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增、修改)和删除增量数据放入VT_INC_编号，注意将其中的删除增量数据的END_DT置以最小日期(借用);最后再将最终目标表的开链数据中PK出现再VT_INC_编号或VT_DEL_编号中的进行关链处理，然后将VT_INC_编号中所有的END_DT不等于最小日期数据(非删除数据)作为开链数据插入最终目标表即可;

此算法基本等同与常规拉算法，只是在最后一步只将属性非空即非0的记录才作为开链数据插入目标表;

此算法基本等同于基于增量数据删除拉链算法，只是在最后一步只将属性非空及非0的记录才作为开链数据插入目标表;

此算法基本等同于基于全量数据删除拉链算法，只是在最后一步只将属性非空及非0的记录才作为开链数据插入目标表;

此算法是对每一组只将PK在当前VT_NEW_编号表中未出现的数据再插入VT_NEW_编号表，最后再将PK未出现在目标表中的数据插入目标表，以保证只进那些PK未进过的数据;

此算法是源表中有日期字段标识当前记录的生效日期，本算法通过对同主键记录按这个生效日期排序后，一次首尾相连行形成一条自然拉链的算法

简而言之，数据库是面向事务的设计，数据仓库是面向主题设计的。

数据库一般存储在线交易数据，数据仓库存储的一般是历史数据。

数据库设计是尽量避免冗余，一般采用符合范式的规则来设计，数据仓库在设计是有意引入冗余，采用反范式的方式来设计。

数据库是为捕获数据而设计，数据仓库是为分析数据而设计，它的两个基本的元素是维表和事实表。维是看问题的角度，比如时间，部门，维表放的就是这些东西的定义，事实表里放着要查询的数据，同时有维的ID。

单从概念上讲，有些晦涩。任何技术都是为应用服务的，结合应用可以很容易地理解。以银行业务为例。数据库是事务系统的数据平台，客户在银行做的每笔交易都会写入数据库，被记录下来，这里，可以简单地理解为用数据库记帐。数据仓库是分析系统的数据平台，它从事务系统获取数据，并做汇总、加工，为决策者提供决策的依据。比如，某银行某分行一个月发生多少交易，该分行当前存款余额是多少。如果存款又多，消费交易又多，那么该地区就有必要设立ATM了。

显然，银行的交易量是巨大的，通常以百万甚至千万次来计算。事务系统是实时的，这就要求时效性，客户存一笔钱需要几十秒是无法忍受的，这就要求数据库只能存储很短一段时间的数据。而分析系统是事后的，它要提供关注时间段内所有的有效数据。这些数据是海量的，汇总计算起来也要慢一些，但是，只要能够提供有效的分析数据就达到目的了。

数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。那么，数据仓库与传统数据库比较，有哪些不同呢让我们先看看WHInmon关于数据仓库的定义:面向主题的、集成的、与时间相关且不可修改的数据集合。

“面向主题的”:传统数据库主要是为应用程序进行数据处理，未必按照同一主题存储数据;数据仓库侧重于数据分析工作，是按照主题存储的。这一点，类似于传统农贸市场与超市的区别—市场里面，白菜、萝卜、香菜会在一个摊位上，如果它们是一个小贩卖的;而超市里，白菜、萝卜、香菜则各自一块。也就是说，市场里的菜(数据)是按照小贩(应用程序)归堆(存储)的，超市里面则是按照菜的类型(同主题)归堆的。

“与时间相关”:数据库保存信息的时候，并不强调一定有时间信息。数据仓库则不同，出于决策的需要，数据仓库中的数据都要标明时间属性。决策中，时间属性很重要。同样都是累计购买过九车产品的顾客，一位是最近三个月购买九车，一位是最近一年从未买过，这对于决策者意义是不同的。

“不可修改”:数据仓库中的数据并不是最新的，而是来源于其它数据源。数据仓库反映的是历史信息，并不是很多数据库处理的那种日常事务数据(有的数据库例如电信计费数据库甚至处理实时信息)。因此，数据仓库中的数据是极少或根本不修改的;当然，向数据仓库添加数据是允许的。

数据仓库的出现，并不是要取代数据库。目前，大部分数据仓库还是用关系数据库管理系统来管理的。可以说，数据库、数据仓库相辅相成、各有千秋。

补充一下，数据仓库的方案建设的目的，是为前端查询和分析作为基础，由于有较大的冗余，所以需要的存储也较大。为了更好地为前端应用服务，数据仓库必须有如下几点优点，否则是失败的数据仓库方案。

1效率足够高。客户要求的分析数据一般分为日、周、月、季、年等，可以看出，日为周期的数据要求的效率最高，要求24小时甚至12小时内，客户能看到昨天的数据分析。由于有的企业每日的数据量很大，设计不好的数据仓库经常会出问题，延迟1-3日才能给出数据，显然不行的。

2数据质量。客户要看各种信息，肯定要准确的数据，但由于数据仓库流程至少分为3步，2次ETL，复杂的架构会更多层次，那么由于数据源有脏数据或者代码不严谨，都可以导致数据失真，客户看到错误的信息就可能导致分析出错误的决策，造成损失，而不是效益。

3扩展性。之所以有的大型数据仓库系统架构设计复杂，是因为考虑到了未来3-5年的扩展性，这样的话，客户不用太快花钱去重建数据仓库系统，就能很稳定运行。主要体现在数据建模的合理性，数据仓库方案中多出一些中间层，使海量数据流有足够的缓冲，不至于数据量大很多，就运行不起来了。

OLAP是联机分析处理

OLTP是联机事务处理

OLAP是数据仓库系统的主要应用，支持复杂的分析 *** 作，侧重决策支持，并且提供直观、易懂的查询结果。

OLTP是传统的关系型数据库的主要应用模式，主要面对基本的、日常的事务处理；比如数据库记录的增、删、改、查。

前言 在事务处理系统中的数据 主要用于记录和查询业务情况 随着数据仓库（DW）技术的不断成熟 企业的数据逐渐变成了决策的主要依据 数据仓库是一种面向决策主题 由多数据源集成 拥有当前及历史总结数据 以读为主的数据库系统 其目的是支持决策 数据仓库要根据决策的需要收集来自企业内外的有关数据 并加以适当的组织处理 使其能有效地为决策过程提供信息 数据仓库中的数据是从许多业务处理系统中抽取 转换而来 对于这样一个复杂的企业数据环境 如何以安全 高效的方式来对它们进行管理和访问就变得尤为重要 解决这一问题的关键是对元数据进行科学有效的管理 元数据是关于数据 *** 纵数据的进程和应用程序的结构和意义的描述信息 其主要目标是提供数据资源的全面指南 元数据不仅定义了数据仓库中数据的模式 来源以及抽取和转换规则等 而且整个数据仓库系统的运行都是基于元数据的 是元数据把数据仓库系统中的各个松散的组件联系起来 组成了一个有机的整体 本文首先介绍了元数据的定义 作用和意义 然后讨论了数据仓库系统中元数据管理的现状和关于元数据的标准化情况 最后提出了建立元数据管理系统的步骤和实施方法 元数据 元数据的概念按照传统的定义 元数据（Metadata）是关于数据的数据 在数据仓库系统中 元数据可以帮助数据仓库管理员和数据仓库的开发人员非常方便地找到他们所关心的数据 元数据是描述数据仓库内数据的结构和建立方法的数据 可将其按用途的不同分为两类 技术元数据（Technical Metadata）和业务元数据（Business Metadata） 技术元数据是存储关于数据仓库系统技术细节的数据 是用于开发和管理数据仓库使用的数据 它主要包括以下信息 &# ; 数据仓库结构的描述 包括仓库模式 视图 维 层次结构和导出数据的定义 以及数据集市的位置和内容 &# ; 业务系统 数据仓库和数据集市的体系结构和模式 &# ; 汇总用的算法 包括度量和维定义算法 数据粒度 主题领域 聚集 汇总 预定义的查询与报告 &# ; 由 *** 作环境到数据仓库环境的映射 包括源数据和它们的内容 数据分割 数据提取 清理 转换规则和数据刷新规则 安全（用户授权和存取控制） 业务元数据从业务角度描述了数据仓库中的数据 它提供了介于使用者和实际系统之间的语义层 使得不懂计算机技术的业务人员也能够 读懂 数据仓库中的数据 业务元数据主要包括以下信息 使用者的业务术语所表达的数据模型 对象名和属性名 访问数据的原则和数据的来源 系统所提供的分析方法以及公式和报表的信息 具体包括以下信息 &# ; 企业概念模型 这是业务元数据所应提供的重要的信息 它表示企业数据模型的高层信息 整个企业的业务概念和相互关系 以这个企业模型为基础 不懂数据库技术和SQL语句的业务人员对数据仓库中的数据也能做到心中有数 &# ; 多维数据模型 这是企业概念模型的重要组成部分 它告诉业务分析人员在数据集市当中有哪些维 维的类别 数据立方体以及数据集市中的聚合规则 这里的数据立方体表示某主题领域业务事实表和维表的多维组织形式 &# ; 业务概念模型和物理数据之间的依赖 以上提到的业务元数据只是表示出了数据的业务视图 这些业务视图与实际的数据仓库或数据库 多维数据库中的表 字段 维 层次等之间的对应关系也应该在元数据知识库中有所体现 元数据的作用在数据仓库系统中 元数据机制主要支持以下五类系统管理功能 （1）描述哪些数据在数据仓库中 （2）定义要进入数据仓库中的数据和从数据仓库中产生的数据 （3）记录根据业务事件发生而随之进行的数据抽取工作时间安排 （4）记录并检测系统数据一致性的要求和执行情况 （5）衡量数据质量 与其说数据仓库是软件开发项目 还不如说是系统集成项目[ ] 因为它的主要工作是把所需的数据仓库工具集成在一起 完成数据的抽取 转换和加载 OLAP分析和数据挖掘等 如图 所示 它的典型结构由 *** 作环境层 数据仓库层和业务层等组成 其中 第一层（ *** 作环境层）是指整个企业内有关业务的OLTP系统和一些外部数据源 第二层是通过把第一层的相关数据抽取到一个中心区而组成的数据仓库层 第三层是为了完成对业务数据的分析而由各种工具组成的业务层 图中左边的部分是元数据管理 它起到了承上启下的作用 具体体现在以下几个方面 &# ; 便于集成&# ; 提高系统的灵活性&# ; 保证数据的质量&# ; 帮助用户理解数据的意义 数据仓库元数据管理现状 元数据管理的主要任务有两个方面 一是负责存储和维护元数据库中的元数据 二是负责数据仓库建模工具 数据获取工具 前端工具等之间的消息传递 协调各模块和工具之间的工作 由以上几节我们了解到元数据几乎可以被称为是数据仓库乃至商业智能（BI）系统的 灵魂 正是由于元数据在整个数据仓库生命周期中有着重要的地位 各个厂商的数据仓库解决方案都提到了关于对元数据的管理 但遗憾的是对于元数据的管理 各个解决方案都没有明确提出一个完整的管理模式 它们提供的仅仅是对特定的局部元数据的管理 当前市场上与元数据有关的主要工具见图 如图 所示 与元数据相关的数据仓库工具大致可分为四类 数据抽取工具 把业务系统中的数据抽取 转换 集成到数据仓库中 如Ardent的DataStage CA（原Platinum）的Decision Base和ETI的Extract等 这些工具仅提供了技术元数据 几乎没有提供对业务元数据的支持 前端展现工具 包括OLAP分析 报表和商业智能工具等 如MicroStrategy的DSS Agent Cognos的PowerPlay Business Objects的BO 以及Brio等 它们通过把关系表映射成与业务相关的事实表和维表来支持多维业务视图 进而对数据仓库中的数据进行多维分析 这些工具都提供了业务元数据与技术元数据相对应的语义层 建模工具 为非技术人员准备的业务建模工具 这些工具可以提供更高层的与特定业务相关的语义 如CA的ERwin Sy ase的PowerDesigner以及Rational的Rose等 元数据存储工具 元数据通常存储在专用的数据库中 该数据库就如同一个 黑盒子 外部无法知道这些工具所用到和产生的元数据是如何存储的 还有一类被称为元数据知识库（Metadata Repository）的工具 它们独立于其它工具 为元数据提供一个集中的存储空间 包括微软的Repository CA的Repository Ardent的MetaStage和Sybase的WCC等 元数据管理的标准化 没有规矩不成方圆 元数据管理之所以困难 一个很重要的原因就是缺乏统一的标准 在这种情况下 各公司的元数据管理解决方案各不相同 近几年 随着元数据联盟MDC（Meta Data Coalition）的开放信息模型OIM（Open Information Model）和OMG组织的公共仓库模型CWM（Common Warehouse Model）标准的逐渐完善 以及MDC和OMG组织的合并 为数据仓库厂商提供了统一的标准 从而为元数据管理铺平了道路 从元数据的发展历史不难看出 元数据管理主要有两种方法 ( ) 对于相对简单的环境 按照通用的元数据管理标准建立一个集中式的元数据知识库 ( ) 对于比较复杂的环境 分别建立各部分的元数据管理系统 形成分布式元数据知识库 然后 通过建立标准的元数据交换格式 实现元数据的集成管理 下面我们分别介绍数据仓库领域中两个最主要的元数据标准 MDC的OIM标准和OMG的CWM标准 MDC的OIM存储模型MDC成立于 年 是一个致力于建立与厂商无关的 不依赖于具体技术的企业元数据管理标准的非赢利技术联盟 该联盟有 多个会员 其中包括微软和IBM等著名软件厂商 年 月MDC接受了微软的建议 将OIM作为元数据标准 OIM的目的是通过公共的元数据信息来支持不同工具和系统之间数据的共享和重用 它涉及了信息系统（从设计到发布）的各个阶段 通过对元数据类型的标准描述来达到工具和知识库之间的数据共享 OIM所声明的元数据类型都采用统一建模语言UML（Universal Modeling Language）进行描述 并被组织成易于使用 易于扩展的多个主题范围（Subject Areas） 这些主题范围包括 &# ; 分析与设计（Analysis and Design） 主要用于软件分析 设计和建模 该主题范围又进一步划分为 UML包（Package） UML扩展包 通用元素（Generic Elements）包 公共数据类型（Common Data Types）包和实体关系建模（Entity Relationship Modeling）包等 &# ; 对象与组件（Object and Component） 涉及面向对象开发技术的方方面面 该主题范围只包含组件描述建模（Component Description Modeling）包 &# ; 数据库与数据仓库（Database and Warehousing） 为数据库模式管理 复用和建立数据仓库提供元数据概念支持 该主题范围进一步划分为 关系数据库模式（Relational Database Schema）包 OLAP模式（OLAP Schema）包 数据转换（Data Transformations）包 面向记录的数据库模式（Record Oriented Database Schema）包 XML模式（XML Schema）包和报表定义（Report Definitions）包等 &# ; 业务工程（Business Engineering） 为企业运作提供一个蓝图 该主题范围进一步划分为 业务目标（Business Goal）包 组织元素（Organizational Elements）包 业务规则（Business Rules）包 商业流程（Business Processes）包等 &# ; 知识管理（Knowledge Management） 涉及企业的信息结构 该主题范围进一步划分为 知识描述（Knowledge lishixinzhi/Article/program/Oracle/201311/18587

经过几年的积累，大部分中大型的企事业单位已经建立了比较完善的CRM、ERP、OA等基础信息化系统。这些系统的统一特点都是:通过业务人员或者用户的 *** 作，最终对数据库进行增加、修改、删除等 *** 作。上述系统可统一称为OLTP(Online Transaction Process，在线事务处理)，指的就是系统运行了一段时间以后，必然帮助企事业单位收集大量的历史数据。但是，在数据库中分散、独立存在的大量数据对于业务人员来说，只是一些无法看懂的天书。业务人员所需要的是信息，是他们能够看懂、理解并从中受益的抽象信息。此时，如何把数据转化为信息，使得业务人员(包括管理者)能够充分掌握、利用这些信息，并且辅助决策，就是商业智能主要解决的问题。如何把数据库中存在的数据转变为业务人员需要的信息大部分的答案是报表系统。简单说，报表系统已经可以称作是BI了，它是BI的低端实现。

国外的企业，大部分已经进入了中端BI，叫做数据分析。有一些企业已经开始进入高端BI，叫做数据挖掘。而我国的企业，大部分还停留在报表阶段。

数据报表不可取代

传统的报表系统技术上已经相当成熟，大家熟悉的Excel、水晶报表、Reporting Service等都已经被广泛使用。但是，随着数据的增多，需求的提高，传统报表系统面临的挑战也越来越多。

1 数据太多，信息太少

密密麻麻的表格堆砌了大量数据，到底有多少业务人员仔细看每一个数据到底这些数据代表了什么信息、什么趋势级别越高的领导，越需要简明的信息。如果我是董事长，我可能只需要一句话:我们的情况是好、中还是差

2 难以交互分析、了解各种组合

定制好的报表过于死板。例如，我们可以在一张表中列出不同地区、不同产品的销量，另一张表中列出不同地区、不同年龄段顾客的销量。但是，这两张表无法回答诸如“华北地区中青年顾客购买数码相机类型产品的情况”等问题。业务问题经常需要多个角度的交互分析。

3 难以挖掘出潜在的规则

报表系统列出的往往是表面上的数据信息，但是海量数据深处潜在含有哪些规则呢什么客户对我们价值最大，产品之间相互关联的程度如何越是深层的规则，对于决策支持的价值越大，但是，也越难挖掘出来。

4 难以追溯历史，数据形成孤岛

业务系统很多，数据存在于不同地方。太旧的数据往往被业务系统备份出去，导致宏观分析、长期历史分析难度很大。

因此，随着时代的发展，传统报表系统已经不能满足日益增长的业务需求了，企业期待着新的技术。数据分析和数据挖掘的时代正在来临。值得注意的是，数据分析和数据挖掘系统的目的是带给我们更多的决策支持价值，并不是取代数据报表。报表系统依然有其不可取代的优势，并且将会长期与数据分析、挖掘系统一起并存下去。

八维以上的数据分析

如果说OLTP侧重于对数据库进行增加、修改、删除等日常事务 *** 作，OLAP(Online Analytics Process，在线分析系统)则侧重于针对宏观问题，全面分析数据，获得有价值的信息。

为了达到OLAP的目的，传统的关系型数据库已经不够了，需要一种新的技术叫做多维数据库。

多维数据库的概念并不复杂。举一个例子，我们想描述2003年4月份可乐在北部地区销售额10万元时，牵扯到几个角度:时间、产品、地区。这些叫做维度。至于销售额，叫做度量值。当然，还有成本、利润等。

除了时间、产品和地区，我们还可以有很多维度，例如客户的性别、职业、销售部门、促销方式等等。实际上，使用中的多维数据库可能是一个8维或者15维的立方体。

虽然结构上15维的立方体很复杂，但是概念上非常简单。

数据分析系统的总体架构分为四个部分:源系统、数据仓库、多维数据库、客户端。

·源系统:包括现有的所有OLTP系统，搭建BI系统并不需要更改现有系统。

·数据仓库:数据大集中，通过数据抽取，把数据从源系统源源不断地抽取出来，可能每天一次，或者每3个小时一次，当然是自动的。数据仓库依然建立在关系型数据库上，往往符合叫做“星型结构”的模型。

·多维数据库:数据仓库的数据经过多维建模，形成了立方体结构。每一个立方体描述了一个业务主题，例如销售、库存或者财务。

·客户端:好的客户端软件可以把多维立方体中的信息丰富多彩地展现给用户。

数据分析案例:

在实际的案例中，我们利用Oracle9i搭建了数据仓库，Microsoft Analysis Service 2000搭建了多维数据库，ProClarity 60 作为客户端分析软件。

分解树好像一个组织图。分解树在回答以下问题时很最高的销售额

·在特定的产品种类内，各种产品间的销售额分布如何

·哪个销售人员完成了最高百分比的销售额

在图1中，可以对PC机在各个地域的销售额和所占百分比一目了然。任意一层分解树都可以根据不同维度随意展开。在该分解树中，在大区这一层是按国家展开，在国家这一层是按产品分类展开。

投影图(图3)使用散点图的格式，显示两个或三个度量值之间的关系。数据点的集中预示两个变量之间存在强的相关关系，而稀疏分布的数据点可能显示不明显的关系。

投影图很适合分析大量的数据。在显示因果关系方面有明显效果，比如例外的数据点就可以考虑进一步研究，因为它们落在“正常”的点群范围之外。

数据挖掘看穿你的需求

广义上说，任何从数据库中挖掘信息的过程都叫做数据挖掘。从这点看来，数据挖掘就是BI。但从技术术语上说，数据挖掘(Data Mining)特指的是:源数据经过清洗和转换等成为适合于挖掘的数据集。数据挖掘在这种具有固定形式的数据集上完成知识的提炼，最后以合适的知识模式用于进一步分析决策工作。从这种狭义的观点上，我们可以定义:数据挖掘是从特定形式的数据集中提炼知识的过程。数据挖掘往往针对特定的数据、特定的问题，选择一种或者多种挖掘算法，找到数据下面隐藏的规律，这些规律往往被用来预测、支持决策。

OLAP(Online Analytical Processing)在线分析处理：

联机分析处理，是数据仓库的核心部心，所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库，用于处理商业智能、决策支持等重要的决策信息；数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析；是处理两种不同用途的工具而已。

OLTP(On-Line Transaction Processing在线事务处理：

联机事务处理系统也称为面向交易的处理系统，其基本特征是顾客的原始数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果。

以上就是关于万字详解ETL和数仓建模全部的内容，包括:万字详解ETL和数仓建模、数据库根数据仓库有什么区别，如何区分、sql中OLTP和ALTP表示什么等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！