星型数据结构设计

1、星型数据库设计 by Craig Utley介绍 创建一个星型数据库(Star Schema Database)是数据仓库开发中最重要的步骤之一。要知道这一步骤有多重要，就需要了解一个标准的在线事务处理系统(OLTP) 是如何转移到最终的星型数据库系统(也叫OLAP系统)。 作为初学者，当你考虑如何建立一个数据仓库的时候，以下问题一定会让你犯晕：· 什么是数据仓库(Data Warehouse)？什么是数据集市(Data Mart)？· 什么是星型数据库(Star Schema Database)？· 为什么我需要一个星型数据库(Star Schema Datab

2、ase)？· 星型数据库不符合范式标准，使用中会出问题吗？· 上面这些术语的具体意思是什么？本文将帮你澄清上述问题，并告诉你如何来创建一个数据仓库，为你们单位的决策服务。术语 通常，读者都会对每个章节或者书本最后附录里的术语列表感到厌烦。但是我还是不得不把这些术语放到前面来介绍。这样做的目的不是为了让你更早地觉得厌烦，而是为我们后面讨论做好准备。因为在数据仓库领域，各个组织对相同的术语都有不同的定义。数据仓库学院()曾尝试统一定义这些术语和概念。在本文中，我会给出我自己认为的最好的理解。但是请注意，我并不是在给数据仓库学院代言。OLTP OLTP代表在线事务处理(Onlin

3、e Transaction Processing)。这是一种标准的，范式化的数据库结构。OLTP是专门为事务处理设计的，要求插入(insert)、修改(update)、删除(delete)操作一定要快。设想一个订单录入的呼叫中心(call center)，工作人员不停地接受呼叫，并把所有订单和订单条目的详细内容录入到数据库中。在这种情况下，数据库的性能显得至关重要，插入(修改、删除)速度要设法达到最快。为达到这一目的，数据库的数据一般都不会有冗余，保留尽可能少的数据。OLAP和星型(Star Schema) OLAP代表在线分析处理(Online Analytical Processing)。

4、不同的人对OLAP有不同的理解。在这里，我们认为OLAP和Star Schema在很大程度上是可以互换的概念，一个星型数据库就是一个OLAP系统。这跟微软所说的OLAP不一样，微软将OLAP概念扩展到了用他们的产品OLAP Service创建出来的立方体结构(cube structure)上面。我们定义，任何存放只读的(read-only)、历史的(historical)、聚合(aggregated)的数据的系统都是OLAP系统。 另外，我们认为OLAP/Star Schema系统就是数据仓库。这样理解不会产生任何问题，虽然在数据仓库中，为了提高查询速度而经常使用立方体结构(cube stru

5、cture)。数据仓库(Data Warehouse)和数据集市(Data Mart) 你可能认为我把两个完全不同的概念混在了一起，实际上，数据仓库和数据集市只是在范围上不一样，而在创建方法和流程上完全一致。所以我把它们放在一起讨论。 数据仓库(或者数据集市)是为了日后查询而采取的一种数据存储方法。这种查询几乎总是用来为某个单位的决策服务的。这也是为什么许多数据仓库都被认为是决策支持系统(DSS, Decision-Support System)。有些人会坚持认为不是所有的数据仓库都是DSS，有些数据仓库只是纯粹的数据归档。可是我们为什么会花时间和精力去创建一个星型数据库，还有立方体结构？目的

6、是为了提高查询速度。这些查询通常要花费大量时间。人们为什么愿意花大量时间去查看数据？可能是为了发现某种趋势。如果是为了发现趋势，那你可以发誓他们是在做某个决定，比如说需要订购多少原材料。还是为决策服务！ 数据仓库和数据集市都是为了存储任何只读的(read-only)、历史的(historical)、聚合的(aggregated)数据的一种存储机制。 只读的，就是说数据不会被更改。如果某个用户要查看昨天某种产品的销售信息，那他不应该有权限去修改销售值，除非已经知道那个销售值有错误。 历史的，可能只是几分钟以前的数据，但一般都指至少一天前的数据。数据仓库都会保存某个阶段，比如五年以来的数据。而标准

7、的OLTP系统通常只保存当前的，或者是活动的数据。比如在订购表中，当商品完成发货并确认客户已经接收，就可以把定购信息转移到归档表中。 聚合的。当我们说数据仓库和数据集市存放聚合值的时候，我们要强调一个典型数据仓库中，都有许多层的聚合(aggregation)。这里我们只假设基本的聚合：数据仓库中的所有数据都按一个时间段聚合。请看下面的例子：店铺销售2种商品，狗粮和猫粮。每天都记录每种商品的销售信息，若干天后我们会得到如下的数据：   销售数量日期订单序号狗粮猫粮4/24/99152 230 326 422 533 

8、   4/25/99137 221 340表1表1中的数据是我们在标准的OLTP系统中能看到的数据。但是在数据仓库系统中，通常不会纪录这么细节的信息。相反，这些数据会按每日的总数进行聚合。数据仓库中记录看上去会是这样： 销售数量日期狗粮猫粮4/24/9915134/25/9998表 2可以看出，通过按日期聚合，我们减少了记录数，只显示了各种商品每天的销售数量，而不是每次交易的数量。 我们当然也可以在一个OLTP系统中，通过查询获得OLAP系统能提供的相同的结果。但是后面我们会看到，有很多理由让我们不要这么做。聚合(Aggregatio

9、n) 聚合并不是一个神秘的概念，它只是指概要的，加起来的值。在星型数据库中，聚合的程度可以不同。OLTP系统 OLTP系统是标准的、范式化的数据库，对事务处理(insert, update, delete)是最优秀的。OLTP系统通过下面途径，获取事务处理的更快速度：最小化重复数据，限制索引数量。(鉴于OLTP系统、三范式已为大家熟知，OLTP介绍部分略) OLTP系统对于查询分析，有诸多缺点。首先，为了获取所需的完整信息，用户经常要同时访问多张表，并把这些表连接(join)起来。连接(Join)查询通常比只访问一张表要慢。其次，OLTP系统限制每张表上的索引数量，这对Insert/updat

10、e/delete等操作有利，但是对查询只会起到反作用。最后，OLTP系统的数据对用户不友好。多数IT专业人员并不想整天为客户创建各种报表，而是希望能提供查询工具给客户，让他们自己创建各种报表。可是多数客户，并不熟悉关系数据库，他们会对连接(join)觉得神秘，会对复杂的表结构觉得恐怖。如何查看信息 一个现实问题：我们该如何查看数据库中的数据？比如说下面几个问题：· 上周Aniseed糖浆销售了多少瓶？· 跟去年相比，今年的调味品销量是升了还是降了？· 以季节或月份为标准，乳制品的销量有周期性吗？· 跟去年同期相比，今年哪些地区的销量降了？什么商品销量降幅

11、最大？ 这些问题都是商业活动中常见的问题。他们有一些共同的地方。第一，每个问题都有时间因素。第二，查看的都是聚合值，都是总量或者总数，而不是单独的事务。最后，都是按某个”by”条件查看数据。按”by”条件是指查看数据时按某些指定的条件。比如” 以季节或者月份为标准，乳制品的销量有周期性吗”这个问题，我们可以细分成”我们想按商品种类(这里只有乳制品一个种类)查看销量，按季度/月份查看”。 找出我们要查看的聚合值，像销售总额，购买某种商品的顾客数量，并且找出相应的”by”条件，就是促使我们进行星型数据库设计的驱动源。让数据库符合我们的期望 如果总是要通过一系列”by”条件来查看聚合值，我们为什么不

12、直接把数据按这种格式存储呢？ 这就是星型数据库！OLTP并不是决策支持系统的基础。OLTP中的T代表Transaction，事务。事务只关心处理订单，修改存货清单，而不关心如何处理复杂的销售趋势分析。与其在OLTP系统中执行巨大的，耗时的查询，还不如创建一个符合我们观察角度的数据库。事实(Fact)和维(Dimension) 当我们观察数据时，通常想察看聚合数据的某种顺序。这些数据叫做度量(measure)。度量就是可以度量和相加的数值。比如销售金额就是一种度量，每个订单都有销售金额。假设每天销售20个产品，每个5美元，销售总额就是100美元。销售金额就是我们想关注的一种度量。此外我们可能还想

13、知道当天的顾客数，是5位顾客一共买了20个产品，还是1位顾客买了所有的20个产品呢？销售金额和顾客数量就是我们想关注的两个度量。 仅仅关心度量还不够。我们观察度量的时候都需要”by”条件。这些”by”条件就叫做维(dimension)。讨论销售金额的时候，总要指定是某一天，某个季度或者某年的销售金额。几乎我们关心的任何度量都离不开时间维。我们可能还想按照产品名称或者产品类型查看销售金额，这些条件都要对应到相应的维上。 由上可知，设计星型数据库的时候，我们首先要确定我们想看什么信息(确定度量)，如何看这些信息(确定维)。维表 我们为什么要分割数据？维表(dimension table)回答了这个

14、问题。比如说，我们通常会根据时间来观察数据，而不会不考虑时间只看总值。如果我们的销售记录最早开始于1989年7月14日，我们会关心从那天起到现在的销售总额，还是更关心某一年和其他年份的销售金额对比情况呢？将某一年和上一年作对比，然后做趋势分析，这是人们用星型数据库来实现的常见功能。 我们可能还需要一个地域维，用来对比某个地区和其他地区的销售额，并发现销售能力比较弱的地区。这可能意味着那个地区出现了新的竞争者，广告做得不够，或者其他有待进一步调查的原因。 当我们开始创建维表的时候，有一些规则要牢记在心。第一，所有维表都要有一个基于单列的主键。这一主键列通常只是一标识列，包含自动递增的数值，并没有

15、真正的含义。有含义的信息都在其他列中，这些列包含了我们要查看的所有描述信息。比如在产品维中，包含了产品描述、类别、子类等等。这些字段不能用来作为连接字段和其他表关联，但是包含了产品的所有描述信息。维表通常都比较胖，因为字段都比较多，每一字段都比较宽。 同时，维表的另一个特性是一般都比较矮。我们的产品种类可能很多，但还是无法跟事实表(fact table)相比。假设我们的产品表中有30,000种产品，我们跟踪这些产品的每天销售记录。就算每天实际上只销售3,000种产品，十年之后，事实表中的记录数将是3,000(种产品) * 365(天/年) *10年=10,950,000！因此，跟事实表相比，维

16、表中的30,000条记录就显得很少。 既然维表这么胖，就会让人产生一种通过范式化精简的冲动。先别急着这么做，随后讨论雪花模型的时候会告诉你原因。维的层次 我们经常在OLTP系统中使用层次结构。但是OLAP系统中的层次结构有所不同，因为维(dimension)的所有层次都存在同一张维表中。 比如产品维中，包含了所有产品。这些产品分属于不同的类别，各产品类别可能又包含子类。像产品号为X12JC的商品，它属于大家电(category)里面的冰箱(sub-category)子类。可能还有更细的分类，但是这里问题的关键是所有这些信息都存储在同一张维表中。维表的结构会如下图所示：图1 请注意categor

17、y列和sub-category列都存在同一张表中，而不是存在不同的表中，然后关联起来。这种一张表中存储多个层次的存储关系，有助于实现数据钻取(drill-down)功能。我们可以按category统计总数，然后对某个子类，进行钻取分别按各个子类统计总数，进一步可以按各个产品统计总数。 下面是一个简单的例子。有2张维表(dimension table)和1张事实表(fact table)关联，事实表中只有一个数值：销售总额(SalesDollars)。 图2 现在要查看某个月份某类商品的销售总额，需要的SQL如下：SELECT Sum(SalesFact.SalesDollars) AS Sum

18、OfSalesDollarsFROM TimeDimension INNER JOIN (ProductDimension INNER JOIN SalesFact ON ProductDimension.ProductID = SalesFact.ProductID)ON TimeDimension.TimeID = SalesFact.TimeIDWHERE ProductDimension.Category=Brass Goods AND TimeDimension.Month=3AND TimeDimension.Year=1999 要钻取，查看子类的销售总额，只需把SQL改成如下语句

19、：SELECT Sum(SalesFact.SalesDollars) AS SumOfSalesDollarsFROM TimeDimension INNER JOIN (ProductDimension INNER JOIN SalesFact ON ProductDimension.ProductID = SalesFact.ProductID)ON TimeDimension.TimeID = SalesFact.TimeIDWHERE ProductDimension.SubCategory=Widgets AND TimeDimension.Month=3AND TimeDimen

20、sion.Year=1999 雪花模型 有时候，维表中的层次会存到独立的表中。这是一种更符合范式的结构，但是会导致查询更困难，相应速度也更慢。 图3表示一个雪花模型。类型层次从产品维ProductionDimension中分离出来，形成单独的一张表。我们可以看出这种结构增加了连接(join)的数量，减慢了查询速度。OLAP系统的目的就是为了加快查询速度，所以雪花模型并不是我们要推荐的数据库模型。有些人认为范式化维表可以节省存储空间。但实际上，在数据仓库中，维表的记录数通常只有所有记录数的1%。因此，通过范式化维表，或者采用雪花模型来节省空间，并不可取。图3创建事实表(Fact Tab

21、le) 事实表存放度量(measure)信息，或者称事实(fact)信息。度量是根据各个维计算出来的一些数值。比如说销售金额是个数值，我们可以按产品、安类型查看总数，可以查看任何时间段的所有总数。 跟维表的又矮又胖相比，事实表一般显得又高又瘦。事实表很高，是因为他们拥有的记录数一般都很巨大。比如看下面这个简单的例子：图4在这个例子中，有产品维，时间维和店铺维。假设有10年的日常销售纪录，一共200家店铺，销售500种产品。那么事实表的记录数将达到365,000,000 (3650天*200家店铺*500种产品)。这就是事实表的”高”。 事实表的”瘦”是因为它所存放的列。首先，事实表的主键由参照

22、其他维表的外键构成，这些列都是数值型数据。此外度量本身也是数值。因此，事实表的单行纪录跟维表的单行纪录相比，都显得很小。事实表的粒度(granularity) 创建星型数据库中，一个很重要的步骤是确定事实表的粒度。数据的粒度，或者频率，通常由时间维决定。比如你只想存储每周、每月的销售总数，而不需要每天的总数。粒度决定了用户可以在不查询OLTP系统的前提下，数据仓库中可钻取的深度。当粒度越低，可向下钻取的深度就越深，但事实表中需要存放的记录数也越多；当粒度越高，可以减少事实表中需要存放的记录数，但是一些比较细节的信息，就无法在OLAP系统中查寻。事实表的大小 从前面的例子中，我们已经发现，如果按

23、天记录销售金额的话，500种产品，200个店铺，10年时间将在事实表中产生365,000,000条记录。但这是最大的可能值。实际上很多情况下我们都不会达到这个最大记录数。在某些日期里，某些产品可能一件也没有卖出，这样的话销售记录就是0或者空值，这些0或者空值不应该放到事实表中去。 尽管这样，事实表还是占据了数据库中的绝大多数纪录，占用了绝大多数的磁盘空间。粒度越低，事实表就越大。从上面的例子可以看出，如果把每天的销售记录改成每周的销售记录的话，最大的可能纪录总数就会减少到差不多52,000,000条。 事实表字段的数据类型有助于使事实表所占用的空间尽可能地小，因为其字段类型都是数值型。 最后请注意，每增加一张维表，就有可能极大地增加事实表的记录数。上面的例子中，如果再增加一维，维表包含20条记录，那么事实表的最多纪录数就可能达到73亿条！改变属性 属性的改变是星型模型面临的最大的挑战之一。看图4的例子，在StoreDimension维表中，每个店铺都对应到相应的地区(region字段)。比如店铺A的地区为纽约，关联维表后得