数据库及数据仓库精要Adhoc报表系统1ppt课件.ppt

数据库及数据仓库精要 终极目标 Ad hoc报表系统 Ad hoc释义 即时的 随意的 特殊的 前言 数据库扮演的角色也叫联机事务处理OLAP OnlineTransactionalProcessing 数据库保存由日常管理过程中涉及的业务操作创建的操作型结构化数据 数据记录系统管理行为 通过各种业务逻辑来交互 反映细粒度的事务数据 保存时间短 主要依赖关系模式建模方法论 数据仓库扮演的角色也叫联机分析处理OLAP OnlineAnalyticalProcessing 数据由联机事务处理来 经过选择和聚集 变为统计分析事实产生的因果 辅助决策制定 通过各种统计分析报表来交互 反映大范围的事实数据 业务事件的累积 保存时间长 主要依赖多维模式建模方法论主题 事实和事件 请注意语义 事实是一个主题 比如 销售 配送 住院 手术等 一个主题对应了多个相互关联的关系模式 也可以叫做相关关系表 事实的实例对应着发生的事件 比如 一个执行的销售 配送 住院 手术等都是一个事件 每个事实由一组提供了事件的量化描述的度量的属性值描述 比如 销售收入 配送量 住院支出 手术时间等通过语义将数据库和数据仓库结合在一起 从报表到报表存在的问题及对策纲领 对策纲领实体 关系所代表的实体 原子化 是指实体本身不能再分解属性原子化 是指实体的属性 值域所代表的属性 本身不能再分解主键原子化 是指实体的主键本身是一个唯一的ID字段联系原子化 是指实体间的联系 关系所代表的联系 都是一对多的联系命题原子化 是指语义角度的联系原子化 现实 面对单一主题 数据规模极小 仅是面对招投标业务追踪 1 第二个表是从第一个表通过高级程序员编写VBA程序实现 第一个表也是专业定岗员工通过Excel专用表单维护和更新的 背后其实还是高级程序员的工作价值体现2 信息化岗位需求及配置 高级程序员四名 六个专业业务人员 满负荷工作以应对数据变动和操作功能变动解决方案 报表列表化 去除冗余数据 建模信息化模型 数据库模型 并影射和实现为Access桌面数据库应用 问题的导入 从Excel表格出发引出的问题 结构良好的表 范式 SQL语言 关系模式及E R图基本表与中间表 临时表不同 基本表及其字段之间的关系 应尽量满足第三范式 是结构良好的表 它可以消除删除行 改变行 修改行 实例 的错误和异常 它具有如下四个特性 1 原子性 基本表中的字段是不可再分解的 2 原始性 基本表中的记录是原始数据 基础数据 的记录 3 演绎性 由基本表与代码表中的数据 可以派生出所有的输出数据 4 稳定性 基本表的结构是相对稳定的 表中的记录是要长期保存的 5 基本表的每个决定因子都必须是候选建 6 非基本表必须分解为两个或多个基本表 三个基本范式 1 1NF是对属性的原子性约束 要求属性具有原子性 不可再分解 2 2NF是对记录的惟一性约束 要求记录有惟一标识 即实体的惟一性 3 3NF是对字段冗余性的约束 即任何字段不能由其他字段派生出来 它要求字段没有冗余大多数结构不良好的表 会产生或包含大量的冗余数据 同时可能会出现删除行 改变行 修改行的错误和异常 这都是都是使用了SQLDMLCURD语句产生的 像中间表 报表和临时表 1 中间表是存放统计分析数据的表 它是为数据仓库 输出报表或查询结果而设计的 有时它没有主键与外键 数据仓库除外 2 临时表是程序员个人设计的 存放临时记录 为个人所用 3 基表和中间表由DBA维护 临时表由程序员自己用程序自动维护 关系是一个由行和列组成的二维表 不一定结构良好 特征为 行包括实体的数据 列包含实体性质的数据 表中的单元格存储单个值 每列的所有实体类型一致 每列具有唯一名称 列的顺序任意 行的顺序任意 任意两行互不重复 这是最大的复合关系模式的条件 符合这个要求的表就是关系型表格 通过E R图将这些概念反映出来 通过名词性和动词性 完全可以描述客观世界 其中名词性描述实体 动词性描述联系或动作影响 通过动词性将不同的实体关联在一起 第一目标或者叫基础目标 统计 汇总及分析报表的工作尽量地自动用Excel或Spreadsheet做 几乎不需人为干预 目录 E R模型的概念与表示实体 联系方法 概念模型及设计 关注现实世界 关系模式的规范化 消除数据冗余 避免操作异常 E R图向关系表 Schema 的转换 逻辑模型及设计 关注信息世界 多维模式与统计分析的关系Excel或Spreadsheet在统计分析中的角色统计分析系统 多维模式建构 的演进终极目标 Ad hoc报表系统 关于现实世界事实及描述的定义 事实包括以下四个方面 第一 事实是指 某物具有某种性质或某些事物具有某种关系 与原子命题 分子命题的分类相对应 事实可以分为原子事实 分子事实 其中 原子事实是最基本 最简单的事实 从中不能再析出其它事实 分子事实是原子事实的复合第二 事实具有客观性 大多数的事实的存在都不依靠我们的意愿 这就是为什么我们把它们叫作 严峻的 不肯迁就的 或 不可抗拒 的理由 大部分物理事实的存在不仅不依靠我们的意愿 而且也不依靠我们的存在 第三 现实世界由各种各样的事实组合而成 事实属于现实世界第四 事实决定命题的真伪 事实是使得命题为真或为假的东西 具体说来 原子命题的真假取决于它是否与原子事实相符合 分子命题的真假取决于组成它的原子命题的真假现实世界的语义描述的存在第一 原子命题第二 分子命题第三 段落 由多个逻辑紧密相关的分子命题构成 第四 主题 由多个逻辑紧密相关的段落构成 E R模型的概念与表示 E R图也称实体 联系图 EntityRelationshipDiagram 提供了表示实体类型 实体属性和相关实体之间联系 三个要素 的方法 用来描述现实世界的概念模型关系数据库使用实体 联系模型 E R模型 进行数据库设计 以构造信息世界的逻辑模型 E R数据库设计工具提供了一个 方框与箭头 的绘图工具 帮助数据库用户建立E R图来描绘数据 逻辑及现实世界E R图可以手绘 亦可以借用Case工具 PowerDesigner Visio或SQLPowerArchitect 做出考察单独的一个联系 动词类型的语义连接及构造能力 联系及相互关联的实体形成的一个描述 构造了一个最基本的原子命题 E R模型的概念与表示 实体集 语义 名词类性 实体 Entity 事物就是在行动影响下物质本身的改变 或者进行 客观存在并可相互区别的事物称为实体 实体可以是具体的 也可以是抽象的概念或联系 具有共性的一类实体可归类为一个实体集 Entityset 属性 Attribute 实体所具有的某一特性称为属性 一个实体可以由若干个属性来刻画 域 Domain 属性的取值范围或类型 键或标识符 Key 标识符是实体中一个或多个属性的集合 可用来唯一标识实体中的一个实例 每个实体都必须至少有一个标识符 如果实体只有一个标识符 则它为实体的主标识符 如果实体有多个标识符 则其中一个被指定为主标识符 其余的标识符就是次标识符 候选标识符 E R模型的概念与表示 联系集 语义 动词类型 实体之间可以通过联系来相互关联 与实体和实体集对应 联系也可以分为联系和联系集 联系集是实体集之间的联系 联系是实体之间的联系 联系是具有方向性的 联系具有方向性 每个方向上都有一个基数 联系的两个方向上各自包含有一角色名 描述该方向联系的作用 按照实体类型中实例之间的数量对应关系 通常可将联系分为4个基本联系分为类 即一对一 ONETOONE 联系 一对多 ONETOMANY 联系 多对一 MANYTOONE 联系和多对多联系 MANYTOMANY 三个特殊联系每个实体类型都有自己的标识符 如果两个实体集之间发生联系 其中一个实体类型的标识符进入另一个实体类型并与该实体类型中的标识符共同组成其标识符时 这种联系则称为标定联系 也叫依赖联系 反之称为非标定联系 也叫非依赖联系 递归联系是实体集内部实例之间的一种联系 通常形象地称为自反联系 同一实体类型中不同实体集之间的联系也称为递归联系 E R模型的概念与表示 使用Case工具时的图符及事例 E R模型的概念与表示 E R图的设计步骤第一步 针对特定的应用 确定实体 属性和实体间的联系 画出局部E R图 第二步 综合各个局部E R图 产生反映数据库整体概念的总体E R图 E R模型的概念与表示 手绘草图事例 弱实体集有些实体集的所有属性都不足以形成主码 这样的实体集称为弱实体集 WeakEntitySet 依赖于其它实体集而存在 与此相对 其属性可以形成主码的实体集称为强实体集 弱实体集所依赖的实体集称为标识实体集 identifyingentityset 相应的关系为标识联系 identifyingrelationship E R模型的概念与表示 弱实体集通常没有主键 以订单的分项为例 订单项实体集可能有编号 局部的编号 商品名称 数量 单价等属性 但是这些属性不足以识别一个定单项 因为完全有可能在另外一张订单中出现相同的内容 必须把订单的关键字 如一个全局的订单编号 和定单项的局部编号结合起来才能标示一个定单项 弱实体集的属性中 用来与标识实体集的键结合以识别一个弱实体集的属性称为部分键 partialkey 弱实体集的主键 它的标识实体集的键 它的部分键 E R模型的概念与表示 手绘草图事例 E R图使用双线矩形表示弱实体集 弱实体集与其标识实体集之间的联系用双线菱形表示 弱实体集的部分键使用虚下划线表示 E R模型的概念与表示 实体集的层次关系现实世界中的很多概念之间都具体层次关系 E R模型使用实体集间的继承和ISA关系来描述这种概念间的层次关系实体集老师或学生都继承自实体集人 并且实体集老师或学生与实体集人之间都满足ISA关系 即老师或学生都是人的一种 ISA关系可以从两个方向进行设计从自上而下的方向 首先设计出人这一实体 然后根据属性的不同 将两种不同的人具体化 specification 为老师或者学生 从自下而上的方向 首先设计出老师或学生 然后将他们的共性提取出来 泛化 generalization 为人 E R模型的概念与表示 层次关系的约束从子实体集之间是否相交角度 不相交 disjoin 泛化要求继承自同一父辈的多个子实体集之没有交集 重叠 overlapping 泛化则允许有交集 从泛化是否完全角度 全参与泛化要求所有父辈实体都必须同时也是某一子辈实体 部分泛化则允许不是任何子辈实体的父辈实体存在 例如 在采用会员制的销售系统中 顾客被分为会员 VIP 与非会员 NONVIP 两种 会员拥有消费积分 credit 非会员拥有固定的折扣率 discount 一个顾客要么是会员 要么是非会员 二者必取其一 因此为全参与不相交 E R模型的概念与表示 手绘草图事例 E R图符 手绘草图事例 实体 联系方法 实体还是属性凡是满足以下两条准则的事物 一般均可作为属性对待 作为属性 不能再具有需要描述的性质 属性必须是不可分的数据项 不能包含其他属性 属性不能与其他实体具有联系 即E R图中所表示的联系是实体之间的联系 例如书籍是一个实体 书号 书名 作者 出版社 定价是书籍的属性 如果应用系统不再需要作者的其他信息 如电话 住址 个人主页等 那么根据原则1可以将作者作为书籍的属性对待 但是如果这些信息是必须的 那么作者作为一个实体看待更为恰当 实体 联系方法 手绘草图事例 实体 联系方法 实体还是联系一般来说 实体对应现实世界中实际存在的事物 是名词类型 联系对应的概念一般是一种动作 是动词类型 例如 书和作者都是现实世界中的名词 因此作为实体 而written by表示作者写书这一动作 因此作为联系 映射基数往往影响到一个概念是作为实体还是联系的选择 若一项贷款只能由一个分行发放 并且只能由一个客户借贷 则将Loan作为Customer与Branch之间的联系比较合适 但如果允许多个客户共同借贷同一项贷款 在这种情况下 将Loan作为实体 实体 联系方法 手绘草图事例 二元关系还是多元关系数据库中使用得最多的是二元联系 通常 将多元关系转换为二元关系 如学校选课系统 涉及到学生 教室 教师 课程等多个实体 可表示为一个四元关系 实体 联系方法 手绘草图事例 但也有一些情况下使用多元联系更好 如需要表达多个实体集间的约束时 如学校选课系统中若一门课程可由多个教师教授 并且若课程和教师确定 则上课的地点也随之确定 实体 联系方法 手绘草图事例 联系属性的放置影响联系属性放置的主要因素是联系的映射基数 对于一对一或一对多联系 选择作为联系属性或实体属性只是体现语义侧重点的不同如销售系统需要记录顾客 Customer 与订单 Order 之间的关系 Possess 由于一个订单只能由一个顾客所有 因此为顾客与订单之间为一对多关系 这时 记录生成订单日期的属性 date 既可以作为联系Possess的属性 也可作为订单的属性 实体 联系方法 手绘草图事例 对于多对多联系 联系的属性不能作为实体的属性 如 顾客与希望书籍之间的联系希望购买 Wish for Wish for有一属性date 表示顾客发出购买意向的日期 这一属性不能作为参与联系的两个实体Customer或Book的属性 实例 在线书店数据库 类似于Amazon的在线书店系统所用的数据库数据库中要求存储所有书籍的相关信息 并对书加以分类 顾客的有关信息也要求存储在数据库中 并且允许用户选择自己感兴趣的书籍类别及希望购买的图书 顾客在决定购买时可以发出订单 同一订单可以包含多种书 每种书可一次购买多本 顾客在订单中提供送货地址 系统根据订单发货 实例 在线书店数据库E R图 手绘草图事例 实例 在线书店数据库 合并分E R图各分E R图之间的冲突主要有三类 属性冲突 1 属性域冲突 即属性值的类型 取值范围或取值集合不同 例如 属性 订单号 有的定义为字符型 有的为数值型 2 属性取值单位冲突 例如 属性 库存 有的以册为单位 有的以千册为单位 命名冲突 1 同名异义 不同意义对象相同名称 例如 Author和Customer均有属性name 2 异名同义 一义多名 同意义对象不相同名称 例如 项目 和 课题 实例 在线书店数据库 结构冲突 1 同一对象在不同应用中具有不同的抽象 例如 作者 在某一局部应用中被当作实体 而在另一局部应用中则被当作属性 2 同一实体在不同局部视图中所包含的属性不完全相同 或者属性的排列次序不完全相同 3 实体之间的联系在不同局部视图中呈现不同的类型 例如 实体E1与E2在局部应用A中是多对多联系 而在局部应用B中是一对多联系 又如在局部应用X中E1与E2发生联系 而在局部应用Y中E1 E2 E3三者之间有联系 解决方法是根据应用的语义对实体联系的类型进行综合或调整 关系模式 设计范式 范式 数据库设计范式 数据库关系表的设计范式 是符合某一种级别的关系模式的集合 构造数据库必须遵循一定的规则 在关系数据库中 这种规则或者叫约束就是范式 关系数据库中的关系必须满足一定的要求 即满足不同的范式第一范式 1NF 在任何一个关系数据库中 第一范式 1NF 是对关系模式的基本要求 不满足第一范式 1NF 的数据库就不是关系数据库 所谓第一范式 1NF 是指数据库表的每一列都是不可分割的基本数据项 同一列中不能有多个值 即实体中的某个属性不能有多个值或者不能有重复的属性 如果出现 就可能需要定义一个新的实体 新实体与原实体之间为一对多关系第二范式 2NF 数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖 部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况 也即所有非关键字段都完全依赖于任意一组候选关键字 如果存在 那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体 新实体与原实体之间是一对多的关系第三范式 3NF 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息 例如 存在一个部门信息表 其中每个部门有部门编号 dept id 部门名称 部门简介等信息 那么在员工信息表中列出部门编号后就不能再将部门名称 部门简介等与部门有关的信息再加入员工信息表中 如果不存在部门信息表 则根据第三范式 3NF 也应该构建它 否则就会有大量的数据冗余 如果存在 应该分离这一部分出来形成一个新的实体 新实体与原实体之间是一对多的关系 关键要理解定义这种范式标准的主要目的是为了减少数据冗余 数据冗余产生的本质就是在一个表中存在字段与字段之间的一对多 或者多对多关系 满足这些规范的数据库是简洁的 结构明晰的 同时 也不会发生插入 insert 删除 delete 和更新 update 操作异常 反之则是乱七八糟 不仅给数据库的编程人员制造麻烦 而且面目可憎 可能存储了大量不需要的冗余信息 解决这个几对几的关系问题 就能轻易实现满足第三范式的数据库设计思考题 假定选课关系表为SelectCourse 学号 姓名 年龄 课程名称 成绩 学分 关键字为组合关键字 学号 课程名称 因为存在如下决定关系 学号 课程名称 姓名 年龄 成绩 学分 这个数据库表不满足第二范式 因为存在如下决定关系 课程名称 学分 学号 姓名 年龄 即存在组合关键字中的字段决定非关键字的情况 造成的潜在问题 1 数据冗余 同一门课程由n个学生选修 学分 就重复n 1次 同一个学生选修了m门课程 姓名和年龄就重复了m 1次 2 更新异常 若调整了某门课程的学分 数据表中所有行的 学分 值都要更新 否则会出现同一门课程学分不同的情况 3 插入异常 假设要开设一门新的课程 暂时还没有人选修 这样 由于还没有 学号 关键字 课程名称和学分也无法记录入数据库 4 删除异常 假设一批学生已经完成课程的选修 这些选修记录就应该从数据库表中删除 但是 与此同时 课程名称和学分信息也被删除了 很显然 这也会导致插入异常 把选课关系表SelectCourse改为如下三个表 学生 Student 学号 姓名 年龄 课程 Course 课程名称 学分 选课关系 SelectCourse 学号 课程名称 成绩 这样的数据库表是符合第二范式的 消除了数据冗余 更新异常 插入异常和删除异常 另外 所有单关键字的数据库表都符合第二范式 因为不可能存在组合关键字 E R图向关系模式的转换及主题数据库 通过实体 联系方法可以方便得得到现实世界的一个抽象模型 但这一模型并不能为数据库管理系统接受 要完成从现实世界到信息世界的转化 还必须将实体 联系方法所得的E R图转化为关系表定义 注意 一定要区分语义 包括名词性和动词性 先是动词性 代表联系集 其次是名词性 代表联系集的动词性所依赖 影响和作用的实体集分析并确定主题 主题数据库是面向业务 面向事务 面向事实和事件的主题的数据组织存储 例如 企业中需要建立的典型的主题数据库有 产品 客户 零部件 供应商 订货 员工 文件资料 工程规范等 其中产品 客户 零部件等数据库的结构 是对有关单证 报表的数据项进行分析整理而设计的 不是按单证 报表的原样建立的 这些主题数据库与企业管理中要解决的主要问题相关联 而不是与通常的计算机应用项目相关联 一个主题数据库的科学的数据结构 是由多个达到 基本表 BaseTable 规范的数据实体构成的 这些基本表具有如下的特性 1 原子性 基本表中的数据项是数据元素 即最小的 不能再分解的信息单元 2 演绎性 可由基本表中的数据生成全部输出数据 即这些基本表是精练的 经过计算或操作 自动化 处理可以产生全部企业管理所需要的数据 3 规范性 基本表中数据满足三范式 3NF 要求 这是依据科学的 能满足演绎性推算要求 并能保证快捷存取的数据结构 实体的转换 将一个普通实体 非弱实体 转换为表定义是相当直观的 实体的每个属性对应表中的一个字段 实体的主键对应表的主键 如Book实体转化到表的结果为 Book isbn title price press stock 联系的转换 一个多对多联系在转换后也对应一个新的交叉实体表 表中的属性包括参与联系各实体的主键联系的描述属性参与联系各实体的主键之和构成新的交叉实体表的超键 如多对多联系Written by转化为表之后其主键将由参与该联系的两个实体Book和Author的主键构成 如下 Written by isbn author serial 联系的转换 一对一和一对多联系A与B之间是一对多联系 不转换为一张单独的表 而只在B转换后的表中增加A的主键属性 当然这些属性将形成一个引用到A的主键的一个外键 以此表示某B实体所从属的A实体 这种方法可以产生更少的表 有利于提高数据库性能 还可以表达更多的约束如对于联系Possess 将在Order表中增加一列customer 表示订单从属的顾客 弱实体的转换 由于弱实体总是全参与它与它的标识实体之间的多对一联系 因此可以采用上面提出的一对多联系方法进行转换 弱实体转换后生成的表的主键由标识实体的主键与弱实体本身的部分键组合而成 如弱实体Item转换后 构成如下 Item order item isbn qty 实体层次的转换 将实体层次转换为表定义时可采用两种方法父辈实体与子辈实体都转换为单独的表通用方法 任何情况适用 每一个子辈实体转换为单独的表 其中既包含各子辈实体的特殊属性 也包含子辈与父辈实体的公有属性 只适用全参与泛化 因无法比哦啊是不从属于任何子辈实体的父辈实体如Customer与VIP NONVIP之间的全参与泛化可用第二种方法转换为 VIP customer name gender birthday city address email credit NONVIP customer name gender birthday city address email discount 一些实际的考虑 一般来说 在将E R图转换到关系表定义的过程中 需要考虑两个实际的问题 性能与数据规范化 提高数据库性能的一个重要方法是进行数据冗余 不要跨表进行处理 用一个表记录所有父子实体 而用一个字段来表示实体的类型 请大家依据关系模式规范化理论讨论如下实体是否要拆分成不同的实体集和联系集 还是不拆分 例如 Customer customer name gender birthday city address email credit discount customer type 在线书店数据库及关系模式 Book isbn title price press stock Author author name city Written by isbn author serial Category category super tag Customer customer name gender birthday city address email credit discount customer type Interest in customer category date Wish for customer isbn date status Order order customer date status payment name address Item item order isbn qty 思考题 电话计费系统关于不同业务或叫特殊功能需求 比如 国内长途 港澳长途 国际长途等 如何设计数据库满足 表结构不变 永远满足需求 比如 减少业务 增加业务基于类似Excel表格的设计和基于E R图实体联系模型 关系模式 模型 的设计比较 数据的统计分析及报表的展示功能 报表展示与制作是面向业务统计分析人员的最终体验 是最高层的数据统计分析应用的需求和表达真实例子展开 PracticalDataWarehouseandBusinessIntelligenceInsights 单主题1 加拿大电信2007年的年度销售报表 1 2 加拿大电信2007年的季度销售报表 4 3 加拿大电信2007年的月度销售报表 12 4 加拿大电信2007年每省的年度销售报表 10 5 加拿大电信2007年的每省的季度销售报表 40 6 加拿大电信2007年的每省的月度销售报表 120 7 若每省有十个县 则需要报表10 12 10 1200多主题这样如果一个主题像上面的报表需求量 则有多少主题 报表数量是幂乘数的关系 尤其是同比和环比这样的统计需求工作场景描述 JustBuildIt 1 新建报表时 大多数人都是使用MicrosoftExcel利用原有已经被Excel表格加工过的数据 很少使用初始规范化的数据源2 一个报表因为加工过的而造成的数据分散 需要多个业务人员协作完成3 多人协作时数据不一致 数据不规范严重影响工作进度 解决方案使用多维建模方法论 建立以业务事件为导向的以主题 统计分析涉及的不同领域数据 为相关的事实表库 统计分析涉及不同数据事件模型 通过对多维模型的钻取 切取 旋转等操作实现报表的动态化和个性化 消除众多关联的中间报表 以实现Ad hoc报表 自动化程度高的报表 逐步从依赖类似MicrosoftExcel 分散部署 数据不一致及不规范的始作俑者 表格过渡到多维统计分析到数据集市再到数据仓库的路线 Excel报表与事实表的相互转换关系 单一的Excel列表 有数据冗余 存在增删改的异常 Excel报表 依据习惯行为建立 没有考虑增删改的操作 关联的Excel列表 消除数据冗余 增删改的异常不存在 例如 多种产品只能放在一个仓库中 一个仓库有多个管理员 构造关系模式 仓库管理员 仓库号 库存产品号 存在数据冗余 重新构造或修改为相互关联的关系模式 仓库号 仓库管理员 仓库号 库存产品号 思考 系名 教师名 学生名 存在数据冗余吗 怎样修改或构造 实际上相互关联的关系模式 E R模型或主题数据库 记录了操作影响的单一事务或形成了一个事件 事件将多个实体关联在一起 这是业务操作型数据库系统的最为显著的特征 事件构成了事实 事实形成了主题 主题数据库 可以被度量 统计分析 理论上通过集合论的关系运算 可以得到任意统计分析结果 所以相对完美的解决方案是逆序 不要按习惯先做报表 有了问题 增删改异常及新的查询需求 才回溯 数据源表 驻留在操作型关系数据库端 中间表 驻留在操作型关系数据库端或被倒出 驻留在Excel端 统计分析表 驻留在Excel端 多维模式 OLAP 的星型和雪花模型 多维模式 由一个事实表 FactTable 和一组维表 DimensionTable 组成 每个维表都有一个维作为主键 所有这些维的主键组合成事实表的主键 事实表的非主键属性称为事实 Fact 它们一般都是数值或其他可以进行计算的数据 而维表大都是文字 时间等类型的数据 按这种方式组织好数据我们就可以按照不同的维 事实表主键的部分或全部 来对这些事实数据进行求和 summary 求平均 average 计数 count 百分比 percent 的聚集计算 甚至可以做20 80分析 这样就可以从不同的角度的数据来分析单一业务主题的事件情况当所有维表都直接连接到事实表上时 将该模型称为星型模型当有一个或多个维表没有直接连接到事实表上 而是通过其他维表连接到事实表上时 将该模型称为雪花模型 雪花模型是对星型模型的维表进一步层次化的结果 多维模式 OLAP 的基本操作类型 钻取 Drill up和Drill down Drill up是在某一维上将低层次的细节数据概括到高层次的汇总数据 或者减少维数 Drill down则相反 它从汇总数据深入到细节数据进行观察或增加新维 钻取报表是指用户通过单击其他报表中的链接打开的报表 钻取报表通常包含某原始汇总报表中所包含的某项的详细信息切取 Slice和Dice 切片 Slice 和切块 Dice是在一部分维上选定值后 关心度量数据在剩余维上的分布 如果剩余的维只有两个 则是切片 如果有三个或以上 则是切块透视 Pivot Pivot是变换维的方向 即在表格中重新安排维的放置 例如行列互换 透视是模型中跟踪不同数据集的对象的子集 通常为特定的用户组或业务方案定义透视 从而便于导航大型数据集 在透视中 表 列和度量值 包括KPI 定义为字段对象 可以选择将在各透视中包括的字段 例如 单个模型可以包含产品 销售 财务 员工和地理数据 在销售部要求产品 销售 促销和地理数据时 可能不需要员工和财务数据 同样 人力资源部门不需要与销售促销和地理有关的数据注意 请关注这些术语和英语专用词 多维模式 OLAP 的基本操作类型事例 Excel表格和关系模式 规范化表 的关系 Excel表格和Excel列表的区别MicrosoftWeb 微软官网 Excel列表注释Excel列表是Excel2003中的新增功能 列表是您可以与较大的工作表独立开来而单独进行操作的一部分工作表 这样操作对周围任何数据都没有影响 例如 可以添加数据 对数据进行排序 重新排列列表中的行 而不影响周围的单元格 Excel列表还可以加快工作速度 因为它提供了一组数据操作工具 包括 自动筛选 按钮和一组聚合函数 可以从同一位置使用这些聚合函数 同时还可以使用Excel列表从不同的外部数据源 关系模式 创建数据透视表 Pivot 右面是一个典型的Excel2003列表 是 自动筛选 按钮 该按钮会自动显示 它们所提供的功能与早期版本的Excel功能相同 您可以使用该按钮快速对数据进行排序和筛选 查找高低值等等是边框 边框将列表中的单元格与周围数据区分开是 插入 行 星号表示行 您可以直接在该行的单元格中键入数据 列表会自动调整大小以容纳新数据是 汇总 行 单击此行中的某个单元格可以从聚合函数列表中进行选择 您可以使用 列表 工具栏显示或隐藏 汇总 行 当您显示 汇总 行时 如果右侧的列中包含数字数据 Excel会将该列中的数据自动进行相加是聚合函数 选择某个函数以将其插入到 汇总 行中选定的单元格 您不必手动输入该函数 这样您可以在更短的时间内更准确地添加计算是调整手柄 通过拖动该手柄可以修改列表的大小 Excel列表与多维模式 OLAP 的关系 在Excel中 在没有连接任何OLAP数据源的情况下 也支持旋转透视表 Pivot 这算是一种非常简易的OLAP前端 微软官网 OLAP是为了提高检索数据的速度 因为在创建或更改报表时 会计算汇总值 这样就只有较少数据送到Excel中 此方法使得可以处理的源数据比使用传统数据库组织的数据更多 因为对于传统数据库 Excel必须先检索所有单个记录后 再计算汇总值 微软官网 在Excel中的OLAP和非OLAP数据透视表的区别 微软官网 Excel列表与Excel数据透视表的关系 用户可以从四种类型的数据源中来创建数据透视表 Pivot Excel数据列表 可以进行增加 删除 修改 查询 CURD 等列表操作外部数据源 比如 关系型数据库 结构良好的文件等 可以视作数据准备的结果多个独立的Excel数据列表其他的数据透