数据仓库解决方案.pptx

1、数据数据仓库仓库数据仓库多维模型多维分析报表报表分发数据级权限管理数据自动加载与处理移动端分析主数据管理与维护数据质量管理数据订阅系统管理财务ERPCRM表格模型数据处理数据存储数据集市数据集市数据集市语义模型数据分析仪表盘平衡计分卡PowerPivotStageODS数据仓库数据集市 Stage库结果与业务系统库结果非常类似 Stage库内存放增量数据 Stage库内的数据在处理过程中将会被清空，故不做实际的数据存储 使用数据批量加载技术 不进行任何的转换、处理和聚合操作，对于数据源为非SQL Server数据库的情况，字段名称保持一致，字段类型及长度转换成SQL Server数据库中相应的

2、类型。Stage库内的所有表需增加InsertDate_Stage字段 SharePoint List缓存表 用于临时保存保存来自SharePoint List的数据，命名 List_+SharePoint List英文名称。例如：List_Holiday 其他数据源缓存表 其他一些数据源，命名可根据实际情况自定义，例如：来自Excel的数据：Xls_+名称、Xlsx_+名称；来自csv文件的数据：Csv_+名称 处于数据仓库与数据源之间，成为数据源的数据备份 降低数据仓库与数据源之间的耦合度，提高灵活性 降低ETL对数据源的影响，减少数据源变更对数据仓库的影响 ODS内存放全量数据 ODS库

3、内的所有表需增加InsertDate_ODS字段和UpdateDate_ODS 数据仓库是面向主题的、集成的、稳定的、随时间不断变化的数据库系统。数据仓库为企业可用数据的存储，是企业众多应用的数据，为企业未来数据中心的原型 数据仓库设计与企业的业务场景息息相关，与用户的需求间接相关 数据仓库具有较强的稳定性，数据仓库的变更所产生的影响是具大的 数据集市是数据仓库的子集 数据集市是面向应用的，为各种应用的数据源 数据集市所包含的元素取决于用户的需求 数据集市尽可能采用“多而小”的设计 没有数据集市的BI架构必将出现性能和灵活性问题 主数据是企业的核心元素数据，包括企业组织架构、客户、产品等，主数

4、据管理系统将实现对企业主数据的统计定义与管理。主数据管理系统将与企业中的业务系统和数据仓库交互，从而确保版本唯一性。主数据管理将增强系统的灵活性、可扩展性及定制化，同时也使用户与系统具有更强的交互性 主数据管理是企业数据中心的关键组成部分，没有主数据管理的系统是不长久的。在设计仓库数据库之初在设计仓库数据库之初把用户的分析需求纳入把用户的分析需求纳入考虑范围是十分有必要考虑范围是十分有必要的。同时，数据仓库的的。同时，数据仓库的构建必需基于业务数据构建必需基于业务数据库，业务数据源的结构库，业务数据源的结构也是不得不考虑的问题。也是不得不考虑的问题。因此在设计数据仓库的因此在设计数据仓库的时候

5、，应该坚持用户驱时候，应该坚持用户驱动与数据驱动相结合的动与数据驱动相结合的设计理念。设计理念。数据仓库模型设计主要分数据仓库模型设计主要分三个阶段：三个阶段：1、概念设计、概念设计2、逻辑设计、逻辑设计3、详细设计、详细设计并分别产生三类设计模型并分别产生三类设计模型1、主题域模型、主题域模型2、业务数据模型、业务数据模型3、物理模型、物理模型11数据仓库模型设计采用迭代式开发，这一点也符合数据仓库数据仓库模型设计采用迭代式开发，这一点也符合数据仓库系统迭代开发的特点。它具有较好的灵活性和易变性，适应系统迭代开发的特点。它具有较好的灵活性和易变性，适应于主题不明确或不确定的需求。于主题不明确

6、或不确定的需求。数据仓库是面向主题来组织数据仓库是面向主题来组织数据，一个数据仓库有若干个主题，数据，一个数据仓库有若干个主题，而每个主题又有一个数据集合体做而每个主题又有一个数据集合体做支撑，这个数据集合称为主题域。支撑，这个数据集合称为主题域。概念设计的中心工作是在需概念设计的中心工作是在需求分析基础上设计的主题域模型。求分析基础上设计的主题域模型。主体域模型是客观到主观之间的桥主体域模型是客观到主观之间的桥梁，是与硬件环境、软件选择无关梁，是与硬件环境、软件选择无关的数据抽象模型，是为下一步建立的数据抽象模型，是为下一步建立业务数据模型、物理模型服务的概业务数据模型、物理模型服务的概念性

7、工具。念性工具。主题域具有两个特性主题域具有两个特性1、独立性，即主题域具有明确的边界与独立的内涵，虽然主题间可以有交、独立性，即主题域具有明确的边界与独立的内涵，虽然主题间可以有交叉，但不影响其独立性。叉，但不影响其独立性。2、完备性，即每个主题的分析要求所需的数据均应能在主题域中得到。采、完备性，即每个主题的分析要求所需的数据均应能在主题域中得到。采用概念数据模型设计就是要设计主题域的数据结构。用概念数据模型设计就是要设计主题域的数据结构。逻辑设计设计到的知识点包括：逻辑设计设计到的知识点包括：业务数据模型设计的建模对象应包含实体、属性、关键字业务数据模型设计的建模对象应包含实体、属性、关

8、键字和联系。和联系。业务数据模型设计应该遵守规范化准则：即第三范式设计业务数据模型设计应该遵守规范化准则：即第三范式设计准则。准则。业务数据模型的业务数据模型的ER图表示法。图表示法。逻辑设计的中心工作是逻辑设计的中心工作是设计业务数据模型，业务数设计业务数据模型，业务数据模型是用具体的软件结构据模型是用具体的软件结构来实现概念数据模型。来实现概念数据模型。目前数据仓库一般是建目前数据仓库一般是建立在关系数据库基础上，因立在关系数据库基础上，因此数据仓库的设计中采用的此数据仓库的设计中采用的业务数据模型就是关系模型。业务数据模型就是关系模型。实体 我们把客观存在并且可以相互区别的事物称为实体。

9、实体可以是实际事物，也可以是抽象事件。属性 描述实体的特性称为属性。关键字 如果某个属性值能唯一地标识出实体集中的每一个实体，可以选作关键字。联系 实体集之间的对应关系称为联系，它反映现实世界事物之间的相互关联。联系分为两种，一种是实体内部各属性之间的联系。另一种是实体之间的联系。实体分为事实实体和维度实体 事实实体 事实实体往往是某个业务动作产生的结果，如合同、订单、审批结果等。事实实体中包括要进行分析的度量，如合同金额、审批数量等。维度实体 维度实体往往是某个业务动作的相关对象，如销售人员、产品等。ER模型（实体联系模型）简称ER图。它是描述概念世界，建立概念模型的实用工具。ER图包括三个

10、要素：实体、属性、实体之间的联系 联系归结为三种类型：一对一联系（1:1）设A、B为两个实体集。若A中的每个实体至多和B中的一个实体有联系，反过来，B中的每个实体至多和A中的一个实体有联系，称A对B或B对A是1:1联系。一对多联系（1:n）如果A实体集中的每个实体可以和B中的几个实体有联系，而B中的每个实体至我和A中的一个实体有联系，那么A对B属于1:n联系。多对多联系（m:n）若实体集A中的每个实体可与和B中的多个实体有联系，反过来，B中的每个实体也可以与A中的多个实体有联系，称A对B或B对A是m:n联系。通过实体与关系描述业务场景 实体与属性的相互转换 实体关系需通过数据进行验证 物理模型

11、是从业务数据模物理模型是从业务数据模型创建而来的，建立物理型创建而来的，建立物理模型通过扩展业务数据模模型通过扩展业务数据模型，使模型中包含关键字型，使模型中包含关键字和物理特性。和物理特性。物理模型设计包括：物理模型设计包括：设计存储结构创建实体 设计索引策略创建索引 设计存储策略创建分区创建物理实体创建物理实体 表表 视图视图 约束约束 所有数据库对象名称均使用26个大写英文字母、下划线或数字来命名，并不得以下划线开头。在任何地方不得使用拼音或拼音的首字母命名。数据仓库分为三层，Stage、ODS和DW，命名规则为 项目名称_+Stage、项目名称_+ODS、项目名称_+DW。每个表代表唯

12、一的实体，表也可以包含两个实体的组合，但两个表不可代表相同的实体。各实体之间需要有严格的关联关系，必须创建主外键关联。每个实体内所存储记录的粒度必须于实体一致，例如订单表内的记录必须代表每一个订单。主键：PK_，整型自增 外键：FK_ 排序键：Sort_ 字符类型：nvarchar 量度类型：float、numeric 视图 尽量少用或不用视图，以避免意想不到的逻辑陷阱 约束 尽量使用约束，以确保数据的完整性。正确的数据所带来的益处会远远大于ETL性能的损失。一个 OLAP模型可以有多个事实表 事实表经常有 millions of rows 事实往往是数字 量度 有些事实可以被累加，另一些不能

13、 最小粒度原则 不欢迎描述性属性（瘦高 vs.矮胖）维度描述事实 逻辑上通过 key 关联 维度表往往包含相当多的属性 典型的属性是文本的、离散的 维度往往含有层次 主键往往是系统产生的 primary key 很可能是共享的 命名规则：Fact+名称，事实表的命名应能够大致描述事实表所包含的信息。例如：FactOrder 事实表中的外键命名与对应的维度表的主键命名保持一致。事实表中日期的外键的形式应与日期维度表的主键保持一致。需标识每一行记录的创建日期，如采用Merge方式，则需标识更新日期。尽可能保留源系统中的记录标识，以方便数据追踪 命名：Dim+维度名称 常规维度 主键 代理键（可选）

14、名称 排序键（可选）自定义汇总公式（可选）父子维度 父键 一元运算符（可选）EmployeeKeyEmployeeID.TimeKeyTheDate.ProductKeyProductIDProduceNameProductBrandProductCategory.CustomerKeyCustomerID.ShipperKeyShipperID.TimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice.TimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice.Produ

15、ct BrandProduct Category IDProduct CategoryProduct Category IDProductKeyProduct NameProduct SizeProduct Brand ID订单客户客户所在区域客户特征分类销售人员销售分公司销售大区产品产品分类大雪花设计可以提供更大的灵活性和可扩展性。列名列名说明及要求说明及要求必须必须字段字段事例事例维度名称维度名称+IDint或bigint型,Primary Key。维度表主键，自增序列是ProductID维度名称维度名称+Code维度成员在源系统中的编码，例如产品编码等否ProductCode维度名称维度

16、名称+Name维度成员名称，如包括多种语言，列名可分别加入语言标识是ProductNameProductENNameProductCNNameSortID排序列否SortIDCustom_Rollup自定义汇总列否Custom_Rollup维度类别名称维度类别名称+ID外键，名称与关联的维度表的主键相同否ProductCategoryIDInsert_Date记录的创建时间 Update_Date记录的更新时间 列名列名说明及要求说明及要求事例事例DateID主键，int，8位长度数字，非自增序列，格式YYYYMMDD20120101DateDatetime类型2012-1-1 00:00:0

17、0YearIDInt，4位长度数字，格式YYYY2012YearNvarchar(50)，50长度字符串2012年、2012MonthIDInt，6位长度数字，格式YYYYMM201201MonthNvarchar(50)，50长度字符串2012年1月、2012.1其他需要展示的列其他需要展示的列 统计特征客户客户统计特征销售FactJimAllTodNPaulFleDavidVPaulMaBobMuSteveBBillGThe BoardEmployeeManagerThe BoardSteveBThe BoardBillGThe BoardJimAllSteveBPaulMaSteveBB

18、obMuSteveBTodNPaulMaDavidVPaulMaPaulFleDavidV数据提取是捕获源数据的过程。有两种捕获数据的主要方法（1）完全刷新：对移入中间数据库的数据进行完全复制。该复制可能替换数据仓库中的内容，及时在新的时间点上添加完整的新副本，或者与目标数据进行比较，以便在目标中生成一条修改记录。（2）增量更新：只捕获源数据中修改的数据，如何捕获数据修改与数据源本身是密切相关的，实际上是逐个实现的问题。唯一标识 更新日期 如何保持历史数据不变？如何处理对历史的修改？对性能影响最大的是设计 对数据进行分类 常用不常用 当前历史 增加筛选条件 避免迪卡尔积 本身已经有了本身已经有

19、了Key的标识以后，是否还需要的标识以后，是否还需要Primary Key?数字之间的比较永远比字符比较快得多。物理存储时，数字简单得多，因为它们长度一样。字符则不同。内存中，字符占的空间大得多。(4 byte 的指针+文本长度*2（Unicode)+2。数字则仅有4 bytes 各程序对字符的处理机制不同 数字没有代码页的问题 创建索引用以提高查询速度 避免索引对ETL的不良影响分区存储后的数据单元分区存储后的数据单元易于：易于：重构 索引 重组 恢复 监控节省存储空间一定程度上的范式带筛选的查询和分组速度快 保持数据量的增长速度 通过时间的筛选，避免查找重复性数据 创建事实表 创建维度表

20、平衡维 星型 雪花型 大雪花型 不平衡维 父子型 维度表与事实表不是绝对的 同一个表，可以同时是维度表与事实表 同一个表，可以有时是维度表，有时是事实表 量度的存储 列存储 行存储 不变化 更新 新增 举例：当某销售人员从一个部门调至另一个部门，他的历史业绩该如何核算？映射表 用于保存映射关系数据，数据来源于ODS表，命名 Mapping_+名称，名称应能够大致描述映射的对象信息。例如：Mapping_OrderProduct，用于保存订单与订单中产品之间的多对多关系。RPT表 用于保存直接生成报表的数据。表结构与需要展示的报表的结构基本相同。命名 RPT_+名称。RPT表的是由于某些报表生成很复杂而生成的，例如用户需要在报表中展示审批信息，每个审批信息展示为一条数据，其中审批人与其角色用冒号分割，不同审批人之间用分号分割。禁止对每张报表都使用RPT表。尽可能减少RPT表的数量。