第25章-数据仓库与决策支持课件

第第2525章章 数据仓库与决策支持数据仓库与决策支持数据仓库的产生操作型处理操作型处理(OLTP)（也叫事务处理）：对数据库联机的（也叫事务处理）：对数据库联机的日常操作，通常是对一个或一组记录的查询和修改日常操作，通常是对一个或一组记录的查询和修改特点：快速响应用户请求，对数据的安全性、完整性以及事务吞吐量要求很高。分析型处理分析型处理(OLAP)：对数据的查询和分析操作，通常是：对数据的查询和分析操作，通常是对海量的历史数据查询和分析对海量的历史数据查询和分析特点：要访问的数据量非常大，查询和分析的操作十分复杂。数据仓库技术数据仓库技术为了构建新的分析处理环境而出现的一种数据存储和组织技术表17.1 操作型数据和分析型数据的区别操作型数据操作型数据分析型数据分析型数据细节细节的的综综合的，或提合的，或提炼炼的的在存取瞬在存取瞬间间是准确的是准确的代表代表过过去的数据去的数据可更新可更新不更新不更新操作需求事先可知道操作需求事先可知道操作需求事先不知道操作需求事先不知道生命周期符合生命周期符合SDLCSDLC完全不同的生命周期完全不同的生命周期对对性能要求高性能要求高对对性能要求性能要求宽宽松松一个一个时时刻操作一元刻操作一元组组一个一个时时刻操作一集合刻操作一集合事事务驱动务驱动分析分析驱动驱动面向面向应应用用面向分析面向分析一次操作数据量小一次操作数据量小一次操作数据量大一次操作数据量大支持日常操作支持日常操作支持管理决策需求支持管理决策需求数据仓库技术(续)数据仓库定义：数据仓库是一个面向主题的，集成的，时变的，非易失的数据集合，支持管理部门的决策过程。本质上和数据库一样，是长期储存在计算机内的、有组织、可共享的数据集合。数据仓库技术(续)一、数据仓库的基本特征数据仓库的数据是面向主题的；数据仓库围绕一些主题如顾客，供应商，产品和销售来组织。排除对于决策支持过程无用的数据，提供特定主题的简明视图。数据仓库的数据是集成的；构造数据仓库是将多个异构的数据源，如关系数据库，一般文件和联机事务记录集成在一起。数据仓库的数据是非易失的的；数据仓库总是物理地分别存放数据，分离的数据仓库。数据仓库的数据是时变的 数据存储从历史的角度提供信息。数据仓库中的关键结构都隐式或显式地包含时间元素。传统数据库与数据仓库的最根本的区别在于侧重点不同 数据仓库是在传统数据库的基础上发展起来的，提供良好的大规模数据分析能力，力图为决策提供有效的技术支持。比较项目比较项目 传统数据库传统数据库 数据仓库数据仓库 （1 1）总体特征）总体特征 围绕高效率的数据处理层开围绕高效率的数据处理层开以提供决策支持为目标以提供决策支持为目标 （2 2）存储内容）存储内容 以当前数据为主以当前数据为主 主要是历史的、存档的、归纳的数据主要是历史的、存档的、归纳的数据 （3 3）面向用户）面向用户 普通的业务处理人员普通的业务处理人员 高级的决策管理人员高级的决策管理人员 （4 4）功能目标）功能目标 面向业务操作，注重实时性面向业务操作，注重实时性 面向主题注重分析功能面向主题注重分析功能 （5 5）使用频率）使用频率 很高很高 较低较低 （6 6）访问特征）访问特征读读/写并重写并重 以读为主、较少写入以读为主、较少写入 （7 7）数据规模）数据规模 较小（较小（100MB-1GB100MB-1GB）较大（较大（10GB10GB以上）以上）（8 8）数据访问量）数据访问量 每次事务处理访问数据较少每次事务处理访问数据较少 每次分析处理访问大量数据每次分析处理访问大量数据 数据仓库系统的体系结构数据仓库体系结构 数据仓库系统的体系结构数据仓库的后台工具 包括数据抽取、清洗、转换、装载和维护(Maintain)工具。数据仓库服务器 相当于数据库系统中的DBMS，负责管理数据仓库中数据的存储管理和数据存取，并给OLAP服务器和前台工具提供存取接口(如SQL查询接口)OLAP服务器 透明地为前台工具和用户提供多维数据视图 OLAP服务器则必须考虑物理上这些分析数据的存储问题前台工具 包括查询报表工具、多维分析工具、数据挖掘工具和分析结果可视化工具等 25.1 决策支持简介用户决策制定时用户决策制定时1.首先根据各业务部门数据库中的数据创建数据仓库，存储各种历史信息和汇总信息2.对数据仓库的进一步应用由功能强大的分析工具来实现决策支持查询新特点：决策支持查询新特点：1.where 子句包含大量的AND OR 2.统计函数 如标准方差3.对一定时间范围内进行聚集4.用户经常提交一系列的相关查询25.1 决策支持简介三类用于决策支持的分析工具第一类：OLAP-支持分组和聚集查询，并能对各种复杂的布尔条件、统计函数和时间序列分析提供支持的系统。由上述查询组成的应用成为联机分析处理OLAP第二类：优化的关系数据库系统传统的DBMS为有效执行OLAP查询而进行了特殊设计。第三类：数据挖掘，在大量的数据集合中，找到有意义的数据趋势或模式。25.2 OLAP：多维数据模型OLAP(On-Line Analytical Processing)即联机分析处理，是以海量数据为基础的复杂分析技术 一、多维数据模型数据分析时用户的数据视图，是面向分析的数据模型，用于给分析人员提供多种观察的视角和面向分析的操作 可用这样来一个多维数组来表示：(维1，维2，维n，度量值)25.2 OLAP：多维数据模型 例如：(地区，时间，电器商品种类，销售额)n三维数组可以用一个立方体来直观地表示n一般地多维数组用多维立方体CUBE（超立方体）来表示25.2 OLAP：多维数据模型一系列数字型度量属性 measure每个度量属性由一组维来确定 dimensions多维数据模型例：度量属性 sales维 product(key:pid)Location(locid)Time(timeid)25.2 OLAP：多维数据模型例：度量属性 sales 维 product(key:pid)Location(locid)Time(timeid)将sales 信息看成一个三维数组salesLocid=1切片图 Sales:三维数组集 8101030205082515123timeidpid11121325.2 OLAP：多维数据模型按照多维数据模型的不同实现方式:1.MOLAP（多维OLAP系统）用多维数组来存储多维数据集的OLAP系统2.ROLAP（关系OLAP系统）将所有信息都转成关系的OLAP系统。25.2 OLAP：多维数据模型对于每个维，相关的一组属性之间可能具有层次关系 countystatecityyearguarterweekmonthdate25.2 OLAP：多维数据模型有关维的信息也可以表示成一组关系：locations(locid:integer,city:string,state:string,county:string)Products(pid:integer,pname:string,category:string,price:real)Times(timeid:integer,date:string,week:integer,month:integer,quarter:integer,year:integer,holiday-flag:boolean)事实表：事实表：将维和度量属性联系起来的关系称为事实表，比维表大的多Sales(pid:imteger,timeid:integer,locid:integer,sales:integer)ROLAP（关系（关系OLAP系统）系统）将所有信息都转成关系的OLAP系统。25.2 OLAP：多维数据模型ROLAP结构用RDBMS或扩展的RDBMS来管理多维数据，用关系的表来组织和存储多维数据两类表：一类是事实(fact)表，另一类是维表事实表用来描述和存储多维立方体的度量值及各个维的码值；维表用来描述维信息。ROLAP用“星形模式”和“雪片模式”来表示多维数据模型25.2 OLAP：多维数据模型星形模式（星形模式（Star Schema）通常由一个中心表(事实表)和一组维表组成 如下图所示的星形模式 星形模式的中心是销售事实表维表有时间维表、顾客维表、销售员维表、制造商维表和产品维表 图 星形模式25.2 OLAP：多维数据模型雪片模式就是对维表按层次进一步细化后形成的图 雪片模式25.2.1 OLAP 查询（1）在一个或者多个维上对某个度量属性进行聚集操作 得到总的销售总额 每个州的总销售额（2）向上综合 对某个维的不同层次汇总 例子：如果已知某个城市的总销售额，可以向上综合得到每个州的总销售额。（3）向下细化：向上综合的逆 例子：给定每个州的总销售额，可以在location维内向下细化，得到每个城市的销售额。（4）旋转操作：在选择的维上聚集 例子：在location和 time 旋转得到交叉表（5）切片：在一个或多个维上进行等值选择 切块：在一个或多个维上进行区域选择25.2.1 OLAP 查询636381811441443838107107 14514575753535110110176176 223223 399399WICATOTAL199519961997TOTAL 每年每个州总销售额 底部汇总行每个州总sales 右部汇总行每年总sales 右下角-总部销售额（4）旋转操作：在选择的维上聚集 例：在location和 time 维旋转得到交叉表，每个地址每个时间段的销售额的表格 旋转时可以结合聚集操作，下图是每个州每年的销售数据25.2.2 和SQL查询的比较SQL：1999支持多维模型的查询例：通过旋转得到的交叉表，也可以通过SQL查询集合得到相同结果SELECT SUM(S.sales)FROM Sales S,Time T,Location L WHERE S.timeid=T.timeid AND S.locid=L.locid GROUP BY T.year,L.state/产生表格主体部分的条目SELECT SUM(S.sales)FROM Sales S,Time T WHERE S.timeid=T.timeid GROUP BY T.year/表格右部汇总行SELECT SUM(S.sales)FROM Sales S,Location L WHERE S.locid=L.locid GROUP BY L.state/表格底部汇总列数据SELECT SUM(S.sales)FROM Sales S,Location L WHERE S.locid=L.locid/右下角累计*如果在K个维上旋转，则产生2K个SQL查询CUBE操作SQL：1999扩展了GROUP BY 操作，带关键字CUBE的GROUP BY从句相当于一组GROUP BY语句，其中每一个GROUP BY 对应K维的一子集例：SELECT T.year,L.state,SUM(S.sales)FROM Sales S,Time T,Location L WHERE S.timeid=T.timeid AND S.locid=L.locid GROUP BY CUBE(T.year,L.state)结果是前面交叉表格CUBE操作CUBE pid,locid,timeid BY SUM sales在集合pid,locid,timeid的八个不同于集合（包括空集合）上对Sales表进行向上综合操作。等价于下面8个SQL语句：SELECT SUM(S.sales)FROM Sales S GROUP BY group-list pid,locid,timeid pid locid pid timeid locid timeid pid locid timeidCUBE查询中各分组查询的网格表示25.3 OLAP实现技术新的索引技术位图索引连接索引25.3.1 位图索引Customers(custid,name,sex,rating)Sex custid name sex rating rating1010101001011010112112JoeJoeMM3 3115115RamRamMM5 5119119SueSueF F5 5112112WooWooMM4 4001000010000001000010000100001000100001025.3.1 位图索引稀疏属性含少量取值的属性 例：sex，2个值；rating，15的整数位图索引思想：用位序列表示稀疏属性的值，每个可能的取值需要用一个位来表示。属性的这组位向量称属性的位图索引。优点：使用位操作高效地完成查询。例：找出有多少男性客户的rating值为5 将属性sex的第1个位向量和属性rating第5个位向量相与 将结果向量中的1进行计数得到结果25.3.2 连接索引例1：Customer(custid)和Purchases(rid)通过属性custid进行联接 创建集合 C表示Customers中custid为C的记录 P表示Purchases中rid为P的记录 两个记录满足连接条件可以进行连接例2：星型模式：事实表总是要和若干个维表进行连接 Sales,Products,Times,Locations连接 连接索引 S,P,t,l是满足连接条件的元组 问题：连接索引的数目会增长很快25.4 SQL:1999中的查询*下面的查询使用传统的SQL-92很难表达：查询每种产品的月销售额的变化的百分比；（）查询总销售额最高的5种产品；()跟踪n天的平均销售额变化；（对于每一天算出前后n天的平均日销售额）（）SQL:1999的窗口子句，可以表达上面查询The WINDOW ClauseThe WINDOW ClauseSELECT L.state,T.month,AVG(S.sales)OVER W AS movavgFROM Sales S,Times T,Location LWHERE S.timeid=T.timeid AND S.locid=L.locidWINDOW W AS(PARTITION BY L.state ORDER BY T.month RANGE BETWEEN INTERUAL 1 MONTH PRECEDING AND INTERUAL 1 MONTH FOLLOWING)处理FROM和WHERE子句生成临时表TempTemp按照PARTITION BY子句被分区（和GROUP BY相似，但是分区中每一行都产生一个结果行，不是每个分区产生一个结果行）分个分区中的行是根据T.month排序的为分区中的每一行建立窗口边界。（上例：每一行的窗口包含该行本身和所有的month值加1和减1之间的行。即month值为6月的行的窗口包含所有month值为5，6，7的行）计算分区中每行的聚集函数，首先要确定它的窗口，然后计算窗口中行的指定的聚集函数。考虑某个州的某一行数据，给出在特定时间某种特定商品的销售额；该行的窗口包括所有描述某个州中本月、前一个月和下一个月的销售额的行，movage是这段时间的销售额的平均值25.5 快速得到查询结果应用趋势：查询处理的重点尽快给用户提供最初的部分查询结果，或者是最好的部分查询结果对用户提交的复杂查询，希望能尽快得到近似的查询结果，然后再进一步精练处理直到最后得到精确查询结果。25.5.1 得到前N个结果的查询得到销售额排在前面的少量商品传统方法先对所有商品按销售额排序，按这个顺序返回结果;如果商品种类很多，代价太高DBMS允许用户指定需要多少条结果记录，据此优化执行 SELECT P.pid,P.name,S.sales FROM Sales S,Products P WHERE S.pid=P.pid AND S.locid=1 AND S.timeid=3 ORDER BY S.sales DESC OPTIMIZE FOR 10 ROWS25.5.1 得到前N个结果的查询假设能根据Sales上的直方图得到销售额的分布情况，然后选定只有排在前十位的商品的销售额才能达到的值C。增加选择条件salesC SELECT P.pid,P.name,S.sales FROM Sales S,Products P WHERE S.pid=P.pid AND S.locid=1 AND S.timeid=3 AND S.salesC ORDER BY S.sales DESC效果取决于是否能精确估计出域值C25.5.2 联机聚集（online aggregation）按州得到平均销售额的查询SELECT L.state,AVG(S.sales)FROM Sales S,Location LWHERE S.locid=L.locidGROUP BY L.state当关系Sales和Locations规模较大时，查询执行代价很高传统查询总是需要返回完整的查询结果，因此响应速度慢25.5.2 联机聚集（online aggregation）联机聚集收到用户提交查询后，尽可能快的返回一个近似的查询结果，随着计算的继续，不断对查询结果进行更新，这种方法叫联机聚集。用统计技术和样品元组聚集代替简单扫描聚集表，我们能给出一个范围。例如：“Alabama州的平均销售额是 5232.5103.4 可信度97”。阻塞算法必须在处理完全部的输入记录后，才能产生输出结果的算法为实现联机聚集，DBMS需要把统计技术与非阻塞算法结合起来。联机聚集方法：状状态态优优先先级级州州平均平均销销售售额额可信度（）可信度（）区区间间Alabama Alabama 5232.5 5232.5 9797103.4 103.4 Alaska Alaska 2832.5 2832.5 9393132.2 132.2 Arizona Arizona 6432.5 6432.5 989852.3 52.3 Wyoming Wyoming 4243.5 4243.5 9292152.3 152.3 进度条当前计算出的销售额和准确值还有多远优先权是否优先计算相应州的平均销售额，可改变优先权，优先级高，计算快，可信度大。有97的可能性位于区间5232.5-103.4,5232.5+103.4为实现联机聚集，DBMS需要把为近似查询提供可信区间的统计技术与关系操作的非阻塞算法、流水线并行技术结合起来。小结：决策支持正在快速发展 决策支持包括对当前和历史数据进行分析以 得到发展趋势，产生汇总数据等等。数据仓库技术是随着人们对大型数据库系统研究的不断深入，在传统数据库技术基础之上发展而来，主要目的是为决策提供支持。数据仓库提供来自种类不同的信息系统的集成化和历史化的信息，为有关部门或企业进行全局范围的战略决策和长期趋势分析提供了有效支持。数据仓库使用尖端分析技术：复杂SQL查询和OLAP多维查询数据库设计新技术：支持索引、视图维护、交互查询 数据挖掘技术一、数据挖掘的概念概念：数据挖掘是从大量数据中发现并提取隐藏在内的、人们事先不知道的但又可能有用的信息和知识的一种新技术目的：帮助决策者寻找数据间潜在的关联，发现经营者被忽略的要素数据挖掘技术涉及数据库技术、人工智能技术、机器学习、统计分析等多种技术决策支持系统(DSS)跨入了一个新阶段 数据挖掘技术(续)二、数据挖掘和传统分析方法的区别 本质区别：数据挖掘是在没有明确假设的前提下去挖掘信息，发现知识 数据挖掘所得到的信息应具有事先未知、有效和可实用3个特征 数据挖掘技术(续)三、数据挖掘的数据源 从数据仓库中来优点：许多数据不一致的问题都较好地解决了，在数据挖掘时大大减少了清理数据的工作量 缺点：建立数据仓库是一项巨大的工程，耗时耗力从数据库中来如果只是为了数据挖掘，可以把一个或几个OLTP数据库导入一个只读的数据库中，然后在上面进行数据挖掘数据挖掘技术(续)四、数据挖掘的功能概念描述关联分析分类和预测聚类孤立点检测趋势和演变分析数据挖掘技术(续)数据挖掘步骤明确挖掘任务对所选择数据进行预处理选择具体的算法进行挖掘对挖掘出的模式进行评价消减重复部分展现最终的结果典型的数据挖掘系统的体系结构小结数据仓库用于数据的存储和组织OLAP集中于数据的分析数据挖掘则致力于知识的自动发现 将它们结合起来就成为一种新的DSS(Decision Support System)构架，成为商务智能(Business Intelligence，BI)的3个支柱 即：DWOLAPDM支持BI的可行方案25.6 视图和决策支持视图：1 简化应用程序 视图，实际上存在的只是它的脚本，内容并不真正存在。对复杂的应用程序从功能角度进行分析，将可以与其它的应用程序共用的那一部分分离出来。对这部分功能，视具体情况可作成不同的数据库实体（如过程），有些可以作成视图。上层应用程序可以从视图中取数据。2实现一定的权限控制 可根据需要对表中的一部分记录作成一个视图 可对表中的一部分字段做一个视图,二者兼而有之,以供一定的角色使用25.6.1 视图 OLAP和数据仓库视图技术和OLAP应用以及数据仓库的关系非常密切。OLAP查询查询一般都是聚集查询视图实体化方法：视图实体化方法：将事先定义好的视图计算出来并进行存储，当用户提交的查询和视图相关时，直接使用前面计算出的结果。CUBE操作操作是聚集查询的集合。优化方法思想：首先选择将聚集查询的某个子集实体化，在进行CUBE操作时，利用前面形成的实体化视图，并进行一些额外的计算来完成操作。数据仓库可以简单地看成一系列异步复制的关系表和需要周期性进行维护的视图。25.6.2 视图上的查询 视图：视图：计算得到每个category和state的产品的销售额 CREAT VIEW RegionalSales（category，sales，state）AS SELECT P.category,S.sales,L.state FROM Products P,Sales S,Locations L WHERE P.pid=S.pid AND S.locid=L.locid 查询：查询：每个category和state的总销售额 SELECT R.category,R.state,SUM(R.sales)FROM RegionalSales R GROUP BY R.category,R.state 修改查询：修改查询：用视图定义替换 RegionalSales SELECT R category,R.state,SUM(R.sales)FROM (SELECT P.category,S.sales,L.state FROM Products P,Sales S,Loctions L WHERE P.pid=S.pid AND S.Locid=L.locid)AS R GROUP BY R.category,R.state*用户提交的基于视图的查询可用查询修改技术来实现。如果基表在远程数据库，查询修改方法将变的不可行。25.7 视图实体化视图实体化方法 将定义好的视图计算出来并进行存储。当用户提交的查询和视图相关时，直接使用前面计算出的结果。实体化的视图可以与基表一样使用。可以在上面创建索引以加快查询速度。缺点 当关系表数据更新时，需要对相关的实体化视图进行维护。25.7.1 视图实体化假如视图RegionalSales已经实体化，并且以 category，state，sales 作为B+树索引码进行存储。则只对索引进行扫描就可回答查询 SELECT R.state,SUM(R.sales)FROM RegionalSales R WHERE R.category=Laptop GROUP BY R.state 处理上面查询，首先使用实体化视图上的索引得到category=Laptop的第一个索引叶子结点，然后在叶子层次进行顺序扫描，直到category的值不等于Laptop为止。25.7.1 视图实体化25.8 实体化视图的维护实体化视图一个视图的元组存储在数据库中叫实体化提供快速存取当基表发生变化时需要维护视图理想的，需要一个增加的视图维护算法 刷新 当视图依赖的表发生变化时，使实体化 视图与之一致称为对实体化视图的刷新 维护 刷新的过程称为维护25.8 实体化视图的维护考虑：（1）当基本表改变时如何刷新视图？如何进行视图的增量维护，即当基本表改变时不需要 从头计算视图。如何在分布环境下，如数据仓库中维护视图（2）什么时候进行视图的刷新？视图维护的二个步骤：传播：当数据库发生变化时改变视图 刷新：根据数据的变化实体化视图表25.8 实体化视图的维护视图的维护策略 立即的方法：当基本表改变时简单地重新计算视图；把刷新作为修改数据表事物的一部分。实体化视图总是一致的,更新操作慢延迟的方法：刷新单独作为一个事物，稍后执行 实体化视图不一致，可以维护多个视图（没有缓慢的更新）25.9 视图的增量维护增量更新增量更新 注意实体化视图中某个特定行可能出现多次，与视图计算过程中该行的生成次数有关。增量维护算法增量维护算法 有效计算视图行的变换，包括新增的行和行的计算的改变。如果某行的计算值变为0，就将该行从视图中删除。视图的增量更新视图的增量更新 开销只与发生变化的多少成比例，不需要完全从新计算25.9 视图的增量维护例：投影产生的视图 V=（R）视图V是由关系R上的投影结果V中的每行有一个相关计数，它的值为R中投影后得到的结果为该行的行数。向R中插入一个行集合Ri，增量更新包括：计算（Ri）并将结果加入V 如果V中已存在该行，则将V中行的计数加上新得到的行数。删除一行集合Rd 计算（Rd）并将结果从V中删除即将V中对应行的计数减去该行的删除的行数。25.9 视图的增量维护例：Salespidtimedlocidsales1111 2511218113115132110133110113210113215V=sales（Sales）25 18 115 210 3如果从Sales中删除一行Sales=10的行 V中值10对应计数变为2如果从Sales中插入Sales为99的一行 V中出现值为99的新行如果从Sales中插入Sales为8的一行 V中值8对应的计数为225.9 视图的增量维护 视图维护策略决定什么时候进行视图更新？(1)立即的视图维护策略 视图可以在更新基本表的同一事物内进行更新 缺点：由于更新而减慢速度，一个基本表上定义的视图越多视图更新对更新操作的影响就越大。(2)延时的视图维护策略 在更新操作后，进行视图更新。将更新内容保存 在日志中，以便以后对视图进行更新。懒惰更新 在对视图V进行查询时，如果V与基本表不一致则更新。缺点：影响查询速度。周期更新 周期性地更新实体化视图。例：每天一次 缺点：视图上的查询结果和基本表不一致，要进行性能与一致性平衡。强制更新 在基本表的改变达到了一定程度时对视图进行更新。