首页 > 服务 > 自助服务 > 知识库 > BI@Report > 高阶应用 > 系统优化

让BI报表快起来

灵巧的设计,让BI更轻快

本文从BI生成 sql 的原理解析入手,结合实际项目中报表设计优化的典型案例,系统讲解了BI分析表设计中性能攸关的设计要素。

1. 理解BI分析表的sql

1.1 单主题sql

一张分析单主题表的典型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因此BIsql语句性能的好坏,报表工程师有很大的决定权。

1.2 多主题sql

如果分析表的数据来源于多个主题,BI分析表的sql需要将多张主题表连接;典型的sql有两种:

1.2.1 先分组后连接

......

(SELECT 表元表达式

FROM 主题表1

WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)

GROUPBY 维表元 (带下钻级次)

) T1

JOIN (支持INNER JOINRIGHT JOINLEFT JOINUNION ALL

(SELECT 表元表达式

FROM 主题表2

WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)

GROUPBY 维表元(带下钻级次)

)T2

ON T1.维表元字段 = T2.维表元字段 ......

红色部分是相对单主题sql新增加的典型元素;其中join方式可以是内连接、左连接、右连接、全连接,具体是哪种取决于主题集属性中的OLAP连接计算规则,如图:

1.2.2 先连接后分组

有些分析需要在两个主题的明细数据上进行运算和关联,此时不得不在sql中将两个主题表先连接再分组统计;其典型的sql如下:

......

SELECT主题表1. 表元表达式1 ,主题表1. 表元表达式2 ,主题表2. 表元表达式1

FROM 主题表1

JOIN(支持INNER JOINRIGHT JOINLEFT JOINUNION ALL

主题表2

ON主题表1.关联字段1=主题表2.关联字段1...

WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)

GROUPBY 维表元(带下钻级次) ......

从表面上看,此处的红色部分新增元素好像和【先分组后连接】差不多,是不是两种sql性能相当呢?

让我们一起考虑一下这样的场景,主题表AB均有10w行明细数据,需要按行业大类(20行)统计来自AB的某些指标;

【先连接后分组】需要首先将两个10w行规模的明细表join起来,之后再按行业分组;

【先分组后连接】会分别在主题A和主题B上分组后再按行业大类join,此时连接的仅仅只是两个20行的子表;性能相比,孰优孰劣,一目了然!

【先连接后分组】方式下,主题明细数据表连接的方式由报表设计中的什么元素决定呢?请看下图,是主题集属性中的表关联设置:

1.3 为什么会多sql?

查看分析表的【详细信息】,有时我们会发现为了计算一张分析表,BI生成了多个子sql嵌套和连接,或者干脆是多个独立的sql,为什么会这样呢?

要弄清这个问题,咱们得先回头再看看之前提到的sql语句:

SELECT表元表达式

FROM 主题表

WHERE 过滤条件(含表元、维表元、主题集过滤条件及数据期条件)

GROUPBY 维表元(带下钻级次)

HAVING 结果集过滤

ORDERBY 排序依据

一个这样的Sql语句可以查询出满足where子句条件的一批指标,如果有指标虽然来自同一个主题,但过滤条件不一样,还能用这一个sql一并统计出来吗?

1.3.1 过滤条件不统一

假设现在需要统计主题表1中的多个指标,对zb1zb2zbm的统计sql如下:

SELECT zb1zb2...zbm

FROM 主题表1

WHERE filter1

主题表1的【zbn】也需要统计分析,其过滤条件是filter2,和其他指标zb1zb2都不一样,那么对zbn的统计能和zb1,zb2等指标一起放在一个sql中吗?

答案很显然,不能!否则算出来的zbn就是filter1过滤后的统计值,而不是分析需求filter2的。

因此,如果在BI分析表上我们为表元zbn设置了特殊的过滤条件,BIsql一定需要单独为这个指标生成一个子sql,比如:

SELECT zbn

FROM 主题表1

WHERE filter2

1.3.2 连接方式不统一

sql元素中,除了过滤条件之外,还有连接方式的不统一也会造成BI生成多个子sql。正常情况下,假设AB主题表连接分析的sql如下:

SELECT主题表A.zb ...主题表B.zb1 ,主题表B.zb2

FROM 主题表A INNERJ OIN 主题表B

ON主题表A.关联字段1=主题表B.关联字段1

若将Bzb1zb2设置为左连接,将Bzb3,设置成右连接,由于连接方式不同,无论是【先分组后连接】还是【先连接后分组】,这两张主题表的连接都必须按指标的连接方式拆分为两次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

1.3.3 多个独立的分析区

如果在报表设计上设置了多个独立的分析区,BI会为每个分析区单独生成独立的sql语句。因此我们需要注意,多个分析区真有必要独立分开吗?如果可以合并到一起,BI会尽可能用一个sql提交数据库处理,在某些场景下,特别是对数据量巨大的主题表而言,两次查询统计变成一次,性能上将会有不小的提升。

1.3.4 跨表取数

2011-08-02 17:14:10执行查询耗时1203毫秒。(内存情况:FREE=134.9M TOTAL=213.9M MAX=910.2M
2011-08-02 17:14:10构造结果表耗时28毫秒。
2011-08-02 17:14:10计算"ZXDZB_QS全市"耗时1232毫秒。(内存情况:FREE=134.9M TOTAL=213.9M MAX=910.2M
2011-08-02 17:14:10执行查询耗时1961毫秒。(内存情况:FREE=132.9M TOTAL=213.9M MAX=910.2M
2011-08-02 17:14:10构造结果表耗时11毫秒。
2011-08-02 17:14:10计算"ZXDZB_TS庭室"耗时1973毫秒。(内存情况:FREE=132.9M TOTAL=213.9M MAX=910.2M
2011-08-02 17:14:10执行查询耗时1184毫秒。(内存情况:FREE=132.9M TOTAL=213.9M MAX=910.2M
2011-08-02 17:14:10构造结果表耗时16毫秒。
2011-08-02 17:14:10计算"ZXDZB_RY人员"耗时1201毫秒。(内存情况:FREE=131M TOTAL=213.9M MAX=910.2M

你一定很熟悉这个方框中的信息模式吧?这是BI中一张分析表的详细信息,但是==,为什么在一个分析报表的详细信息中会看到如此多不同报表的sql生成和计算日志? 原来这张分析表使用了跨表取数,为了计算这张报表,系统先触发执行了其他报表的sql,具体详情请参考《tips201106》;这种情形下,虽然本表没有产生多个sql,但是间接地通过计算其他表,也执行了多个独立的sql,造成本表计算时间超长。

1.4 决定性能的sql要素

作为一个报表工程师,在设计报表的过程中,有哪些和sql性能相关的要素是我们可以调控,需要特别关注的呢?基于以上对BI分析表sql的理解,报表工程师们可着重关注如下要素: 1.过滤条件 2.连接模式 3.Sql个数 4.Sql数据量

下面将分别从这几个方面具体谈谈如何优化BI分析报表的设计。

2. 设计要素之1--过滤条件

BI分析表中的过滤条件在报表计算时都会转换成SQL语句中的WHERE条件,在大数据量的情况下,WHERE条件不够优化,会直接导致SQL语句运行效率低下,最直接的表现就是SQL语句执行时没用到索引或者用到的索引不够好。 我们都知道索引在rolap数据仓库中至关重要,好的索引对查询统计分析的性能提升具有不可替代的作用。因此,一个好的where条件会尽可能充分利用好的索引,决不会破坏数据查询走索引的可能。 BI报表的主题集、维表元、指标表元上都可以设置数据期条件和过滤条件,报表工程师们在这些过滤条件中编写的表达式都将直接决定报表sql语句中where子句的质量;什么样的过滤能构成一个质量上乘的where子句?什么样的过滤一定会造成where子句效率的损失?我们在编写BI报表过滤条件时又该注意哪些问题呢?

2.1 杜绝在指标列上使用函数

联系
电话

您好,商务咨询请联系

咨询热线:400-0011-866转0

手机咨询:137-0121-6790

社区
交流

产品技术问题交流

bbs.esensoft.com

9分钟快速处理问题