零售业务

刘超 18天前 ⋅ 539 阅读   编辑

  本文我们为大型超市开发有关其销售事务的维度模型。

目录
  1、维度模型设计的4步过程(选择业务过程、声明粒度、确认维度、确认事实)
  2、零售业务案例研究
  3、维度表设计细节
  4、实际的销售模式
  5、零售模式的扩展能力
  6、无事实的事实表
  7、维度与事实表键
  8、抵制规范化的冲动

 

一、维度模型设计的4步过程

  1、选择业务过程

  业务过程是由组织完成的微观活动,比如,获得订单、开具发票、接收付款、处理服务电话、处理索赔等。业务过程包含以下公共特征,理解它们将有助于区分组织中不同的业务过程:
  a、业务过程通常用行为动词表示,因为它们通常表示业务执行的活动。与之相关的维度描述与每个业务过程事件关联的描述性环境。
  b、业务过程通常由某个操作型系统支持,比如,账单或购买系统
  c、业务过程建立或获取关键性能度量。有时这些度量是业务过程的直接结果,度量从其他时间获取。分析人员总是想通过过滤器和约束的不同组合,来审查和评估这些度量
  d、业务过程通常由输入激活,产生输出度量。在许多组织中,包含一系列过程,它们既是某些过程的输出,也是某些过程的输入。用维度建模人员的话来说,一系列过程产生一系列事实表。

  我们需要仔细了解业务以区分组织的业务过程,因为商业用户难以快速回答下列问题,”您对何种业务过程感兴趣?“但用户又希望在BI系统中分析来自业务过程的性能度量。
  有时业务用户讨论的是业务战略规划,而不是业务过程。这些规划往往是由管理层为提高竞争优势而制定的抽象企业规划。我们需要将这些业务规划分解到基本业务过程中。这意味着需要深入挖掘、理解数据和操作型系统,以支持对规划进行分析的需求。

  我们也需要了解业务过程不是什么。企业业务部门或企业功能职责并不等于业务过程。将注意力放在业务过程,而不是放在功能化的部门,可以更方便地获得一致的企业信息。如果以部门为边界建立维度模型,则不可避免地将不同标号的数据及数据值重复使用。确保一致性的最好方法是一次发布数据。

  2、声明粒度
  声明粒度意味着精确定义某个事实表的每一行表示什么。粒度传递的是与事实表度量有关的细节级别。它回答“如何描述事实表中每行内容?”这一问题。粒度由获取业务过程事件的操作型系统的物理实现确定。
  典型的粒度声明如下:
  a、客户销售事务上的某个产品扫描到一行中
  b、医生开具的票据的列表内容项采用一行表示
  c、机场登机口处理的每个登机牌采用一行表示
  d、仓库中每种材料库存水平的每日快照采用一行表示
  e、每个银行账户每月的情况采用一行表示
  上述粒度声明以业务术语表示。也许我们期望粒度由对事实表的主键描述,尽管最终的粒度与主键是等价的,但将维度集合列出,然后假定这一集合就是粒度声明的方法是不正确的。无论何时,都应该以业务术语表示粒度。

  维度建模者有时会忽略声明粒度这一在4步设计过程中从表面看起来可有可无的步骤。请不要这样做。声明粒度是不容忽视的关键步骤。多年来,从对大量维度设计调试的情况来,最常见的错误就是在设计过程之初,没有为事实表声明粒度。如果不能清楚地定义粒度,整个设计就像建立在沙里之上,对候选维度的讨论处于兜圈子的状态,不适当的的事实将隐藏在设计中。不适当的维度始终笼罩着BI实现。设计组的每个人都要对事实粒度达成共识,这一点非常重要。讨论到此,可能会发现第3步或者第4步设计过程的粒度说明是错误的。尽管如此,您也必须返回第2步,重新正确地定义粒度,然后考虑第3步或第4步的问题。

  1、确定维度
  维度要解决的问题是“业务人员如何描述来自业务过程度量事件的数据?”应该使用健壮的维度集合来修饰事实表,这些维度表示承担每个度量环境中所有可能的单值描述符。如果粒度清楚,维度通常易于区分,因为它们表示的是与“谁、什么、何处、何时、为何、如何”关联的事件。常见维度的实例包括日期、产品、客户、雇员、设备等。在选择每个维度时,应该列出所有具体的、文本类型的属性以充实每个维度表。

  2、确定事实
  可以通过回答“过程的度量是什么?”这一问题来确定事实表。商业用户非常愿意分析这些性能度量。设计中的所有候选事实必须符合第2步的粒度定义。明显属于不同粒度的事实必须放在不同的事实表。奠定事实是可加性数值,比如,订货数量或成本总额(单位:元)等。
  需要综合考虑业务用户需求和数据来源的实时情况,并与4个步骤联系起来,如下图所示。强烈建议坚决抵制仅仅只考虑数据来源来建模数据。将注意力放在数据上可能不会像与商业用户交流那样复杂,但数据不能替代业务用户的输入。遗憾的是,许多组织依然在采用这种看似最省力的数据驱动的方法,当然这样做基本不能取得成功。

 

二、零售业务案例研究

  假设你在永辉超市工作。该超市由944个分布于29个省的门店组成。每个门店都有完整的部门,包括杂货、冷冻食品、日常生活用品、肉类、农产品、烘烤食品、花卉、保健/美容产品等。每个商店包含被称为产品统一编号(SKU)的60000种不同的上架产品。
  最常用的数据来自顾客购买商品使用的收银机。销售点系统(POS)在每个收款台扫描产品条形码,计算顾客从收款台带走的商品,如下图(网上找到的图片)所示为收银机发票。其他数据来自商店后端的供货商发货数据。

 

  对超市店来说,管理方面主要关注对订单、库存、销售产品的组织工作,目的是实现利润最大化。利润最终来源于赚取每种商品尽可能多的差价,降低获得产品的开销, 提供具有较强竞争力的环境以吸引更多的顾客消费。显然, 管理决策与价格和促销有关。商店管理层与总部市场部门将耗费大量时间考虑价格和促销。商店中的促销包括临时降价、报纸广告和广告插页、门店展示及礼券等。大幅降低商品价格是最直接、最有效的带来销售高潮的方法。纸巾价格降5元,特别是当辅以广告和展示的情况下,可以便纸巾销售上升10个百分点。遗憾的是,如此大的降价通常难以维持,因为纸巾可能是亏本销售。这些问题产生的结果是,所有形式的促销是分析超市店经营的重要组成部分。既然对将要研究的商业案例进行了描述,下面将开始讨论维度模型的设计问题。

  1、第一步、选择业务过程

  设计的第l步是通过对业务需求以及可用数据源的综合考虑,决定对哪种业务过程开展建模工作。

  注意:第l个DW/BI项目应该将注意力放在最为关键的、最易实现的用户业务过程。最易实现涉及一系列的考虑,包括数据可用性与质量,以及组织的准备工作等。

  在此零售业务案例研究中,管理层希望更好地理解通过POS系统获得的客户购买情况。因此将要建模的业务过程是POS零售交易。该数据保证商业用户能够分析被销售的产品,它们是在哪几天、在哪个商店、处于何种促销环境中被销售的。

  2、第2 步:声明粒度

  业务过程确定后,设计小组将面临一系列有关粒度的决策。在维度模型中应该包含哪个级别的细节数据呢?

  有许多理由要求以最低的原子粒度处理数据。原子粒度数据具有强大的多维性。事实度量越详细,就越能获得更确定的事实。将所知的所有确定的事实转换成维度。在这点上,原子数据与多维方法能够实现最佳匹配。

  原子数据能够提供最佳的分析灵活性,因为原子数据可以被约束并以某种可能的方式上卷。维度模型中的细节数据可以适应商业用户比较随意的查询请求。

  注意:设计开发的维度模型应该表示由业务过程获取的最详细的原子信息

  当然,也可以定义汇总粒度来表示对原子数据的聚集。然而, 一旦选择了级别较高的粒度,就限制了建立更细节的维度的可能性。粒度较高的模型无法实现用户下钻细节的需求。如果用户不能访问原子数据,则不可避免会面临分析障碍。尽管聚集数据对性能调整有很好的效果,但这种效果的获得仍然不能替代允许用户访问最低粒度的细节。用户可以方便地通过细节数据获得汇总数据,但不能从汇总数据得到细节数据。遗憾的是,一些行业专家对这一问题始终模糊不清。他们认为维度模型仅适合汇总数据,因此批评维度建模方法,认为这种方法需要预先考虑业务问题。当详细的原子数据在维度模型巾实际可用时,这种误解定会烟消云散。
  在本案例研究中,最细粒度的数据是POS交易的单个产品,假设POS系统按照一个购物车中某种产品为单一项而上卷所有销售。尽管用户可能不会对分析与特定POS交易关联的单项感兴趣,但这是能预测所有他们需要获得的数据的方法。例如,他们可能希望知道周一与周日的销售差别,或者他们希望评估是否值得备存大量的某品牌的商品,或者他们希望知道有多少购物者利用了洗发液5元的降价促销,或者他们希望确定某个具有竞争性的苏打水产品大幅促销所带来的减价影响。尽管上述查询不需要某一特定交易的数据,但他们提出的查询请求需要以准确的方式对详细数据执行分片操作而获得。如果仅选择提供汇总数据,则无法获得这些问题的正确答案。

  注意:DW/BI系统几乎总是要求数据尽可能最细粒度来表示,不是因为需要查询单独的某行,而是因为查询需要以非常精确的方式对细节进行切分

  3、第3 步:确定维度

  事实表粒度选择完毕后,维度的选择就比较直接了。产品与事务立即呈现。在主维度框架内,可以考虑其他维度是否可以被属性化为POS度量。例如,销售日期、销售商店、哪种销售的产品被促销、处理销售的收款员、可能的支付方法等。我们将这些以另外的设计原则表达。

  注意:详细的粒度说明确定了事实表的主要维度。然后可以将更多维度增加到事实表上,只要这些额外的维度自然地承担主维度合并的某个值。如果附加的维度会产生与粒度不符的其他事实行,则取消该维度或重新考虑粒度声明。

  以下的描述性维度应用于该案例中:日期、产品、门店、促销、收银员、支付方式。此外, POS 交易票据数量作为一个特殊维度也包含在其巾, 如事务号码的退化维度所述。

  在使用描述性属性填充维度表前,需要完成4步过程的最后一步。不希望在设计的这一阶段只见树木不见森林。

  4、第4 步:确定事实

  设计的最后一步是确认应该将哪些事实放到事实表中。粒度声明有助于稳定相关的考虑。事实必须与粒度吻合:放入POS 交易的单独产品线项。在考虑可能存在的事实时,可能会发现仍然需要调整早期的粒度声明或维度选择。

  POS系统收集的事实包括销售数量、单价、折扣、净支付价格、扩展折扣、销售额(元)等。
  备注:
  a、扩展销售额(元)等于销售数量乘以单位价格
  b、扩展销售折扣额等于销售数量乘以单位折扣额

  某些复杂的POS 系统也提供产品的标准成本,由供货商发布给商店。假设这些成本事实随时可用且不需要记述详细的基于活动的成本来源,则可以将扩展开销额包含在事实表中。如下展示了事实表雏形:

零售销售事实表  
日期维度 Date Key(FK)
Product Key(FK) 产品维度
商店维度 Store Key(FK)
Promotion Key(FK) 促销维度
收银员维度 Cashier Key(FK)
Payment Method Key(FK) 支付方式维度
POS Transaction # (DD)
Sales Quantity
Regular Unit Price
Discount Unit Price
Net Unit Price
Extended Discount Dollar Amount
Extended Sales Dollar Amount
Extended Cost Dollar Amount
Extended Gross Profit Dollar Amount

  4 类事实一一涉及所有维度的销售数量、销售可扩展额、销售、成本额一一均是完全可加的。可以对事实表按照维度属性不受限制地开展切片或切块操作。针对这4 类事实开展的汇总工作都是合法正确的。

  1、计算获得的事实
  可通过从扩展销售总额中减去扩展成本总额的方式获得总利润额,也称为收入。尽管是通过计算所得,但对所有维度来说,总利润额也是完全可加的。可以计算任意时间段内,所有商店所销售产品的任意组合的总利润额。维度建模者有时感到疑惑,是否应该将计算获得的事实放入数据库中。我们通常推荐将它们物理存储在数据库中。在本案例研究中,总利润额计算非常直接明了,但存储它就意味着其计算与ETL过程保持一致性,消除了用户计算错误产生的可能性。用户不正确表达总利润额的成本覆盖了少数增量存储成本。因此,存储总利润额也能确保所有用户和Bl报表应用引用总利润额时能够保持一致性。因为总利润额可以通过计算单一事实表行的相邻数据而获得,因此某些人认为应该采用与表差别不大的视图来执行类似的计算。如果所有用户在访问数据时都通过视图且没有用户可以采用特别的查询工具绕过视图而直接切问物理表时,可以考虑使用这种方法。视图是减少用户错误且节省存储的一种合理适当的方法,但数据库管理员要保证通过视图访问数据的方式不会产生意外。同样, 某些组织希望通过Bl工具执行计算工作。再次强调,如果所有用户访问数据时使用公共工具,这样做也是可行的。但据我们的经验来看,这样的情况很难实现。然而,有时某一报表的不可加度量,例如,百分比或比率,则必须由BI工具计算, 因为此类计算不能被预先计算出来并存储在事实表中。OLAP 多维数据库更适合这样的环境。

  2、不可加事实
  利润率可通过利润总额除以扩展销售总额获得利润率是非可加事实,因为它不能从任何维度被汇总。可以计算任意产品集合、商店或者日期的利润率,方法是分别记录收入汇总,以及开销汇总然后做除法计算。

  注意:百分比和比例(例如,利润率)是不可加的。应当将其分子分母分别存在在事实表中。比例可采用BI工具计算事实表的任意分片,只需要记住计算的是汇总的比率,而不是比率的汇总

  单价是另一种不可加事实。与事实表中的扩展额不同,对所有维度汇总单价将是毫无意义的。考虑以下实例,以1元的单价销售1个小器件,而以2元销售4个小器件(每个5毛),可以汇总销售数量是5,销售额也应该汇总(1元加2元),因此总的销售额度是3元。然后不能汇总单价(1元加上5毛)并说总单价是1.5元,适当的加权平均单价的计算可以通过利用整个销售额(3元)除以总的销售数量(5件)得到平均单件为6毛,一般不会使用这样的计算,我们仅观察每个交易的单价。为分析平均价格,在计算总额与总额销售数量的比值前,必须增加销售额及销售数量。幸运的是,多数BI工具能够正确执行此类函数。当然也存在一些问题,例如,是否非可加事实应该物理存储在事实表中。此类问题是真实存在的,并能够获得有限的分析值,除了在报表中打印单独值或直接在事实表中应用过滤器,他们都是非典型的,不常见的。某些情况下,基本的非可加事实(例如,温度)通常是从其他源系统获得的。此类非可加事实需要通过多个记录求平均值来获得。如果业务分析师同意这样做,将非可加事实存储在事实表中也是有意义的。

  3、事务事实表

  事务型业务过程是最常见的业务过程,表示这些过程的事实表具有以下特征:
  a、原子事务事实表的粒度可在事务环境下被简介地描述,例如,每个事务一行
  b、由于这些事实表记录的是一个事务事件,所以他们通常是比较稀疏的,在本章的案例研究中,我们肯定不可能将所有产品放到一个购物车中。
  c、即使事务事实表无法预测,分布稀疏,它们依然可能非常庞大。数据仓库中多数包含数十亿、数万亿行的表往往都是事务事实表。
  d、事务事实表趋向成为多维化
  e、事务事件返回的度量通常是可加的,只要它们通过数量来扩展,而不是获取单位度量。

  在设计初期,先估计一下最大的表的情况,也就是估计事实表的行数是非常有必要的。在本案例中,可以通过与源系统专家讨论,理解在每个基本周期内产生多少POS事务行项。零售业每天的流量波动比较明显,因此需要在合理的周期内理解事务活动。作为一种选择,可以估计每年新增到事实表中的行数量,方法是用每年收入总额除以平均每项的销售价格。假设销售总额为40亿元,客户票据中平均每项价格为2元,可以计算出每年大约有20亿事务项。这种估计方式是典型的工程化估计,能够使您获得非常接近实际的设计。作为一个设计者,应该始终通过多角度度量来确定您的计算是否合理。

三、维度表设计细节

  以上我们己经对4 步过程进行了研究,下面将返回维度表并关注如何为其设计健壮的属性的细节。

  1、日期维度

  日期维度是一种常见的维度,我们使用"日期维度"表示粒度按天处理的维度表,这有助于区分日期维度和当天时间(time-of-day)维度。与多数其他维度不同,可以提前建立日期维度表。可以在表中按行表示10年或20年的不同日期,因此可以涵盖存储的历史,也可以包含未来的几年。即使包括20年,日期行也仅仅大约有7300行,因此是相对较小的维度表,销售事务的日期维度表,建议部分列可以如下设计。

日期维度  
Date Key (PK) 日历键
Date 日历列
Full Date Description 完整日期描述
Day of Week 星期列
Day Number in Calendar Month
Day Number in Calendar Year
Day Number in Fiscal Month
Day Number in Fiscal Year
Last Day in Month Indicator
Calendar Week Ending Date
Calendar Week Number in Year
Calendar Month Name 日历月
Calendar Month Number in Year
Calendar Year-Month (YYYY-MM) 日历年-月
Calendar Ouarter 日历季度
Calendar Year-Ouarter
Calendar Year 日历年
Fiscal Week 财务周
Fiscal Week Number in Year
Fiscal Month 财务月
Fiscal Month Number in Year
Fiscal Year-Month 财务年-月
Fiscal Quarter 财务季度
Fiscal Year-Quarter
Fiscal Year 财务年
Holiday Indicator 是否假日
Weekday Indicator 周天标识

  在日期维度表中由行表示的特定日期定义,使用星期列可以建立用于比较周一与周日业务的报表;日期列是日历月列从每月1号开始,根据不同的月份以28、29、30 、31日结束,该列用于比较每个月的相同一天的情况,类似地,可以用每年的月号码(1、......、12)支持跨月的简单日期计算。对于报表,需要增加长标识和缩写标识,例如,比如日历月。此外,年-月(YYYY-MM)列作为报表的表头也非常有效。也希望增加季度号码(Ql 、.、Q4) ,以及2020-0l这样的年-季度属性。如下所示的样例行包含几个日期维度列。

日期键 日期列 完整日期描述 星期列 日历月 日历季度 日历年 财务年-月 是否假日 周天标识
20200101 01/01/2020 January 1,2020 Tuesday January 01 2020 F2020-01 Holiday Weekday
20200102 01/02/2020 January 2,2020 Wednesday January 01 2020 F2020-01 Non-Holiday Weekday
20200103 01/03/2020 January 3,2020 Thursday January 01 2020 F2020-01 Non-Holiday Weekday
20200104 01/04/2020 January 4,2020 Friday January 01 2020 F2020-01 Non-Holiday Weekday
20200105 01/05/2020 January 5,2020 Saturday January 01 2020 F2020-01 Non-Holiday Weekday
20200106 01/06/2020 January 6,2020 Sunday January 01 2020 F2020-01 Non-Holiday Weekday
20200107 01/07/2020 January 7,2020 Monday January 01 2020 F2020-01 Non-Holiday Weekday
20200108 01/08/2020 January 8,2020 Tuesday January 01 2020 F2020-01 Non-Holiday Weekday

  一些设计者在这一点上有疑问,为什么需要如此详尽的日期维度表? 他们存在疑问的原因在于,如果事实表上的日期键是日期类型的列,则SQL查询可以直接约束事实表的这一日期键,利用SQL提供的日期语义按照月或年过滤,从而可避免非常昂贵的连接操作。之所以这样设计是因为用户可能不大熟悉SQL日期语义,所以难以实现典型的日历分组。SQL日期函数不支持以属性(例如,工作日与周末、假日、财务周期、季度)进行过滤。业务需要按照非标准的日期属性对事实分片,那么建立一个详尽的日期维度就是基本的需求。日历逻辑由维度表解决,而不是由应用代码来解决

  注意:维度模型总是需要详尽的日期维度表。SQL日期函数不支持范围广泛的日期属性,包括财务周期、季节、假日、周末等。与其试图将这些非标准日历计算放入查询中,不如放在日期维度表中,通过查询直接获得

  a、文本属性的标识
  与大多数操作型标识类似,日期维度的假日标识是一种简单的带有两个可能值的标识。由于维度表属性用于报表和下拉式查询过滤列表中的值,所以该标识应该用有意义的值,例如,假日或非假日,而不是用神秘的Y/N、1/0 或真/假表示。如下所示,设想某个报表需要比较假日与非假日的某产品销售情况。标识采用越有意义的领域值,就越能够转换为有意义的、能够自我解析的报表。与其在BI应用中将标识编码成难以理解的标识,不如将其编码为数据库中存储的可解释的值,这样它们能够对所有用户保持一致,无论是何种BI报表环境或工具。

月销售情况 月销售情况
期间 June 2020 期间 June 2020
产品 Baked Well Sourdough 产品 Baked Well Sourdough
假日指示符 扩展销售额(元) 假日指示符 扩展销售额(元)
N 1009 Holiday 6298
Y 6298 Non-holiday 1009

  类似的参数对工作日标识也是起作用的,可以包含工作日或周末。周六或周日显然可以分配到周末类型中。当然,多个日期表属性可以共同构建约束。可以方便地比较工作日假日与周末假日。

  b、当前与相对日期属性
  大多数日期维度属性不应该更新。2020年6月1日将始终上卷到6月、日历第2季度、2020年。然而,某些属性可以增加到基本日期维度中,这些属性可随时间改变,包括IsCurrentDay、IsCurrentMonth 、IsPrior60Days等。IsCurrentDay显然每天都需要更新。该属性对建立总是指向当前天的报表有用。需要细致考虑的是IsCurrentDay所涉及的日期。大多数数据仓按天加载数据,因此IsCurrentDay涉及昨天(或者更准确地说, 是最近的加载日期) 。可在日期维度中增加属性表示企业日历,例如IsFiscalMonthEnd
  一些维度属性包括对滞后属性的更新。滞后日期列中值为0表示今天,-1表示昨天,+1表示明天等等。该属性易于成为可计算列而不是物理地存储。可用于为月、季度和年设置类似结构。许多BI工具包括实现前期计算的功能,因此这些滞后列可能没有存在的必要
  c、将当天时间(time-of-day)作为维度或事实
 尽管日期和时间可以合起来作为操作型日期/时间,但我们通常将当天时间从日期维度中分离出来,以避免在日期维度中执行行计算的复杂度。正如上面所提到的那样,20年的日期维度历史记录大约包含7300行。如果改变维度的粒度为每行表示每天的每分钟,则将会由每天的1440分钟产生将近1000万行。如果将时间跟踪到秒级别,则每年将产生3100万行。由于日期维度可能是最常用的约束模式中的维度,因此应该尽量使其保持较小的容量,易于管理。
  由于希望基于汇总时间分组来过滤或上卷时间周期,例如,15分钟间隔、小时、午餐时间、当天时间等将被视为完整的维度表,每个固定时间周期一行,例如,24小时周期内每分钟一行,将在维度中产生1440行。
  如果不需要按照当天时间分组上卷或过滤,当天时间将按照简单日期/时间事实处理,放入事实表中。顺便说一下,用户通常对滞后时间更感兴趣。例如, 事务的持续时间,而不是离散的开始时间和结束时间。滞后时间可方便地通过获得不同的时间戳来计算。这些日期/时间戳也允许应用程序确定两个不同事务之间的时间差别,即使这些事务存在于不同的日期、月份或年

  2、产品维度

  产品维度描述仓库中存储的每个SKU(产品统一编码)。尽管典型的仓库可能保存有60000个SKU ,当你所负责的不同营销方案和历史产品不再有效时, 产品维度可能有3000000行以上。产品维度几乎总是来源于操作型产品主文件。多数超市在其总部管理其产品主文件,并将部分子集频繁地下载到每个商店的POS系统上。为每个新产品定义适当的产品编号(唯一的SKU号)是管理层的职责。
  a、扁平化多对一层次
  产品维度表示每个SKU的大多数描述性属性。商品层次是属性的主要分组之一。单个的SKU上卷到品牌,品牌上卷到类别,类别分类上卷到部门。每一不同层次都存在多对一关系。此类商品层次与其他属性参见如下所示的产品子集。

产品键 产品描述 品牌描述 子类描述 类别描述 部门描述 脂肪含量
1 Baked Well Light Sourdough Fresh Bread Baked Well Fresh Bread Bakery Reduced Fat
2 Fluffy Sliced Whole Wheat Fluffy Pre-Packaged Bread Bakery Regular Fat
3 Fluffy Light Sliced Whole Wheat Fluffy Pre-Packaged Bread Bakery Reduced Fat
4 Light Mini Cinnamon RolIs Light Pre-Packaged Sweeten Bread Bakery Non-Fat
5 Diet Lovers Vanilla 2 Gallon Coldpack Ice Cream Frozen Desserts Frozen Foods Non-Fat
6 Light and Creamy Butter Pecan 1 Pint Freshlike Ice Cream Frozen Desserts Frozen Foods Reduced Fat
7 Chocolate Lovers 1/2 Gallon Frigid Ice Cream Frozen Desserts Frozen Foods Regular Fat
8 Strawberry Ice Creamy 1 Pint Icy Ice Cream Frozen Desserts Frozen Foods Regular Fat
9 Icy Ice Cream Sandwiches Icy Novelties Frozen Desserts Frozen Foods Regular Fat

  对每个SKU,商品层次的所有级别都被定义好。一些属性,例如,SKU描述,具有唯一性。在本例中,在SKU描述列中大约有300000个不同的值。从另一个极端来看,在部门属性列中仅包含大约50种不同的值。因此,平均来看,部门属性中大约有6000个重复值。这种情况是完全可以接受的。不需要将这些重复值分解到另一个规范化的表中以节省空间。记住与针对事实表空间的需求比较来说,维度表空间需求要简单得多。

  注意:将重复的低粒度值保存在主维度表中是一种基本的维度建模技术。规范化这些值将其放入不同的表将难以实现简单化与高性能的主要目标,正如抵制规范化的冲动所要讨论的那样

  产品维度表中的大多数属性并不是商品层次的组成部分。包装类型属性的值可能包括瓶、包、盒等。任何部门的任何SKU可能采用类型属性的某一个值。将该属性上的约柬合并为对整个商品层次属性的约束,往往是比较有意义的。例如,可以在谷物类查找以包包装的所有SKU。换句话说,可以针对维度属性浏览,无论它们是否属于商品层次。产品维度表通常包含多个明确的层次。
  我们推荐的部分超市维度模型的产品维度下表所示。

产品维度
Product Key (PK) 产品key
SKU Number (NK) 产品编号
Product Description 产品描述
Brand Description 品牌描述
Subcategory Description
Category Description
Department Number 部门编号
Department Description
Package Type Description
Package Size
Fat Content
Diet Type
Weight
Weight Unit of Measure
Storage Type
Shelf Life Type
Shelf Width
Shelf Height
Shelf Depth

  b、具有内嵌含义的属性
  在维度表中按照自然键概念确定的操作型产品代码通常情况下具有内嵌的含义,不同部分表示产品的不同特征。在此情况下,由多个部分组成的属性应该完整保存在维度表中,也可以分解到不同的组成部件上,将被当成不同属性处理。例如,操作型代码的第5到第9个字符表示制造商,则制造商的名称也应该被包含在维度表属性中。

  c、作为属性或事实的数字值
  有时可能会遇到某些数字值,很难判断应该将其归入维度属性分类, 还是归入事实分类。经典的例子是产品的价格标准列表。产品价格很显然是一个数字值,因此初始的想法可能是将其当成事实来对待。但是通常标准价格变化缓慢,不像其他事实表中的数量值,对不同的度量事件产生不同的值。
  如果某个数字值主要用于计算目的,则它可能应该属于事实表。因为标准价格是非可加的,可用它乘以数量获得扩展总额,这个值是可加的。另外,如果标准价格主要用于价格变化分析,也许变化度量应该被存储在事实表中。如果能预先定义稳定的数字值,用于过滤和分组,则它应该被当成产品维度属性对待。

  有时,数字值可同时用于计算以及过滤/分组功能。在此情况下,应当在事实表和维度中同时存储该值。也许事实表中的标准价格表示销售事务的价格,而维度属性则标记为指示其当前情况的标准价格。

  注意:可用于事实计算和维度约束、分组及标记的数据元素应该被保存在两个不同的位置,即使聪明的程序员可以编写应用程序来访问单一地址的这些数据元素。非常重要的是,维度模型应尽可能保持一致,应用开发应该简单且可预见。涉及计算的数据应该放入事实表中,涉及约束、分组和标记的数据应该放入维度表中

  d、下钻维度属性
  合理的产品维度表可包含大约50个左右的描述性属性。每个属性可作为约束和构建行头指针标识的来源。下钻只不过是从维度表中请求行头指针以提供更多信息。
  假定有一个简单报表,用于按部门汇总销售额。如下所示,如果需要下钻,可从产品维度中拖曳任何属性,如品牌,放入报表,紧靠部门之后,可以自动下钻下一个层次的细节情况。可以根据脂肪含量属性下钻,即使该属性并不在需要上卷的商品层次上。

销售美元
部门名称 总额
Bakery 12331
Frozen Foods 31776

  按照品牌下钻:

销售美元
部门名称 品牌名称 总额
Bakery Baked Well 3009
Bakery FluHy 3024
Bakery Light 6298
Frozen FoodS Coldpack 5321
Frozen FoodS Freshlike 10476
Frozen FoodS Frigid 7328
Frozen FoodS Icy 2184
Frozen FoodS QuickFreeze 6467

  或按照脂肪含量下钻:

销售美元
部门名称 胭脂含量 总额
Bakery Nonfat 6298
Bakery Reduced fat 5027
Bakery Regular fat 1006
Frozen Foods Nonfat 5321
Frozen Foods Reduced fat 10476
Frozen Foods Regular fat 15979

  注意:在维度模型上下钻只不过从维度表中增加了行头指针属性。上卷操作将移除行表头。可以根据属性从多个层次上卷或下钻,其中部分属性不是层次的组成部分

  产品维度是大多数维度模型中的常见维度。需要注意的是,构建该维度可能使用大量的描述性属性。健壮完整的维度属性集合将会转换为商业用户的健壮完整的分析能力。我们将在以后进一步探索产品维度,并将讨论如何处理产品属性发生变化的问题。

  3、商店维度

  商店维度描述零售连锁店的每个门店。与产品主文件在每个大型食品杂货连锁业都己经存在不同,一般没有一个全面完整的商店主文件。POS系统可能仅仅支持交易记录上的商店号。在此情况下,项目组必须从多个操作型源汇集构建商店维度所需的各种元素。通常在总部都会存在一个商店的房产部门,利用该部门的信息可定义详细的商店主文件。
  a、多层次维度表
  商店维度是本案例中主要的地理维度。每个商店可以被考虑为一个地址。可按任何地理属性对商店进行上卷操作,例如,邮编、国家、省份等。按照一般的观点,城市和省不在一个层次上。因为许多省包含好多城市,因此可能需要在商店维度中包含一个城市-省 (City-Province)属性。
  商店也可能按照内部组织层次上卷,这种层次包含商店街区和地区。这两个不同的商店层次,用维度来表示都非常容易,因为用一行就可以方便地将组织层次和地理位置定义清楚。

  注意:在一个维度表中表示多个层次并不常见。跨多个层次的属性名称和值应该具有唯一性

  推荐的超市店维度表见如下

商店维度
Store Key (PK)
Store Number (NK)
Store Name
Store Street Address
Store City
Store County
Store City-Province
Store Province
Store Zip Code
Store Manager
Store District
Store Region
Floor Plan Type
Photo Processing Type
Financial Service Type
Selling Square Footage
Total Square Footage
First Open Date
Last Remodel Date

  表中的Floor Plan类型, Photo Processing类型以及Financial Services 类型都是短文本描述符,描述特定的商店。不要用一位字符代码描述它们,而应该采用10~20个字符的描述符,这样当使用下拉方式过滤列表时或使用报表标记时能够具有可理解的含义。
  描述Selling Square Footage的列是数字并且理论上是跨商店可加的。可能会试图将其放入事实表中,然而,很显然,它是商店的一种约束属性,用于约束或标记的可能比作为可加元素进行计算的可能大。出于该原因,将该属性放入商店维度表中。

  b、维度表中的日期
  商店维度中的首次开店日期与最后改建日期是日期类型的列。然而,如果用户希望按照非标准的日历属性(如开店日的财务周期)分组和约束,则它们通常需要连接键以复制到日期维度表中。这些日期维度拷贝可通过视图结构用SQL描述,并与主要日期维度存在语义上的差别。视图定义如下所示:

create view first_open_date (first_open_day_number , first_open_month ,. . . ) as select day_number , month , ......  from date

  构建该视图后,系统似乎建立了日期维度的另一个称为FIRST OPEN DATE 的物理拷贝。该新日期数据表的约束与连接到事实表的主日期维度约束之间没有什么关系。建立的视图是商店维度允许的支架,有关支架的概念以后介绍。注意我们仔细地重新对视图中的所有列进行了标记,以便能与主日期维度的列区分开来。这些针对单一物理日期维度的不同逻辑视图是维度所扮演角色的实例,我们将在以后更详细地讨论这一问题。

  4、促销维度

  促销维度可能是零售业模式中最有趣的维度。促销维度描述了销售商品的促销条件。促销条件包括临时降价、终端通道展示、广告、礼券等。促销维度通常被认为是一种因果维度,因为它描述了认为可能导致产品销售发生改变的因素。
  无论是总部还是商店的商业分析师都希望能够确定的是,某个促销是否有效。促销基于以下一个或多个因素来判断:
  - 促销产品的销售是否在促销期间获得大幅增加,也称为提升。提升多少的度量可以根据未进行促销活动时,该产品的基本销售情况来定。基本销售情况可以从先前历史销售情况估计出来,某些情况下,可通过复杂模型获得。
  - 促销产品在促销前或促销后的销售,与促销期间的销售比较,是否有降低,这种降低是否抵消了促销期间的销售增益。换句话说,是否将常规价格产品的销售转换到降价销售产品上?
  - 促销产品在销售方面表现良好,但是其他与其相邻的产品的销售却显著降低了。(销售侵蚀) 。
  - 促销分类中的所有产品是否都获得了销售方面的净总增益,将考虑促销前、促销期间、促销后的时间段(市场增大) 。
  - 促销是否有利可图。通常促销的利润考虑整个促销分类的利润与基本销售利润之比,当然需要考虑促销期间和销售侵蚀,以及促销开销的影响。
  影响销售的潜在因果条件不需要由POS系统直接处理。事务系统跟踪降价。礼券的出现通常也通过交易获得,因为客户要么在销售时出示礼券,要么不出示。广告和橱窗展示条件可能需要其他源的介入。
  各种可能存在的因果条件是高度关联的。临时降价通常与广告或终端通道展销相关。出于此类原因, 在促销维度中为每个发生的促销条件的组合建立一行是具有实际意义的。在过去一年中,有1000个广告, 5000个临时降价,1000个终端通道展销,但仅有10000次该三个条件的组合能够影响任何特定的产品。例如,对某次促销,多数门店同时采用上述三种促销机制。但一些商店可能没有布置终端通道展销。在此情况下,需要两个不同的促销条件行,一行包括通常的降价加广告加展示,另外一行包括降价加广告。推荐的促销维度表如下所示

促销维度
Promotion Key (PK)
Promotion Code
Promotion Name
Price Reduction Type
Promotion Media Type
Ad Type
Display Type
Coupon Type
Ad Media Name
Display Provider
Promotion Cost
Promotion Begin Date
Promotion End Date

  纯粹从逻辑上考虑, 通过将4个不同的因果机制(降价、广告、展示、礼券)区分开,建立不同的维度而不是将它们合并在一个维度中,这一方法将会记录与促销信息相似的信息。当然,最终如何选择是设计者的权利。赞成将4个维度放在一起的理由如下:
  - 如果4个因果机制高度关联,合并而成的单一维度不会比任何一个单个维度大很多。
  - 合并成单一维度可方便浏览,观察降价、广告、展示、礼券的相互影响关系。然而,这样的浏览仅展示了可能的促销组合。对维度表的浏览无法揭示促销对哪个商店或产品有影响,此类信息显然需要浏览事实表方能获得。
  赞成将4个因果机制划分到4个不同的维度巾的原因如下:
  - 对业务群体来说,当分别考虑不同的机制时,不同的维度可能更易于理解。在业务需求访谈期间,这一问题就会显露出来。
  - 对不同维度的管理可能比对合并维度的管理更直接。
  记住这两种选择在内容上没有差别

  注意:应当仔细权衡包含在促销维度中的促销成本属性。该属性可用于约束和分组。然而,该成本没有出现在表示独立产品销售的POS事务事实表中,因为其粒度不符. 成本应该驻留在粒度为整个促销的事实表中。

  空外键、空属性和空事实
  通常,许多销售事务包括未被促销的产品。客户并不会只将促销产品放入其购物车中,当然希望他们的购物车中装满付全价购买的商品。促销维度必须包含一行,具有唯一键0或-1,用以表示这不含促销条件,避免事实表中出现空的促销键。如果将一个空值放在事实表中的己被声明为外键的列, 则违背了参照完整性的要求。另外,参照完整性警告,包含空值的键是给用户带来困惑的主要原因,因为他们无法实现与空值的连接操作

  警告:不要在事实表中使用空值键。正确的设计应在对应维度表中包括一行以表明该维度不可用于度量。

  有时我们会遇到维度属性值是空值的情况。当某一给定的维度行未被完全填充时,或者有些属性未被应用到所有维度行时,就会导致出现空值。无论是哪种情况,我们建议用描述性字符串替换那些空值,例如,用Unknown(未知)或Not Applicable(不适用)等。空值基本不会出现在下拉菜单的属性值或报表的分组上, 需要用特殊语法加以区分。如果用户按某个完整填充的维度属性分组,然后按带有空值的维度属性分组, 查询结果会不一样。您将会接到用户电话, 告诉您数据出现不一致性。与其让属性空值存在,不如用空白键或问号替换空值。最好标注上条件,用户能从他们的查询中排除那些带有Unknown或Not Applicable 的行。值得注意的是某些OLAP 产品禁止使用空值属性,因此这也是一个避免使用空值的理由。
  最后,有时我们可能在事实表中也会遇见空值。让事实表非空的方法可通过聚集函数处理,如sum 、min、max 、count 和avg 等。如果用零值替换可能会使聚集计算产生倾斜。
  数据挖掘工具可能使用不同的技术处理空值。在建立数据挖掘观察集合时可能需要做些推荐之外的额外转换工作。

  5、其他零售业维度

  任何出现在事实表度量事件中的表示单一值的描述性属性是增加到一个己存在维度或自身维度的一个好的选择。有关某个维度是否应该与某个事实表关联的决策应该基于事实表声明的粒度来确定是或者不是。例如,可能由出纳员确定每个事务。对应的出纳员维度可能包含非专属于员工属性的子集。类似于促销维度,出纳员维度针对每个通过自助服务注册的事务可能没有对应的出纳员行。
  一种展现支付方式的更棘手的情况是,也许商店有严格的规则, 每个事务仅能接受一种支付方式。作为维度建模人员比较简单的方式是将一个简单的支付方式维度附加到销售模式中,该维度可能包括支付方式描述,以及将支付方式分组为现金等价物或信用卡支付类型。
  现实生活中,支付方式通常呈现出更复杂的场景。如果单个POS交易可以接受多种支付方法,则支付方式可能不是以声明粒度的单一方式出现。与其将声明的粒度替换为非自然的,例如,每种产品每种支付方式一行这样的方式,不如将支付方式获取到不同的事实表中,其粒度要么是每个事务一行(各种支付方式选项可出现在不同事实表中) ,要么是每个事务的每个支付方式一行(需要与每行关联的不同的支付方式维度)。

  6、事务号码的退化维度

  零售事实表的每个列表项行都包含POS事务号码。在某个操作型的父/子关系数据库中,POS事务号码是事务头指针记录的键。包括所有将事务作为一个整体的有效信息。例如,事务日期和商店标识。然而,在维度模型中,已经从其他维度获得了该头指针信息。POS事务号仍然有用,因为它可用于分组键, 将购买的所有产品放在一个单一的市场购物篮事务中。还能确保与操作型系统的关联。
  尽管POS 事务号码看起来像事实表中的维度键,但是当POS 事务维度被清空时,描述性项可能会出现错误。因为产生的维度是空的,我们将POS 事务号码称为退化维度(文章图中以DD标记区分)。自然存在的操作型发票号码,例如, POS 事务号码, 处于本身的事实表中,没有连接到维度表中。当事实表粒度表示单一事务或事务列表时,退化维度是比较常见的,因为退化维度表示双亲的唯一标识符。订单号码、发票号码、提货单号码几乎总是出现在维度模型的退化维度中。
  退化维度通常在事实表的主键中起着重要的作用。在本案例研究中, 销售事实表主键包含退化POS 事务号码以及产品键,假定扫描市场购物篮的相同产品将被分组到单一列表项中。

  注意:操作型事务控制号码,例如,订单号码、发票号码、提货羊号码通常产生空的维度并且表示为事务事实表中的退化维度。退化维度是没有对应维度表的维度键。

  如果出于某些原因,在所有其他维度建立后, 一个或多个属性合理地留下,似乎属于头指针实体,则可以通过标准连接建立规范维度行。然而这样做就不会保留退化维度。

四、实际的销售模式

  采用我们的销售POS模式设计,下面看看如何将它实际应用到查询环境中。某个商业用户可能对2020l月期间波士顿区通过促销快餐分类的周销售总量感兴趣。如下所示,可以按日期维度中的月和年、商店维度中的区、产品维度中的分类加以约束。
                        零售业务销售模式查询

 

  备注:上图地址
  如果查询工具按照周结束日期和促销分组汇总销售数量,SQL查询结果看起来类似如下所示。可以消楚看到维度模型与关联查询之间的关系。高质量维度属性是至关重要的,因为它们是查询约束和报表标签的来源。如果使用具有更多功能的Bl工具, 结果出现为交叉"主元"报表,该报表对业务用户来说比按照、SQL 语句获得的按列排列数据更有吸引力。

日历周/结束日期 促销名称 扩展销售美元数额
January 6,2020 No Promotion 2647
January 13,2020 No Promotion 4851
January 20,2020 Super Bowl Promotion 7248
January 27,2020 Super Bowl Promotion 13798

 

部门名称 未促销扩展销售美元总额 Super Bowl促销扩展销售美元总额
January 6,2020 2647 0
January 13,2020 4851 0
January 20,2020 0 7248
January 27,2020 0 13798

五、零售模式的扩展能力

  让我们将注意力转移到扩展初始维度设计方面。在首次推出零售模式几年后,超市开发了频繁购物者程序。与其注意身份不明的购买者通过现金收款柜台购买了26件物品,还不如重视能够确定的特定购买人。简单地设想一下商业用户分析购物模式的兴趣,他们主要是对地理、人口统计学、行为以及购物者的其他不同特征感兴趣。
  处理新出现的频繁购物者信息相对来说是比较直接的。建立新的频繁购物者维度表,并在事实表上增加一个外键。由于无法要求购物者拿着以往的收款机发票,以新的频繁购物者号记录历史销售事务,所以只能以默认购物维度代理键替代,对应先前的频繁者购物程序维度行到历史事实表行。同样,并不是每个在超市店购物的人都有一个频繁购物者卡,因此希望在购物维度中包含一个频繁购物者的不确定行。正如在前面促销维度中讨论的那样,在事实表中不能存在空的频繁购物者键。
  我们的原始模式方便地进行了扩展,以实现这一新维度,主要是因为POS 事务数据在最初建模时就是以最细粒度级别构建的。增加的维度可方便地应用细粒度,不必改变维度键或事实,所有现存的BI应用不需要任何改变,仍然可以运行。如果最初定义的粒度是日零售销售(按天、商店、产品和促销汇总的事务),而不是事务列表细节数据,则无法合并到频繁购买者维度上。过早地聚集和汇总限制了增加补充维度的能力,因为增加的维度通常无法在更高粒度级别上应用。
  维度模型可预见的对称性确保它们能够承受一些源数据相当显著的变化,以及建模假设为无效的现有的BI 应用,包括:
  a、新维度属性。如果发现了维度的新文本描述符,可以把这些属性作为新列增加进去。所有现存的应用将可以不受这些属性的影响而继续其工作。如果新属性仅在某特定时间点可用,则老的维度行中将插入不可用或类似的描述。要警告的是,如果商业用户想要根据新确定的属性跟踪历史数据变化,则该场景将更加复杂。在此情况下,特别需要注意将要讨论的缓慢变化维度
  b、新维度。如前所述,可在事实表上增加新维度,在事实表上增加新的外键列并将新维度的主键填写到该外键列上。
  c、新可度量事实。如果新的可度量事实可用,可以将它们方便地增加到事实表。最简单的实例是当新事实在同一个度量事件中可用,并与己经存在的事实粒度相同时。此时, 事实表被改变,增加了新列,值被填充至表中。如果新事实仅在某个时间点可用,则将空值填充到旧事实表行中。更复杂的情况是,当新的可度量事实以不同粒度出现时,如果新事实不能分配或分派到事实表的原始粒度,新事实应有属于自己的事实表,因为在同一个事实表中出现不同的粒度是错误的。

六、无事实的事实表

  前面介绍的零售模式无法解决的一个重要问题是:处于促销状态但尚未销售的产品包括哪些?销售事实表所记录的仅仅是实际卖出的SKU 。事实表行中不包括由于没有销售行为而SKU 为零值的行, 因为如果将包含零值的SKU 都加到事实表中,则事实表将变得无比巨大。
  在关系世界中,回答上述"关注什么未发生"这样的问题需要促销范围或事件事实表。促销范围事实表的键可以是研究案例中的日期、产品、商店、促销等。这看起来与刚刚设计的销售事实表相似。然而,粒度存在显著差别。在促销范围事实表中,将为每天(或每周,如果促销是以一周为持续期的话)每个商店中促销的产品加载一行,无论产品是否卖出。事实表能够确保看到被促销定义的键之间的关系,与其他事件例如产品销售无关。我们将其称为无事实的事实表,因为它没有度量结果,仅仅获得所包括的键之间的关系,如下所示。为便于计算,可以包括虚拟事实,例如,本例中的促销计数,它始终包含常量值1.这是一种包装方法,可使BI应用避免对外键计数。

 

  为确定当前促销的产品中哪些尚未卖出, 需要两步过程: 首先,查询促销无事实的事实表,确定给定时间内促销的产品。然后确定通过POS 销售事实表哪些产品己经卖出去了。答案就是上述两个结果列表的差集。如果您的数据存储在OLAP 多维数据库中,则回答类似"什么未发生? "这样的问题比较容易,因为多维数据库通常包含未发生行为的确切值。

七、维度与事实表键

  到目前为止,已经设计完成了模式,下面将重点放到维度和事实表的主键以及其他行标识符上。
  1、维度表代理键
  维度表的唯一主键应该是代理键而不是来自于操作型系统的标识符,也就是所谓的自然键。代理键有许多其他的称谓: 无意义键、整数键、非自然键、人工键和合成键等等。代理键简单地以按照顺序序列生成的整数表示。产品行的第l行代理键为1,则下一产品行的键为2 ,如此进行。实际的代理键值没有业务上的意义。代理键的作用仅仅就是连接维度表与事实表。通观本文,列名带有Key后缀的,表示该键是主键PK或外键FK ,表示可能是代理键。
  有时建模人员不愿意放弃使用自然键,因为他们希望基于操作型代码查询事实表,而不希望与维度表做连接操作。他们不希望失去包含由多个部分组成的具有业务含义的键。然而,应该避免使用包含业务含义的智能多维键,因为我们所做出的任何假设最终都可能会变得无效。同样,查询和数据访问应用不应该有任何与键的依赖关系,因为这些逻辑很容易失效。即使自然键看起开似乎是比较稳定的且毫无意义,也不要试图使用它们作为维度表的主键。

  注意:数据仓库中维度表与事实表的每个连接应该基于无实际含义的整数代理键。应该避免使用自然键作为维度表的主键

  最初,利用操作型自然键作为维度模型的主键实现起来可能比较便捷。但从长远来看,使用代理键的效果会更好。有时,我们把它们当成是数据仓库中的流感疫苗一一类似于免疫, 最初建立和管理代理键可能会带来一定的麻烦,但从长远来看,效果是巨大的。特别是考虑到可以避免大量返工的危险。下面列举其优点:
  a、为数据仓库缓冲操作型系统的变化:代理键确保仓库小组维持对DW/BI 环境的控制,而不受制于生产代码的建立、更新、删除、循环、重用等操作型规则。在许多组织中,历史的操作型代码,例如,不活跃账户码或废弃的产品代码,在经过一段休眠期后,可能会被重新分配。如果账户号在不活跃12个月后重新使用,操作型系统不会停止运行,因为其业务规则禁止数据闲置如此长的时间。但是对DW/BI系统来说,可能需要保存数据许多年。代理键为数据仓库提供了一种机制,用于区分同一个操作型账号的两个不同的实例。如果仅仅依赖操作型代码,可能在获取或整理数据时遭遇键重叠的问题。
  b、集成多个源系统:代理键能够确保数据仓库小组从多个操作型源系统中集成数据,即使它们缺乏一致性的源键,通过后端整理,建立交叉引用映射表可将多个自然键连接成为一个公共的代理键。
  c、改进性能:代理键是尽可能小的一个整数, 这样能够确保方便地适应未来预期的粒度变化(维度行的数量变化) 。通常操作型代码是庞大的字母数字组合串,甚至是由一组字段构成。转换到越小的事实表中的代理键越小, 事实表的索引就越小,则能够一次输入,输出更多的事实表行。通常,对大多数维度来说,4字节整数足够了。4 字节整数是单一整数,不是4 个十进制数字。它有32 位, 能够处理大约20亿正数值(232)或40亿正负数值(-232 ~~ +232) 。对任何维度来说, 这一数量足够使用。如果您的事实表包含10 亿数据行, 事实表行中的每个字节可以转化为另外多个GB的存储。
  d、处理空值或未知条件:如前所述,特定的代理键值用于记录不涉及操作型代码的维度条件, 例如,非促销条件或匿名客户。可分配一个代现键区分这些缺乏操作型代码的情况。类似地, 事实表有时包含确定的日期。SQL没有这类日期类型值用于确定的日期或不可应用的日期。
  e、支持维度属性变化跟踪:一种主要的处理维度属性变化的技术需要代理键处理单向然铀的多个轮廓 。这实际上也是使用代理键的最重要的原因之一,我们将在以后描述这一问题。伪代理键简单地将自然键粘接到一起,增加一个时间戳,这种方式存在危险。需要避免多个维度和事实表的连接。有时称为双筒连接,主要原因在于这样会降低性能和易用性。

  当然,代理键需要分配和管理,但并未超出人们的预想。需要在ETL 系统建立并维护交叉参考表,用于以代理键替代每个事实表和维度表行。在第四章中我们会设计一个管理代理键的过程。
  2、维度中自然和持久的超自然键
  类似代理键,由操作型源系统分配和使用的自然键使用其他名称,例如,业务键、产品键和操作键等。在本文中,它们用NK标识表示。自然键通常被建模为维度表的属性。如果自然键来自于多个源, 可以使用字符日期类型作为源代码,例如,SAPI43251或CRMI6539152 。如果同一个实体在两个操作型源系统中表示,则可能在维度中存在两个自然键属性, 表示不同系统的实体。操作型自然键通常组成有意义的组合键,例如,产品的业务列表或来源国,这些组件应该被分开,当成不同的属性使用。
  在跟踪维度表属性变化时, 重要的是能够确定一个标识符用于唯一地和可靠地区分维度实体的属性变化。尽管操作型自然键适合做这一工作,有时出于不确定的业务规则(例如,组织合并)或要么处理重复条目要么从多源数据处理数据集成,自然键会发生变化。如果维度的自然键没有受到完全的保护和保存, ETL系统需要分配永久的持久性标识符,也被称为超自然键。持久的超自然键被DW/BI 系统控制并在系统生命周期中保持不变。类似维度代理键,它是一种简单的整数序列分配方法。类似前期讨论的自然键,持久的超自然键被当成维度属性处理,它不能作为维度表的代理主键的替换方式。以后也将讨论ETL系统处理这些持久性标识符的责任。
  3、退化维度的代理键
  尽管通常不会给退化维度分配代理键,但每种环境下仍然需要评估以确定是否需要。如果事务控制号在跨多个本地系统或重用时不是唯一的,则需要分配代理键。例如,销售商的POS 系统可能不会为多个商店分配唯一的事务号。当得到其最大可分配号码后,系统可能会归零并重用先前使用过的控制号。事务控制号可能会大到24 字节的字母数字列。最后,与BI工具的能力有关,需要分配代理键(建立关联维度表)以横向钻取事务号。显然,以此方式对应维度表建模的控制号维度不再退化。
  4、日期维度的智能键
  正如我们已经注意到的那样,日期维度具有特殊的特征和需求。日历日期是固定的预先可确定的,不需要担心删除日期或处理新的、未预见的日历上的日期。因为日期维度具有可预测性,因此可以在日期维度中使用更加智能的键。如果序列整数作为日期维度的主键, 则该键应该按照时间先后顺序分配。换句话说,第1年的1月1号代理键应该分配为1,1月2号为2,2月1号应该为32等。
  更具一般性的是,日期维度的主键是一个有意义的整数,其格式为YYYYMMDD。YYYYMMDD键并未打算提供给业务用户和他们的BI应用,而是采用整数键的格式,防止他们绕过日期维度直接查询事实表。事实表的YYYYMMDD 键的过滤对可用性和性能具有决定性影响。对日历属性的过滤和分组发生在维度表,而不应该处于BI应用的代码中。
  然而,YYYYMMDD 键可用于分区事实表。分区确保能够将表划分为更小的表。按照日期对一个大的事实表进行划分是可行的,因为可以移除旧数据,加载新数据,索引当前分区,而不需要操作事实表的其他内容,减少了加载、备份、归档以及查询响应的时间。如果日期键是有序的整数, 年按照增量l到希望的年份,月从l到12等等,则可以直接用程序更新和维护分区。使用智能YYYYMMDD 键提供代理,将使分区的管理更加方便。
  尽管YYYYMMDD 整数是日期维度键中最常用的方法, 一些关系数据库优化器却愿意使用真正的日期类型列分区。在此情况下,优化器知道在3月1日至4月1日之间存在31个值,而与20200301和20200401对应,则存在100个值(20200401-20200301=100) 。同样, 12月1 日到l月1 日之间存在31个值,与之相对,20191201到20200101之间存在8900个整数值(20200103-20191201=8900) 。这些值将影响优化器的查询策略选择,并缩短查询时间。如果优化器包含日期类型的值,应该考虑使用日期键。如果日期类型键的唯一合理性被DBA 以管理为由简化了,那么不会感觉有压力。
  采用更多的智能日期键,无论是否周期性分配或采用更具有语义的YYYYMMDD 整数或采用数据类型列,都需要保留特殊的日期键值以应对事实表刚刚加载时日期未知的情况。
  5、事实表的代理键
  尽管我们坚决主张在维度表中使用代理键,但并未要求在事实表中一定使用代理键。事实表中的代理键通常只是对后端ETL处理有帮助。如前文所述,事实表的主键通常包括表外键的子集以及退化维度。然而,事实表的单行代理键可以获得一些有意思的后端效益。类似其维度副本,事实表代理键是一个简单整数,不包含任何业务含义,按照事实表行顺序分配。虽然事实表代理键不可能获得查询性能方面的改进,但它的确可以带来以下的利益:
  a、直接的唯一标识。单一事实表行可以由此键直接获得。在ETL 处理过程中,不需要查询多个维度就可以识别出特定的行。
  b、返回或恢复海量加载。若某一加载涉及大量的行,这些行带有顺序分配的代理键,在完成前过程停止, 则通过观察表中的最大键,数据库管理员能够准确地确定过程在何处停止。数据库管理员可以不执行完全加载,只定义一个需要加载的范围键,或从正确的点重新开启加载过程。
  c、插入加删除的替换更新。事实表代理键成为事实表中真正的物理键。不再是仅仅由一系列维度外键组合而成的事实表键,至少到目前为止与关系数据库管理系统有关。因此它可能采用插入加删除的方式替换事实表更新操作。第一步是将新行放入数据库巾,并保留要替换行的所有业务外键。由于键实现仅仅依赖于外键,替换行具有新代理键,因此这一步是可以实现的。第二步是删除原始行,由此完成更新。对大数据集的更新,这样的步骤显然比真正采用的更新操作要好。查询的处理具有前文描述的返回和恢复能力。插入不需要采用全套的事务机制保护。最后的删除工作执行得非常安全,因为插入操作己经完成了。
  d、使用事实表代理键作为父/子模式中的父节点。一个事实表包含的行是另外粒度更细的事实表的父指针。父表中的事实表代理键也会暴露在子表中。使用事实表代理键而不使用自然父键与在维度表中使用代理键一样都存在争议。自然键是混乱且无法预测的,然而代理键是明确的整数并由ETL 系统分配,而不是由源系统分配。当然,除了包括父事实表的代理键外,低粒度事实表包括父节点的维度外键,因此子事实表也包括父维度的外键,因此子事实不必遍历父事实表的代理键就可以被分片或分块。我们将在以后讨论,不应当直接将事实表与其他事实表连接。

八、抵制规范化的冲动
  本节将直接面对几个诱使具有规范化建模背景的建模者采用规范化建模的自然冲动。我们一直在有意识地打破传统建模规则,因为我们主要关注体现易用性和性能的价值,而不是关注事务处理的效率。
  1、具有规范化维度的雪花模式
  带有重复文本的扁平非规范化维度表便来自操作型世界的数据建模者非常不舒服。让我们回到案例研究的产品维度表。300000个产品上卷到50个不同的部门。不是冗余存储20个字节的部门描述在产品维度表中,具有规范化阅历的建模者希望存储2字节部门代码,并为部门编码建立新的部门维度。事实上,如果原始设计中的所有描述符都被规范地放入不同的维度中,他们会感到更满意。他们认为这样的设计节省了空间,因为300000行维度表仅包含代码,没有冗长的描述符。
  此外,一些建模者主张,维度表越规范则越容易管理。如果部门描述符发生变化,他们只需要更新部门维度,而不需要在原始产品维度中执行6000次重复工作。维护通常由规范化处理解决,但所有这些都发生在ETL 系统后端,距离数据被加载到展现区的维度模式中已经有很长时间了。
  规范化的维度表被称为雪花模式。冗余属性从扁平非规范化维度表中移除,放置于不同规范化的维度表巾。图“产品维度的雪花模式”描述部分符合第3范式的产品维度的雪花模式。与图“产品维度的雪花模式”比较,图“产品维度表”令人吃惊,多么复杂的雪花模式(我们的例子是最简单的) 。设想将图“零售业务销售模式查询”的所有模式层次都规范化会是什么样呢?
  雪花模式是维度建模的合法分支,然而,我们建议抵制采用雪花模式的冲动主要出于设计动机:易用性和性能。
  a、众多的雪花模式表构成了一个复杂的结果。业务用户不可避免地要与复杂性抗争。简单化是维度建模的主要目标之一。
  b、多数数据库优化器也要考虑处理雪花模式的复杂性。大量的表和连接操作通常导致缓慢的查询性能。连接结果定义的复杂性增加了优化器选择错误策略的可能性。
  c、与雪花模式维度表有关的磁盘空间节省问题并不是非常明显。如果将300000行中的20字节的部门描述符替换为2字节描述,可节省5.4MB(300000*18字节) 。与此同时,事实表具有10GB , 维度表容量与事实表比较呈几何级数减少。为节省磁盘空间而规范化维度表的努力通常可以认为是浪费时间。
  d、雪花模式对用户浏览维度的能力具有负面影响。浏览通常包含约束一个或多个维度属性并寻找其他属性基于约束属性的不同值。浏览允许用户理解维度属性值之间的关系。
  e、显然,如果仅希望获得分类描述列表, 雪花模式产品维度表非常不错。然而,如果想要浏览分类中的所有品牌,则需要遍历品牌和分类维度。如果还希望获得分类中每个品牌的包装类型,则需要遍历更多的表才行。执行这些看起来比较简单的查询所要采用的SQL 语句相当复杂, 需要获取其他维度或事实表。
  f、最后,雪花模式无法实现位图索引。在索引低粒度的列(例如,产品维度表中的分类和部门属性)时,位图索引非常有用。位图索引能提高查询或针对问题中的单一列约束的性能。雪花模式不可避免地影响了利用这些性能的能力。

                           产品维度的雪花模式
 

  注意:固定深度层次在维度表中应该被扁平化。规范化雪花模式维度表不利于多属性浏览并妨碍了位图索引的使用。通过规范化维度表所节省的磁盘空间通常不会超过整个模式所需要空间的1% 。应该知道牺牲一些维度空间有利于改善性能和可用性。

  一些数据库提供商认为他们的平台可以加速完全规范化维度模型的查询而不会带来性能问题。如果没有通过非规范化的方法获得性能的满足,是不错的选择。然而,我们仍然希望实现一种具有非规范化维度的逻辅上的维度模型,以获得易于被业务用户以及他们所使用的BI应用理解的模式。
  过去,一些BI工具对雪花模式带有一种偏好,雪花模式解决了BI工具的特殊需求。同样,如果所有数据都通过OLAP 多维数据库发布给商业用户(雪花模式用于装载多维数据库,但对用户来说是不可见的),则采用雪花模式是可以接受的。然而, 在上述环境下,而要考虑用户改变其BI工具时所造成的影响,以及未来迁移到其他工具的灵活性问题。
  2、支架表
  尽管我们一般不推荐使用雪花模式,但某些场景下是可以使用的,例如,为某个事实表范围之内的维度建立附加的支架维度,如下图所示。在该例中,"一旦删除"支架表示日期维度,该维度与主维度呈雪花模式。支架表日期属性具有描述性的独特的标记用于区分与业务过程有关的其他日期。只有当业务希望按照非标准的日历属性(例如,财务周期、商业日期指示或假日周期)过滤或分组日期时,针对主维度属性的日期属性构建支架才有意义。否则,只需要考虑将日期属性作为产品维度中的标准日期类型列对待。如果使用了日期支架,注意当标准日期维度表按照范围存储时,支架日期将发生错误。

 

  后续我们将学习到更多有关支架的实例,例如,有关处理客户的县级人口统计学属性的问题。
  尽管支架表可以节省空间井能够确保相同的属性被一致地引用,但它仍然存在缺点。支架表引入了更多的连接,连接严重降低了系统的性能。更重要的是, 支架表不易为商业用户理解,限制了用户在单一维度中浏览属性的能力。

  警告:尽管可以使用支架哀, 但出于对其潜在影响的考虑,维度模型尽量不要大量使用支架表. 尽量不要使用支架表,纵然使用也是不得己, 而不应该当成一条原则来使用

  3、包含大量维度的蜈蚣事实表
  维度模式中的事实表自然地具有高度规范化和紧凑的特性。无法进一步规范化事实表键之间的极端复杂的多对多关系,因为维度之间不是相互关联的。每个商店每天都开门。迟早,促销的产品差不多会在每个商店被卖出。
  有趣的是, 尽管对非规范化维度表不太感冒,但某些建模者仍然试图非规范化事实表。尽管存在规范化维度层次的难以控制的期望,但是知道雪花模式是存在问题的。因此通过加入事实表消除了规范化表。不是在事实表上建立单一产品外键,他们将产品层次上频繁分析的元素也当成外键,例如,品牌、分类、部门等等。同样,日期键划分为一系列连接不同周、月、季度、年维度表的键。在知道它以前,原先紧凑的事实表变成连接大量维度表的奇形怪状的怪物。我们将这样的设计称为蜈蚣事实表,因为它们可能会有100 条腿,如下所示。

 

  即使有紧凑的格式, 事实表也是维度模型中的巨兽。包含太多维度表的事实表设计,将导致事实表需要更多磁盘空间。尽管非规范化维度表需要额外的空间, 但事实表仍然是最大的问题,因为事实表是最大的表,维度表与之比较不是一个数量级的。在蜈蚣表实例中, 无法实现对多部分构成的键构建有效的索引。大量的连接无论是对可用性还是对性能来说都是一个问题。
  多数业务过程可以用不超过20 个维度的事实表表示。如果某个设计有25个或更多维度,应该考虑采取措施合并关联的维度。具有良好关联的属性,例如, 层次级别,以及具有统计相关性的属性, 都应该放入一个维度中。在产生的新维度比不同维度的笛卡尔积小很多的情况下,可以考虑合并这些维度。

  注意:大量的维度通常表明某些维度不是完全独立的,应该合并为一个维度. 将同一层次的元素表示为事实表中不同维度是维度建模常见的错误

  列数据库开发可以减少与蜈蚣事实表有关的查询和存储的负担。不是将表的每行都存储,列数据库将表列作为连续对象存储, 表列建立了索引。即使基本的物理存储是按列存储的,在查询级别上,表仍按熟悉的行方式显示。在进行查询时,只有命名列从磁盘上被检索,而不是以传统的面向行的关系数据库那样检索整行。列数据库更有利于处理前面讨论的娱蛤表。然而, 浏览跨层次关联维度属性可能会受到一些影响。

九、总结
 无论处于那一行业,我们都鼓励在处理维度模型设计时采用4 步过程方法。注意清楚地声明与维度模型关联的粒度是特别重要的。加载事实表时,获取原子数据将会带来最大的灵活性,因为可按任何可能的方式对数据进行汇总。若事实表存储的是汇总数据,那么在很多情况下,将会遭遇无法有效实现汇总需求的情况。另一个至关重要的问题是,在构建维度表时使用详细的、健壮的描述性属性,以方便分析过滤和标识。


注意:本文归作者所有,未经作者允许,不得转载

全部评论: 0

    我有话说: