商业智能研究(十五) materialized view+dimension提高mondrian性能

按着上一篇的步骤部署好了mondrian 之后
进入数据库,修改所有以agg开头的表格，把它们重命名或者或者drop掉，下一篇我会解释为什么的.

修改 WEB-INF / mondrian.properties 文件，加上如下两个key
mondrian.trace.level=1
mondrian.debug.out.file=e:/mondrianfoodmart.log
然后把mondrian.properties文件copy 到tomcat 的 bin 目录下重新启动tomcat.

打开浏览器进入JPivot with arrows 的example .
一直drill down product. All Products -> Drink -> Alcoholic Beverages -> Beer and Wine -> Beer -> Good -> Good Imported Beer

你会在e:/ 下找到一个mondrianfoodmart.log 的文件，打开这个文件，你会看到类似与下面的语句 :
SqlMemberSource.getMemberChildren: executing sql [select "time_by_day"."the_year" as "c0" from "time_by_day" "time_by_day" group by "time_by_day"."the_year" order by "time_by_day"."the_year" ASC], exec 31 ms, exec+fetch 31 ms, 2 rows
你可以把这个文件copy一份到其他地方，我们就是要分析这个文件，来知道mondrian到底执行了那些sql语句.

它的语法格式大概如下:
**** executing sql [ 执行的sql ] exec 时间 ， exec + fetch 时间 ， 取得的数据行数.
其中前面的**** 部分是看你执行的那些操作，
mondrian 做drill down 的时候一般执行3 个 sql ,

1 . 取得左边的dimension 的一个层次结构下的子元素的数目.比如All Products 下就有三个: Drink , Food , Non-Consumable
一般通过如下sql取得 :
SELECT COUNT(DISTINCT "product_class"."product_family") AS "c0" FROM "product_class" "product_class";


2 . 取得dimension 子元素的名称 : 象如下sql :
SELECT "product_class"."product_department" AS "c0"
FROM "product" "product", "product_class" "product_class"
WHERE "product"."product_class_id" = "product_class"."product_class_id"
AND "product_class"."product_family" = 'Drink'
GROUP BY "product_class"."product_department"
ORDER BY "product_class"."product_department" ASC;


3 . 取得对应dimension的实际数据 , 象如下sql：
SELECT "time_by_day"."the_year" AS "c0", "product_class"."product_family" AS "c1",
SUM("sales_fact_1997"."store_sales") AS "m0", SUM("sales_fact_1997"."store_cost") AS "m1"
FROM "time_by_day" "time_by_day", "sales_fact_1997" "sales_fact_1997", "product_class" "product_class",
"product" "product"
WHERE "sales_fact_1997"."time_id" = "time_by_day"."time_id"
AND "time_by_day"."the_year" = 1997
AND "sales_fact_1997"."product_id" = "product"."product_id"
AND "product"."product_class_id" = "product_class"."product_class_id"
GROUP BY "time_by_day"."the_year",
"product_class"."product_family";


打开你的文本编辑器，我用的是vi , 执行两个替换命令
1 . :%s/^.*executing sql \[//g
2 . :%s/\], exec.*$/;/g
第一个命令是将所有' executing sql [ '之前的字符串去掉，第二个命令是将所有 ' ] exec ' 之后的字符串换成 ;
这个时候就只剩下可以执行的sql 了.把里面的内容copy 到你的sql 编辑器里面，我用的是oracle 官方的sql developer , 把它format一下，然后依次执行一下里面的所有sql ,看一下结果.

通过研究这个结果，我们可以确定product 的层次关系:
1 . 在product表里面，没有使用联合主键来确定product的唯一性，而是使用 product_id 作为主键，其中product_name 也是唯一的，对应 : Good Imported Beer , Good Light Beer .
2 . product_name 上一级是brand_name ，对应 : Good , Pearl ,Portsmounth , Top Measure , Walrus
3 . brand_name 的上一级是subcategory ,它在product_class表里面，product 和 product_class 通过product_class_id 链接起来，同样的，product_class 表没有用联合主键来定义唯一性，而是用product_class_id 来做主键，其中product_subcategory 是唯一的，跟product_class_id 是一一对应的. subcategory 有：Beer , Wine
4 . product_subcategory的上一级是category ，对应 : Beer and Wine .
5 . category的上一级是department , 对应 : Alcoholic Beverages , Beverages , Dairy .
6 . department的上一级是family ,也是最顶级了 , 对应 : Drink , Food ,Non-Consumable .

在我的例子中，我将使用Time 和 Product 来做Dimension ， 应为他们比较有代表性，
time_by_day 表中，有十个column ,最后一个 fiscal_period 没有用.
1 . time_id 这个表中的主键
2 . the_date 数据类型是timestamp,是唯一的,其中定义了从1997年1月1日开始到1998年12月31日的所有日期.
3 . the_day 定义的星期,比如 Monday .
4 . the_month 定义的月份，比如September
5 . the_year 年份 ， 1997 和 1998 .
6 . day_of_month 月份中的日期， 比如23代表那个月份的23号 .
7 . week_of_year 一年中的第几个星期，比如40 代表一年中的第40个星期
8 . month_of_year 一年中的第几个月，比如9 代表第九个月，
9 . quarter , 季度 .
Time dimension 建立在time_by_day 表上，其中可以用多个层级来表示Time的level 关系，比如the_year -> quarter -> the_month -> the_date ,或者year -> week_of_year -> the_day -> the_date .

Product Dimension 建立在product 和 product_class 表上，是跨表的dimension
product.product_id 主键
product.product_name 最低的level .
product.brand_name 第二level .
product.product_class_id 映射到product_class 的外键
product 其他colun 都是非主属性了.
product_class.product_class_id 主键
product_class.product_subcategory 唯一的，对应product_class_id 第三level.
product_class.product_category 第四level.
product.product_department 第五level.
product.product_family 第六level.

下篇接着写dimension + materialized view .