ADS层是由需求指标形成的,也就是由需求决定的,而其他层级的数据大部分是由ADS层来决定的。然后ADS层的数据大部分都是来自于DWD、DWS关联DIM层,一般一个原型图对应着一张ADS层的表,而ADS层的表里面的指标,来源于多张业务事实表,一般都需要从多个DWD或者DWS层的表中获取对应的指标字段,而且一般都需要关联DIM层的维度,所以就需要多个insert从多张事实表中获取原型指标字段。
获取原型指标字段数据的方法是,建一张tmp临时的ADS层表,然后通过多个insert语句从DWD层或者DWS层关联DIM层的表中来获取需要的ADS层的某一个或者多个指标字段,这些字段在bi原型中需要使用到的,插入到临时表中,得到的表会是一层一层的,而且每一层都有null值,且出现维度冗余,因为不可能ADS层的指标数据都从一张业务事实表获取到的,所以只能通过多张事实表来插入。最后就是写总的insert语句,将维度合并,减少冗余,得到最简的ADS层表以提高检索效率。
现在根据原型“征订目录教材教辅-供应链汇总表”,提取到了ads层的需求指标表“ads_subs_chain_sum”,现在需要根据这个ads层的表从dwd或者dws或者关联dim表进行获取ads层中的指标字段。有一些字段可以从某层中直接获取,有一些则需要经过聚合或者分组才能得到对应的指标字段。
现在他做的就是将ads层的表,分多个insert插入到一个tmp表中,然后获取到指标需要的所有字段数据,就是一层一层的拼接到一起,但是有些维度会重合,最后再对此tmp表进行分组合并维度得到对应合并后的数据插入到ads层的表中。
关联到相关的dim维度,然后将维度得到指标表的字段数据。维度都要,因为后续需要通过维度来合并数据。
由多个insert语句从不同的事实表中获取字段插入到tmp表,最后再对临时表进行合并维度得到指标表。
案例代码:
ADS层的表:ads_honry
建一张ADS层临时表tmp_ads_honry
每次采集时都清空一下临时表,最后再将整理好的指标插入到正式指标表中。
truncate table tmp_ads_honry
临时的获取d指标字段的insert语句:
insert into tmp_ads_honry
select
a , -- 维度字段a
b , -- 维度字段b
c , --维度字段 c
sum(d) -- 原型指标d
null e -- 原型指标e
null f -- 原型指标f
from dwd_honryd left join dim_honry
group by a,b,c
临时的获取e指标字段的insert语句:
insert into tmp_ads_honry
select
a , -- 维度字段a
b , -- 维度字段b
c , --维度字段 c
null d -- 原型指标d
sum(e) -- 原型指标e
null f -- 原型指标f
from dwd_honrye left join dim_honry
group by a,b,c
临时的获取f指标字段的insert语句:
insert into tmp_ads_honry
select
a , -- 维度字段a
b , -- 维度字段b
c , --维度字段 c
null d -- 原型指标d
null e -- 原型指标e
sum(f) -- 原型指标f
from dwd_honryd left join dim_honry
group by a,b,c
总的ads表
insert into ads_honry
a , -- 维度字段a
b , -- 维度字段b
c , --维度字段 c
sum(d) -- 原型指标d
sum(e) -- 原型指标e
sum(f) -- 原型指标f
from dwd_honryd left join dim_honry
group by a,b,c