一、前言
安装了saiku之后,每次修改schema文件,非常耗时,每次都要经历若干步骤:修改xml、上传、重启才能生效,并且非常不利于学习和理解MDX和模式文件,踌躇之际,发现了这个工具,十分小巧方便!saiku安装过程可参考上一篇博客:http://www.cnblogs.com/liqiu/p/5183894.html
二、下载
这是一个pentaho的一个工具,有很多版本,下载地址:https://sourceforge.net/projects/mondrian/files/schema%20workbench/3.11.0/
下载之后,执行里面的启动命令即可,windows执行:workbench.bat;linux执行:workbench.sh。我使用的是mac,效果如图:
三、初始化数据:
运行这款软件肯定依赖一个数据库,Mysql或者Postgresql都可以,我使用的是PostgreSql,下面是网络上搜索到的建表语句:
CREATE TABLEsale
(
saleidinteger NOT NULL,
proidinteger,
cusidinteger,
unitpricedouble precision,
numinteger,CONSTRAINT sale_pkey PRIMARY KEY(saleid)
);CREATE TABLEcustomer
(
cusidinteger NOT NULL,
gendercharacter(1),CONSTRAINT customer_pkey PRIMARY KEY(cusid)
);CREATE TABLEproduct
(
proidinteger NOT NULL,
protypeidinteger,
pronamecharacter varying(32),CONSTRAINT product_pkey PRIMARY KEY(proid)
);CREATE TABLEproducttype
(
protypeidinteger NOT NULL,
protypenamecharacter 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,number)values(1,1,1,340.34,2)insert into sale(saleId,proId,cusId,unitPrice,number)values(2,1,2,140.34,1)insert into sale(saleId,proId,cusId,unitPrice,number)values(3,2,3,240.34,3)insert into sale(saleId,proId,cusId,unitPrice,number)values(4,3,4,540.34,4)insert into sale(saleId,proId,cusId,unitPrice,number)values(5,4,1,80.34,5)insert into sale(saleId,proId,cusId,unitPrice,number)values(6,5,2,90.34,26)insert into sale(saleId,proId,cusId,unitPrice,number)values(7,6,3,140.34,7)insert into sale(saleId,proId,cusId,unitPrice,number)values(8,7,4,640.34,28)insert into sale(saleId,proId,cusId,unitPrice,number)values(9,6,1,140.34,29)insert into sale(saleId,proId,cusId,unitPrice,number)values(10,7,2,740.34,29)insert into sale(saleId,proId,cusId,unitPrice,number)values(11,5,3,30.34,28)insert into sale(saleId,proId,cusId,unitPrice,number)values(12,4,4,1240.34,72)insert into sale(saleId,proId,cusId,unitPrice,number)values(13,3,1,314.34,27)insert into sale(saleId,proId,cusId,unitPrice,number)values(14,3,2,45.34,27)
4、配置数据源
点击下面右下脚的图标:
配置数据库链接:
我选择的是PostgreSql,确认即可
5、核心步骤,创建schema
5.1 创建空的schema
5.2 修改schema名称,命名是:qiu-schema
5.3 添加立方体
命名是:qiu-cube
5.4 在立方体里面添加事实表
5.5 在立方体里面添加维度:qiuDimension
5.6 在维度下面,添加层次。其实不需要添加,他会默认添加一下,点击qiuDimension左侧的小图标即可
5.7 在qiu-Hierarchy下面添加维度表,咱们选择的是customer
5.8 继续添加一个层次:qiuLevel
到这里最困难的都已经完成了
5.9 添加度量
到这里一个简单的模式文件就建成了,点击最右侧的带有铅笔样式的图标即可看见xml文件:
如果上面有遗漏的部分,把这个xml文件替换进去,重新点击铅笔样式的图标即可充新生成
6、添加MDX语句,测试模式文件
选择File,在选择MDX Query,即可创建查询对话框,插入如下语句:
select{[Measures].qiuMeasure}oncolumns,
{([qiuDimension].[allCustomer])}onrowsfrom [qiu-cube]
效果如图: