多维分析后台实践 6:大事实表关联小维表

【摘要】
用实例、分步骤,详细讲解多维分析(OLAP)的实现。点击了解多维分析后台实践 6:大事实表关联小维表

实践目标

本期目标是在上期基础上,将客户宽表全部数据与分支机构等多个维表关联,并通过维表字段进行切片和分组计算。

实践的步骤:

1、 对客户表进行预处理,完成维度序号化计算。

2、 对维表进行预计算,完成维度之间的关联。

3、 关联宽表客户数据和维表,并计算切片和分组:用 Java 代码调用 SPL 实现。

各个维表和客户表的关联关系如下图:

..

维表包括:

1、 分支机构表 department

2、 地址表 locations

3、 国家表 countries

4、 区域表 regions

5、 贵宾表 vip

6、 信用评级表 credit_grade

维表字段和上期相同。

 

多维分析计算的目标可以用下面 Oracle 的 SQL 语句表示:

select v.interest_rate1,ct.country_id,c.job_id,sum(c.balance) sum,count(c.customer_id) count

from customer c

 left join department d on c.department_id=d.department_id

 left join locations l on d.location_id=l.location_id

 left join countries ct on l.country_id=ct.country_id

 left join regions r on r.region_id=ct.region_id

 left join vip v on c.vip_id=v.vip_id

 left join credit_grade cg on c.credit_grade=cg.credit_grade

where d.applause_rate in (26,30,33,65,67,74,75,77)

and r.region_name in ('Asia','Europe','Americas')

and v.charge_ratio in (9,11)

and cg.quota2 in(50000,80000)

and c.job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')

and c.flag1='1' and c.flag8='1'

group by v.interest_rate1,ct.country_id,c.job_id

 

维度序号化

分支机构维表数据如下:

..

客户表中的 department_id 字段存储的是人为的编码(如下图),需要转换为分支机构表中的序号。

..

我们可以在 etl.dfx 中追加转换代码:


A

1

=file("data/customer.ctx").open().cursor()

2

=file("data/department.btx").import@b().keys(department_id).derive(#:num)

3

=A1.switch(department_id,A2:department_id)

4

=A3.new(department_id.num:department_id,job_num,employee_id,begin_date,customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8,vip_id,credit_grade)

5

=file("data/customer亿 Dept.ctx").create@y(#department_id,#job_num,#employee_id,#begin_date,#customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8,vip_id,credit_grade).append(A4)

A1:打开客户组表游标。

A2:读取分支机构表数据,以 department_id 作为主键,并新增计算列 num 保存序号。

A3:用客户组表游标关联 A2。

A4:在游标中,用 num 代替 department_id。

A5:将转换好的游标输出到 customerDept.ctx。

维表预关联

改写 init.dfx,在内存中加载维表数据并进行关联。代码如下:

 


A

B

1

=file("data/job.btx").import@ib()

>env(job,A1)

2

=file("data/department.btx").import@b().keys(department_id)

=file("data/vip.btx").import@b().keys(vip_id)

3

=file("data/credit_grade.btx").import@b().keys(credit_grade)

=file("data/locations.btx").import@b().keys(location_id)

4

=file("data/countries.btx").import@b().keys(country_id)

=file("data/regions.btx").import@b().keys(region_id)

5

=A2.switch(location_id,B3.switch(country_id,A4.switch(region_id,B4)))

6

=env(department,A5)

=env(vip,B2)

7

=env(credit_grades,A3)


A1:取出集文件中的职业类型数据,@i 表示只有一列时读成序列。

B1:存入全局变量 job。

A2-B4:分别取出集文件中的分支机构、vip、信用评级、地址、国家、区域数据,建立主键。

A5:把 department、locations、contries 和 regions 表按照层次关联起来。

A6:department 存入全局变量。B6:vip 存入全局变量。

A7:credit_grades 存入全局变量。

写好的 init.dfx 要放入节点机主目录,启动或重启节点机时会被自动调用。

关联计算

改写前面几期的 olap.dfx 和 customer.dfx。

输入参数仍然是两个:表名 arg_table 值为 customer,其他参数采用 json 格式,arg_json 样例如下:

{

       aggregate:

              [

                     {

                            func:"sum",

                            field:"balance",

                            alias:"sum"

                     },

                     {

                            func:"count",

                            field:"customer_id",

                            alias:"count"

                     }

              ],

       group:

              [

                     "vip_id.interest_rate1:vip_id_interest_rate1",

               "department_id.location_id.country_id.country_id:department_id_location_id_country_id",

                     "job_id"

              ],

       slice:

              [

                     {

                            dim:"department.applause_rate",

                            interval:null,

                            value:[26,30,33,65,67,74,75,77]

                     },

 

                     {

                      dim:"department.location_id.country_id.region_id.region_name",

                            value:["Asia","Europe","Americas"]

                     },

                     {

                            dim:"vip.charge_ratio",

                            value:[9,11]

                     },

                     {

                            dim:"credit_grades.quota2",

                            value:[50000,80000]

                     },

                     {

                            dim:"job_id",

                      value:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"]

                     },

                     {

                            dim:"flag1",

                            value:"1"

                     },

                     {

                            dim:"flag8",

                            value:"1"

                     }

              ]

}

其中,group 和 slice 中出现了 department_detail.location_id 和 department_detail.applause_rate 等。多维分析计算时要先将过滤条件转换为客户表自身的字段条件,做游标前过滤,再关联客户表和维表计算。

department_detail.location_id.country_id.country_id:department_detail_location_id_country_id 中的冒号是给外键属性化字段取一个别名,用做最后结果的字段名。

第一步:改写 customer.dfx。

在改写 olap.dfx 之前,先改写 customer.dfx。改变处理 arg_json 参数的方法,SPL 代码如下:


A

B

C

D

1

func




2


if A1.value==null

return   "between("/A1.dim/","/A1.interval(1)/":"/A1.interval(2)/")"

3


else if ifa(A1.value)

return   string(A1.value)/".contain("/A1.dim/")"

4


else if   ifstring(A1.value)

return A1.dim/"==\""/A1.value/"\""

5


else

return   A1.dim/"=="/A1.value

6

func




7


=[]

=A6.select(like(dim,B6/".*"))

8


for C7

=[func(A1,B8)]


9



>B7|=C8


10


=${B6}.pselect@a(${B7.concat("  &&")})

=A6\C7


11


=C10.create().record([C6,null,B10])


12


return B11|C10



13

=json(arg_json)

=date("2000-01-01")


14

=A13.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c()


15

=A13.group.(if(~=="job_id","job_num",~))


16

=A15.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c()

17

=A13.aggregate.(field)

=A15.(if(~=="begin_yearmonth","begin_date",~))

=(A17|B17).id()


18

=[]




19

for C17

if   like(A19,"department_id.*")

>C17(#A19)="department_id"

>A18|=["department_id,department:#"]

20


if   like(A19,"vip_id.*")

>C17(#A19)="vip_id"

>A18|=["vip_id,vip:#"]

21


if   like(A19,"credit_grade.*")

>C17(#A19)="credit_grade"

>A18|=["credit_grade,credit_grade:#"]

22

=C17.id().concat@c()



23

=func(A6,A13.slice,"vip","vip_id")

=func(A6,A23,"credit_grades","credit_grade")

=func(A6,B23,"department","department_id")

24

=[]




25

for C23

if   A25.dim=="begin_date" && A25.value!=null

>A25.value=int(interval@m(C13,eval(A25.value))*100+day(eval(A25.value)))

26


else if   A25.dim=="begin_date" && A25.value==null

=A25.interval.(~=int(interval@m(C13,eval(~))*100+day(eval(~))))

27


else if   A25.dim=="job_id"

>A25.dim="job_num"


28



>A25.value=A25.value.(job.pos@b(~))

29


else if like(A25.dim,   "flag?")

>A25.value=int(A25.value)


30


=[func(A1,A25)]

>A24|=B30


31

=A13.group.(~.split(":")).(~=~.m(-1))|A13.aggregate.(alias)

=A31(A31.pos("job_id"))="job(job_num):job_id"

32

=A31(A31.pos("begin_yearmonth"))="month@y(elapse@m(date(\""/C13/"\"),begin_yearmonth)):begin_yearmonth"

33

=A31(A31.pos("begin_date"))="elapse@m(B4,begin_date\\100)+(begin_date%100-1):begin_date"

34

return   A22,A14,A16,A24.concat("&&  "),A31.concat@c(),A18.concat(";")

A1 到 C5 是子程序,在调用的时候才会执行。代码与前面几期完全一致,没有改变。

A6 到 C12 是新增的子程序,在下面调用的时候介绍。

A12:将 arg_json 解析成序表。解析的结果是多层嵌套的序表,如下图:

..

其中的 aggregate 为:

..

其中的 group 为:

..

其中的 slice 为:

..

A14:先将 aggregate 计算成冒号相连的字符串序列,如下图:

..

再将序列用逗号连接成一个字符串:sum(balance):sum,count(customer_id):count,也就是聚合表达式。

A15:将 group 中的 job_id 替换为 job_num。

A16:将 group 中的 begin_yearmonth 转换为整数计算。

A17-C17:计算需要从组表中读取的字段名。计算结果如下:

..

其中有关联维表的字段,需要转换为组表自身的字段。

A18:定义一个空序列,准备存放需要关联的维表和字段。

A19:循环计算 C17,循环体是 B19 到 C21。

B19:如果 A19 包含 "department_id.*",那么就需要关联分支机构序表。

C19:将 C17 的相应成员赋值为 "department_id"。

D19:A18 中增加一个成员 "department_id,department:#"。客户表 department_id 预先已经做了维表序号化,所以这里可以直接写 #。

B20-D20:同样处理 vip 维表。

B21-D21:同样处理 credit_grade 维表。

A19 循环结束,此时 A18 计算结果如下:

..

 

A22:对处理之后的 C17 去重,用逗号连接为字符串 "balance,customer_id,department_id,job_num,vip_id"。

A23:以 A13.slice、维表 "vip" 和维表字段 "vip_id" 为输入参数,调用 A6 子程序。

三个参数分别存入 A6、B6、C6 中。A6 如下图:

..

此时 A6 的第三个成员为 vip.charge_ratio,子程序的目标是将其计算为组表自身字段 vip_id 的过滤条件。

B7:准备一个空序列,存放 vip 有关的过滤条件。

C7:过滤出 vip 有关的过滤条件,如下:

..

B8:开始循环计算 C7。循环体是 C8 到 C9。

C8:调用 A1,将 B8 计算为集算器表达式:

..

C9:将 C8 合并到 B7 中:

..

B10:用 B7 中所有的条件来过滤 vip 维表,得到 vip 维表的序号:

..

C10:将 A6 中去掉 C7:

..

B11:C10 创建新纪录:

..

B12:合并 B11 和 C10 返回结果,结束 A6 子程序。

A23:子程序返回后,A23 序列中 vip 有关的过滤条件都转换成客户表 vip_id 字段的条件了,如下图:

..

B23:用同样的办法将 A23 中的 credit_grades.* 条件,转换为客户表 credit_grade 字段的条件。

C23:用同样的办法将 A23 中的 department.* 条件,转换为客户表 department_id 字段的条件。

A24:定义一个空序列,准备存放切片(过滤条件)表达式。

A25:循环计算 slice,循环体是 B25 到 C30。其中:B25 到 C29 是对 slice 的 value 或者 interval 做性能优化的转换。

B25:如果 A25 的 dim 是 begin_date 并且 value 不是空,也就是 begin_date 等于单一日期的情况。此时 C25 要将单一日期转换为整数赋值给 value。

B26:如果 A25 的 dim 是 begin_date 并且 value 是空,也就是 begin_date 在一个范围内取值的情况。此时 C25 要将起止日期转换为整数赋值给 interval。

B27:如果 A10 的 dim 是 job_id,也就是 job_id 取枚举值的情况。例如:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"].contain(job_id)。此时 C27 要将 A10 的 dim 改为 job_num。C28 要将 A25 的 value 枚举值转换为在全局变量 job 序列中的位置,也就是 job_num 整数序列,例如:[5,7,2,15,16]。

B29:如果 A25 的 dim 是 flag1、flag2…flag8,也就是标志位等于 "1" 或者 "0" 的情况。此时 C29 要将 A25 的 value 值从字符串转化为整数。

B30:用 B25 到 C29 对 slice 的 value 或者 interval 做性能优化转换结果作为参数,调用子程序 A1。

子程序 A1(B2 到 B5),和第一篇 customer.dfx 的 func 代码相同,不再赘述。

C30:func A1 的返回结果追加到 A24 中。继续 A25 中的循环,到循环结束,就准备好了切片表达式的序列。

A31:准备结果集显示值转换的表达式。将 A13.group.(~.split(":")).(~=~.m(-1)) 和 A13.aggregate.alias 序列合并,如下图:

..

A13.group.(~.split(":")).(~=~.m(-1)) 是将 group 中有冒号的,取冒号后面的别名,否则直接用 group 的值。

C31:将 A31 中的 job_id 替换成转换语句。语句的作用是:将结果集中的 job_num 转换为 job_id。

A32-A33:生成表达式,用于计算结果中的 begin_date 和 begin_yearmonth 从整数转换为日期。

A34:返回各个表达式字符串,依次是:

需要的字段:balance,customer_id,department_id,job_num,vip_id

聚合表达式:sum(balance):sum,count(customer_id):count

分组表达式:

vip_id.interest_rate1:vip_id_interest_rate1,department_id.location_id.country_id.country_id:department_id_location_id_country_id,job_num

切片表达式:

[2].contain(department_id)

&& [5,8].contain(credit_grade)

&& [2].contain(vip_id)

&& [5,7,2,15,16].contain(job_num)

&& flag1==1 && flag8==1

结果集显示值转换表达式:

vip_id_interest_rate1,department_id_location_id_country_id,job(job_num):job_id,sum,count

外键关联表达式:

department_id,department:#;vip_id,vip:#

 

第二步:改写 olap.dfx。

网格参数是 arg_table(值为 customer)和 arg_json。SPL 代码如下:


A

1

=call(arg_table/".dfx",arg_json)

2

=file("data/"/arg_table/"Dept.ctx").open()  

3

=A2.cursor@m(${A1(1)};${A1(4)};2)

4

=A3.switch(${A1(6)})


=A4.groups(${A1(3)};${A1(2)})


=A5.new(${A1(5)})


return A6

A1:根据 arg_table 参数的值调用 customer.dfx,调用参数是 arg_json,返回值是 customer.dfx 返回的各个表达式字符串。

A2:根据 arg_table 参数,打开组表文件。

A3:对 A2 建立带过滤条件的游标。实际执行的语句是:

= A2.cursor@m(balance,customer_id,department_id,job_num,vip_id; [4,5,6,12,16,18,22,23,24,27].contain(department_id) && [5,8].contain(credit_grade) && [2].contain(vip_id) && [5,7,2,15,16].contain(job_num) && flag1==1 && flag8==1;2)

A4:将游标关联内存中的维表。实际执行的语句是:

=A3.switch(department_id,department:#;vip_id,vip:#)

A5:对关联之后的游标,做小结果集分组计算。实际执行的语句是:

=A4.groups(vip_id.interest_rate1:vip_id_interest_rate1,department_id.location_id.country_id.country_id:department_id_location_id_country_id,job_num;sum(balance):sum,count(customer_id):count),执行结果如下:

..

A6:将分组结果转换为显示值。实际执行的语句是:=A5.new(vip_id_interest_rate1,department_id_location_id_country_id,job(job_num):job_id,sum,count),执行结果:

..

A7:返回计算结果。

 

第三步:用 Java 代码调用

olapMem.dfx 编写好之后,可以在多维分析中作为存储过程调用,Java 代码和前面几期大部分相同,只是 dfx 文件名称、arg_table 和 arg_json 值不同。Java 代码如下:

public void testOlapServer(){

       Connection con = null;

       java.sql.PreparedStatement st;

       try{

              // 建立连接

              Class.forName("com.esproc.jdbc.InternalDriver");

              // 根据 url 获取连接

              con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");

              // 调用存储过程,其中 olap 是 dfx 的文件名

st =con.prepareCall("call olap(?,?)");

st.setObject(1, "newCustomer");

st.setObject(2, "{aggregate:[{func:\"sum\",field:\"balance\",alias:\"sum\"},{func:\"count\",field:\"customer_id\",alias:\"count\"}],group:[\"vip_id.interest_rate1:vip_id_interest_rate1\",\"department_id.location_id.country_id.country_id:department_id_location_id_country_id\",\"job_id\"],slice:[{dim:\"department.applause_rate \",interval:null,value:[26,30,33,65,67,74,75,77]},{dim:\"department.location_id.country_id.region_id.region_name\",value:[\"Asia\",\"Europe\",\"Americas\"]},{dim:\"vip.charge_ratio\",value:[9,11]},{dim:\"credit_grades.quota2\",value:[50000,80000]},{dim:\"job_id\",value:[\"AD_VP\",\"FI_MGR\",\"AC_MGR\",\"SA_MAN\",\"SA_REP\"]},{dim:\"flag1\",value:\"1\"},{dim:\"flag8\",value:\"1\"}]}");//arg_json

// 执行存储过程

st.execute();

// 获取结果集

ResultSet rs = st.getResultSet();

// 继续处理结果集,将结果集展现出来

             

       }

       catch(Exception e){

              out.println(e);

       }

       finally{

              // 关闭连接

              if (con!=null) {

                     try {con.close();}

                     catch(Exception e) {out.println(e);       }

              }

       }

}

 

Java 代码和 SPL 执行的总时间是 3 秒。