数据仓库实施指南

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

一、数据仓库相关概念什么是数据仓库一种面向分析的环境；一种把相关的各种数据转换成有商业价值的信息的技术。数据仓库理论的创始人在其《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设计——数据抽取取策略触发器方方式触发器方方式是普普遍采取取的一种种增量抽抽取机制制该方式式。根据据抽取要要求在要要被抽取取的源表表上建立立插入、、修改、、删除3个触发