【摘要】
用实例、分步骤，详细讲解多维分析（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 秒。