数据仓库实施指南

数据仓库实施指南梅花与剑1231数据仓库相关概念数据仓库体系结构数据仓库实施方法目录

一、数据仓库相关概念什么是数据仓库一种面向分析的环境;一种把相关的各种数据转换成有商业价值的信息的技术。数据仓库理论的创始人W.H.Inmon在其《BuildingtheDataWarehouse》一书中，给出了数据仓库的四个基本特征:面向主题，数据是集成的，数据是不可更新的，数据是随时间不断变化的。

一、数据仓库相关概念区分业务型与分析型数据库一、数据仓库相关概念业务型数据库的特点用于减少冗余和提高精度适合于数据的写入和更新而不是数据的读取一、数据仓库相关概念业务型数据库的问题一、数据仓库相关概念分析型数据库的特点一、数据仓库相关概念星型结构的好处一、数据仓库相关概念维度一、数据仓库相关概念度量度量也叫事实，是用于评价业务状况的数值型数据例如:销售额、成本、利润、库存量、交易数在企业活动中通常是通过如销售额、费用、库存量和定额一类的关键性能指标——度量来监测业务的成效。不同的度量反映出不同的业务性质。度量之间相互独立。度量是业务量化的表示。一、数据仓库相关概念多维立方体露营装备2005年在欧洲的销量一、数据仓库相关概念多维分析一、数据仓库相关概念维度、层和类别1232数据仓库相关概念数据仓库体系结构数据仓库实施方法目录

一、数据仓库相关概念Inmon的企业信息化工厂企业数据仓库ETL组织数据用于部门级分析多维模型原子数据的集成仓库第三范式操作型应用程序用户…数据集市DSS应用程序挖掘&探索…一、数据仓库相关概念Kimball的维度数据仓库维度数据仓库ETL数据集市:数据仓库中的主题区域原子数据的集成仓库维度模型操作型应用程序用户…一、数据仓库相关概念独立型数据集市ETL相互独立的数据仓库操作型应用程序…DSS系统分析系统…ETLETL………专卖分析数据仓库二、数据仓库体系结构数据中心用户是谁?要解决什么问题?用户:领导—决策支持业务人员—报表、业务操作信息中心—服务领导和业务人员解决问题:数据分散:难管理、难应用数据标准不统一:可信度低办法:集中:数入一库集成:数出一门应用:报表、数据挖掘、数据共享二、数据仓库体系结构数据中心五大体系信息资源标准体系数据交换服务体系数据加工存储体系数据分析应用体系信息安全保障体系二、数据仓库体系结构信息资源标准体系-"建、管、用”数据标准：主数据标准、元数据标准、数据元标准、指标体系、指数体系、数据模型标准等技术标准：数据交换标准等管理标准：数据中心管理办法、数据中心管理流程运维标准：数据安全管理规范，数据发布、共享及服务规范等标准如何落地?二、数据仓库体系结构数据交换服务体系ETL数据复制WEB服务MQ中间表共享文件方式共享二、数据仓库体系结构数据加工存储体系数据建模ODS：缓冲数据区（全量)规范数据区（增量)整合数据区（明细维度模型-按主题，近期数据)DW：整合数据区（明细维度模型-按主题，全量数据)汇总数据区（按需汇总，全量)DM：多维模型-面向业务部门二、数据仓库体系结构信息安全保障体系内容:数据安全保障:传输、加工存储、分析应用数据质量保障实施安全保障:身份鉴别、访问控制、安全审计、容错、备份恢复等注意事项:安全意识权限控制责任清晰运维工作实时性应急措施及应急演练1233数据仓库相关概念数据仓库体系结构数据仓库实施方法目录

三、数据仓库实施方法数据中心实施路径信息系统建模用户视图登记数据项整理数据元素规范化业务建模标准制定主数据建模企业数据建模源头目标分析模型明确项目范围和目标三、数据仓库实施方法维度模型设计——四步维度建模法1选取业务过程2定义粒度3选定维度4确定事实三、数据仓库实施方法第一步:选取业务过程原则:针对业务流程进行维度建模确保某个业务流程中的核心数据只被抽取一次保证数据仓库中业务数据一致性误区:不针对业务流程而针对业务部门进行维度建模为某个部门或某张报表建立单独的维度模型三、数据仓库实施方法第二步:定义粒度粒度的解释：粒度传递了同事实表度量值相联系的细节所达到的程度方面的信息。简单的说，反映了事实表的明细程度。粒度举例：超市小票上的购物清单（某位顾客某天在某个超市的某个收银台购买了什么商品)医生的处方药品清单仓库每种产品库存值的月快照原则：最小粒度原则。优先考虑具有原子粒度的业务信息，这些数据不能再做进一步的细分数据仓库中存储汇总的、概要性的数据主要是基于数据库性能上的考虑汇总数据不能成为最底层细节数据的替代品三、数据仓库实施方法第三步:选择维度原则:在粒度确认后，选取能从各个角度，充分描述问题的维度为每个维度添加丰富的维度属性误区:没有定义粒度就开始选定维度示例:常见维度包括日期、产品、顾客、事务类型和状态三、数据仓库实施方法第四步:确定事实原则：事实表由包含引用维度的外键和事实构成。在需求调研时我们可以通过提出"您需要对哪些指标进行统计？”这样的问题来确定事实。事实表应该包含所有与业务过程有关的事实。反映不同业务过程的事实应该放在不同的事实表中。具有不同粒度的事实必须放在不同的事实表中事实一般在各维度上都有良好的可加性三、数据仓库实施方法维度建模总结维度建模总原则:需求驱动和数据驱动相结合维度模型1.业务处理2.粒度3.维度4.事实业务需求实际数据业务需求调研数据资源规划三、数据仓库实施方法案例背景:国家烟草专卖局下有33个省级公司，300多家市公司负责卷烟的销售。每个市公司都有配套部门，包括计划、物流、仓储、分拣等人员，并有几十个卷烟品牌在各地销售，每个卷烟品牌下面又有多个卷烟规格。各个规格按按价格可以分为多个价类。国家局已经建设的相关系统对每天卷烟的采购、库存和销售情况进行监控，采集了相关数据。管理人员希望对卷烟的销售情况进行灵活的分析，及时发现销售情况的问题。三、数据仓库实施方法维度建模实例第一步:选取业务处理过程:需要建立一个模型，反映省市公司卷烟的采购、库存及销售过程第二步:定义粒度每天、每个市公司、每个规格卷烟的采购、库存及销售情况三、数据仓库实施方法维度建模实例第三步:选取维度日期维度:年、季度、月、日企业维度:省公司、市公司产品维度:品牌、规格价类维度:价类三、数据仓库实施方法维度建模实例第四步:选择事实可加型事实半可加型事实:非可加型事实三、数据仓库实施方法添加维度表属性确定哪些字段的值需要被筛选掉或者需要存在确定是否需要同时存储编号和描述,或者只是编号,或者只是描述的信息增加的维度属性会为用户带来更多的查询条件丰富的维度属性将使查询变得更加灵活三、数据仓库实施方法维度表设计技巧代理键：为每个维度表分配一个代理键，用于唯一地区分表中的每行记录。数据仓库中维度和事实表之间的每个连接都应该用没有明确含义的整型代理关键字来建立。应该避免使用自然的操作型产品编码。丰富的维度组合：在维度表中提供丰富和全面的维度属性集合，可以显著地提高分析性能。（代码与描述符、标志和标志值)多列组合字段：存储全部属性（xxx-yyy-zzz，拆分为xxx、yyy、zzz)三、数据仓库实施方法维度表设计技巧当事实表和维度表有多重关系时，没有必要为维度建立多个副本。每个角色都能通过在事实表中连接维度的视图或别名到适当的外键来存取。避免空值。维度列中不应该存在空值，而应该选择某个值作为当数据无效时存储的值。（数值列经常用0代替，日期经常默认为将来的某个日期)三、数据仓库实施方法区分数值型维度如果一个属性通常用于聚集或汇总，那么它就是事实;如果通常被用于提供聚集或汇总的环境，那么它就是维度。如果一个元素值用于过滤查询、排序、控制聚集、区分主从关系等，该元素通常是维度。数量单位一般是维度，数量汇总一般是事实。

大多数的维度的内容都会有不同程度的改变。比如：用户的职务变更客户更改了他的名称或地址生产企业的变化我们如何去处理这些维度中的变化呢?直接更新维度属性值插入新维度行新增一列保留历史三、数据仓库实施方法缓慢变化维三、数据仓库实施方法方式一:直接更新维度属性值产品关键字(PK)规格编码规格名称品牌编码品牌名称产品类型编码产品类型名称生产企业1116901001红双喜（软）1201红双喜0自产自销上海烟草集团产品关键字(PK)规格编码规格名称品牌编码品牌名称产品类型编码产品类型名称生产企业1116901001红双喜（软）1201红双喜0自产自销广东中烟不能维护维度的历史信息三、数据仓库实施方法方式二:插入新维度行产品关键字(PK)规格编码规格名称品牌编码品牌名称产品类型编码产品类型名称生产企业1116901001红双喜（软）1201红双喜0自产自销上海烟草集团2226901001红双喜（软）1201红双喜0自产自销广东中烟可以维护维度的历史信息。维度表中出现包含重复的信息,可以设置当前版本标识位,通过提供distinct查询视图。

日期关键字(FK)产品关键字(FK)企业关键字(FK)价类关键字(FK)调拨价格批发价格购进量库存量销售量毛利率20111111223221119.324.22001001500.3220112222223221119.324.22501201510.36按照新的生产企业增加一行历史记录仍然引用旧的维度信息新的记录引用新的维度信息三、数据仓库实施方法方式三:新增一列保留历史可以有限度地维护维度的历史信息。维度表中不会出现包含重复的信息产品关键字(PK)规格编码规格名称品牌编码品牌名称产品类型编码产品类型名称生产企业1116901001红双喜（软）1201红双喜0自产自销上海烟草集团产品关键字(PK)规格编码规格名称品牌编码品牌名称产品类型编码产品类型名称生产企业历史生产企业1116901001红双喜（软）1201红双喜0自产自销广东中烟上海烟草集团新增一列保留上一生产企业三、数据仓库实施方法事实表设计技巧——事务事实表

事务事实表，也称为事务粒度的事实表。用于跟踪发生在非连续时间点（即当事务/事件发生时)上的每次事务。比如订单的预订、发货等。粒度。事实表的每一行描述一个特定的事件，但不一定是单个事件。稀疏性。仅当事务发生时才生成一条记录。（比如2号没有销售行为发生，事实表中就没有2号的销售记录。)可加性。事务事实表通常记录可加的事实。大部分可加事实都应该被拆分为完全可加的事实被存储。（比如同比、占比、利润率等)日期关键字(FK)产品关键字(FK)企业关键字(FK)销量2012-2-122222321002012-2-122222331202012-2-42222232120……………………三、数据仓库实施方法事实表设计技巧——累计快照事实表

周期快照事实表用来记录有规律的,可预见时间间隔的业务累计数据。通常的时间间隔可以是每天、每周或者每月。典型的例子是库存日快照事实表、银行账户每日余额事实表等。日期关键字(FK)产品关键字(FK)企业关键字(FK)库存量2012-2-122222321002012-2-222222321102012-2-322222321202012-2-42222232120……………………日期关键字(FK)产品关键字(FK)企业关键字(FK)库存量2012-2-122222321002012-2-322222321202012-2-42222232120……………………1、快照事实表包含至少一个半可加性质的事实。2.快照事实表是稠密的。在周期内无论是否发生事务，都会记录一行。

如果在周期内不记录行，查询会非常困难：检查相关日期（2012-2-2)。如果没有找到，检查前一天。重复上述步骤，直到找到为止。

三、数据仓库实施方法事实表设计技巧——累计快照事实表

累计快照事实表一般用来涵盖一个事务的生命周期内的不确定的时间跨度。典型的例子是用多个日期字段的订单发货事实表。粒度。累计快照设计的粒度是依照在业务流程中可识别的实体来构造的。实体的每个实例在事实表中恰好有一行记录。记录里程碑的关键日期。便于统计不同里程碑的花费时间。事务和累积快照模型能够相互补充。如果需要同时构建事务模型和累积模型,应该使用事务星型模型作为累计快照的数据源。

合同签订日期(FK)合同鉴章日期(FK)合同发货日期(FK)合同到货日期(FK)产品(FK)供货方(FK)采购方(FK)价格合同量发货量到货量2012/2/12012/2/42012/2/52012/2/711190080012.32020202012/2/52012/2/72012/2/9

11190080012.32020

2012/2/122012/2/13

11190080012.320

卷烟销售合同事实表三、数据仓库实施方法ETL设计ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是多个数据源中分散、零乱、标准不统一的数据整合到一起。ETL的设计分三部分:数据抽取、数据的清洗转换、数据的加载。在设计ETL的时候也是从这三部分出发。ETL三个部分中，花费时间最长的是T(清洗、转换)的部分，一般情况下这部分工作量是整个ETL的2/3。数据的加载一般在数据清洗完了之后直接写入DW中去。

三、数据仓库实施方法ETL设计——数据抽取数据抽取就是从外部异构数据源抽取数据，但是并不是所有的抽取源表数据项都有实际的意义。第一步:确定抽取范围第二步:制定抽取策略第三步:数据抽取方式三、数据仓库实施方法ETL设计——数据抽取策略时间戳方式需要在源系统中业务表中统一添加时间字段作为时间戳（如表中已有相应的时间字段，可以不必添加)，每当源系统中更新修改业务数据时，同时修改时间戳字段值。当作ETL加载时，通过系统时间与时间戳字段的比较来决定进行何种数据抽取。优点：ETL系统设计清晰，源数据抽取相对清楚简单，速度快。可以实现数据的增量抽取。缺点：时间戳维护需要由源系统完成，需要修改源系统中业务表结构；所有添加时间戳的表，在业务系统中，数据发生变化时，同时更新时间戳字段，需要对源系统业务操作程序作修改，工作量大，改动面大，风险大。三、数据仓库实施方法ETL设计——数据抽取策略全删全插方式每次ETL操作均删除目标表数据，由ETL全新加载数据。优点:ETL加载规则简单，速度快缺点:对于维表加代理键不适应，当源系统产生删除数据操作时，OLAP层将不会记录到所删除的历史数据。不可以实现数据的递增抽取，不适合大数据量的抽取。

三、数据仓库实施方法ETL设计——数据抽取策略全表对比方式在ETL过程中，抽取所有源数据，并进行相应规则转换，完成后先不插入目标，而对每条数据进行目标表比对。根据主键值进行插入与更新的判定，目标表已存在该主键值的，表示该记录已有，并进行其余字段比对，如有不同，进行Update操作，如目标表没有存在该主键值，表示该记录还没有，即进行Insert操作。优点:对已有系统表结构不产生影响，不需要修改业务操作程序，所有抽取规则由ETL完成，管理维护统一，可以实现数据的递增加载。没有风险。

缺点:ETL比对较复杂，设计较为复杂，速度较慢三、数据仓库实施方法ETL设计——数据抽取策略业务日志表方式在源系统中添加系统日志表，当业务数据发生变化时，更新维护日志表内容，当作ETL加载时，通过读日志表数据决定加载那些数据及如何加载。优点:不需要修改源系统表结构，源数据抽取清楚，速度较快。可以实现数据的递增加载。缺点:日志表维护需要由源系统完成，需要对源系统业务操作程序作修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响。工作量较大，改动较大。有一定风险。三、数据仓库实施方法ETL设计——数据抽取策略