资源描述
星型数据库设计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)结构来存储预先计算的值,用来加快查询速度。
展开阅读全文