mondrain完整案例

来源：互联网发布：阀门公称压力算法编辑：程序博客网时间：2024/06/05 22:36

mondrain完整案例

1.创建表格

-- sales_fact_1997表

create table sales_fact_1997(    product_id int,    time_id int,    customer_id int,    promotion_id int,    store_id int,    store_sales DECIMAL(10,4),    store_cost DECIMAL(10,4),    unit_sale DECIMAL(10,4));

--product表

create table product(    proId int,    proName varchar(30));

2.添加数据

3.创建mdx语句

<?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>

4.java代码

import java.io.PrintWriter;
import mondrian.olap.Connection;
import mondrian.olap.DriverManager;
import mondrian.olap.Query;
import mondrian.olap.Result;

public void testMySqlPro() {System.out.println("测试MySql");String connectString = "Provider=mondrian;"+ "Jdbc=jdbc:mysql://localhost:3306/foodmart?useUnicode=true&characterEncoding=utf-8;"+ "Catalog=file:E:\\2012\\yhz\\test\\terzz\\src\\demo\\pro.xml;"+ "JdbcUser=root;JdbcPassword=root;"+ "JdbcDrivers=com.mysql.jdbc.Driver"; Connection connection = DriverManager.getConnection(connectString, null);String mdxStr = "SELECT {[Measures].[Unit Sales], [Measures].[Store cost], [Measures].[Store Sales]} ON COLUMNS"+ ",{[Product].members} ON ROWS  FROM [Sales]";Query query = connection.parseQuery(mdxStr);@SuppressWarnings("deprecation")Result result = connection.execute(query);result.print(new PrintWriter(System.out, true));}

5.查询结果如下

测试MySqllog4j:WARN No appenders could be found for logger (mondrian.olap.MondrianProperties).log4j:WARN Please initialize the log4j system properly.Axis #0:{}Axis #1:{[Measures].[Unit Sales]}{[Measures].[Store Cost]}{[Measures].[Store Sales]}Axis #2:{[Product].[All Products]}{[Product].[mp3]}{[Product].[mp4]}{[Product].[数码相机]}{[Product].[桌子]}{[Product].[椅子]}{[Product].[洗衣机]}{[Product].[电视机]}Row #0: 78Row #0: 7.32Row #0: 32.12Row #1: Row #1: Row #1: Row #2: Row #2: Row #2: Row #3: Row #3: Row #3: Row #4: Row #4: Row #4: Row #5: Row #5: Row #5: Row #6: 23Row #6: 2.32Row #6: 21.12Row #7: 55Row #7: 5.00Row #7: 11.00