workbench创建cube示例,基于pentaho平台

成立一个多维度立体数据阐发报表

工具/原料

• workbench
• mysql
• navicat for mysql
• pentaho平台

方式/步骤

1. 1

   新建数据库public,utf-8

   SET FOREIGN_KEY_CHECKS=0;

   -- ----------------------------

   -- Table structure for customer

   -- ----------------------------

   DROP TABLE IF EXISTS `customer`;

   CREATE TABLE `customer` (

     `cusid` int(11) NOT NULL,

     `gender` char(1) DEFAULT NULL,

     PRIMARY KEY (`cusid`)

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   -- ----------------------------

   -- Records of customer

   -- ----------------------------

   INSERT INTO `customer` VALUES ('2', 'M');

   INSERT INTO `customer` VALUES ('3', 'M');

   INSERT INTO `customer` VALUES ('4', 'F');

   -- ----------------------------

   -- Table structure for product

   -- ----------------------------

   DROP TABLE IF EXISTS `product`;

   CREATE TABLE `product` (

     `proid` int(11) NOT NULL,

     `protypeid` int(11) DEFAULT NULL,

     `proname` varchar(32) DEFAULT NULL,

     PRIMARY KEY (`proid`)

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   -- ----------------------------

   -- Records of product

   -- ----------------------------

   INSERT INTO `product` VALUES ('1', '1', '洗衣机');

   INSERT INTO `product` VALUES ('2', '1', '电视机');

   INSERT INTO `product` VALUES ('3', '2', 'mp3');

   INSERT INTO `product` VALUES ('4', '2', 'mp4');

   INSERT INTO `product` VALUES ('5', '2', '数码相机');

   INSERT INTO `product` VALUES ('6', '3', '椅子');

   INSERT INTO `product` VALUES ('7', '3', '桌子');

   -- ----------------------------

   -- Table structure for producttype

   -- ----------------------------

   DROP TABLE IF EXISTS `producttype`;

   CREATE TABLE `producttype` (

     `protypeid` int(11) NOT NULL,

     `protypename` varchar(32) DEFAULT NULL,

     PRIMARY KEY (`protypeid`)

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   -- ----------------------------

   -- Records of producttype

   -- ----------------------------

   INSERT INTO `producttype` VALUES ('1', '电器');

   INSERT INTO `producttype` VALUES ('2', '数码');

   INSERT INTO `producttype` VALUES ('3', '家具');

   -- ----------------------------

   -- Table structure for sale

   -- ----------------------------

   DROP TABLE IF EXISTS `sale`;

   CREATE TABLE `sale` (

     `saleid` int(11) NOT NULL,

     `proid` int(11) DEFAULT NULL,

     `cusid` int(11) DEFAULT NULL,

     `unitprice` double DEFAULT NULL,

     `num` int(11) DEFAULT NULL,

     PRIMARY KEY (`saleid`)

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   -- ----------------------------

   -- Records of sale

   -- ----------------------------

   INSERT INTO `sale` VALUES ('1', '1', '1', '340.34', '2');

   INSERT INTO `sale` VALUES ('2', '1', '2', '140.34', '1');

   INSERT INTO `sale` VALUES ('3', '2', '3', '240.34', '3');

   INSERT INTO `sale` VALUES ('4', '3', '4', '540.34', '4');

   INSERT INTO `sale` VALUES ('5', '4', '1', '80.34', '5');

   INSERT INTO `sale` VALUES ('6', '5', '2', '90.34', '26');

   INSERT INTO `sale` VALUES ('7', '6', '3', '140.34', '7');

   INSERT INTO `sale` VALUES ('8', '7', '4', '640.34', '28');

   INSERT INTO `sale` VALUES ('9', '6', '1', '140.34', '29');

   INSERT INTO `sale` VALUES ('10', '7', '2', '740.34', '29');

   INSERT INTO `sale` VALUES ('11', '5', '3', '30.34', '28');

   INSERT INTO `sale` VALUES ('12', '4', '4', '1240.34', '72');

   INSERT INTO `sale` VALUES ('13', '3', '1', '314.34', '27');

   INSERT INTO `sale` VALUES ('14', '3', '2', '45.34', '27');

   
2. 2

   打开psd东西，workbench.bat，双击启动， 点击毗连数据库

   
3. 3

   阅读《官方mondrianschema编写指南.pdf》，在资猜中：官方mondrianschema编写指南.pdf,领会cube相关概念

4. 4

    成立cube，成立流程如下图，新建一个Schema

   
5. 5

   填写Schema名字为public, 点击保留到当地文件夹，文件是xml格局,用于saiku解析利用

   
6. 6

   增添一个cube作为数据阐发

   
7. 7

   点窜立方体名字

   
8. 8

   添加事实表格

   
9. 9

   以发卖表格作为事实表

10. 10

    增添维度

    
11. 11

    同样的操作，建立用户维度，

    
12. 12

    测试

    
13. 13

    到bi后台，增添public数据库毗连

    
14. 14

    导入cube设置装备摆设文件

    
15. 15

    建立cube视图

    
16. 16

    编纂视图