mondrain完整案例

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.查询结果如下

测试MySql
log4j: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: 78
Row #0: 7.32
Row #0: 32.12
Row #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: 23
Row #6: 2.32
Row #6: 21.12
Row #7: 55
Row #7: 5.00
Row #7: 11.00