Mondrian之QuickStart

lxjchengcu

浏览: 23502 次
性别:
来自: 成都

最近访客更多访客>>

tank924

imbadh

965133879

疯狂熊猫

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (8)

社区版块

存档分类

Oracle JSP Tomcat JDBC Web

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>

</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了