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

1、 一. 数据仓库的架构 仓库（Data Warehouse DW ）是为了便于多维分析和多角度展现而将数据按特走的模式进 行存储所建立起来的关系型数据库r它的数据基于OLTP源系统。数据仓库中的数据是细节的、 集成的、面向主题的,以OLAP系统的分析露求为目的。 仓库的架构模型包括了星型架构（图二：pic2.bmp ）与雪花型架构（图三：pic3.bmp ）两 种模式。如图所示，星型架构的中间为事实表,四周为维度表,类似星星；而相比较而言，雪花 型架构的中间为事实表，两边的维度表可以再有其关联子表，从而表达了清晰的维度层次关系。 从OLAP系统的分析需求和ETL的处理效率两方面来考虑：星型结构

2、聚合快，分析效率高；而 雪花型结构明确，便于与OLTP系统交互。因此,在实际项目中,我们将综合运用星型架构与雪 花型架构来设计数据仓库。 另吆下面我们就来看一看构建企业级数据仓库的流程。 二. 构建企业级数据仓库五步法 （）、确走主题 即确走数据分析或前端展现的主题。例如：我们希望分析某年某月某一地区的啤酒销售情况，这 就是一个主题。主题要体现出某一方面的各分析角度（维度）和统计数值型数据（呈度）之间的 关系r确走主题时要综合考虑。 我们可以形象的将一个主题想象为一颗星星：统计数值型数据（星度）存在于星星中间的事实表; 分析角度（维度）是星星的各个角；我们将通过维度的组合,来考蔡量度。那么,某

3、年某月某 一地区的啤酒销售情况这样一个主题，就要求我们通过时间和地区两个维度的组合，来考察销 售情况这个量度。从而，不同的主题来源于数据仓库中的不同子集r我们可以称之为数据集市。 集市体现了数据仓库某一方面的信息，多个数据集市构成了数据仓库。 （二八确走量度 在确走了主题以后哉们将考虑要分析的技术指标，诸如年销售额之类。它们一般为数值型娠。 我们或者将该数据汇总,或者将该数据取次数、独立次数或取最大最小值等，这样的数据称为量 呈度是要统计的指标，必须事先选择恰当，基于不同的呈度可以进行复杂关键性能指标(KPI) 等的设计和计算。 (三) 、确走事实数据粒度 在确走了呈屋之后，我们要考虑至I该呈

4、度的汇总情况和不同维度下呈度的聚合情况。考虑到呈度 的聚合程度不同，我们将采用最小粒康原则，即将題的粒度设置到最小。 例如：假设目前的数据最小记录到秒，即数据库中记录了每一秒的交易额。那么,如果我们可以 确认，在将来的分析需求中，时间只需要精确到天就可以的话，我们就可以在ETL处理过程中， 按天来汇总数据，此时，数据仓库中量度的粒度就是天；反过来，如果我们不能确认将来的 分析零求在时间上是否需要精确到秒，那么，我们就需要遵循最小粒度原则，在数据仓库的 事实表中保留每一秒的数据,以便日后对秒进行分析。 在采用最小粒度原则的同时，我们不必担心海量数垢所带来的汇总分析效率问题，因为在后 续建立多维分

5、析模型(CUBE )的时候，我们会对数据提前进行汇总，从而保障产生分析结果的 效率。关于建立多维分析模型(CUBE )的相关问题，我们将在下期栏目中予以阐述。 (四) 、确走维屋 维度是指分析的各个角度。例如我们希望按照时间，或者按照地区，或者按照产品进行分析，那 么这里的时间、地区、产品就是相应的维度。基于不同的维度，我们可以看到各星度的汇总情况, 也可以基于所有的维度进行交叉分析。 这里我们首先要确走维度的层次(Hierarchy )和级别(Level)(图四：pic4.bmp )。如图所 示，我们在时间维度上，按照年-季度-月形成了一个层次，其中年、季康、月 成为了这个层次的3个级别;同

6、理,当我们建立产品维度时，我们可以将产品大类-产品子类- 产品划为一个层次,其中包含产品大类、产品子类、产品三个级别。 刃吆r我们分析中所用到的这些维度，在数据仓库中的存在形式是怎样的呢？ 我们可以将3个级别设置成一张碧 表中的3个字段,比如时间维度；我们也可以使用三张表， 分别保存产品大类、产品子类、产品三部分数据r比如产品维度。 另外，值得一提的是，我们在建立维度表时要充分使用代理键。代理键是数值型的ID号码（例 如图六中每张表的第一个字段），它唯一标识了每一维度成员。更重要的是,在聚合时，数值型 字段的匹配和比较,JOIN效率高，便于聚合。同时，代理键对缓慢变化维度有着重要的意义， 在原

7、数据主键相同的情况下，它起到了对新数据与历史数垢的标识作用。 在此,我们不妨谈一谈维度表随时间变化的问题，这是我们经常会遇到的情况，我们称其为缓憬 变化维度。 比如我们増加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的雇性，此时， 维度表就会被修改或者増加新的记录行。这样，我们在ETL的过程中，就要考虑到缓慢变化维 度的处理。对于缓慢变化维康，有三种情况: 1、缓慢变化维度第一种类型：历史数据需要修改。这种情况下,我们使用UPDATE方法来修 改维度表中的数据。例如：产品的ID号码为123 ,后来发现ID号码错了，雲要改写成456 , 另吆r我们就在ETL处理时r直接修改维度表中

8、原来的ID号码为4560 2、缓慢变化维度第二种类型：历史数据保留,新增数据也要保留。这时，要将原戋 新数据插入r我们使用UPDATE /INSERT0比如:某一员工2005年在A部门r 2006年时他 调到了 B部门。那么在统计2005年的数据时就应该将该员工定位到A部门；而在统计2006 年数据时就应该走位到B部门r然后再有新的数据插入时r将按照新部门（B部门）进行处理， 这样我们的做法是将该维度成员列表加入标识列，将历史的娠标识为过期，将目前的芟 标识为当前的” o另一种方法是将该维度打上时间戳，即将历史数据生效的时间段作为它的一 个雇性，在与原始表匹配生成事实表时将按照时间段进行关联，

9、这种方法的好处是该维度成员生 效时间明确。 3、缓慢变化维度第三种类型：新增蛹维度成员改变了属性。例如：某一维度成员新加入了一 列,该列在历史数据中不能基于它浏览而在目前姻和将来数据中可以按照它浏览,那么此时 我们需要改变维度表雇性r即加入新的字段列。那么，我们将使用存储过程或程序生成新的维度 属性在后续的数据中将基于新的雇性进行查看。 （五八创建事实表 在确走好事实数据和维度后,我们将考虑加载事实表。 在公司的大量数据堆积如山时，我们想看看里面究竟是什么r结果发现里面是一笔笔生产记录 笔笔交易记录那么这些记录是我们将要建立的事实表的原始i 我们的做法是将原始表与维度表进行关联r生成事实表（图

10、六：pic6.bmp ）。注意在关联时有 为空的数据时（数据源脏），需要使用外连接，连接后我们将各维康的代理键取出放于事实表中， 事实表除了各维度代理键外，还有各量度数据,这将来自原始表，事实表中将存在维度代理键和 条数尽呈多（粒度 各呈度，而不应该存在痫述性信息，即符合瘦高原则“，即要求事实表娄 最小）而描述性信息尽量少。 如果考虑到扩展,可以将事实表力D-唯一标识列，以为了以后扩展将该事实作为雪花型维度，不 过不霁要时一般建议不用这样做。 事实数摇表是数据仓库的核心r需要精心维护，在JOIN后将得到事实数据表，一般记录条数都 比较大，我们需要为其设置复合主键和索引，以实现数据的完整性和基于

11、数据仓库的查询性能优 化。事实数据表与维度表一起放于数据仓库中，如果前端需要连接娄 仓库进行查询，我们还需 要建立一些相关的中间汇总表或物化视图以方便查询。 三、什么是ETL 在数据仓库的构建中，ETL贯穿于项目始终，它是整个数据仓库的生命线,包括了数据清洗、整 合、转换、加载等各个过程。如果说数据仓库是一座大H ,那么ETL就是大凰的根基。ETL抽取整合数据的好坏直接影D向到最终的结果展现。所以ETL在整个数据仓库项目中起着十分关键的 作用r必须摆到十分重要的位置。 ETL是数据抽取（Extract）、转换（Transform ）、加载（Load ）的简写r它是指：将OLTP 系统中的数据抽

12、取出来r并将不同数据源的 然后加载 到数据仓库中。例如：下图就向我们展示了 ETL的数据转换效果。（图七：pic7.bmp ） 另吆.在这一转换过程中,我们就完成了对数据格式的更正、对数据字段的合并、以及新増指标 的计算三项操作。类似地，我们也可以根据其他需求r完善数据仓库中的数据。 简而言之r通过ETL ,我们可以基于源系统中的娠来生成数据仓库。ETL为我们搭建了 OLTP 系统和OLAP系统之间的桥梁。 五、项目实践技巧 （一）、准备区的运用 在构建数据仓库时,如果数据源位于一台服务器上 ,数据仓库在另一台服务器端，考虑到数据 源Server端访问频繁，并且数据量大，需要不断更新，所以可以

13、建立准备区妥 库（图八 pic8.bmp ）。先将数据抽取到准备区中r然后基于准备区中的数据进行处理r这样处理的好处 是防止了在原OLTP系统中频繁访问，进行娄 运戦排序等操作。 例如我们可以按照天将数据抽取到准备区中r基于数据准备区,我们将进行数摇的转换、整合、 将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表、转换中间表和临时表以 及ETL日志表等。 （二八 时间戳的运用 信息，那么按照时 时间维度对于某一事实主题来说十分重要，因为不同的时间有不同的统计芟 间记录的信息将发挥很重要的作用。在ETL中，时间戳有其特殊的作用r在上面提到的缓慢变化维度中我们可以使用时间戳标识维度成

14、员；在记录数据库和数据仓库的操作时,我们也将使 用时间戳标识信息。例如：在进行姻抽取时,我们将按照时间戳对OLTP系统中的数据进行抽 取，比如在午夜0 : 00取前一天的数据r我们将按照OLTP系统中的时间戳取GETDATE到 GETDATE减一天，这样得到前一天数据。 （二）.日志表的运用 在对数据进行处理时，难免会发生数据处理错误,产生出错信息，那么我们如何获得出错信息并 及时修正呢？方法是我们使用一张或多张Log日志表，将出错信息记录下来，在日志表中我们 将记录每次抽取的条数、处理成功的条数、处理失败的条数、处理失败的数据、处理时间等等。 这样r当数据发生错误时r我们很容易发现问题所在r

15、然后对出错的数据进行修正或重新处理。 （四）、使用调度 祀时数据仓库进行増星更新时必须使用调度（图九：pic9.bmp ）,即对事实数据表进行増量更 新处理。在使用调度前要考虑到事实数据臺,确走需要多长时间更新一次。比如希望按天进行直 看,可吆我们最好按天进行抽取，如果数据星不大，可以按照月或半年对数堀进行更新。如果有 缓憬变化维度情况调度时零要考虑到维度表更新情况在更新事实数据表之前要先更新维度表。 调度是数据仓库的关键环节，要考虑缜密。在ETL的流程搭建好后r要走期对其运行r所以调 度是执行ETL流程的关键步骤。每一次调度除了写入Log日志表的妥 处理信息外,还要使用 发送Email或报警服务等,这样也方便的技术人员对ETL流程的把握r增强了安全性和数据处 理的准确性。 五、总结 构建企业级数