DB研討會 : [分享]mondrian，OLAP引擎

drop table tb_employee;
create table tb_employee
(
     employee_id     number,             --职员id     
     employee_name   varchar2(10)        --职员姓名
);
drop table tb_time;
create table tb_time
(
    time_id   number,        --时间id
    the_year  char(4),       --年
    the_month char(2)        --月
);
drop table tb_salary;
create  table tb_salary
(
    employee_id  number,                --职员id    
    time_id      number,                --时间id
    salary       number(19,4)           --薪酬
);

<?xml version="1.0"?>
<Schema name="Mondrian">
<Cube name="CubeTest">
    <Table name="TB_SALARY" />
    <Dimension name="Employee"  foreignKey="EMPLOYEE_ID" >
        <Hierarchy hasAll="true" primaryKey="EMPLOYEE_ID">          
            <Table name="TB_EMPLOYEE" />
        <Level name="employeeId" column="EMPLOYEE_ID"   uniqueMembers="true" >
                <Property name="employeeName" column="EMPLOYEE_NAME"/>
            </Level>
        </Hierarchy>
    </Dimension>
    <Dimension name="Time" foreignKey="TIME_ID" >
        <Hierarchy  hasAll="false" primaryKey="TIME_ID" >
            <Table name="TB_TIME" />
            <Level name="year" column="THE_YEAR" uniqueMembers="false" />
            <Level name="month" column="THE_MONTH" uniqueMembers="false" />
        </Hierarchy>
    </Dimension>        
    <Measure name="Salary" column="SALARY" aggregator="sum" />
    </Cube> 
</Schema>

1 <%@ page import="mondrian.olap.*"%>
<%
2 Connection connection = 
  DriverManager.getConnection
("Provider=mondrian;                 
 Jdbc=jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:dbname; 
JdbcUser=dbuser;                                     
 JdbcPassword=dbpasswd;                                
Catalog=file:///c:/Tomcat4.1/webapps/mywebapp/WEB-INF/mondriantest.xml; 
JdbcDrivers=oracle.jdbc.driver.OracleDriver;",null,false);  
3 String queryStr=
"select {[Measures].[Salary]} ON COLUMNS,
{[Employee].[employeeId].Members} ON ROWS 
from CubeTest "; 
4 Query query =connection.parseQuery(queryStr);
5 Result result = connection.execute(query);
out.println("get result");
%>