星型数据结构设计

星型数据库设计by Craig Utley介绍创建一个星型数据库(Star Schema Database)是数据仓库开发中最重要的步骤之 一。要知道这一步骤有多重要，就需要了解一个标准的在线事务处理系统(OLTP) 是如何转移到最终的星型数据库系统(也叫OLAP系统)。作为初学者，当你考虑如何建立一个数据仓库的时候，以下问题一定会让你犯 晕： 什么是数据仓库(Data Warehouse) ？什么是数据集市(Data Mart) ？ 什么是星型数据库(Star Schema Database) ？为什么我需要一个星型数据库(Star Schema Database) ？星型数据库不符合范式标准，使用中会出问题吗？上面这些术语的具体意思是什么？本文将帮你澄清上述问题，并告诉你如何来创建一个数据仓库，为你们单位的决策 服务。术语通常，读者都会对每个章节或者书本最后附录里的术语列表感到厌烦。但是我 还是不得不把这些术语放到前面来介绍。这样做的目的不是为了让你更早地觉得厌 烦，而是为我们后面讨论做好准备。因为在数据仓库领域，各个组织对相同的术语 都有不同的定义。数据仓库学院(http:/www.dw-)曾尝试统一定义这些 术语和概念。在本文中，我会给出我自己认为的最好的理解。但是请注意，我并不 是在给数据仓库学院代言。OLTPOLTP代表在线事务处理(Online Transaction Processing)o这是一种标准的，范 式化的数据库结构。OLTP是专门为事务处理设计的，要求插入(insert)、修改 (update)删除(delete)操作一定要快。设想一个订单录入的呼叫中心(call center)， 工作人员不停地接受呼叫，并把所有订单和订单条目的详细内容录入到数据库中。 在这种情况下，数据库的性能显得至关重要，插入(修改、删除)速度要设法达到最 快。为达到这一目的，数据库的数据一般都不会有冗余，保留尽可能少的数据。OLAP 和星型(Star Schema)OLAP代表在线分析处理(Online Analytical Processing)o不同的人对OLAP有 不同的理解。在这里，我们认为OLAP和Star Schema在很大程度上是可以互换的 概念，一个星型数据库就是一个OLAP系统。这跟微软所说的OLAP不一样，微 软将OLAP概念扩展到了用他们的产品OLAP Service创建出来的立方体结构(cube structure)上面。我们定义，任何存放只读的(read-only)、历史的(historical)、聚合 (aggregated)的数据的系统都是OLAP系统。另外，我们认为OLAP/Star Schema系统就是数据仓库。这样理解不会产生任 何问题，虽然在数据仓库中，为了提高查询速度而经常使用立方体结构(cube structure)。数据仓库Dote Wire龙ose)和数据集市Date Mart)你可能认为我把两个完全不同的概念混在了一起，实际上，数据仓库和数据集 市只是在范围上不一样，而在创建方法和流程上完全一致。所以我把它们放在一起 讨论。数据仓库(或者数据集市)是为了日后查询而采取的一种数据存储方法。这种查 询几乎总是用来为某个单位的决策服务的。这也是为什么许多数据仓库都被认为是 决策支持系统(DSS, Decision-Support System)。有些人会坚持认为不是所有的数据 仓库都是DSS，有些数据仓库只是纯粹的数据归档。可是我们为什么会花时间和 精力去创建一个星型数据库，还有立方体结构？目的是为了提高查询速度。这些查 询通常要花费大量时间。人们为什么愿意花大量时间去查看数据？可能是为了发现 某种趋势。如果是为了发现趋势，那你可以发誓他们是在做某个决定，比如说需要 订购多少原材料。还是为决策服务！数据仓库和数据集市都是为了存储任何只读的(read-only)、历史的(historical) 聚合的(aggregated )数据的一种存储机制。只读的，就是说数据不会被更改。如果某个用户要查看昨天某种产品的销售信 息，那他不应该有权限去修改销售值，除非已经知道那个销售值有错误。历史的，可能只是几分钟以前的数据，但一般都指至少一天前的数据。数据仓 库都会保存某个阶段，比如五年以来的数据。而标准的OLTP系统通常只保存当前 的，或者是活动的数据。比如在订购表中，当商品完成发货并确认客户已经接收， 就可以把定购信息转移到归档表中。聚合的。当我们说数据仓库和数据集市存放聚合值的时候，我们要强调一个典 型数据仓库中，都有许多层的聚合(aggregation)o这里我们只假设基本的聚合：数 据仓库中的所有数据都按一个时间段聚合。请看下面的例子：店铺销售2种商品， 狗粮和猫粮。每天都记录每种商品的销售信息，若干天后我们会得到如下的数据：日期订单序号销售数量狗粮猫粮4/24/99152230326422表1中的数据是我们在标准的OLTP系统中能看到的数据。但是在数据仓库系统 中，通常不会纪录这么细节的信息。相反，这些数据会按每日的总数进行聚合。数 据仓库中记录看上去会是这样：日期销售数量狗粮猫粮4/24/9915134/25/9998可以看出，通过按日期聚合，我们减少了记录数，只显示了各种商品每天的销售数 量，而不是每次交易的数量。我们当然也可以在一个OLTP系统中，通过查询获得OLAP系统能提供的相 同的结果。但是后面我们会看到，有很多理由让我们不要这么做。聚合(Aggregation)聚合并不是一个神秘的概念，它只是指概要的，加起来的值。在星型数据库 中，聚合的程度可以不同。OLTP系统OLTP系统是标准的、范式化的数据库，对事务处理(insert, update, delete )是最 优秀的。OLTP系统通过下面途径，获取事务处理的更快速度：最小化重复数据， 限制索引数量。(鉴于OLTP系统、三范式已为大家熟知，OLTP介绍部分略)OLTP系统对于查询分析，有诸多缺点。首先，为了获取所需的完整信息，用 户经常要同时访问多张表，并把这些表连接(join)起来。连接(Join )查询通常比只访 问一张表要慢。其次，OLTP系统限制每张表上的索引数量，这对 Insert/update/delete等操作有利，但是对查询只会起到反作用。最后，OLTP系统的 数据对用户不友好。多数IT专业人员并不想整天为客户创建各种报表，而是希望 能提供查询工具给客户，让他们自己创建各种报表。可是多数客户，并不熟悉关系 数据库，他们会对连接(join)觉得神秘，会对复杂的表结构觉得恐怖。如何查看信息一个现实问题：我们该如何查看数据库中的数据？比如说下面几个问题：上周Aniseed糖浆销售了多少瓶？跟去年相比，今年的调味品销量是升了还是降了？以季节或月份为标准，乳制品的销量有周期性吗？跟去年同期相比，今年哪些地区的销量降了？什么商品销量降幅最大？这些问题都是商业活动中常见的问题。他们有一些共同的地方。第一，每个问 题都有时间因素。第二，查看的都是聚合值，都是总量或者总数，而不是单独的事 务。最后，都是按某个” by”条件查看数据。按” by”条件是指查看数据时按某些指定 的条件。比如”以季节或者月份为标准，乳制品的销量有周期性吗”这个问题，我 们可以细分成”我们想按商品种类(这里只有乳制品一个种类)查看销量，按季度/月 份查看”。找出我们要查看的聚合值，像销售总额，购买某种商品的顾客数量，并且找出 相应的” by”条件，就是促使我们进行星型数据库设计的驱动源。让数据库符合我们的期望如果总是要通过一系列” by”条件来查看聚合值，我们为什么不直接把数据按这 种格式存储呢？这就是星型数据库！ OLTP并不是决策支持系统的基础。OLTP中的T代表 Transaction，事务。事务只关心处理订单，修改存货清单，而不关心如何处理复杂 的销售趋势分析。与其在OLTP系统中执行巨大的，耗时的查询，还不如创建一个 符合我们观察角度的数据库。事实(Fact)和维(Dimension)当我们观察数据时，通常想察看聚合数据的某种顺序。这些数据叫做度量 (measure)o度量就是可以度量和相加的数值。比如销售金额就是一种度量，每个订 单都有销售金额。假设每天销售20个产品，每个5美元，销售总额就是100美 元。销售金额就是我们想关注的一种度量。此外我们可能还想知道当天的顾客数， 是5位顾客一共买了 20个产品，还是1位顾客买了所有的20个产品呢？销售金额 和顾客数量就是我们想关注的两个度量。仅仅关心度量还不够。我们观察度量的时候都需要” by”条件。这些” by”条件就 叫做维(dimension)。讨论销售金额的时候，总要指定是某一天，某个季度或者某年 的销售金额。几乎我们关心的任何度量都离不开时间维。我们可能还想按照产品名 称或者产品类型查看销售金额，这些条件都要对应到相应的维上。由上可知，设计星型数据库的时候，我们首先要确定我们想看什么信息(确定 度量)，如何看这些信息(确定维)。维表我们为什么要分割数据？维表(dimension table)回答了这个问题。比如说，我 们通常会根据时间来观察数据，而不会不考虑时间只看总值。如果我们的销售记录 最早开始于1989年7月14日，我们会关心从那天起到现在的销售总额，还是更关 心某一年和其他年份的销售金额对比情况呢？将某一年和上一年作对比，然后做趋 势分析，这是人们用星型数据库来实现的常见功能。我们可能还需要一个地域维，用来对比某个地区和其他地区的销售额，并发现 销售能力比较弱的地区。这可能意味着那个地区出现了新的竞争者，广告做得不 够，或者其他有待进一步调查的原因。当我们开始创建维表的时候，有一些规则要牢记在心。第一，所有维表都要有 一个基于单列的主键。这一主键列通常只是一标识列，包含自动递增的数值，并没 有真正的含义。有含义的信息都在其他列中，这些列包含了我们要查看的所有描述 信息。比如在产品维中，包含了产品描述、类别、子类等等。这些字段不能用来作 为连接字段和其他表关联，但是包含了产品的所有描述信息。维表通常都比较胖， 因为字段都比较多，每一字段都比较宽。同时，维表的另一个特性是一般都比较矮。我们的产品种类可能很多，但还是 无法跟事实表(fact table)相比。假设我们的产品表中有30,000种产品，我们跟踪这 些产品的每天销售记录。就算每天实际上只销售3,000种产品，十年之后，事实表 中的记录数将是3,000(种产品)* 365(天/年)*10年=10,950,000 ！因此，跟事实表相 比，维表中的30,000条记录就显得很少。既然维表这么胖，就会让人产生一种通过范式化精简的冲动。先别急着这么 做，随后讨论雪花模型的时候会告诉你原因。维的层次我们经常在OLTP系统中使用层次结构。但是OLAP系统中的层次结构有所 不同，因为维(dimension)的所有层次都存在同一张维表中。比如产品维中，包含了所有产品。这些产品分属于不同的类别，各产品类别可 能又包含子类。像产品号为X12JC的商品，它属于大家电(category)里面的冰箱 (sub-category)子类。可能还有更细的分类，但是这里问题的关键是所有这些信息都 存储在同一张维表中。维表的结构会如下图所示：FincliJutlDiiTi 日 nsiunProductID|ProductCodeIProductName1Category1SubCategoryIBrandHeight|Width|图1请注意category列和sub-category列都存在同一张表中，而不是存在不同的表 中，然后关联起来。这种一张表中存储多个层次的存储关系，有助于实现数据钻取 (drill-down)功能。我们可以按category统计总数，然后对某个子类，进行钻取一分 别按各个子类统计总数，进一步可以按各个产品统计总数。下面是一个简单的例子。有2张维表(dimension table)和1张事实表(fact table) 关联，事实表中只有一个数值：销售总额(SalesDollars)。ProductDimensioninwIDim 日 nsicin TimelDDayOWeek DayOfMonth DayOfYear Month Quarter Year Holiday Weekend现在要查看某个月份某类商品的销售总额，需要的SQL如下：SELECT Sum(SalesFact.SalesDollars) AS SumOfSalesDollarsFROM TimeDimension INNER JOIN (ProductDimension INNER JOINSalesFact ON ProductDimension.ProductID = SalesFact.ProductID) ON TimeDimension.TimeID = SalesFact.TimeIDWHERE ProductDimension.Category=Brass Goods AND TimeDimension.Month=3 AND TimeDimension.Year=1999要钻取，查看子类的销售总额，只需把SQL改成如下语句：SELECT Sum(SalesFact.SalesDollars) AS SumOfSalesDollarsFROM TimeDimension INNER JOIN (ProductDimension INNER JOINSalesFact ON ProductDimension.ProductID = SalesFact.ProductID) ON TimeDimension.TimeID = SalesFact.TimeIDWHERE ProductDimension.SubCategory=Widgets AND TimeDimension.Month=3 AND TimeDimension.Year=1999雪花模型有时候，维表中的层次会存到独立的表中。这是一种更符合范式的结构，但是 会导致查询更困难，相应速度也更慢。图3表示一个雪花模型。类型层次从产品维ProductionDimension中分离出 来，形成单独的一张表。我们可以看出这种结构增加了连接(join)的数量，减慢了 查询速度。OLAP系统的目的就是为了加快查询速度，所以雪花模型并不是我们要 推荐的数据库模型。有些人认为范式化维表可以节省存储空间。但实际上，在数据 仓库中，维表的记录数通常只有所有记录数的1%。因此，通过范式化维表，或者 采用雪花模型来节省空间，并不可取。Cat 日 gury CategorylDCategoryNameFWuctDim 日 nsicinProductlDICategorylD (FK) ProductCode ProductName Brand Height WidthSalesFactroductlD (FK) .TimelD (FK)SalesDollarsTimeDimensionTimelDDayOfWeek DayOfMonth DayOfYear Month Quarter Year Holiday Weekend创建事实表(Fact Table)事实表存放度量(measure)信息，或者称事实(fact)信息。度量是根据各个维计 算出来的一些数值。比如说销售金额是个数值，我们可以按产品、安类型查看总 数，可以查看任何时间段的所有总数。跟维表的又矮又胖相比，事实表一般显得又高又瘦。事实表很高，是因为他们 拥有的记录数一般都很巨大。比如看下面这个简单的例子：TimeDimensionProductlDIProductCode ProductName Category SubCategory Brand Height WidthSalesFactProductlD (FK)TimelD (FK)StorelD (FK)SalesDollarsTimelDDayOfWeek DayOfMonth DayOfYear Month Quarter Year Holiday WeekendStoreDimensionStorelDStoreName ParentChain Region Territory Zone Address City State Zip在这个例子中，有产品维，时间维和店铺维。假设有10年的日常销售纪录，一共 200家店铺，销售500种产品。那么事实表的记录数将达到365,000,000 （3650天 *200家店铺*500种产品）。这就是事实表的”高”。事实表的”瘦”是因为它所存放的列。首先，事实表的主键由参照其他维表的外 键构成，这些列都是数值型数据。此外度量本身也是数值。因此，事实表的单行纪 录跟维表的单行纪录相比，都显得很小。事实表的粒度“血如她）创建星型数据库中，一个很重要的步骤是确定事实表的粒度。数据的粒度，或 者频率，通常由时间维决定。比如你只想存储每周、每月的销售总数，而不需要每 天的总数。粒度决定了用户可以在不查询OLTP系统的前提下，数据仓库中可钻取 的深度。当粒度越低，可向下钻取的深度就越深，但事实表中需要存放的记录数也 越多；当粒度越高，可以减少事实表中需要存放的记录数，但是一些比较细节的信 息，就无法在OLAP系统中查寻。事实表的大小从前面的例子中，我们已经发现，如果按天记录销售金额的话，500种产品， 200个店铺，10年时间将在事实表中产生365,000,000条记录。但这是最大的可能 值。实际上很多情况下我们都不会达到这个最大记录数。在某些日期里，某些产品 可能一件也没有卖出，这样的话销售记录就是0或者空值，这些0或者空值不应该 放到事实表中去。尽管这样，事实表还是占据了数据库中的绝大多数纪录，占用了绝大多数的磁 盘空间。粒度越低，事实表就越大。从上面的例子可以看出，如果把每天的销售记 录改成每周的销售记录的话，最大的可能纪录总数就会减少到差不多52,000,000 条。事实表字段的数据类型有助于使事实表所占用的空间尽可能地小，因为其字段 类型都是数值型。最后请注意，每增加一张维表，就有可能极大地增加事实表的记录数。上面的 例子中，如果再增加一维，维表包含20条记录，那么事实表的最多纪录数就可能 达到73亿条！改变属性属性的改变是星型模型面临的最大的挑战之一。看图4的例子，在 StoreDimension维表中，每个店铺都对应到相应的地区（region字段）。比如店铺A 的地区为纽约，关联维表后得到的某一张报表如表3所示。当公司重组销售网络， 店铺A的地区改成了芝加哥。如果我们只是直接更新表中的地区（region字段）信 息，之后我们再查看芝加哥地区的历史销售信息，得到的其实是店铺A在纽约的 历史销售信息，而不是店铺A在芝加哥的真实销售纪录。StoreIDStoreNameRegionFactYear003店铺A纽约1,0002001直接修改店铺A的地区信息003店铺A芝加哥1,0002001003店铺A芝加哥1,5002002增加一条信息，保留原来的纪录不变003店铺A纽约1,0002001013店铺A芝加哥1,5002002新增列中加入原先店铺的StoreIDStoreIDStoreNameRegionFactYearPreviousStoreID003店铺A纽约1,0002001013店铺A芝加哥1,5002002003表3在这种情况下，我们一般增加一条新的纪录，用来保存店铺A的新信息，而 保留旧的纪录不变。可是这样店铺A相当于完全变成了一个新店铺，无法比较该 店铺当前和以前的销售信息。我们可以在表中增加一列，记录该店铺的以前 Storel D，来解决这个问题。以上的问题没有绝对的解决办法，属性改变时，需要针对不同的情况作出相应 的调整。聚合(aggregation)事实表上的数据已经是某种程度上的聚合值，但有时候用户会要求更高层次上 的聚合值。比如说事实表中存放的是每天的销售金额，但用户也会经常要查看每周 的销售金额。我们当然可以用SQL把每天的金额相加得到每周的值，但是这样又 要花费运行时间。于是一些设计者就把这些值预先计算出来，存在数据库中。在时 间维的DayOfWeek字段上增加一条值为9的纪录，用来表示这是按周累计的值。这是以前的数据仓库系统中常用的方法，现在的系统中一般都使用立方体 (cube)结构来存储预先计算的值，用来加快查询速度。