精华内容
下载资源
问答
  • 数据仓库常见建模方法与建模实例演示

    万次阅读 多人点赞 2020-04-14 15:52:09
    大数据的数仓建模是通过建模的方法更好的组织、存储数据,以便在 性能、成本、效率和数据质量之间找到最佳平衡点。一般主要从下面四点考虑 访问性能:能够快速查询所需的数据,减少数据I/O 数据成本:减少不必要的...

    1.数据仓库建模的目的?

      为什么要进行数据仓库建模?大数据的数仓建模是通过建模的方法更好的组织、存储数据,以便在 性能、成本、效率和数据质量之间找到最佳平衡点。一般主要从下面四点考虑

    1. 访问性能:能够快速查询所需的数据,减少数据I/O
    2. 数据成本:减少不必要的数据冗余,实现计算结果数据复用,降低大数 据系统中的存储成本和计算成本 
    3. 使用效率:改善用户应用体验,提高使用数据的效率
    4. 数据质量:改善数据统计口径的不一致性,减少数据计算错误 的可能性,提供高质量的、一致的数据访问平台

    2.常见的数据建模方法

    数据仓库本质是从数据库衍生出来的,所以数据仓库的建模也是不断衍生发展的。从最早的借鉴数据库的范式建模,到逐渐提出维度建模,Data Vault模型,Anchor模型等等,越往后建模的要求越高,越需满足3NF,4NF等。但是对于数据仓库来说,目前主流还是维度建模,会夹杂着范式建模。

    数据仓库建模方法论可分为:范式建模、维度建模、Data Vault模型、Anchor模型。

    3.常见四种建模方法的建模步骤与演示

    3.1.范式建模(E-R模型)

             将事物抽象为“实体”、“属性”、“关系”来表示数 据关联和事物描述;实体:Entity,关系:Relationship,这种对数据的抽象 建模通常被称为ER实体关系模型  
           ER模型是数据库设计的理论基础,当前几乎所有的OLTP系统 设计都采用ER模型建模的方式,且该建模方法需要满足3NF。Bill Inom提出的数仓理论,推荐采用ER关系模型进行建模,BI架构提出分层架构,数仓底层ods、dwd也多采用ER关系模型就行设计。
         但是 逐渐随着企业数据的高增长,复杂化,数仓全部使用ER模型建模 显得越来越不合时宜。为什么呢,因为其按部就班的步骤,三范式等,不适合现代化复杂,多变的业务组织。
    E-R模型建模的步骤(满足3NF)如下:
    1.  抽象出主体         (教师,课程)
    2. 梳理主体之间的关系   (一个老师可以教多门课,一门课可以被多个老师教)
    3. 梳理主体的属性    (教师:教师名称,性别,学历等)
    4. 画出E-R关系图

    3.2.维度建模

           维度建模,是数据仓库大师Ralph Kimball提出的,是数据仓库工程领域最流行的数仓建模经典。
          维度建模以分析决策的需求出发构建模型,构建的数据模型为分析需求服务,因此它重点解决用户如何更快速完成分析需求,同时还有较好的大规模复杂查询的响应性能。维度建模是面向分析的,为了提高查询性能可以增加数据冗余,反规范化的设计技术。

          Ralph Kimball提出对数据仓库维度建模,并且将数据仓库中的表划分为事实表、维度表两种类型。

    3.2.1.事实表

          在ER模型中抽象出了有实体、关系、属性三种类别,在现实世界中,每一个操作型事件,基本都是发生在实体之间的,伴随着这种操作事件的发生,会产生可度量的值,而这个过程就产生了一个事实表,存储了每一个可度量的事件。

    以电商行业为例:电商场景:一次购买事件,涉及主体包括客户、商品、商家,产生的可度量值 包括商品数量、金额、件数等

             

        事实表根据粒度的角色划分不同,可分为事务事实表、周期快照事实表、累积快照事实表。注意:这里需要值得注意的是,在事实表的设计时,一定要注意一个事实表只能有一个粒度,不能将不同粒度的事实建立在同一张事实表中。

    1. 事务事实表,用于承载事务数据,通常粒度比较低,它是面向事务的,其粒度是每一行对应一个事务,它是最细粒度的事实表,例如产品交易事务事实、ATM交易事务事实。
    2. 周期快照事实表,按照一定的时间周期间隔(每天,每月)来捕捉业务活动的执行情况,一旦装入事实表就不会再去更新,它是事务事实表的补充。用来记录有规律的、固定时间间隔的业务累计数据,通常粒度比较高,例如账户月平均余额事实表。
    3. 累积快照事实表,用来记录具有时间跨度的业务处理过程的整个过程的信息,每个生命周期一行,通常这类事实表比较少见。

    3.2.2.维度表

            维度,顾名思义,业务过程的发生或分析角度。比如从颜色、尺寸的角度来比较手机的外观,从cpu、内存等较比比较手机性能维。维度表一般为单一主键,在ER模型中,实体为客观存在的事物,会带有自己的 描述性属性,属性一般为文本性、描述性的,这些描述被称为维度。
           比如商品,单一主键:商品ID,属性包括产地、颜色、材质、尺寸、单价等, 但并非属性一定是文本,比如单价、尺寸,均为数值型描述性的,日常主要的维度抽象包括:时间维度表、地理区域维度表等
     
    案例:某电商平台,经常需要对订单进行分析,以某宝的购物订单为例,以维度建 模的方式设计该模型
          涉及到事实表为订单表、订单明细表,维度包括商品维度、用户维度、商家维度、区域维 度、时间维度 
             商品维度:商品ID、商品名称、商品种类、单价、产地等 
             用户维度:用户ID、姓名、性别、年龄、常住地、职业、学历等 
             时间维度:日期ID、日期、周几、上/中/下旬、是否周末、是否假期等 

    维度分为:

    (1)退化维度(DegenerateDimension)

    在维度类型中,有一种重要的维度称作为退化维度,亦维度退化一说。这种维度指的是直接把一些简单的维度放在事实表中。退化维度是维度建模领域中的一个非常重要的概念,它对理解维度建模有着非常重要的作用,退化维度一般在分析中可以用来做分组使用。

    (2)缓慢变化维(Slowly Changing Dimensions)

    维度的属性并不是始终不变的,它会随着时间的流逝发生缓慢的变化,这种随时间发生变化的维度我们一般称之为缓慢变化维(SCD)。比如员工表中的部门维度,员工的所在部门有可能两年后调整一次。

    3.2.3.维度建模模型的分类

      维度建模按数据组织类型划分可分为星型模型、雪花模型、星座模型。
    (1) 星型模型

    星型模型主要是维表和事实表,以事实表为中心,所有维度直接关联在事实表上,呈星型分布。

    (2)雪花模型

        雪花模型,在星型模型的基础上,维度表上又关联了其他维度表。这种模型维护成本高,性能方面也较差,所以一般不建议使用。尤其是基于hadoop体系构建数仓,减少join就是减少shuffle,性能差距会很大。

    尖叫提示:所以由上可以看出
    1.  星型模型和雪花模型主要区别就是对维度表的拆分
    2. 对于雪花模型,维度表的涉及更加规范,一般符合3NF,有效降低数据冗余,维度表之间不会相互关联,但是
    3. 而星型模型,一般采用降维的操作,反规范化,不符合3NF,利用冗余来避免模型过于复杂,提高易用性和分析效率,效率相对较高。

    (3)星座模型

         星座模型,是对星型模型的扩展延伸,多张事实表共享维度表。数仓模型建设后期,大部分维度建模都是星座模型。

    3.2.4. 维度建模步骤

    维度建模步骤:选择业务过程->声明粒度->确定维度->确定事实。旨在重点解决数据粒度、维度设计和事实表设计问题。

    声明粒度,为业务最小活动单元或不同维度组合。以共同粒度从多个组织业务过程合并度量的事实表称为合并事实表,需要注意的是,来自多个业务过程的事实合并到合并事实表时,它们必须具有同样等级的粒度。 

    3.3 DataVault模型

              Data Vault是Dan Linstedt发起创建的一种模型方法论,Data Vault是在ER模型的基础上衍生而来,模型设计的初衷是有效的组织基础数据层,使之易扩展、灵活的应对业务的变化,同时强调历史性、可追溯性和原子性,不要求对数据进行过度的一致性处理。同时设计的出发点也是为了实现数据的整合,并非为数据决策分析直接使用。
              Data Vault模型是一种中心辐射式模型,其设计重点围绕着业务键的集成模式。这些业务键是存储在多个系统中的、针对各种信息的键,用 于定位和唯一标识记录或数据

    Data Vault模型包含三种基本结构 :

    1. 中心表-Hub :唯一业务键的列表,唯一标识企业实际业务,企业的业务主体集合 
    2. 链接表-Link: 表示中心表之间的关系,通过链接表串联整个企业的业务关联关系 
    3. 卫星表- Satellite: 历史的描述性数据,数据仓库中数据的真正载体

    3.3.1 中心表-Hub

    3.3.2 链接表-Link

    3.3.3 卫星表- Satellite

    3.3.4 Data Vault模型​​​​​​建模流程

    1. 梳理所有主要实体 
    2. 将有入边的实体定义为中心表 
    3. 将没有入边切仅有一个出边的表定义为中心表 
    4. 源苦衷没有入边且有两条或以上出边的表定义为连接表 
    5. 将外键关系定义为链接表

    尖叫提示:Hub想像成人体的骨架,那么Link就是连接骨架的韧带组织, 而satelite就是骨架上的血肉。 Data Vault是对ER模型更近一步的规范化,由于对数据的拆解和更偏向于基础数据组织,在处理分析类场景时相对复杂, 适合数仓低层构建,目前实际应用场景较少
     

    3.4Anchor模型

    1.  Anchor是对Data Vault模型做了更近一步的规范会处理,初衷是为了 设计高度可扩展的模型,核心思想是所有的扩张只添加而不修改,于 是设计出的模型基本变成了k-v结构的模型,模型范式达到了6NF
    2. 由于过度规范化,使用中牵涉到太多的join操作,目前木有实际案例,仅作了解

    4.四种模型总结

    以上为四种基本的建模方法,当前主流建模方法为: ER模型、维度模型
    1. ER模型常用于OLTP数据库建模,应用到构建数仓时更偏重数据整合, 站在企业整体考虑,将各个系统的数据按相似性一致性、合并处理,为 数据分析、决策服务,但并不便于直接用来支持分析。缺陷:需要全面梳理企业所有的业务和数据流,周期长,人员要求高。
    2. 维度建模是面向分析场景而生,针对分析场景构建数仓模型;重点关注快 速、灵活的解决分析需求,同时能够提供大规模数据的快速响应性能。针对性 强,主要应用于数据仓库构建和OLAP引擎低层数据模型。优点:不需要完整的梳理企业业务流程和数据,实施周期根据主题边界而定,容易快速实现demo
    3.  数仓模型的选择是灵活的,不局限于某一种模型方法
    4. 数仓模型的设计也是灵活的,以实际需求场景为导向
    5. 模型设计要兼顾灵活性、可扩展,而对终端用户透明性
    6. 模型设计要考虑技术可靠性和实现成本

    5.常用建模工具

    建模工具,一般企业以Erwin、powerdesigner、visio,甚至Excel等为主。也有些企业自行研发工具,或使用阿里等成熟套装组件产品。

    展开全文
  • 数据仓库查询性能 Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer   概览: 星型联接查询优化 分区表并行处理 ROW 和 PAGE 压缩 分区对齐的索引视图 较前期同类产品相比,SQL Server 2008 将...
    SQL Server 2008
    数据仓库查询性能
    Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer
     
    概览:
    • 星型联接查询优化
    • 分区表并行处理
    • ROW 和 PAGE 压缩
    • 分区对齐的索引视图

    较前期同类产品相比,SQL Server 2008 将提供功能更为强大的关系数据仓库,但是您可能仍希望了解如何充分利用这项新技术来构建性能良好的数据仓库,以便对数十亿行的数据进行决策支持查询。或者您可能希望了解哪些功能将有助于您获取针对决策支持查询和报告的最佳查询性能,或者该新版本的 SQL Server ® 可实际带来哪种性能改进。
    越接近实际产品发布,越会产生许多问题。我们在此深入探讨 SQL Server 2008 中一些与性能相关的最重要数据仓库功能,希望能对您有所裨益。

    逻辑数据库设计:维度建模
    事务性业务线应用程序通常使用规范化数据库架构。关系数据仓库的逻辑数据库架构设计不太注重规范化。现在的许多关系数据仓库设计都使用维度建模方法,这种方法的流行得益于 Ralph Kimball 和 Margy Ross 合著的《The Data Warehouse Toolkit:The Complete Guide to Dimensional Modeling》。
    如果经常与数据仓库打交道,您可能已经熟悉关系数据仓库的常见架构模式(如星型架构和雪花型架构)。维度建模将维度表与事实表区分开来。维度表存放主数据(如产品、客户、商店或国家),而事实表则存储事务性数据(如销售、订单、采购或利润)。
    维度表和事实表是通过主键 (PK)/外键 (FK) 关系链接在一起的。您会发现许多数据仓库并未强行要求将 FK 约束作为降低存储要求的方法。这就节省了基础索引的存储开销,降低了事实表的维护成本。数据仓库中的维度表通常非常的小 — 一般最少几千行,最多几百万行。事实表则非常大,存放着上亿到十几亿行。因此,事实表的存储要求是逻辑设计的真正重点。
    在确定应从维度表中选择哪个键来维护事实表/维度表关系时,这一大小因素也在考虑之内。基于维度业务键的组合键(表示维度所代表实体的实际标识符)通常包含多个列。请注意,对于事实表中的对应外键而言,这是个问题,因为会对每个事实表行重复这一多列组合键。
    为此,一种常见的方法是使用小的代理键在事实表及其维度之间建立关系。代理键是一个整数型标识列,用作维度表的虚拟主键。事实表引用更小的代理键后,大型事实表的存储要求得以显著降低。 图 1 说明了使用维度表和代理键型事实表的维度建模数据仓库架构。
    Figure 1  包含一个事实表和两个维度表的星型架构示例 (单击该图像获得较大视图)
    雪花型架构设计将一个或多个维度散布到多个级别(例如,某个客户维度的客户、国家和地区),从而规范化数据中存在过度冗余的大型维度。各级别由单个表代表,从而使该架构呈雪花状。而星型架构设计则不将其任何维度散布到多个表内。星型架构形状象星星,其中维度表围绕四周,中心是事实表。
    通过使用维度建模的星型架构或雪花型架构,决策支持查询采用以下典型模式:查询从事实表选择多个感兴趣的度量、通过代理键联接事实行和一个或多个维度、针对维度表的业务列应用筛选谓词、按一个或多个业务列分组,并统计在一段时间内从事实表检索的度量。以下即是该模式的示例,它有时也被称为星型联接查询:
    复制代码
    select ProductAlternateKey,
    CalendarYear,sum(SalesAmount)
    from FactInternetSales Fact
         join DimTime 
    on Fact.OrderDateKey = TimeKey
         join DimProduct 
    on DimProduct.ProductKey =
       Fact.ProductKey
    where CalendarYear between 2003 and 2004
          and ProductAlternateKey like 'BK%'
    group by ProductAlternateKey,CalendarYear
    

    物理设计
    关系数据仓库中的许多 SQL 查询都遵循星型联接查询结构。然而,决策支持查询通常应时而变,因为决策者们总是希望通过新方法来更好地了解其基础业务数据。这就是数据仓库要面对大量临时查询的原因。这也使得决策支持查询和维度建模数据仓库架构的物理设计富有挑战性。
    通过使用 SQL Server,数据仓库设计人员通常首先建立一个蓝图或物理设计,它可根据工作负载的变化进行精细调整。可根据您数据仓库环境的实际情况自由使用或更改该蓝图。如果是这样,请谨记数据库物理设计的最佳实践,如索引更新维护所引起的性能影响以及索引的存储要求。

    事实表
    蓝图设计旨在预计典型的星型查询形状并构建事实表的索引。事实表的聚集索引将多个维度代理键列(外键列)作为索引键。最常用的列应出现在索引键列表中。您可能希望确认它确实为工作负载中最常执行的查询提供了一个不错的访问路径。
    此外,蓝图为事实表中的每个维度代理(外键)列创建了一个单列非聚集索引。从而为某一维度极具选择性的查询提供了一个高效访问路径。
    聚集索引的目标是为提高工作负载中大部分查询的性能。非聚集索引组的目标是为特定客户或产品检索事实表度量的查询。这些非聚集索引可帮您提高效率,例如,您不必扫描事实表来检索单个客户的销售数据。

    维度表
    在对维度表应用蓝图设计时,需为每个维度表创建索引。它们包括针对维度代理键列的非聚集主键约束索引,以及针对维度实体业务键列的聚集索引。对于大型维度表,还应考虑增加针对高选择性谓词中常用列的非聚集索引。
    聚集索引在数据仓库维护期间可提高提取、转换和加载 (ETL) 效率,而维护对于时间要求往往很严格。例如,通过缓慢地更改维度,现有行得到适当更新,同时尚未出现在维度中的行则附加到维度表。为成功达到目标,该访问模式要求在 ETL 期间维度表的查询和更新一切顺利。
    要对利用 SQL Server 构建的关系数据仓库进行物理设计,我们提到的蓝图设计是个不错的起点。根据该典型关系数据仓库设置,我们可研究 SQL Server 2008 中包含的重要新功能。

    星型联接查询优化
    处理事实表通常是在维度建模关系数据仓库中执行星型联接查询时最大的开销。这一点显而易见,因为即使是高选择性的查询,从事实表检索的行数都远比从任何维度表检索出的要多得多。因此,针对事实表使用最好的访问路径对于实现良好的查询性能而言至关重要。
    通过使用 SQL Server,查询优化器可自动从一组备选方案中选择开销最低的访问路径。在数据仓库环境中,主要目标是确保查询优化器为星型联接查询的执行计划选择最佳的备选访问途径。SQL Server 的查询优化器中使用多种功能自动提供性能良好的星型查询执行计划。
    可将星型联接查询视为分成三个不同的类别(如 图 2 所示)。这几大类还有助于 SQL Server 引擎找出适用于这些查询的正确计划。SQL Server 所依据的主要概念是这些查询相对事实表的选择性。查询从事实表提取的行越少,越具选择性。事实表检索行的百分比用作这些查询类的直观区分标准。这些百分比代表来自典型客户部署的值,但它们并非用于生成访问路径定义的严格分界线。
    Figure 2  星型联接查询的选择性范围 (单击该图像获得较大视图)
    第一类为高选择性查询,最多处理事实表中 10% 的行。第二类为中等选择性,所包含的查询处理事实表行的 10% 到 75% 。第三类是低选择性查询,在事实表中处理的行超过 75%。图中的方框还着重指出了每个选择性类别中的基本查询执行计划选项。

    基于选择性的计划选项
    由于高选择性星型查询检索的事实表行通常不超过 10%,因此这些查询可随机访问事实表。所以,该类的查询计划主要依赖于嵌套循环联接以及对事实表的(非聚集)索引搜索和书签查询。由于它们对事实表执行随机 I/O,因此如果需要检索大部分事实表,可通过连续 I/O 获取更好的性能。由于从事实表检索的行数超过了一定数量,所以需要另一种查询计划。
    由于中等选择性的星型查询会处理事实表中相当多的行,哈希联接和事实表扫描或事实表范围扫描通常是对事实表的首选访问路径。SQL Server 使用位图筛选器来改善这些哈希联接的性能。
    图 3 显示了 SQL Server 如何使用这些位图筛选器来改善星型联接查询执行期间的联接性能。该图显示了针对 Product 和 Time 两个维度表的一个查询计划,这两个表通过代理键与事实表联接。查询针对两个维度表使用筛选谓词(如 WHERE 子句),以便只能有一行符合所有维度。两个联接运算符旁边的小红表格是这一特征的指示符。
    Figure 3  减少了联接的星型联接查询计划 (单击该图像获得较大视图)
    每个联接的联接实施都是哈希联接,它允许 SQL Server 从维度表将有关合格行的信息提取到两个维度表的联接减少信息中。图中的绿色方框代表联接减少信息数据结构。从底层维度表取得数据后,SQL Server 会在查询执行期间自动把这些数据结构移到处理事实表的运算符(如表扫描)。该运算符使用有关维度表行的信息来除去不满足维度联接条件的事实表行。
    从事实表检索行后,SQL Server 在查询处理期间的前期就会剔除这些事实表行。这样,在查询计划的后续运算符中无需再处理已剔除的行,从而可节省 CPU 甚至磁盘 I/O 的开销。SQL Server 使用位图表示来有效表达查询执行期间的联接减少信息数据结构。

    星型联接优化管道
    优化过程使用标准的试探法来实现联接查询优化,以生成最初的一组查询执行计划备选方案。然后调用特殊的扩展项来生成其他查询计划备选方案。
    对于数据仓库,扩展项会检测星型架构、雪花型架构以及星型查询模式,并评估查询相对事实表的选择性。如果架构和查询形式与模式匹配,SQL Server 会自动向计划空间添加更多的查询计划,然后基于开销的优化会对其进行处理以选择最有前景的查询执行计划。
    在查询执行期间,SQL Server 还会监视联接减少在运行时的实际选择性。如果选择性发生变化,SQL Server 会动态重新排列联接减少信息数据结构,以便首先应用具有最大选择性的计划。

    星型联接试探法
    数据仓库的许多物理设计均遵循星型架构,但并不完全指定事实表和维度表之间的关系(例如之前提到的外键约束)。如果缺少明确指定的外键约束,SQL Server 必须依赖试探法来检测星型架构查询模式。可使用以下试探法来检测星型联接查询模式:
    1. 参与 n 元联接的最大表被视为是事实表。对于事实表的最小大小还有一些限制。例如,如果即使是最大的表也并未超过指定的大小,则不将 n 元联接视为星型联接。
    2. 星型联接查询中二元联接的所有联接条件必须为单列等式谓词。联接必须为内部联接。尽管可能听起来比较具有限制性,但是它涉及典型星型架构中使用代理键的事实表和维度表之间的绝大多数联接。如果联接拥有更复杂的联接条件且不符合以上所述模式,则将从星型联接排除该联接。例如,如果其中两个联接具有更复杂的联接谓词,五路联接可生成三路星型联接(其他两个联接以后再用)。
    请注意,存在试探法规则。在实际情况中,很少会让试探法将维度表选作事实表。它会影响计划选择,但不会影响所选计划的正确性。然后,按选择性降序排列星型联接中的二元联接。本文中联接选择性定义为事实表的输入基数与联接的结果基数的比率 — 联接选择性表示具体维度减少了多少事实表基数。通常,我们希望首先考虑具有更高选择性的联接。
    如果结果查询计划的估计查询花销适合,SQL Server 中的查询处理器会针对符合星型号联接模式和上述条件的查询自动应用优化。因此,您无需更改应用程序即可受益于这一显著的性能改进。但要注意,某些星型联接优化(如联接减少)只有 SQL Server Enterprise Edition 才提供。

    星型联接性能结果
    在开发 SQL Server 2008 中的星型联接优化时,我们根据基准和实际客户工作负载进行了大量性能研究。其中三种工作负载产生的结果颇具价值。
    Microsoft 销售组织数据仓库 该工作负载跟踪用于 Microsoft 销售组织内部决策支持的数据仓库的性能。我们列出了一个示例快照,其中的数据库约为 750GB(包括索引)。在此工作负载中,许多查询的联接数都大于 10,因此处理起来有一定难度。
    零售客户 这组试验针对零售行业中的数据仓库客户(包括常规店铺和在线展示)。客户的特点是维度建模的雪花型架构和规范的星型联接查询。我们使用约 100GB 的原始数据来填充试验用仓库快照。
    决策支持工作负载 这组试验调查针对 100GB 维度建模数据库的决策支持工作负载的性能。 图 4 显示这三个工作负载的结果。该图显示了工作负载中所有查询的查询响应时间标准几何平均数。它明确指出了从工作负载运行任意查询时所期望的查询性能。图中的柱条比较了未使用星型联接优化时的基准性能 (1.0) 和星型联接优化后的性能。它们均是使用的 SQL Server 2008。
    Figure 4  星型联接优化后的性能改进 (单击该图像获得较大视图)
    如图所示,所有工作负载均有显著改进(12% 到 30%)。尽管具体的优势可能不尽相同,但是我们期望在 SQL Server 2008 中引入新的星型联接特定优化扩展项后,针对 SQL Server 引擎的决策支持工作负载可改善约 15%–20%。

    分区表并行处理
    为加快大型数据仓库的查询处理速度,数据库管理员常常按日期对大型事实表进行分区。它将数据放到不同的文件组内,从而减少了处理某一数据范围内的行时所必须搜索的数据量,并且当文件组被部署到多个物理磁盘上时,可利用底层磁盘系统的并发性能。
    SQL Server 2005 可以将大型关系分成较小的逻辑块,大型表的管理因而得以改善。它还被成功用于改善查询处理,尤其是对于大型决策支持应用程序而言更显成效。
    遗憾地是,某些使用 SQL Server 2005 的客户发现针对这些分区表的查询存在性能问题 — 尤其是在并行共享内存多处理器机器上运行时。在 SQL Server 2005 中针对分区表处理并行查询时,仅分配可用线程的一个子集执行查询时会出现上述情况。
    试想有一台查询最多可并行使用 64 个线程的 64 核机器,并且查询会涉及两个分区。使用 SQL Server 2005 时,它仅接收 64 个线程中的 2 个,因此可能仅使用了机器 CPU 能力的 2/64(3.1%)。据报告称,对于相同的事实表,分区情况下的查询性能可能比未分区时在相同机器上运行相同查询时的性能要差 10 倍甚至更多。
    需要注意的是,SQL Server 2005 针对涉及单个分区的查询进行了专门优化。此时,查询处理器会分配所有可用线程来执行扫描。这种特殊优化为在多核机器上执行的单分区查询带来了显著的性能提高,对于那些查询涉及多个分区的客户而言,他们当然也期待获得这一提高。
    SQL Server 2008 引入了新的分区表并行处理 (PTP) 功能,通过更好地利用现有硬件的处理能力(而不管查询涉及多少个分区或者各个分区的相对大小如何),改进了分区情况下的查询性能。在包含分区事实表的典型数据仓库情形中,用户会发现并行计划的执行查询有了显著的改进,尤其是当可用处理器核数大于查询所涉及的分区数时。该新功能默认启用,无需任何调整或配置。
    假设我们有一个分布在四个分区中的事实表,它代表按销售日期组织的销售数据。 图 5 中的图表将有助于您形象地描述该示例。请注意,这里使用的并非针对整个日期范围的单个聚集索引(就像在非分区时一样),而是为事实表中每个分区的日期列设定一个聚集索引。现在假设查询 Q 汇总最近七天的销售数据。由于新的销售数据始终通过最后一个分区(即 P4)进入事实表,因此查询很可能会涉及到不同的分区,具体视其执行时间而定。如图表第一行所示,Q1 查询仅涉及单个分区,而 Q2 查询则涉及两个分区,因为执行时相关数据分布在 P3 和 P4 分区上。
    Figure 5  可用新 PTP 功能 (单击该图像获得较大视图)
    现在假设有八个可用线程。在 SQL Server 2005 上执行 Q1 和 Q2 可能产生一些意外的行为。SQL Server 2005 具有一个优化功能,如果优化器知道在编译时查询仅涉及一个分区,则会将该分区当作单个非分区表,并生成一个使用所有可用线程来访问表的计划。
    这样,涉及单个分区 (P3) 的 Q1 将生成由八个线程进行处理的计划(未显示)。对于涉及两个分区的 Q2,执行程序向每个分区分配一个线程,即使底层硬件还有其他可用线程。因此,Q2 仅利用了可用 CPU 能力中极小的一部分,并且很可能执行速度明显慢于 Q1。
    在 SQL Server 2008 上执行 Q1 和 Q2 时,可用硬件利用率更高、性能更好并且对于行为的预测能力也更强。对于 Q1,执行程序仍分配全部八个可用线程来处理 P2 中的数据(未显示)。同时,Q2 则会产生一个并行计划,其中的执行程序将以轮叫方式将所有可用线程分配给 P3 和 P4,因而产生图中最下面一行所示的效果,其中两个分区中的每个分区各获得四个线程。CPU 得以充分利用,且 Q1 和 Q2 的性能不相仲伯。
    通过该线程轮叫分配,如果处理器核数多于查询所涉及的分区数,查询的性能将更好。然而,遗憾地是,有时为分区分配线程并不象示例中这么简单。
    对于多核处理器机器上的分区表, 图 6 进一步显示了从 SQL Server 2005 到 SQL Server 2008 所产生的性能改进。该图表着重指出了分区表的扫描性能。对于这个在 64 核且 RAM 为 256GB 的系统上执行的特殊测试,我们将 121GB 的单个表分成 11 个分区,每个均为 11GB。对于该图中所示的测试组,我们使用堆文件组织以及冷缓冲区和热缓冲区启动。所有查询均对数据执行简单扫描。
    Figure 6  启用了新 PTP 功能的 SQL Server 的扫描性能 (单击该图像获得较大视图)
    y 轴显示了响应时间(秒),x 轴表示并行度 (DOP),它模拟分配给查询的线程数。如您所见,在冷启动和热启动两种情况下,响应时间持续减少,直到 DOP 达到 22。此时,I/O 系统对于冷启动情形变为满负荷状态。其原因是本示例中使用的查询受 I/O 限制。如果是受 CPU 限制的工作负载,该限制可能不存在,或者仅在 DOP 更高时才会发生。
    然而,代表热启动情形的曲线则随着 DOP 级别的增加,其响应时间仍在继续降低。对于 SQL Server 2005,在 DOP 11 附近,两条曲线都开始变平,因为在处理多个分区时,每个分区的线程数仅限于 1。
    必须指出的一点是,实际上,增加 DOP 所取得的响应时间提高永远不可能是线性的。预期的行为更类似于阶跃函数 — 它反映出查询必须等待最慢的子件。因此,例如,仅向扫描再添加一个线程并不会加快查询,除非所有其他扫描也获得了能加快速度的其他线程。
    对于其他各种硬件和文件配置,我们也通过其他试验测试了新的 PTP 行为。此时,在当 DOP 超过 1 线程/分区时,我们在吞吐量范围中观察到了类似行为。
    最后(但同样重要),SQL Server 2008 中的新 PTP 功能还改善了查询计划的可读性,并允许深入洞察具体工作负载的执行。例如,作为 PTP 功能的一部分,在 showplan XML 中表示并行和串行计划的方式已得到改进,并且编译时和运行时执行计划中提供的分区信息也已增强。

    数据压缩
    随着商业智能的日益普及,企业将越来越多的数据灌入到其数据仓库中进行分析。这使得所管理的数据量呈指数级增长。在 1995 年,Winter Corporation 的第一份数据库容量调查指出,当时全球最大的系统包含的数据量是 TB。十年之后,最大的数据库扩大了近 100 倍。更令人惊讶的是数据仓库的大小每两年即增至原来的三倍。要管理如此大的数据并为数据仓库查询提供可接受的性能,难度可想而知。这些查询通常都非常复杂(涉及多个联接和累计),并要访问大量数据。工作负载中的许多查询还都受 I/O 限制。
    本机数据压缩是这一问题的解决方案。SQL Server 2005 SP2 为小数和数字型数据引入了一个新的可变长度存储格式,即 vardecimal 存储格式。这一新存储格式可显著降低数据库的大小。而空间的节省又有助于在以下两方面改善受 I/O 限制查询的性能。首先,要读取的页面更少,其次,由于数据是以压缩格式存储在缓冲池中,因而提高了页面预期寿命(换句话说,它加大了在缓存中找到所请求页面的机率)。当然,由于需压缩和解压缩数据,从数据压缩获得的空间节省确实会产生一定的 CPU 开销。
    SQL Server 2008 基于 vardecimal 存储格式构建,提供以下两类压缩:ROW 压缩和 PAGE 压缩。通过用可变长度存储格式存储所有固定长度的数据类型,ROW 压缩扩展了 vardecimal 存储格式。
    固定长度数据类型示例包括:整数、字符和浮点数据类型。即使 SQL Server 将这些数据类型存储成可变长度格式,数据类型的语义仍保持不变(从应用程序角度看,数据类型仍为固定长度数据类型)。因此,既能从数据压缩受益,又无需对应用程序做任何更改。
    PAGE 压缩将给定页面上一行或多行的列数据冗余降至最小。它使用 LZ78 (Lempel-Ziv) 算法的专有实现方法,在页面上仅存储一次冗余数据,然后从多个列对其进行引用。请注意,在使用 PAGE 压缩时,实际也包括了 ROW 压缩。
    可针对表、索引或者分区表和索引的一个或多个分区启用 ROW 和 PAGE 压缩。因此,可极其灵活地选择要压缩的表、索引和分区,在空间节省和 CPU 影响之间取得巧妙的平衡。 图 7 显示了针对销售表(通过对齐索引分区)实现的这一平衡。
    Figure 7  具有不同压缩设置的分区表 (单击该图像获得较大视图)
    每个分区代表一个季度,其中“十月-十二月”为最后一个季度。假设头两个分区的访问频率不高,第三个分区为中等频率,最后一个分区的访问频率最高。此例中,一个可能的配置为前两个分区启用 PAGE 压缩,这样空间节省最大且对工作负载性能造成的影响最小,对第三个分区使用 ROW 压缩,最后一个分区不使用任何压缩。
    可使用 Alter Table 或 Alter Index 数据定义语言 (DDL) 语句来联机或脱机启用压缩。SQL Server 还提供用于估计空间节省量的存储过程。所获得的空间节省取决于压缩对象的数据分布和架构。
    许多客户数据库的测试结果均显示,大部分客户都可将其数据库的大小缩小 50–65%,并且受 I/O 限制的查询的性能得到了显著改善。然而,估计对受 CPU 限制查询的性能影响则有点棘手,具体取决于查询的复杂程度。在 SQL Server 中,仅当访问索引或表时才会发生解压缩开销。如果扫描运算符的相对 CPU 开销低于查询的整体 CPU 开销(数据仓库情形通常都是这样),您会发现对 CPU 利用率的影响少于 20-30%。

    分区对齐的索引视图
    在 SQL Server 2008 中,分区对齐的索引视图允许您更加有效地在关系数据仓库中创建和管理摘要汇总,并在之前无法有效使用它们的情形中使用它们。因而改进了查询性能。通常,您会有一个按日期分区的事实表。会针对该表定义索引视图(或摘要汇总)以加快查询速度。如果切换到新的表分区中,针对该分区表定义的分区对齐的索引视图的匹配分区也会相应切换,并且为自动切换。
    它是优于 SQL Server 2005 的一个重大改进,在 SQL Server 2005 中,必须丢弃针对分区表定义的所有索引视图,然后才能使用 ALTER TABLE SWITCH 操作来切换入或切换出分区。SQL Server 2008 中的分区对齐索引视图功能向您提供了针对大型分区表定义的索引视图的各项好处,而不必对整个分区表重新构建汇总。这些好处包括自动维护汇总以及索引视图匹配。

    分区级锁升级
    SQL Server 支持范围分区,即允许针对可管理性分区数据,或根据其使用模式对数据进行分组。因此,可按月度或季度等标准对销售数据进行分区。可将分区映射到自身的文件组,再将文件组映射到一组文件。它提供了两大好处。首先,可将分区作为独立的单元进行备份和恢复。第二,可根据使用模式或查询负载将文件组映射到慢速或快速 I/O 子系统。
    数据访问模式是这里非常有趣的特点。查询和 DML 操作可能仅需访问或处理分区的一个子集。举例而言,如果要分析 2004 年度的销售数据,仅需访问相关的分区,理想情况下,不应受到并发访问其他分区数据的查询的影响(系统资源除外)。在 SQL Server 2005 中,并发访问其他分区中的数据可能导致将表锁定,从而影响对其他分区的访问。
    为将该干扰降至最低,SQL Server 2008 引入了表级选项来控制分区级或表级的锁升级。默认情况下,启用表级的锁升级(如 SQL Server 2005)。但,您可覆盖表的锁升级策略。例如,可按以下所示设置锁升级:
    复制代码
    Alter table <mytable> set (LOCK_ESCALATION = AUTO)
    
    该命令指示 SQL Server 选择适合于表架构的锁升级粒度。如果表并未分区,则锁升级为表级。如果表已分区,则锁升级粒度为分区级。该选项还用作 SQL Server 反对表级锁粒度的提示。

    结束语
    SQL Server 2008 中提供了诸多有助于关系数据仓库决策支持查询提高性能的增强功能,本文仅做了简要阐述。但是,请记住,尽管决策支持查询的竞争性响应时间至关重要,仍存在其他关键要求,只是它们并不在本文讨论范围之内。
    与关系数据仓库相关的一些其他功能包括:
    • 支持 T-SQL 中的 MERGE 语法,以使用一个语句更新、删除或插入(维度)数据并浏览数据库。
    • 优化了 SQL Server 引擎的日志记录性能,从而提高了 ETL 效率。
    • 将设置进行了分组,以便于在 T-SQL 中编写汇总决策支持查询。
    • 对备份进行压缩,以降低完整备份和增量备份的 I/O 需求。
    • 对资源进行监管,以控制对不同工作负载的系统资源分配。
    请浏览 SQL Server 网页,以查看有关所有此类出色功能的详细信息,网址为 microsoft.com/sql
    展开全文
  • 数据仓库常见建模方法与大数据领域建模实例综述

    千次阅读 多人点赞 2021-05-01 14:01:51
    为什么要进行数据仓库建模? 随着DT时代互联网、智能设备等信息技术的发展,数据开始井喷式的增长,如何讲这些数据进行有序、有结构地分类组织存储是我们面临的一个挑战。 如果把数据看作图书馆里的书,我们希望看到...

    大家好,我是云祁,好久不见~

    今天来和大家聊聊数仓常见的一些建模方法和具体的实例演示,一起来看看吧。

    一、为什么需要数据建模?

    在开始今天的话题之前,我们不妨思考下,到底为什么需要进行数据建模?

    随着从IT时代到DT时代的跨越,数据开始出现爆发式的增长,这当中产生的价值也是不言而喻。如何将这些数据进行有序、有结构地分类组织存储,是我们所有数据从业者都要面临的一个挑战。

    如果把数据看作图书馆里的书,我们希望看到它们在书架上分门别类地放置,而不是乱糟糟的堆砌在一起。

    大数据的数仓建模正是通过建模的方法,更好的组织、存储数据,以便在性能、成本、效率和数据质量之间找到最佳平衡点,一般我们会从以下面四点考虑:

    • 性能:能够快速查询所需的数据,减少数据I/O的吞吐。
    • 成本:减少不必要的数据冗余,实现计算结果的复用,降低大数据系统中的存储成本和计算成本。
    • 效率:改善用使用数据的体验,提高使用效率。
    • 质量:改善数据统计口径的不一致性,减少数据计算错误的可能性,提供高质量的、一致的数据访问平台。

    因此,毋庸置疑,大数据系统、数据平台都需要数据模型方法来帮助更好的组织和存储数据,数据建模的工作,也正是围绕上述四个指标取得最佳的平衡而努力。

    二、从 OLTP 和 OLAP 系统的区别看模型方法论的选择

    OLTP系统通常面向的主要数据操作是随机读写,主要采用3NF的实体关系模型存储数据,从而在事务处理中解决数据的冗余和一致性问题。

    OLAP系统面向的主要数据操作是批量读写,事务处理中的一致性不是OLAP所关注的,其主要关注数据的整合,以及在一次性的复杂大数据查询和处理的性能,因此它需要采用不同的建模方法,例如维度建模。

    如果大家想进一步了解 OLAP系统,可以学习这篇文章: 关于OLAP数仓,这大概是史上最全面的总结!

    三、典型的数据仓库建模方法论

    数据仓库本质是从数据库衍生出来的,所以数据仓库的建模也是不断衍生发展的。

    从最早的借鉴关系型数据库理论的范式建模,到逐渐提出维度建模等等,越往后建模的要求越高,越需满足3NF、4NF等。但是对于数据仓库来说,目前主流还是维度建模,会夹杂着范式建模。

    数据仓库建模方法论可分为:E-R模型、维度模型、Data Vault模型、Anchor模型。

    3.1 E-R模型

    将事物抽象为“实体”、“属性”、“关系”来表示数据关联和事物描述,这种对数据的抽象建模通常被称为E-R实体关系模型。

    数据仓库之父 Bill Inmon 提出的建模方法,从全企业的高度设计一个3NF模型,用实体关系(Entity Relationship)模型来描述企业业务,满足3NF。

    数据仓库的3NF与OLTP系统中的3NF的区别在于,它是站在企业角度面向主题的抽象,而不是针对某个具体的业务流程。

    采用 E-R模型建设数据仓库模型的出发点是整合数据,对各个系统的数据以整个企业角度按主题进行相似的组合和合并,并进行一致性处理,为数据分析决策服务,但是并不能直接用于分析决策。

    作为一种标准的数据建模方案,它的实施周期非常长,一致性和扩展性比较好,能经得起时间的考验。但是随着企业数据的高速增长、复杂化,数仓如果全部使用E-R模型进行建模就显得越来越不适合现代化复杂、多变的业务组织,因此一般只有在数仓底层ODS、DWD会采用E-R关系模型进行设计。

    E-R建模步骤分为三个阶段:

    • 高层模型:一个高度抽象的模型,描述主要的主题以及主题间的关系,用于描述企业的业务总体概况。
    • 中层模型:在高层模型的基础上,细化主题的数据项。
    • 物理模型(底层模型):在中层模型的基础上,考虑物理存储,同时基于性能和平台特点进行物理属性的设计,也可能做一些表的合并、分区的设计等。


    E-R模型在实践中最典型的代表是 Teradata 公司基于金融业务发布的 FS-LDM (Financial Services Logical Data Model ),它通过对金融业务的高度抽象和总结,将金融业务划分为10大主题,企业基于此模型适当调整和扩展就能快速实施落地。

    3.2 维度模型

    维度模型是数据仓库领域 Ralph Kimball 大师倡导的,是数据仓库工程领域最流行的数仓建模经典。

    维度建模以分析决策的需求出发构建模型,构建的数据模型为分析需求服务,因此它重点解决用户如何更快速完成分析需求,同时还有较好的大规模复杂查询的响应性能。

    其中典型的代表就是使用星型模型,以及在一些特殊场景下使用的雪花模型。

    其设计主要分为以下几个步骤:

    1. 选择需要进行分析决策的业务过程。业务过程可以是单个业务事件,比如交易的支付、退款等;也可以是某个事件的状态,比如当前账户的余额;还有就是一系列相关业务事件组成的业务流程,具体需要我们分析的是某些事件发生的情况,还是当前状态,或是事件流转效率。
    2. 选择粒度。在事件分析中,我们要预判所有分析需要细分的程度,从而决定选择的粒度。粒度是维度的一个组合。
    3. 识别维表。选择好粒度之后,就需要基于这个粒度来设计维表,包括维度属性,用于分析时进行分组和筛选。
    4. 选择事实。确定分析需要衡量的指标。

    在 Ralph Kimball 提出对数据仓库维度建模,我们将数据仓库中的表划分为事实表、维度表两种类型。

    针对维度建模中事实表和维度表的设计,之前有详细介绍过,感兴趣的同学可以看:维度建模技术实践——深入事实表维度建模的灵魂所在——维度表设计

    在这里,我就以常见的电商场景为例:在一次购买的事件中,涉及主体包括客户、商品、商家,产生的可度量值会包括商品数量、金额、件数等。

    事实表根据粒度的角色划分不同,可分为事务事实表、周期快照事实表、累积快照事实表等。

    1. 事务事实表:用于承载事务数据,任何类型的事件都可以被理解为一种事务,比如商家在交易过程中的常见订单、买家付款,物流过程中的揽货、发货、签收,退款中的申请退款。
    2. 周期快照事实表:快照事实表以预定的间隔采样状态度量,比如自然年至今或者历史至今的下单金额、支付金额、支付买家数、支付商品件数等等状态度量。
    3. 累计快照事实表:数据不断更新,选取多业务过程日期。用来记录具有时间跨度的业务处理过程的整个过程的信息,每个生命周期一行,通常这类事实表比较少见。

    我们继续就上述的电商场景,聊聊在维表设计时需要关注的一些东西:

    1. 缓慢变化维度:例如会员表的手机号、地址、生日等属性。
    2. 退化维度 :订货单表的订单编号、物流表的物流编号等。
    3. 雪花维度:满足第三范式的维度关系结构。
    4. 非规范化扁平维度:商品维表众中产品、品牌、类目、品类等。
    5. 多层次维度:地区维度的省、市、区县,商品的类目层级。
    6. 角色维度:日期维度在物流中扮演发货日期、送货日期、收获日期等不同角色。

    接下来就是针对维度建模按照数据的组织类型,可以划分为星型模型、雪花模型、星座模型。

    1. 星型模型:星型模型主要是维表和事实表,以事实表为中心,所有维度直接关联在事实表上,呈星型分布。


    2. 雪花模型,在星型模型的基础上,维度表上又关联了其他维度表。这种模型维护成本高,性能方面会差一些。


    3. 星座模型,是对星型模型的扩展延伸,多张事实表共享维度表。实际上数仓模型建设后期,大部分维度建模都是星座模型。

    简单总结下就是:

    1. 星型模型和雪花模型主要区别就是对维度表的拆分。
    2. 对于雪花模型,维度表的涉及更加规范,一般符合3NF,有效降低数据冗余,维度表之间不会相互关联。
    3. 星型模型,一般采用降维的操作,反规范化,不符合3NF,通过利用冗余来避免模型过于复杂,提高易用性和分析效率,效率相对较高。

    3.3 DataVault 模型

    Data Vault 是 Dan Linstedt 发起创建的一种模型,它是 E-R 模型的衍生,其设计的出发点也是为了实现数据的整合,但不能直接用于数据分析决策。

    它强调建立一个可审计的基础数据层,也就是强调数据的历史性、可追溯性和原子性,而不要求对数据进行过度的一致性处理和整合。

    同时它基于主题概念将企业数据进行结构化组织,并引入了更进一步的范式处理来优化模型,以应对源系统变更的扩展性。 Data Vault 型由以下几部分组成:

    1. Hub - 中心表:是企业的核心业务实体,由实体 Key、数仓序列代理键、装载时间、数据来源组成,不包含非键值以外的业务数据属性本身。
    2. Link - 链接表:代表 Hub 之间的关系。这里与 ER 模型最大的区别是将关系作为一个独立的单元抽象,可以提升模型的扩展性。它可以直接描述 1:1、1:2和n:n的关系,而不需要做任何变更。它由 Hub的代理键、装载时间、数据来源组成。
    3. Satellite - 卫星表:数仓中数据的主要载体,包括对链接表、中心表的数据描述、数值度量等信息。

    Data Vault 模型比 E-R 模型更容易设计和产出,它的 ETL 加工可实现配置化。我们可以将 Hub 想象成人的骨架,那么 Link 就是连接骨架的韧带,而 SateIIite 就是骨架上面的血肉。

    3.4 Anchor 模型

    Anchor 对 Data Vault 模型做了进一步的规范化处理,它的核心思想是所有的扩展只是添加而不是修改,因此将模型规范到6NF,基本变成了 k-v 结构化模型。

    1. Anchors :类似于 Data Vault 的 Hub ,代表业务实体,且只有主键。
    2. Attributes :功能类似于 Data Vault 的 Satellite,但是它更加规范化,将其全部 k-v 结构化, 一个表只有一个 Anchors 的属性描述。
    3. Ties :就是 Anchors 之间的关系,单独用表来描述,类似于 Data Vault 的 Link ,可以提升整体模型关系的扩展能力。
    4. Knots :代表那些可能会在 Anchors 中公用的属性的提炼,比如性别、状态等这种枚举类型且被公用的属性。

    由于过度规范化,使用中牵涉到太多的Join操作,这里我们就仅作了解。

    四、总结

    以上为四种基本的建模方法,目前主流建模方法为: E-R模型、维度模型。

    E-R模型通常用于OLTP数据库建模,应用到构建数仓时就更偏向于数据整合,站在企业整体考虑,将各个系统的数据按相似性一致性、合并处理,为数据分析、决策服务,但并不便于直接用来支持分析。

    维度建模是面向分析场景而生,针对分析场景构建数仓模型;重点关注快速、灵活的解决分析需求,同时能够提供大规模数据的快速响应性能。针对性强,主要应用于数据仓库构建和OLAP引擎低层数据模型。

    数据仓库模型的设计是灵活的,不会局限于某一种模型,需要以实际的需求场景为导向,需要兼顾灵活性、可扩展性以及技术可靠性及实现成本。

    我是「云祁」,一枚热爱技术、会写诗的大数据开发猿,欢迎大家关注呀!

    Respect ~

    展开全文
  • 数据仓库构建实施方法及步骤

    千次阅读 2013-10-25 10:58:30
    数据仓库是面向主题的、集成的、不可更新的、随时间的变化而不断变化的,这些特点决定了数据仓库的系统设计不能采用同开发传统的OLTP数据库一样的设计方法。 数据仓库系统的原始需求不明确,且 不断变化与增加,...

    数据仓库是面向主题的、集成的、不可更新的、随时间的变化而不断变化的,这些特点决定了数据仓库的系统设计不能采用同开发传统的OLTP数据库一样的设计方法。

    数据仓库系统的原始需求不明确,且 不断变化与增加,开发者最初不能确切了解到用户的明确而详细的需求,用户所能提供的无非是需求的大的方向以及部分需求,更不能较准确地预见到以后的需求。因此,采用原型法来进行数据仓库的开发是比较合适的,因为原型法的思想是从构建系统的简单的基本框架着手,不断丰富与完善整个系统。但是,数据仓库的设计开发又不同于一般意义上的原型法,数据仓库的设计是数据驱动的。这是因为数据仓库是在现存数据库系统基础上进行开发,它着眼于有效地抽取、综合、集成和挖掘已有数据库的数据资源,服务于企业高层领导管理决策分析的需要。但需要说明的是,数据仓库系统开发是一个经过不断循环、反馈而使系统不断增长与完善的过程,这也是原型法区别于系统生命周期法的主要特点。因此,在数据仓库的开发的整个过程中,自始至终要求决策人员和开发者的共同参与和密切协作,要求保持灵活的头脑,不做或尽量少做无效工作或重复工作。

    数据仓库的设计大体上可以分为以下几个步骤:

    概念模型设计; 
    技术准备工作; 
    逻辑模型设计;
    物理模型设计; 
    数据仓库生成; 
    数据仓库运行与维护。 
     
    下面我们六个主要设计步骤为主线,介绍在各个设计步骤中设计的基本内容。

    第一节  概念模型设计

    进行概念模型设计所要完成的工作是:

    <1>界定系统边界   
    <2>确定主要的主题域及其内容

    概念模型设计的成果是,在原有的数据库的基础上建立了一个较为稳固的概念模型。因为数据仓库是对原有数据库系统中的数据进行集成和重组而形成的数据集合,所以数据仓库的概念模型设计,首先要对原有数据库系统加以分析理解,看在原有的数据库系统中“有什么”、“怎样组织的”和“如何分布的”等,然后再来考虑应当如何建立数据仓库系统的概念模型。一方面,通过原有的数据库的设计文档以及在数据字典中的数据库关系模式,可以对企业现有的数据库中的内容有一个完整而清晰的认识;另一方面,数据仓库的概念模型是面向企业全局建立的,它为集成来自各个面向应用的数据库的数据提供了统一的概念视图。 
    概念模型的设计是在较高的抽象层次上的设计,因此建立概念模型时不用考虑具体技术条件的限制。

    1 界定系统的边界

    数据仓库是面向决策分析的数据库,我们无法在数据仓库设计的最初就得到详细而明确的需求,但是一些基本的方向性的需求还是摆在了设计人员的面前:

    ·要做的决策类型有哪些? 
    ·决策者感兴趣的是什么问题? 
    ·这些问题需要什么样的信息? 
    ·要得到这些信息需要包含原有数据库系统的哪些部分的数据?

    这样,我们可以划定一个当前的大致的系统边界,集中精力进行最需要的部分的开发。因而,从某种意义上讲,界定系统边界的工作也可以看作是数据仓库系统设计的需求分析,因为它将决策者的数据分析的需求用系统边界的定义形式反映出来。

    2 确定主要的主题域 
     
    在这一步中,要确定系统所包含的主题域,然后对每个主题域的内容进行较明确的描述,描述的内容包括:

    ·  主题域的公共码键; 
    ·  主题域之间的联系; 
    ·  充分代表主题的属性组。   
      
    第二节  技术准备工作

    这一阶段的工作包括:技术评估,技术环境准备。 
    这一阶段的成果是:技术评估报告、软硬件配置方案、系统(软、硬件)总体设计方案。管理数据仓库的技术要求与管理操作型环境中的数据与处理的技术要求区别很大,两者所考虑的方面也不同。我们之所以在一般情况下总是将分析型数据与操作型数据分离开来,将分析型数据单独集中存放,也就是用数据仓库来存放,技术要求上的差异是一个重要原因。

    1技术评估

    进行技术评估,就是确定数据仓库的各项性能指标。一般情况下,需要在这一步里确定的性能指标包括:

    ·管理大数据量数据的能力; 
    ·进行灵活数据存取的能力; 
    ·根据数据模型重组数据的能力; 
    ·透明的数据发送和接收能力; 
    ·周期性成批装载数据的能力; 
    ·可设定完成时间的作业管理能力。

    2 技术环境准备

    一旦数据仓库的体系化结构的模型大体建好后,下一步的工作就是确定我们应该怎样来装配这个体系化结构模型,主要是确定对软硬件配置的要求;我们主要考虑相关的问题:

    ·预期在数据仓库上分析处理的数据量有多大? 
    ·如何减少或减轻竞争性存取程序的冲突? 
    ·数据仓库的数据量有多大? 
    ·进出数据仓库的数据通信量有多大?等等。

    根据这些考虑,我们就可以确定各项软硬件的配备要求,并且在这一步工作结束时各项技术准备工作应已就绪,可以装载数据了。这些配备有:

    ·直接存取设备(DASD); 
    ·网络; 
    ·管理直接存取设备(DASD)的操作系统; 
    ·进出数据仓库的界面(主要是数据查询和分析工具); 
     
    管理数据仓库的软件,目前即选用数据库管理系统及有关的选件,购买的DBMS产品不能满足管理数据仓库需要的,还应考虑自己或软件集成商开发有关模块等等。

    第三节  逻辑模型设计

    在这一步里进行的工作主要有:

    分析主题域,确定当前要装载的主题; 
    确定粒度层次划分; 
    确定数据分割策略; 
    关系模式定义; 
    记录系统定义

    逻辑模型设计的成果是,对每个当前要装载的主题的逻辑实现进行定义,并将相关内容记录在数据仓库的元数据中,包括:

    ·适当的粒度划分; 
    ·合理的数据分割策略; 
    ·适当的表划分; 
    ·定义合适的数据来源等。

    1 分析主题域

    在概念模型设计中,我们确定了几个基本的主题域,但是,数据仓库的设计方法是 一个逐步求精的过程,在进行设计时,一般是一次一个主题或一次若干个主题地逐步完成的。所以,我们必须对概念模型设计步骤中确定的几个基本主题域进行分析,并选择首先要实施的主题域。选择第一个主题域所要考虑的是它要足够大,以便使得该主题域能建设成为一个可应用的系统;它还要足够小,以便于开发和较快地实施。如果所选择的主题域很大并且很复杂,我们甚至可以针对它的一个有意义的子集来进行开发。在每一次的反馈过程中,都要进行主题域的分析。

    2 粒度层次划分

    数据仓库逻辑设计中要解决的一个重要问题是决定数据仓库的粒度划分层次,粒度层次划分适当与否直接影响到数据仓库中的数据量和所适合的查询类型。确定数据仓库的粒度划分,可以使用在粒度划分一节中介绍的方法,通过估算数据行数和所需的DASD数,来确定是采用单一粒度还是多重粒度,以及粒度划分的层次。

    3 确定数据分割策略

    在这一步里,要选择适当的数据分割的标准,一般要考虑以下几方面因素:数据量(而非记录行数)、数据分析处理的实际情况、简单易行以及粒度划分策略等。数据量的大小是决定是否进行数据分割和如何分割的主要因素;数据分析处理的要求是选择数据分割标准的一个主要依据,因为数据分割是跟数据分析处理的对象紧密联系的;我们还要考虑到所选择的数据分割标准应是自然的、易于实施的:同时也要考虑数据分割的标准与粒度划分层次是适应的。  

    4 关系模式定义

    数据仓库的每个主题都是由多个表来实现的,这些表之间依靠主题的公共码键联系在一起,形成一个完整的主题。在概念模型设计时,我们就确定了数据仓库的基本主题,并对每个主题的公共码键、基本内容等做了描述在这一步里,我们将要对选定的当前实施的主题进行模式划分,形成多个表,并确定各个表的关系模式。 
       
    第四节  物理模型设计

    这一步所做的工作是确定数据的存储结构,确定索引策略,确定数据存放位置,确定存储分配。 
    确定数据仓库实现的物理模型,要求设计人员必须做到以下几方面:

    要全面了解所选用的数据库管理系统,特别是存储结构和存取方法。 
    了解数据环境、数据的使用频度、使用方式、数据规模以及响应时间要求等,这些是对时间和空间效率进行平衡和优化的重要依据。 
    了解外部存储设备的特性,如分块原则,块大小的规定,设备的I/O特性等。

    1 确定数据的存储结构

    一个数据库管理系统往往都提供多种存储结构供设计人员选用,不同的存储结构有不同的实现方式,各有各的适用范围和优缺点,设计人员在选择合适的存储结构时应该权衡三个方面的主要因素:存取时间、存储空间利用率和维护代价。

    2 确定索引策略

    数据仓库的数据量很大,因而需要对数据的存取路径进行仔细的设计和选择。由于数据仓库的数据都是不常更新的,因而可以设计多种多样的索引结构来提高数据存取效率。

    在数据仓库中,设计人员可以考虑对各个数据存储建立专用的、复杂的索引,以获得最高的存取效率,因为在数据仓库中的数据是不常更新的,也就是说每个数据存储是稳定的,因而虽然建立专用的、复杂的索引有一定的代价,但一旦建立就几乎不需维护索引的代价。 
     
    3 确定数据存放位置

    我们说过,同一个主题的数据并不要求存放在相同的介质上。在物理设计时,我们常常要按数据的重要程度、使用频率以及对响应时间的要求进行分类,并将不同类的数据分别存储在不同的存储设备中。重要程度高、经常存取并对响应时间要求高的数据就存放在高速存储设备上,如硬盘;存取频率低或对存取响应时间要求低的数据则可以放在低速存储设备上,如磁盘或磁带。

    数据存放位置的确定还要考虑到其它一些方法,如:决定是否进行合并表;是否对一些经常性的应用建立数据序列;对常用的、不常修改的表或属性是否冗余存储。如果采用了这些技术,就要记入元数据。

    4 确定存储分配

    许多数据库管理系统提供了一些存储分配的参数供设计者进行物理优化处理,如:块的尺寸、缓冲区的大小和个数等等,它们都要在物理设计时确定。这同创建数据库系统时的考虑是一样的。

    第五节  数据仓库的生成

    在这一步里所要做的工作是接口编程,数据装入。

    这一步工作的成果是,数据已经装入到数据仓库中,可以在其上建立数据仓库的应用,即DSS应用。

    1 设计接口

    将操作型环境下的数据装载进入数据仓库环境,需要在两个不同环境的记录系统之间建立一个接口。乍一看,建立和设计这个接口,似乎只要编制一个抽取程序就可以了,事实上,在这一阶段的工作中,的确对数据进行了抽取,但抽取并不是全部的工作,这一接口还应具有以下的功能:

    ·从面向应用和操作的环境生成完整的数据; 
    ·数据的基于时间的转换; 
    ·数据的凝聚; 
    ·对现有记录系统的有效扫描,以便以后进行追加。

    当然,考虑这些因素的同时,还要考虑到物理设计的一些因素和技术条件限制,根据这些内容,严格地制定规格说明,然后根据规格说明,进行接口编程。从操作型环境到数据仓库环境的数据接口编程的过程和一般的编程过程并无区别,它也包括伪码开发、编码、编译、检错、测试等步骤。

    在接口编程中,要注意:

    ·保持高效性,这也是一般的编程所要求的; 
    ·要保存完整的文档记录; 
    ·要灵活,易于改动; 
    ·要能完整、准确地完成从操作型环境到数据仓库环境的数据抽取、转换与集成。

    2 数据装入

    在这一步里所进行的就是运行接口程序,将数据装入到数据仓库中。主要的工作是: 
      
    ·确定数据装入的次序; 
    ·清除无效或错误数据; 
    ·数据“老化” ; 
    ·数据粒度管理; 
    ·数据刷新等。

    最初只使用一部分数据来生成第一个主题域,使得设计人员能够轻易且迅速地对已做工作进行调整,而且能够尽早地提交到下一步骤,即数据仓库的使用和维护。这样既可以在经济上最快地得到回报,又能够通过最终用户的使用、尽早发现一些问题并提出新的需求,然后反馈给设计人员,设计人员继续对系统改进、扩展。
      
    第六节  数据仓库的使用和维护

    在这一步中所要做的工作有建立DSS应用,即使用数据仓库理解需求,调整和完善系统,维护数据仓库。建立企业的体系化环境,不仅包括建立起操作型和分析型的数据环境,还应包括在这一数据环境中建立起企业的各种应用。数据仓库装入数据之后,下一步工作是:一方面,使用数据仓库中的数据服务于决策分析的目的,也就是在数据仓库中建立起DSS应用;另一方面,根据用户使用情况和反馈来的新的需求,开发人员进一步完善系统,并管理数据仓库的一些日常活动,如刷新数据仓库的当前详细数据、将过时的数据转化成历史数据、清除不再使用的数据、调整粒度级别等。我们把这一步骤称为数据仓库的使用与维护。

    1 建立DSS应用

    使用数据仓库,即开发DSS应用,与在操作型环境中的应用开发有着本质区别,开发DSS应用不同于联机事务处理应用开发的显著特点在于:

    ·DSS应用开发是从数据出发的; 
    ·DSS应用的需求不能在开发初期明确了解; 
    ·DSS应用开发是一个不断循环的过程,是启发式的开发。

    DSS应用主要可分为两类:例行分析处理和启发式分析处理。例行分析处理是指那些重复进行的分析处理,它通常是属于部门级的应用,如部门统计分析,报表分析等等;而个人级的分析应用经常是随机性很大的,企业经营者受到某种信息启发而进行的一些即席的分析处理,所以我们称之为启发式的分析处理。

    DSS应用开发的大致步骤如下:

    步骤l--确定所需的数据。为满足DSS应用的要求,我们必须从数据仓库中确定一个可能用到的数据范围。这是一个试探的过程。 
    步骤2--编程抽取数据。根据上面得到的数据范围,编写一个抽取程序来获得这些数据。为适应分析需求多变的特点,要求所编写的抽取程序应该通用,易于修改。 
    步骤3--合并数据。如果有多个数据抽取源,要将抽取来的数据进行合并、提炼,使数据符合分析处理的要求。 
    步骤4--分析数据。在上步准备好的数据基础上进行分析处理,并看所得的结果是否满足了原始的要求,如果不能满足,则返回步骤1,开始新的一次循环,否则就准备最终分析结果报告。 
    步骤5--回答问题。生成最终分析结果报告。-般情况下,最终的分析结果报告是在许多次的循环后得到的,因为一次分析处理很少是在一次循环后就完成的。 
    步骤6--例行化、一次分析处理的最后、我们要决定是否将在上面已经建立的分析处理例行化。如果建立的分析处理是重复进行的部门级的DSS应用,那么最好是将它例行化,这样在进行下一次同样的分析处理时,不必再重复上述六步的循环过程。而且,不断地积累这种例行处理,形成一个集合,我们就可以通过组合这些已有的处理来生成新的一个较大的复杂处理,或完成一个复杂处理的一部分。

    2 理解需求,改善和完善系统,维护数据仓库

    数据仓库的开发是逐步完善的原型法的开发方法,它要求:要尽快地让系统运行起来,尽早产生效益;要在系统运行或使用中,不断地理解需求,改善系统;不断地考虑新的需求,完善系统。

    维护数据仓库的工作主要是管理日常数据装入的工作,包括刷新数据仓库的当前详细数据,将过时的数据转化成历史数据.清除不再使用的数据,管理元数据,等等;另外,如何利用接口定期从操作型环境向数据仓库追加数据,确定数据仓库的数据刷新频率,等等。

    展开全文
  • 数据仓库架构和建设方法

    千次阅读 2019-04-18 10:32:29
    1.数据仓库概要 1.1.数据仓库起因      在建设数据仓库之前,数据散落在企业各部门应用的数据存储中,它们之间有着复杂的业务连接关系,从整体上看就如一张巨大的蜘蛛网:结构上错综复杂,却又...
  • 数据仓库建模方法与建模案例

    千次阅读 2020-07-28 09:51:57
    1. 数据仓库建模的目的是什么?2. 常见的数据建模方法有哪些?3. 常见的建模工具有哪些?1.数据仓库建模的目的?为什么要进行数据仓库建模?大数据的数仓建模是通过建模的方法更好的组织、存储数据,以便在 性能、...
  • 我们在之前的文章中已经学习了数据仓库hive,如果要对数据仓库进行交互查询,则需要交互查询的引擎用于提高查询效率。本章介绍presto。 简介 Presto是一个开源的分布式SQL查询引擎,适用于实时交互式分析查询,...
  • 原创:数据仓库架构和建设方法

    千次阅读 2017-06-23 11:27:10
    1.数据仓库概要 1.1.数据仓库起因  在建设数据仓库之前,数据散落在企业各部门应用的数据存储中,它们之间有着复杂的业务连接关系,从整体上看就如一张巨大的蜘蛛网:结构上错综复杂,却又四通八达。...④缺乏...
  • 数据仓库建设的方法 绝对的好文

    万次阅读 2016-09-12 17:22:10
    在上一期的专栏文章中,我们曾经提到:数据分析系统的总体架构分为四个部分 —— 源系统、数据仓库、多维数据库、客户端(图一:pic1.bmp) 其中,数据仓库(DW)起到了数据大集中的作用。通过数据抽取,把数据...
  • Mybatis的插件 PageHelper 分页查询使用方法

    万次阅读 多人点赞 2017-08-30 18:54:16
    Mybatis的一个插件,...在github上仓库地址为:Mybatis-PageHelper 它支持基本主流与常用的数据库,这可以在它的文档上看到。这里记录一下使用的基本方法 0.查看文档与使用准备 开发文档有中文文档也有英文文档
  • 问题导读: 1、如何理解IBM 的 TDWM 概念...5、数据仓库建模方法都有哪些?  所谓水无定势,兵无常法。不同的行业,有不同行业的特点,因此,从业务角度看,其相应的数据模型是千差万别的。目前业界较为主流的是
  • 但是对于有查询条件的时候,between就比较复杂了,因为查询条件出来的数据 列的值的范围是多少 我们 并不知道。 所以 需要另外的一种 分页逻辑,数值类型int类型的自增id。 只要id的排序顺序一定,无论 ...
  • 数据仓库

    千次阅读 多人点赞 2018-04-14 17:35:47
    数据仓库 1 . 数据仓库的基本概念 数据仓库,英文名称为 Data Warehouse,可简写为 DW 或 DWH。数据仓库的目的是构建面向分析的集成化数据环境,为企业提供决策支持(DecisionSupport)。它出于分析性报告和...
  • 美团数据仓库的演进

    千次阅读 2015-10-30 17:14:50
    数据仓库
  • 数据仓库基本知识

    万次阅读 多人点赞 2017-10-31 17:35:04
    数据仓库是什么 根据统计,每个企业的数据量每2~3年时间就会成倍增长,这些数据蕴含着巨大的商业价值,而企业所关注的通常只占在总数据量的2%~4%左右。 因此,企业仍然没有最大化地利用已存在的数据资源,以...
  • 搜索引擎的查询方法

    万次阅读 2010-11-18 22:03:00
    从总体来看,搜索引擎的查询方法有两大类:一类是基于关键词的基本查询方法,它是最常见和最为有效的一种方法;另一类是基于Web目录的分类查询方法,虽然功能略逊于前者,但是也有自己的优势,而且这两种方法还可以...
  • 数据仓库方案选型

    万次阅读 2017-11-07 10:43:20
    总体框架通常采用三层体系结构:前端工具(顶层)-OLAP服务器(中间层)-数据仓库服务器(底层)。底层的数据仓库服务器通常是一个关系数据库系统(各种表关联的sql统计会更方便一些,非关系型数据库目前在这方面还是...
  • 数据仓库设计

    千次阅读 2019-03-28 14:11:41
    如果在有限的资源动态的数据情况下,向前可历史追溯,向后对不断增加的报表实现兼容,这就需要一套科学的数据管理方法。数据仓库是一门数据管理的科学,数据仓库的核心就是计算、存储和维护之间的博弈。 标...
  • 数据仓库笔记

    千次阅读 2019-11-24 18:53:26
    数据仓库要点 第二章 数据仓库 1、B树索引 考题:为何B树等在数据库中广泛使用的索引技术无法直接被引入数据仓库? 1、B树要求属性必须具有许多不同的值,比如身份证号这种取值字段,取值范围很广,几乎没有重复。 2...
  • 数据仓库建设

    万次阅读 2018-07-18 23:31:52
    1.数据仓库概要 1.1.数据仓库起因  在建设数据仓库之前,数据散落在企业各部门应用的数据存储中,它们之间有着复杂的业务连接关系,从整体上看就如一张巨大的蜘蛛网:结构上错综复杂,却又四通八达。在企业级数据...
  • 1、什么是维度表? 2、维度表设计原则 (1)维度属性尽量丰富,为数据使用打下基础 (2)给出详实的、富有意义的文字描述 (3)区分数值型属性和事实 ...(4)沉淀出通用的维度属性,为建立一致性...3、维度表设计方法
  • 数据仓库面试题

    万次阅读 多人点赞 2020-07-20 12:49:16
    文章目录数据仓库的定义?数据仓库和数据库的区别?如何构建数据仓库?什么是数据中台?数据中台、数据仓库、大数据平台的关键区别是什么?基础能力上的区别业务能力上的区别大数据的一些相关系统?如何建设数据中台...
  • 数据仓库与联机分析处理笔记

    千次阅读 2017-11-21 00:10:42
    本文为《数据挖掘:概念与技术》中“数据仓库与联机分析处理”的阅读笔记。4. 数据仓库与联机分析处理 ETL:用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。 数据仓库...
  • Hive离线数据仓库

    千次阅读 2018-11-22 20:51:18
    Hive是基于Hadoop的数据仓库工具,提供了一系列的工具,可以用来进行数据提取、转化、加载,是一种可以存储、查询和分析存储在Hadoop中的大规模数据机制。它可以将结构化的数据文件映射成一张表,并提供完成的sql...
  • 【Springboot学习笔记】SpringBoot+Mybatis+Thymeleaf+Layui数据表单从零开始实现按条件模糊分页查询的方法目录1.搭建环境1.1直接从网上下载SpringBoot样例网站程序1.2使用IDEA新建SpringBoot样例网站运行SpringBoot...
  • 如何搭建数据仓库

    千次阅读 2020-12-10 10:31:30
    其实很多企业做数据仓库的时候,都忽略了数仓与BI、数据库的差异,只去搞底层数据,不去做数据服务和应用,其实就是把数据仓库给狭义化了。其实数据仓库可以看成是BI的基础版本、数据库的升级版本,我们可以把公司里...
  • 数据仓库设计思路

    千次阅读 2012-07-11 22:35:12
    数据仓库建模方法:实体关系模型、维度建模。数据仓库逻辑模型设计:确定主题域->粒度层次划分->确定数据分割策略->关系模式确定。数据仓库物理模型设计:确定数据存储结构->确定数据仓库索引策略->对表归并优化存储...
  • 数据仓库的含义

    千次阅读 多人点赞 2019-01-31 21:46:12
    文章目录数据仓库什么是数据仓库应用场景特点数据仓库是面向主题的什么是主题?数据仓库的数据是集成的数据仓库的数据是不可更新的数据仓库的数据是随时间...元数据介绍1)元数据的定义2)元数据的存储方式3)元...
  • 数据仓库(二)之维度建模篇

    万次阅读 多人点赞 2018-09-12 22:29:28
    维度建模是一种将数据结构化的逻辑设计方法,它将客观世界划分为度量和上下文。度量是常常是以数值形式出现,事实周围有上下文包围着,这种上下文被直观地分成独立的逻辑块,称之为维度。它与实体-关系建模有很大的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 58,600
精华内容 23,440
关键字:

仓库查数方法