##########分组字段实现
在schema的physicalschema中的columnDefs中采用SQL自定义一列,使用case when
<CalculatedColumnDef name='quarter' type='String'>
<ExpressionView>
<SQL dialect='mysql'>
case
when <Column name='month'/> in(12,11,10) then '第四季度'
when <Column name='month'/> in(9,8,7) then '第三季度'
when <Column name='month'/> in(6,5,4) then '第二季度'
else '第一季度'
end
</SQL>
</ExpressionView>
</CalculatedColumnDef>
<CalculatedColumnDef name='quarter' type='String'>
<ExpressionView>
<SQL dialect='mysql'>
case
when <Column name='month'/> > 9 then '第四季度'
when <Column name='month'/> > 6 then '第三季度'
when <Column name='month'/> > 3 then '第二季度'
else '第一季度'
end
</SQL>
</ExpressionView>
</CalculatedColumnDef>
############calculatemember,nameset,calculatedColumnDef用法注意
calculatemember定义的字段和nameset定义的集合不需要在attribute和level中重新定义,但是在physicalschema中通过calculated columnDef定义的列,如果要用的话是需要在attribute和level中同步定义的
######MDX支持的SQL函数postgresql实现遇到的问题
select
"sale_month"."date" as "c0",
"sale_month"."date_name" as "c1",
LENGTH("sale_month"."date_name") as "c2",
CONCAT(CONCAT("sale_month"."year_name", ' ', "sale_month"."month_name"),' ',"sale_month"."day_name") as "c3",
TRIM(both 'Y' from "sale_month"."date_name") as "c4",
"sale_month"."year" as "c5",
"sale_month"."year_name" as "c6",
"sale_month"."month" as "c7",
"sale_month"."month_name" as "c8",
LOWER("sale_month"."month_name") as "c9",
"sale_month"."day" as "c10",
"sale_month"."day_name" as "c11",
case when "sale_month"."saleamount" > 666666 then 1 else -1 end as "c12",
log(2,"sale_month"."saleamount") as "c13",
LN("sale_month"."saleamount") as "c14",
POWER("sale_month"."saleamount",-3) as "c15",
FLOOR("sale_month"."saleamount") as "c16",
ROUND("sale_month"."saleamount") as "c17",
extract(day from "sale_month"."date") as c18,
to_date("sale_month"."date_varchar",'YYYY-MM-DD') as c19,
"sale_month"."date" + interval '1 month' as c20,
age("sale_month"."date","sale_month"."date" + interval '1 month') as c21
from "sale_month" as "sale_month"
group by "sale_month"."date", "sale_month"."date_name",
LENGTH("sale_month"."date_name"),
CONCAT(CONCAT("sale_month"."year_name", ' ', "sale_month"."month_name"),' ',"sale_month"."day_name"),
TRIM(both 'Y' from "sale_month"."date_name"),
"sale_month"."year",
"sale_month"."year_name",
"sale_month"."month",
"sale_month"."month_name",
LOWER("sale_month"."month_name"),
"sale_month"."day", "sale_month"."day_name",
case when "sale_month"."saleamount" > 666666 then 1 else -1 end,
LOG(2,"sale_month"."saleamount"),
LN("sale_month"."saleamount"),
POWER("sale_month"."saleamount",-3),
FLOOR("sale_month"."saleamount"),
ROUND("sale_month"."saleamount"),
extract(day from "sale_month"."date"),
to_date("sale_month"."date_varchar",'YYYY-MM-DD'),
"sale_month"."date"+interval '1 month',
age("sale_month"."date","sale_month"."date" + interval '1 month')
order by "sale_month"."date" ASC NULLS LAST,
"sale_month"."date_name" ASC NULLS LAST,
LENGTH("sale_month"."date_name") ASC NULLS LAST,
CONCAT(CONCAT("sale_month"."year_name", ' ', "sale_month"."month_name"),' ',"sale_month"."day_name") ASC NULLS LAST,
TRIM(both 'Y' from "sale_month"."date_name") ASC NULLS LAST,
"sale_month"."year" ASC NULLS LAST,
"sale_month"."year_name" ASC NULLS LAST,
"sale_month"."month" ASC NULLS LAST,
"sale_month"."month_name" ASC NULLS LAST,
LOWER("sale_month"."month_name") ASC NULLS LAST,
"sale_month"."day" ASC NULLS LAST,
"sale_month"."day_name" ASC NULLS LAST,
case when "sale_month"."saleamount" > 666666 then 1 else -1 end ASC NULLS LAST,
LOG(2,"sale_month"."saleamount") ASC NULLS LAST,
LN("sale_month"."saleamount") ASC NULLS LAST,
POWER("sale_month"."saleamount",-3) ASC NULLS LAST,
FLOOR("sale_month"."saleamount") ASC NULLS LAST,
ROUND("sale_month"."saleamount") ASC NULLS LAST,
extract(day from "sale_month"."date"),
to_date("sale_month"."date_varchar",'YYYY-MM-DD'),
"sale_month"."date"+interval '1 month',
age("sale_month"."date","sale_month"."date" + interval '1 month')
注意:
log(),ln(),power()函数saiku没显示,但是底层的sql计算无问题
不支持age()函数,初步判断因为函数的返回值是interval类型,不在SCHEMEA支持的数据类型中
###########MDX支持的SQL函数oracle实现遇到的问题
select
"SALE_MONTH"."date" as "c0",
"SALE_MONTH"."DATE_NAME" as "c1",
LENGTH("SALE_MONTH"."date") as "c2",
"SALE_MONTH"."YEAR_NAME"||' '|| "SALE_MONTH"."MONTH_NAME"||' '||"SALE_MONTH"."DAY_NAME" as "c3",
TRIM("SALE_MONTH"."DATE_NAME") as "c4",
"SALE_MONTH"."YEAR" as "c5",
"SALE_MONTH"."YEAR_NAME" as "c6",
"SALE_MONTH"."MONTH" as "c7",
"SALE_MONTH"."MONTH_NAME" as "c8",
LOWER("SALE_MONTH"."MONTH_NAME") as "c9",
"SALE_MONTH"."DAY" as "c10",
"SALE_MONTH"."DAY_NAME" as "c11",
DECODE(SIGN("SALE_MONTH"."SALEAMOUNT"-666666),1,1,-1) as "c12",
LOG(3,"SALE_MONTH"."SALEAMOUNT") as "c13",
LN("SALE_MONTH"."SALEAMOUNT") as "c14",
POWER("SALE_MONTH"."SALEAMOUNT",-3) as "c15",
FLOOR("SALE_MONTH"."SALEAMOUNT") as "c16",
ROUND("SALE_MONTH"."SALEAMOUNT") as "c17",
ADD_MONTHS("SALE_MONTH"."date",-2) as "c18",
EXTRACT( day from "SALE_MONTH"."date") as "c19" ,
TO_NUMBER("SALE_MONTH"."date" - ADD_MONTHS("SALE_MONTH"."date",-2)) as c20,
TO_DATE("SALE_MONTH"."DATE_VARCHAR",'yyyy-MM-dd HH:mi:ss') as c21
from "SALE_MONTH" "SALE_MONTH"
group by "SALE_MONTH"."date",
"SALE_MONTH"."DATE_NAME",
LENGTH("SALE_MONTH"."date"),
"SALE_MONTH"."YEAR_NAME"||' '|| "SALE_MONTH"."MONTH_NAME"||' '||"SALE_MONTH"."DAY_NAME",
TRIM("SALE_MONTH"."DATE_NAME"),
"SALE_MONTH"."YEAR",
"SALE_MONTH"."YEAR_NAME",
"SALE_MONTH"."MONTH",
"SALE_MONTH"."MONTH_NAME",
LOWER("SALE_MONTH"."MONTH_NAME"),
"SALE_MONTH"."DAY", "SALE_MONTH"."DAY_NAME",
DECODE(SIGN("SALE_MONTH"."SALEAMOUNT"-666666),1,1,-1),
LOG(3,"SALE_MONTH"."SALEAMOUNT"),
LN("SALE_MONTH"."SALEAMOUNT"),
POWER("SALE_MONTH"."SALEAMOUNT",-3),
FLOOR("SALE_MONTH"."SALEAMOUNT"),
ROUND("SALE_MONTH"."SALEAMOUNT"),
ADD_MONTHS("SALE_MONTH"."date",-2),
EXTRACT( day from "SALE_MONTH"."date") ,
TO_NUMBER("SALE_MONTH"."date" - ADD_MONTHS("SALE_MONTH"."date",-2)),
TO_DATE("SALE_MONTH"."DATE_VARCHAR",'yyyy-MM-dd HH:mi:ss')
order by "SALE_MONTH"."date" ASC NULLS LAST,
"SALE_MONTH"."DATE_NAME" ASC NULLS LAST,
LENGTH("SALE_MONTH"."date") ASC NULLS LAST,
"SALE_MONTH"."YEAR_NAME"||' '|| "SALE_MONTH"."MONTH_NAME"||' '||"SALE_MONTH"."DAY_NAME" ASC NULLS LAST,
TRIM("SALE_MONTH"."DATE_NAME") ASC NULLS LAST,
"SALE_MONTH"."YEAR" ASC NULLS LAST,
"SALE_MONTH"."YEAR_NAME" ASC NULLS LAST,
"SALE_MONTH"."MONTH" ASC NULLS LAST,
"SALE_MONTH"."MONTH_NAME" ASC NULLS LAST,
LOWER("SALE_MONTH"."MONTH_NAME") ASC NULLS LAST,
"SALE_MONTH"."DAY" ASC NULLS LAST,
"SALE_MONTH"."DAY_NAME" ASC NULLS LAST,
DECODE(SIGN("SALE_MONTH"."SALEAMOUNT"-666666),1,1,-1) ASC NULLS LAST,
LOG(3,"SALE_MONTH"."SALEAMOUNT") ASC NULLS LAST,
LN("SALE_MONTH"."SALEAMOUNT") ASC NULLS LAST,
POWER("SALE_MONTH"."SALEAMOUNT",-3) ASC NULLS LAST,
FLOOR("SALE_MONTH"."SALEAMOUNT") ASC NULLS LAST,
ROUND("SALE_MONTH"."SALEAMOUNT") ASC NULLS LAST,
ADD_MONTHS("SALE_MONTH"."date",-2) ASC NULLS LAST,
EXTRACT( day from "SALE_MONTH"."date") ASC NULLS LAST,
TO_NUMBER("SALE_MONTH"."date" - ADD_MONTHS("SALE_MONTH"."date",-2)) ASC NULLS LAST,
TO_DATE("SALE_MONTH"."DATE_VARCHAR",'yyyy-MM-dd HH:mi:ss') ASC NULLS LAST
注意:
log(),ln(),power()函数saiku没显示,但是底层的sql计算无问题
################关于Mondrian连接postgres的时候columnDefs中dialect种必须包含generic的问题
可能是个bug官网给的demo中dialect写的是“postgres”,当dialect='postgres'且没有dialect='generic'的时候,报错:mondrian.olap.MondrianException: Mondrian Error:Internal error: View has no 'generic' variant
也就是一个cloumn需要重复定义两次
解决:
改为dialect='postgresql',问题解决