BI项目技术总结(2)
----DW设计
1 数据仓库模型设计
注意事项：本文的所有SQL脚本都经过测试，如有问题请随时联系沟通。
1. 设计模型图例
星型设计模式雪花型设计模式维表1(Dim)维表2(Dim)维表1(Dim)维表2(Dim)事实表(Fct)事实表(Fct)维表3(Dim)维表3(Dim) 2. 设计模式对比
? 星型设计
优点：使用星型结构，使维度和事实表的关系简单、清晰，且计算没有冗余。 缺点：前期数据仓库设计要求高，合理规划事实表与维表的星型结构。 ? 雪花型设计
优点：数据仓库前期的设计要求不高，易于边实施边设计。
缺点：使用雪花型结构，使维度与事实表的关系复杂，且出现多表关联的冗余，降低sql的运行效率。
2 数据仓库设计注意事项
以星型设计模式为例简单阐述数据仓库设计的注意事项。 ? 维表设计注意事项（Dim）
1) 维度设计原则：以事实表为中心，维度表确保星型连接优化；
2) 使用整型键列连接事实表和维度表，可以最低占用内存，便于压缩，优化查询； 3) 避免主外键约束，在ETL过程中提高数据库执行效率； 4) 在Loopup所使用的业务键上建立聚集索引： – 业务键不作维度表主键 – 可以提升Loopup速度；
5) 在维度主键、其他常用列上建立非聚集索引； 6) 不建议对维度表分区。 ? 事实表设计注意事项（Fct）
1) 一般在事实表的维度列上创建聚集索引，支持对指定的维度提取事实表的业务数据； 2) 在其他常用列上建立非聚集索引，
3) TempDB的大小与事实表相匹配，以Oracle为例，一般建议TempDB大小为事实表的20%
左右（仅供参考）；
4) 对大表进行分区，一般在日期列或者地区代码列上建立分区索引 – 易于新增和删除数据 – 易于新增和删除分区 – 提高数据执行效率
– 易于数据管理：恢复、备份等； 5) 日期型数据建议用整型格式表示(20090215) – 作为索引值可以提高数据库执行效率 – 易用于条件查询
3 数据仓库模式选择
对于BI项目，一般数据量比较大，会达到千万级、亿级的数据记录。这就要对事实表设计时既要考虑数据的粒度，同时要兼顾系统的运行效率。 目前数据仓库主要设计模式如下：
1) 关系型OLAP(ROLAP)表示基于关系数据库的OLAP实现（Relational OLAP）；
2) 多维型OLAP(MOLAP) 基于多维数据存储的在线分析处理，MOLAP服务器提供数据存储管理，一
般是放在物理的Cube当中，多维数据在存储中将形成\立方块（Cube）\的结构,在MOLAP中对\立方块\的\旋转\、\切块\、\切片\是产生多维数据报表的主要技术。 数据仓库模式总结
1、 由于数据仓库数据量大，如果简单使用关系型OLAP，会严重影响系统运行效率，一般客户
无法接受。可以在关系型模式保存业务明细数据的同时，根据KPI指标对明细业务数据提前、定时预处理生成一定粒度的汇总表，为前台的报表展现高效提供汇总数据即相当于在关系数据库生成OLAP。
2、 Oracle数据仓库提供多维型数据生成立方体，可以方便的提供旋转、切块等多维报表技术；
同时提供的关系型数据库功能也可以下钻到明细数据。
4 性能调优
1. 性能参数调优（以Oracle9为例） ? 系统参数设置
Oracle的内存参数主要是SGA（共享池：Shared Pool、缓冲区高速缓存：Database Buffer Cache、大型池：Large Pool、 Java池：ava Pool）和PGA（Program Global Area）。 ? 共享池
用于缓存最近被执行的SQL语句和最近被使用的数据定义。
主要包括：Library cache（共享SQL区）和Data dictionary cache（数据字典缓冲区）; 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息; ? 缓冲区高速缓存
用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能；
? 大型池
是SGA中一个可选的内存区域，它只用于shared server环境。
? Java池
Java Pool为Java命令的语法分析提供服务。
? PGA
是为每个连接到Oracle database的用户进程保留的内存。
根据项目实践，系统默认的参数设置偏小，需要调整。如果数据量达到百万级必须调整，
会明显提高系统效率。 ? 调整的原则
SGA+PGA<系统内存总量-操作系统-其他应用程序所需内存。 PGA一般能够满足业务需求，不必调整；
如果扩大SGA的各项参数，数据仓库SGA中的各项参考比例如下： 共享池：缓冲区高速缓存：大型池：Java池=2：7：0.5：0.5。 ? 注意事项
SGA的分配比例仅供参考。 2. 数据库常用优化方式 ? 表空间设计
1） UNDO、TEMP、索引表空间与数据表空间分别独立创建；
2） 业务数据频繁增删改操作需要扩大UNDO表空间，UNDO空间偏小，导致空间不足； 3） 查询主要依赖TEMP表空间，建议根据业务数据量大小的20%创建TEMP表空间。 ? 归档模式
OLAP业务系统数据的处理以查询为主，增删改的业务操作很少，建议把归档模式设置为：非归档(NOARCHIVELOG)。对于业务数据的操作不必写入日志文件，可以避免日志文件的迅速膨胀而占用空间。
? SQL语句优化(以Oracle为例)
1） WHERE子句中的连接顺序
ORACLE采用自下而上（自右而左）的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其它WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
低效：SELECT * FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
高效：SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’; 2） 删除全表时用TRUNCATE替代DELETE
当DELETE删除表中的记录时，有回滚段(rollback segments ) 用来存放可以被恢复的信息，而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息，所以执行时间也会很短。同时需要注意TRUNCATE只在删除全表时适用，因为TRUNCATE是DDL而不是DML。
3） 尽量多使用COMMIT
在系统中对每个DELETE、INSERT和UPDATE操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放UNDO表空间而大大提高。 4） 用EXISTS替代IN、=
在许多查询中，为了满足一个条件往往需要对另一个基础表进行连接，例如查询销售信息往往需要连接企业信息表，在这种情况下，使用EXISTS而不用IN将提高查询的效率，这样就不会对企业信息表进行全表扫描，只要找到符合条件的企业信息就停止扫描企业信息表。
低效：SELECT * FROM PERSONS A WHERE A.PERSONID IN(SELECT B.PERSONID FROM EMP B); 高效：SELECT * FROM PERSONS A WHERE EXISTS(SELECT B.PERSONID FROM EMP B WHERE B.PERSONID = A.PERSONID); 5） 用NOT EXISTS替代NOT IN、<>
NOT IN子句将执行一个内部的排序和合并，对子查询中的表执行了一个全表遍历，降低系统运行效率，而NOT EXISTS则避免了子查询表的全表遍历的额外工作。
6） 优化group by，提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。
低效： SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效： SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB 7） 有条件的使用UNION-ALL 替换UNION
使用UNION-ALL 替换UNION的前提是：所连接的各个表中无主关键字相同的记录，因为UNION ALL 将重复输出两个结果集合中相同记录。当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后对结果进行排序。如果用UNION ALL替代UNION， 这样排序就不是必要了，效率就会因此得到提高。
? 索引的应用
1） 为事实表创建索引, Oracle提供了一些针对数据仓库应用的索引,例如：位图索引(bitmap index)、位图连接索引(bitmap join index)、 分区索引(partitioned index)等。 2） 对于经常多表关联查询建议创建聚集索引； 3） 使用指定的索引
BI项目总结_DW
