Saiku_学习_02_Schema Workbench 开发mdx和模式文件

一、前言

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);

View Code

四、配置数据源

1.添加数据库驱动

将数据库驱动放入 drivers 文件夹中

2.配置数据源

五、创建schema

1. 创建空的schema

file -> new schema

修改schema名称，命名是：qiu-schema

2.添加立方体(cube)

schema 右键 -> add cube

修改name为 qiuCube

3. 在立方体里面添加事实表Table

4.在立方体里面添加维度：qiuDimension

5.在维度下面，添加层次。

其实不需要添加，他会默认添加一下，点击qiuDimension左侧的小图标即可

6. 在qiu-Hierarchy下面添加维度表

咱们选择的是customer

7.添加一个层次：qiuLevel

9.添加度量

到这里一个简单的模式文件就建成了，点击最右侧的带有铅笔样式的图标即可看见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="&#25152;&#26377;&#21517;&#31216;" 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>

View Code

六、添加MDX语句，测试模式文件

file -> new MDX Query，即可创建查询对话框，运行以下语句：

select
       {[Measures].qiuMeasure}
on columns,
       {([qiuDimension].[allCoustomer])}
on rows
from [qiuCube]

效果如下图：

七、空指针异常

原因：填写信息的时候信息不全。漏掉allLevelName、还有字段类型要为Integer

二、参考资料

1.Schema Workbench 开发mdx和模式文件

2.saiku、mondrian前奏之——立方体、维度、Schema的基本概念