环境:BIEE 11.1.1.7...
背景:
物理表有一指标列,然后用filter在前端过滤了A\B\C\D\E 5个列,然后用filter的A+B+C+D+E做了汇总展示(A\B\C\D\E之和不等于指标列之和),时间维展示有Qx,Hx,Year,期间还有币种切换,场景切换,其它维度关联,其它指标展示等等
用union all汇总成结果集。
分析生成的物理SQL中包含了N长的and or。。自然造成性能问题,优化之。
1、看执行计划,建索引,效果不理想。优化的时间不是一个级别,无效。
2、模拟A\B\C\D\E的逻辑在物理层建伪列,无效。
3、通过ETL做冗余,按月按逻辑把Qx,Hx,Year的数据冗余出来,时间维仍保持关联月份,建立多个逻辑表源,在表源里期望根据币种切换会话变量自动选择符合的逻辑表源,试之,默认走第一个表源。内容限定还是直接加到where条件里,没有预期的判定效果,无效。
4、设置指标filter,避免case when判定指标,通过indexcol结合case when判定变量去取指标,避免逻辑SQL过多的问题;检查物理SQL,已经和之前不是一个级别(优化前最初的5k多行到优化后的几百行)
5、设置相关索引,避免全表扫描,基本达到预期。
感悟:
1、完成一个功能的时候,要提前考虑性能的影响。完成功能仅仅是个开始,要不然挖坑后,不是别人跳,就是自己跳。
2、在N个维度中设置逻辑层事实表对应维度级别的小技巧。在物理层选中连接的维表查找维度,在逻辑层逐一编辑修改维度名称,原名称放置到备注,修改名称为数字1,2,3,4。。。,这样在设置逻辑层级别时候,需要关联的维度自动排序在最顶端,设置好后再修改回去维度名称。避免瞪瞎眼都会找错维度的尴尬。
3、indexcol函数,你值得拥有。要善用之,活用之。例如要筛选列T,列值有A1,A2,比如当变量值为A,筛选A1,当变量值为B,筛选值为A1,A2,一般会想case when 变量=A then 列T。。。咦。in。。。咋整?(变量=A and T=A1) or (变量=B and T in (A1,A2))?
其实换个角度就可以了。公式:indexcol(case when 变量=A then 0 else 1 end,列T,A1)=A1,当等于A的时候过滤T=A1,等于B的时候恒等。