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