数据仓库设计技巧

商业智能核心技术与应用数据仓库设计数据仓库的基本概念数据仓库是面向主题的、集成的、稳定的、随时间不断变化的数据库系统。它介于数据源与多维数据库之间，起到承上启下的作用。数据仓库是通过对来自不同的数据源进行统一处理及管理，通过灵活的展现方法来帮助决策支持。数据源数据仓库决策数据仓库的设计理念在设计仓库数据库之初把用户的分析需求纳入考虑范围是十分有必要的。同时，数据仓库的构建必需基于业务数据库，业务数据源的结构也是不得不考虑的问题。因此在设计数据仓库的时候，应该坚持用户驱动与数据驱动相结合的设计理念。业务数据模型设计的设计范式1NF

如果一个表中没有重复组（即行与列的交叉点上只有一个值，而不是一组值）,且定义了关键字、所有非关键属性都依赖于关键字，则这个表属于第一范式（常记成1NF）

2NF

如果一个表属于1NF，且不包含部分依赖性，既没有任何属性只依赖于关键字的一部分，则这个表属于第二范式（常记成2NF）。3NF

如果一个表属于2NF，且不包含传递依赖性（即没有一个非关键属性依赖于另一个非关键属性，或者说没有一个非关键属性决定另一个非关键属性），则这个表是第三范式（常记成3NF）。业务数据模型设计的ER图表示ER模型（实体联系模型）简称ER图。它是描述概念世界，建立概念模型的实用工具。ER图包括三个要素：实体、属性、实体之间的联系联系归结为三种类型：

一对一联系（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联系。

多对多联系举例统计特征FatLeanUglyWealthy统计特征客户客户统计特征销售Fact客户统计特征JohnFatRichardLeanJohnUglyRichardWealthy客户JohnRichardCristian客户销售额John1.0Richard3.0Cristian10.0每个统计特征对销售人员来说是怎么样的呢？统计特征销售额AllDemographics14.0Fat1.0Lean3.0Ugly1.0Wealthy3.0数据仓库模型设计最佳实践数据仓库模型设计主要分三个阶段：1、概念设计2、逻辑设计3、详细设计并分别产生三类设计模型1、主题域模型2、业务数据模型3、物理模型数据仓库模型设计采用迭代式开发，这一点也符合数据仓库系统迭代开发的特点。它具有较好的灵活性和易变性，适应于主题不明确或不确定的需求。概念设计数据仓库是面向主题来组织数据，一个数据仓库有若干个主题，而每个主题又有一个数据集合体做支撑，这个数据集合称为主题域。概念设计的中心工作是在需求分析基础上设计的主题域模型。主体域模型是客观到主观之间的桥梁，是与硬件环境、软件选择无关的数据抽象模型，是为下一步建立业务数据模型、物理模型服务的概念性工具。主题域具有两个特性1、独立性，即主题域具有明确的边界与独立的内涵，虽然主题间可以有交叉，但不影响其独立性。2、完备性，即每个主题的分析要求所需的数据均应能在主题域中得到。采用概念数据模型设计就是要设计主题域的数据结构。数据仓库项目的主题域模型示例逻辑设计逻辑设计设计计到的知识点点包括：业务数据模型型设计的建模模对象应包含含实体、属性性、关键字和和联系。业务数据模型型设计应该遵遵守规范化准准则：即第三三范式设计准准则。业务数据模型型的ER图表示法。逻辑设计的中中心工作是设设计业务数据据模型，业务务数据模型是是用具体的软软件结构来实实现概念数据据模型。目前数据仓库库一般是建立立在关系数据据库基础上，，因此数据仓仓库的设计中中采用的业务务数据模型就就是关系模型型。业务数据模型型设计的建模模对象实体我们把客观存存在并且可以以相互区别的的事物称为实实体。实体可可以是实际事事物，也可以以是抽象事件件。属性描述实体的特特性称为属性性。关键字如果某个属性性值能唯一地地标识出实体体集中的每一一个实体，可可以选作关键键字。联系实体集之间的的对应关系称称为联系，它它反映现实世世界事物之间间的相互关联联。联系分为为两种，一种种是实体内部部各属性之间间的联系。另另一种是实体体之间的联系系。逻辑设计方法法—定义实体主题域主题实体粒度类型说明生皮分选物料物料物料代码，唯一标识每种物料维度物料分类物料分类代码，唯一标识每种物料分类维度分选结果分选结果物料代码*仓库*供应商*产地*皮种量度组逻辑设计方法法—定义实体层层级日月季年物料物料分类供应商逻辑设计方法法—定义量度组组主题域量度组量度聚合计算公式说明生皮分选分选结果分选结果数量累加财务员工员工工资平均逻辑设计方法法—定义关系主题实体采购分选结果预算日期日**月*季年物料物料**物料分类逻辑设计的关关键因素粒度特例详细设计物理模型是从从业务数据模模型创建而来来的，建立物物理模型通过过扩展业务数数据模型，使使模型中包含含关键字和物物理特性。物理模型设计计包括：设计存储结构构——创建实体设计索引策略略——创建索引设计存储策略略——创建分区设计存储结构构——创建实体创建物理实体体表视图约束创建事实表只包括主键、、外键和度量量事实表一个OLAP模型可以有多多个事实表事实表经常有有millionsofrows事实往往是数数字‘量度’有些事实可以以被累加，另另一些不能最小粒度原则则不欢迎描述性性属性（瘦瘦高vs.矮胖）创建维度表维度描述事实实逻辑上通过key关联维度表往往包包含相当多的的属性典型的属性是是文本的、离离散的维度往往含有有层次主键往往是系系统产生的primarykey很可能是共享享的维度表设计常规维度主键代理键（可选选）名称排序键（可选选）自定义汇总公公式（可选））父子维度父键一元运算符（（可选）数据仓库的星星型结构Employee_DimEmployeeKeyEmployeeID...EmployeeKeyTime_DimTimeKeyTheDate...TimeKeyProduct_DimProductKeyProductIDProduceNameProductBrandProductCategory...ProductKeyCustomer_DimCustomerKeyCustomerID...CustomerKeyShipper_DimShipperKeyShipperID...ShipperKeySales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...TimeKeyCustomerKeyShipperKeyProductKeyEmployeeKey多个外键事实维度键数据仓库的雪雪花型结构二级维度表Sales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...Product_Brand_IdProductBrandProductCategoryIDProduct_Category_IdProductCategoryProductCategoryIDProduct_DimProductKeyProductNameProductSizeProductBrandID主维度度表雪花型型结构构的特特点节省存存储空空间一定程程度上上的范范式星型vs.雪花型型Whichoneisbetter?长期以以来的的争论论两种观观点各各有支支持者者争论在在继续续……目前看看来，，大部部分更更加倾倾向于于星型型支持星星型维维度的的论点点事实表表总会会是很很大的的，在在维度度表上上节省省的空空间相相对来来说是是很小小的增加了了数据据模型型的复复杂度度查询操操作概概念上上更复复杂了了从数据据仓库库到多多维数数据库库的加加载时时间会会更长长因此，，只有有当维维度表表极大大，存存储空空间是是个问问题时时，才才考虑虑雪花花型维维度简而言言之，，最好好就用用星型型维度度即可可支持雪雪花型型维度度的论论点从数据据仓库库到多多维数数据库库的加加载过过程中中，雪雪花型型维度度的效效率更更高。。雪花型型维度度描述述了更更清晰晰的层层次概概念。。只有当当最终终用户户可能能直接接访问问数据据仓库库时才才考虑虑星型型。（（而这这是不不被建建议的的）。。思考：：PrimaryKey与KeyPrimaryKeykeyNameColorSizeDescription1A001捷达银灰…………2B003宝马火红…...……3C010切诺基宝石蓝…………………………………………本身已已经有有了Key的标识识以后后，是是否还还需要要PrimaryKey?技巧：：创建建数字字主键键数字之之间的的比较较永远远比字字符比比较快快得多多。物理存存储时时，数数字简简单得得多，，因为为它们们长度度一样样。字字符则则不同同。内存中中，字字符占占的空空间大大得多多。(4byte的指针针+文本长长度*2（Unicode)+2。数字字则仅仅有4bytes支持变变化维维度注意维度表表与事事实表表不是是绝对对的同一个个表，，可以以同时时是维维度表表与事事实表表同一个个表，，可以以有时时是维维度表表，有有时是是事实实表创建““父子子型””维度EmployeeManagerTheBoard<None>SteveBTheBoardBillGTheBoardJimAllSteveBPaulMaSteveBBobMuSteveBTodNPaulMaDavidVPaulMaPaulFleDavidV父子维维度缓慢变变化维维不变化化更新新增举例：：当某某销售售人员员从一一个部部门调调至另另一个个部门门，他他的历历史业业绩该该如何何核算算？创建物物理模模型主键：：PK_，整整型自自增外键：：FK_排序键键：Sort_字符类类型：：nvarchar量度类类型：：float、numeric创建视视图、、约束束视图尽量少少用或或不用用视图图，以以避免免意想想不到到的逻逻辑陷陷阱约束尽量使使用约约束，，以确确保数数据的的完整整性。。正确确的数数据所所带来来的益益处会会远远远大于于ETL性性能的的损失失。设计索索引策策略——创建索索引创建索索引用用以提提高查查询速速度避免索索引对对ETL的不不良影影响设计存存储策策略——创建分分区分区存存储后后的数数据单单元易易于：：重构索引重组恢复监控设计数数据更更新数据提取是捕获获源数数据的的过程程。