a.xml
- <!-- 以下涉及到表明列名的地方,都必须为大写,因为Java代码会将其转化为 select "COLUMN_A" from "TABLEA" 的形式,都带上了引号 -->
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE Schema SYSTEM "../../../resources/mondrian.dtd" >
- <Schema name="consume">
- <Cube name="consume">
- <!-- 事实表(fact table) -->
- <Table name="FACT_TABLE" />
- <!-- 物品维 -->
- <Dimension name="物品名称" foreignKey="ITEMID"><!--在事实表上的外键列名,注意要大写-->
- <Hierarchy hasAll="true" allMemberName="所有物品" primaryKey="ITEMID">
- <Table name="DIMETION_TABLE_1"></Table>
- <Level name="ITEMNAME" column="ITEMNAME"></Level>
- </Hierarchy>
- </Dimension>
- <Dimension name="购买时间" foreignKey="LOGDAY" type="TimeDimension">
- <Hierarchy hasAll="true" allMemberName="所有时间" primaryKey="LOGDAY" >
- <Table name="DIMETION_TABLE_2"></Table>
- <!-- 这里两个level必须要找需要显示的顺序进行配置 formatter是为了格式化数据,需要写java代码实现mondrian.olap.Member.MemberFormatter接口 -->
- <Level name="D_WEEK" column="D_WEEK" type="Numeric" levelType="TimeWeeks" uniqueMembers="false" formatter="com.mycompany.modrain.formatter.WeekMemberFormatter"></Level>
- <!-- type根据db里的字段进行匹配常用的有Date,Numeric,Time,Boolean等,uniqueMembers用于优化SQL的生成,如果在表里是唯一的,那么就可以设置为true -->
- <Level name="LOGDAY" column="LOGDAY" type="Date" levelType="TimeDays" uniqueMembers="true"></Level>
- </Hierarchy>
- </Dimension>
- <Measure name="数量" column="CNT" aggregator="sum" datatype="Numeric" />
- </Cube>
- </Schema>
- <%@ page session="true" contentType="text/html; charset=UTF-8" %>
- <%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
- <%@ taglib uri="http://java.sun.com/jstl/core" prefix="c"%>
- <jp:mondrianQuery id="query01"
- catalogUri="/WEB-INF/queries/a.xml"
- jdbcDriver="oracle.jdbc.driver.OracleDriver"
- jdbcUrl="jdbc:oracle:thin:@xx.xx.xx.xx:instance" jdbcUser="username" jdbcPassword="password">
- select {[购买时间].[所有时间]} ON COLUMNS,
- {[物品名称].[所有物品]} ON ROWS
- from [consume]
- </jp:mondrianQuery>