-
数据存储
-
数据处理
-
数据分析
本文从BI生成 sql 的原理解析入手，结合实际项目中报表设计优化的典型案例，系统讲解了BI分析表设计中性能攸关的设计要素。
一张分析单主题表的典型sql语句如下：
SELECT id,avg(sal) avgsal
FROM table
WHERE id<>"0000"
GROUP BY id
Having avg(sal)>5000
ORDER BY avgsal
这段语句中的粗黑体是sql语句的保留关键字，他们是sql语句的主体骨架，蓝色部分是查询统计的具体数据库表、字段、过滤条件、分组和排序依据；将蓝色部分"翻译"成BI分析表中的设计元素后，sql语句如下：
SELECT 表元表达式
FROM 主题表
WHERE 过滤条件 （含表元、维表元、主题集过滤条件及数据期条件）
GROUPBY 维表元（带下钻级次）
HAVING 结果集过滤
ORDERBY 排序依据
可以很清楚地看到，报表工程师们在制作分析表，设计报表和表元的各种属性时，其实是在间接地编写着BI分析报表的sql；因此BI中sql语句性能的好坏，报表工程师有很大的决定权。
如果分析表的数据来源于多个主题，BI分析表的sql需要将多张主题表连接；典型的sql有两种：
......
(SELECT 表元表达式
FROM 主题表1
WHERE 过滤条件（含表元、维表元、主题集过滤条件及数据期条件）
GROUPBY 维表元 （带下钻级次）
) T1
JOIN （支持INNER JOIN、RIGHT JOIN、LEFT JOIN、UNION ALL）
(SELECT 表元表达式
FROM 主题表2
WHERE 过滤条件（含表元、维表元、主题集过滤条件及数据期条件）
GROUPBY 维表元（带下钻级次）
)T2
ON T1.维表元字段 = T2.维表元字段 ......
红色部分是相对单主题sql新增加的典型元素；其中join方式可以是内连接、左连接、右连接、全连接，具体是哪种取决于主题集属性中的OLAP连接计算规则，如图：
有些分析需要在两个主题的明细数据上进行运算和关联，此时不得不在sql中将两个主题表先连接再分组统计；其典型的sql如下：
......
SELECT主题表1. 表元表达式1 ，主题表1. 表元表达式2 ,主题表2. 表元表达式1
FROM 主题表1
JOIN（支持INNER JOIN、RIGHT JOIN、LEFT JOIN、UNION ALL）
主题表2
ON主题表1.关联字段1=主题表2.关联字段1...
WHERE 过滤条件（含表元、维表元、主题集过滤条件及数据期条件）
GROUPBY 维表元（带下钻级次） ......
从表面上看，此处的红色部分新增元素好像和【先分组后连接】差不多，是不是两种sql性能相当呢？
让我们一起考虑一下这样的场景，主题表A和B均有10w行明细数据，需要按行业大类（20行）统计来自A和B的某些指标；
【先连接后分组】需要首先将两个10w行规模的明细表join起来，之后再按行业分组；
【先分组后连接】会分别在主题A和主题B上分组后再按行业大类join，此时连接的仅仅只是两个20行的子表；性能相比，孰优孰劣，一目了然！
【先连接后分组】方式下，主题明细数据表连接的方式由报表设计中的什么元素决定呢？请看下图，是主题集属性中的表关联设置：
查看分析表的【详细信息】，有时我们会发现为了计算一张分析表，BI生成了多个子sql嵌套和连接，或者干脆是多个独立的sql，为什么会这样呢？
要弄清这个问题，咱们得先回头再看看之前提到的sql语句：
SELECT表元表达式
FROM 主题表
WHERE 过滤条件（含表元、维表元、主题集过滤条件及数据期条件）
GROUPBY 维表元（带下钻级次）
HAVING 结果集过滤
ORDERBY 排序依据
一个这样的Sql语句可以查询出满足where子句条件的一批指标，如果有指标虽然来自同一个主题，但过滤条件不一样，还能用这一个sql一并统计出来吗？
假设现在需要统计主题表1中的多个指标，对zb1，zb2zbm的统计sql如下：
SELECT zb1，zb2...zbm
FROM 主题表1
WHERE filter1
主题表1的【zbn】也需要统计分析，其过滤条件是filter2，和其他指标zb1，zb2都不一样，那么对zbn的统计能和zb1,zb2等指标一起放在一个sql中吗？
答案很显然，不能！否则算出来的zbn就是filter1过滤后的统计值，而不是分析需求filter2的。
因此，如果在BI分析表上我们为表元zbn设置了特殊的过滤条件，BI的sql一定需要单独为这个指标生成一个子sql，比如：
SELECT zbn
FROM 主题表1
WHERE filter2
在sql元素中，除了过滤条件之外，还有连接方式的不统一也会造成BI生成多个子sql。正常情况下，假设A和B主题表连接分析的sql如下：
SELECT主题表A.zb ...主题表B.zb1 ，主题表B.zb2
FROM 主题表A INNERJ OIN 主题表B
ON主题表A.关联字段1=主题表B.关联字段1
若将B的zb1，zb2设置为左连接，将B的zb3,设置成右连接，由于连接方式不同，无论是【先分组后连接】还是【先连接后分组】，这两张主题表的连接都必须按指标的连接方式拆分为两次join；如下：
（SELECT主题表A.zb ，主题表B.zb1 ，主题表B.zb2
FROM 主题表A LEFT JOIN 主题表B
ON 主题表A.关联字段1=主题表B.关联字段1 ）
和
（SELECT主题表A.zb，... 主题表B.zb3
FROM 主题表A
RIGHTJOIN 主题表B
ON 主题表A.关联字段1=主题表B.关联字段1 ）
如果在报表设计上设置了多个独立的分析区，BI会为每个分析区单独生成独立的sql语句。因此我们需要注意，多个分析区真有必要独立分开吗？如果可以合并到一起，BI会尽可能用一个sql提交数据库处理，在某些场景下，特别是对数据量巨大的主题表而言，两次查询统计变成一次，性能上将会有不小的提升。
|
2011-08-02 17:14:10执行查询耗时1秒203毫秒。（内存情况：FREE=134.9M TOTAL=213.9M MAX=910.2M） |
你一定很熟悉这个方框中的信息模式吧？这是BI中一张分析表的详细信息，但是==，为什么在一个分析报表的详细信息中会看到如此多不同报表的sql生成和计算日志？ 原来这张分析表使用了跨表取数，为了计算这张报表，系统先触发执行了其他报表的sql，具体详情请参考《tips201106》；这种情形下，虽然本表没有产生多个sql，但是间接地通过计算其他表，也执行了多个独立的sql，造成本表计算时间超长。
作为一个报表工程师，在设计报表的过程中，有哪些和sql性能相关的要素是我们可以调控，需要特别关注的呢？基于以上对BI分析表sql的理解，报表工程师们可着重关注如下要素： 1.过滤条件 2.连接模式 3.Sql个数 4.Sql数据量
下面将分别从这几个方面具体谈谈如何优化BI分析报表的设计。
BI分析表中的过滤条件在报表计算时都会转换成SQL语句中的WHERE条件，在大数据量的情况下，WHERE条件不够优化，会直接导致SQL语句运行效率低下，最直接的表现就是SQL语句执行时没用到索引或者用到的索引不够好。 我们都知道索引在rolap数据仓库中至关重要，好的索引对查询统计分析的性能提升具有不可替代的作用。因此，一个好的where条件会尽可能充分利用好的索引，决不会破坏数据查询走索引的可能。 BI报表的主题集、维表元、指标表元上都可以设置数据期条件和过滤条件，报表工程师们在这些过滤条件中编写的表达式都将直接决定报表sql语句中where子句的质量；什么样的过滤能构成一个质量上乘的where子句？什么样的过滤一定会造成where子句效率的损失？我们在编写BI报表过滤条件时又该注意哪些问题呢？
