saiku的查询都是通过cube来进行的。因此每当我们要进行一次多维度查询时,都要先修改xml、上传、重启才能生效,不仅效率低,还不利于学习和理解MDX和模式文件。
通过 workbench 图形界面工具,可以自动生成 Schema 文件
下载地址:https://sourceforge.net/projects/mondrian/files/schema%20workbench/
选择最新版下载解压即可。
运行这款软件需要依赖一个数据库,Mysql或者Postgresql都可以,我使用的是mysql,下面是网络上搜索到的建表语句:
CREATE TABLE sale
(
saleid integer NOT NULL,
proid integer,
cusid integer,
unitprice double precision,
num integer,
CONSTRAINT sale_pkey PRIMARY KEY (saleid)
);
CREATE TABLE customer
(
cusid integer NOT NULL,
gender character(1),
CONSTRAINT customer_pkey PRIMARY KEY (cusid)
);
CREATE TABLE product
(
proid integer NOT NULL,
protypeid integer,
proname character varying(32),
CONSTRAINT product_pkey PRIMARY KEY (proid)
);
CREATE TABLE producttype
(
protypeid integer NOT NULL,
protypename character varying(32),
CONSTRAINT producttype_pkey PRIMARY KEY (protypeid)
);
insert into Customer(cusId,gender) values(1,'F');
insert into Customer(cusId,gender) values(2,'M');
insert into Customer(cusId,gender) values(3,'M');
insert into Customer(cusId,gender) values(4,'F');
insert into producttype(proTypeId,proTypeName)values(1,'电器');
insert into producttype(proTypeId,proTypeName)values(2,'数码');
insert into producttype(proTypeId,proTypeName)values(3,'家具');
insert into product(proId,proTypeId,proName)values(1,1,'洗衣机');
insert into product(proId,proTypeId,proName)values(2,1,'电视机');
insert into product(proId,proTypeId,proName)values(3,2,'mp3');
insert into product(proId,proTypeId,proName)values(4,2,'mp4');
insert into product(proId,proTypeId,proName) values(5,2,'数码相机');
insert into product(proId,proTypeId,proName)values(6,3,'椅子');
insert into product(proId,proTypeId,proName)values(7,3,'桌子');
insert into sale(saleId,proId,cusId,unitPrice,num)values(1,1,1,340.34,2);
insert into sale(saleId,proId,cusId,unitPrice,num)values(2,1,2,140.34,1);
insert into sale(saleId,proId,cusId,unitPrice,num)values(3,2,3,240.34,3);
insert into sale(saleId,proId,cusId,unitPrice,num)values(4,3,4,540.34,4);
insert into sale(saleId,proId,cusId,unitPrice,num)values(5,4,1,80.34,5);
insert into sale(saleId,proId,cusId,unitPrice,num)values(6,5,2,90.34,26);
insert into sale(saleId,proId,cusId,unitPrice,num)values(7,6,3,140.34,7);
insert into sale(saleId,proId,cusId,unitPrice,num)values(8,7,4,640.34,28);
insert into sale(saleId,proId,cusId,unitPrice,num)values(9,6,1,140.34,29);
insert into sale(saleId,proId,cusId,unitPrice,num)values(10,7,2,740.34,29);
insert into sale(saleId,proId,cusId,unitPrice,num)values(11,5,3,30.34,28);
insert into sale(saleId,proId,cusId,unitPrice,num)values(12,4,4,1240.34,72);
insert into sale(saleId,proId,cusId,unitPrice,num)values(13,3,1,314.34,27);
insert into sale(saleId,proId,cusId,unitPrice,num)values(14,3,2,45.34,27);
将数据库驱动放入 drivers 文件夹中
file -> new schema
修改schema名称,命名是:qiu-schema
schema 右键 -> add cube
修改name为 qiuCube
其实不需要添加,他会默认添加一下,点击qiuDimension左侧的小图标即可
咱们选择的是customer
到这里一个简单的模式文件就建成了,点击最右侧的带有铅笔样式的图标即可看见xml文件:
<Schema name="qiuSchema">
<Cube name="qiuCube" visible="true" cache="true" enabled="true">
<Table name="sale">
</Table>
<Dimension type="StandardDimension" visible="true" foreignKey="cusid" highCardinality="false" name="qiuDimension">
<Hierarchy name="qiuHierarchy " visible="true" hasAll="true" allMemberName="allCoustomer" allMemberCaption="所有名称" allLevelName="all levels" primaryKey="cusid">
<Table name="customer">
</Table>
<Level name="qiuLevel" visible="true" column="gender" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>
<Measure name="qiuMeasure" column="num" datatype="Integer" aggregator="sum" visible="true">
</Measure>
</Cube>
</Schema>
file -> new MDX Query,即可创建查询对话框,运行以下语句:
select
{[Measures].qiuMeasure}
on columns,
{([qiuDimension].[allCoustomer])}
on rows
from [qiuCube]
效果如下图:
原因:填写信息的时候信息不全。漏掉allLevelName、还有字段类型要为Integer
2.saiku、mondrian前奏之——立方体、维度、Schema的基本概念
3.