Mondrian之QuickStart

Mondrian是一个ROLAP的开源实现，大型项目一般会使用MOLAP，对Mondrian这类东西来说，一般用在中小型项目中比较合适，并且，如果要在项目中真正地使用起来，还需要了解它的源代码，毕竟它还不算一个很完善的产品，还有一些bug，另外它的实现极其耗内存，cube存放于内存中，大量的对象也放在session中，所以我个人认为它还有许多的地方需要改进，好，废话少说，还是说说如何使用这个东西。

因为对Oracle比较熟悉，所以我就用Oracle来存放我的sample data，
1，
先创建一个用户(扬帆)：
create user yf
identified by yf
default tablespace USERS;
grant connect to YF;
grant create any table to YF with admin option;
alter user YF
quota 50m on users;

2，
现在开始建表，这个sample project主要是想表现一个跨国公司雇员的薪资情况：
--雇员表
create table employee
(
ID number(5) not null,
NAME varchar2(50) not null,
CITY varchar2(20) not null,
STATE_PROVINCE varchar2(20) not null,
COUNTRY varchar2(20) not null
);
--月份表(我们的维度表)
create table MONTH_DIMENSION
(
ID number(5) not null,
THE_MONTH number(2) not null,
THE_YEAR number(4) not null
);

--薪水明细表(我们的事实表)
-- Create table
create table SALARY_DETAIL
(
ID number(5) not null,
TIME_ID number(5) not null,
EMPLOYEE_ID number(5) not null,
MONEY number(6) not null
);
--

3，
现在开始加一些sample data,
DELETE FROM EMPLOYEE;
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (1, 'LiJun', 'ChengDu', 'SiChuan', 'China');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (2, 'WangDong', 'ChengDu', 'SiChuan', 'China');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (3, 'ZhangSan', 'MeiShan', 'SiChuan', 'China');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (4, 'LiXiaoJun', 'JiangYou', 'SiChuan', 'China');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (5, 'ZhouDong', 'ChangSha', 'HuNan', 'China');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (6, 'Jack', 'Victoria', 'CA', 'United States');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (6, 'Vince', 'Victoria', 'CA', 'United States');
insert into EMPLOYEE (ID, NAME, CITY, STATE_PROVINCE, COUNTRY)
values (8, 'Tom', 'Santiago', 'CA', 'United States');



INSERT INTO month_dimension(id, the_month, the_year) VALUES(1,10,2007);
INSERT INTO month_dimension(id, the_month, the_year) VALUES(2,11,2007);
INSERT INTO month_dimension(id, the_month, the_year) VALUES(3,12,2007);
INSERT INTO month_dimension(id, the_month, the_year) VALUES(4,1,2008);
INSERT INTO month_dimension(id, the_month, the_year) VALUES(5,2,2008);



DELETE FROM SALARY_DETAIL;
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(1,1,1,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(2,2,1,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(3,3,1,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(4,4,1,300);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(5,5,1,300);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(6,1,2,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(7,2,2,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(8,3,2,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(9,4,2,300);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(10,5,2,300);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(11,1,3,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(12,2,3,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(13,3,3,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(14,4,3,300);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(15,5,3,300);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(16,1,4,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(17,2,4,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(18,3,4,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(19,4,4,300);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(20,5,4,300);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(21,1,5,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(22,2,5,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(23,3,5,200);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(24,4,5,300);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(25,5,5,300);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(26,1,6,800);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(27,2,6,800);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(28,3,6,800);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(29,4,6,850);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(30,5,6,850);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(31,1,7,600);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(32,2,7,600);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(33,3,7,600);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(34,4,7,650);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(35,5,7,650);

INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(36,1,8,700);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(37,2,8,700);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(38,3,8,700);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(39,4,8,800);
INSERT INTO SALARY_DETAIL(ID,TIME_ID,EMPLOYEE_ID,MONEY) VALUES(40,5,8,800);
COMMIT;





4，
下载mondrian，把其中的mondrian.war解压到tomcat的webapp目录下为mondrian，把oracle jdbc的驱动ojdbc14.jar放到mondrian/WEB-INF/lib目录下。


5，
开始创建cube，mondrian的cube是以xml方式定义的，你如果觉得手写xml不方便，可以用它的java GUI工具Workbench来帮助你做，不过手写很简单。新建一个YF_Salary.xml放到mondrian\WEB-INF\queries目录下，内容如下：
<?xml version="1.0"?>
<Schema name="ecwise">
<Dimension name="Time" type="TimeDimension">
<Hierarchy hasAll="true" primaryKey="ID">
<Table name="MONTH_DIMENSION"/>
<Level name="Year" column="THE_YEAR" type="Numeric" uniqueMembers="true"
levelType="TimeYears"/>
<Level name="Month" column="THE_MONTH" uniqueMembers="false" type="Numeric"
levelType="TimeMonths"/>
</Hierarchy>
</Dimension>
<Dimension name="Employee">
<Hierarchy hasAll="true" primaryKey="ID">
<Table name="EMPLOYEE"/>
<Level name="Country" column="COUNTRY" uniqueMembers="true"/>
<Level name="State Province" column="STATE_PROVINCE" uniqueMembers="true"/>
<Level name="City" column="CITY" uniqueMembers="false"/>
<Level name="Employee Name" column="name" uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Cube name="Salary">
<Table name="SALARY_DETAIL"/>
<DimensionUsage name="Time" source="Time" foreignKey="TIME_ID"/>
<DimensionUsage name="Employee" source="Employee" foreignKey="EMPLOYEE_ID"/>

<Measure name="Income" column="MONEY" aggregator="sum"/>
</Cube>
</Schema>


6，
cube已经定义好，我们可以写jsp来显示这个cube了，
新建salary.jsp，内容如下：
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<jp:mondrianQuery id="query01" jdbcDriver="oracle.jdbc.driver.OracleDriver" jdbcUrl="jdbc:oracle:thin:@192.168.3.92:1521:data" catalogUri="/WEB-INF/queries/YF_Salary.xml"
jdbcUser="yf" jdbcPassword="yf" connectionPooling="false">
select
{[Measures].[Income]} on columns,
{([Time].[All Times],[Employee].[All Employees])} ON rows
from Salary
</jp:mondrianQuery>

<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>

把这个jsp放到mondrian\WEB-INF\queries下，然后再建一个html文件来定向到这个jsp
新建salary.html，放到mondrian目录下，内容如下：
<html>
<head>
<title>example for Mondrian</title>
</head>
<body>
<p>Mondrian examples:</p>
<ul>
<li><a href="testpage.jsp?query=salary">YF Salary</a></li>
</ul>
</body>
</html>

7，
一切准备就绪，开始启动tomcat，浏览器上键入http://localhost:8080/mondrian/salary.html，然后再点击链接就可以看到我们的cuble了。