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了