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

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

东西/原料

workbench
mysql
navicat for mysql
pentaho平台

方式/步调

1
新建数据库public,utf-8
/*
Navicat MySQL Data Transfer
Source Server : localhost-hero
Source Server Version : 50711
Source Host : localhost:3306
Source Database : public
Target Server Type : MYSQL
Target Server Version : 50711
File Encoding : 65001
Date: 2018-02-28 10:27:15
*/
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
打开psd东西，workbench.bat，双击启动，点击毗连数据库
3
阅读《官方mondrianschema编写指南.pdf》，在资猜中：官方mondrianschema编写指南.pdf,领会cube相关概念
4
成立cube，成立流程如下图，新建一个Schema
5
填写Schema名字为public, 点击保留到当地文件夹，文件是xml格局,用于saiku解析利用
6
增添一个cube作为数据阐发
7
点窜立方体名字
8
添加事实表格
9
以发卖表格作为事实表
10
增添维度
11
同样的操作，建立用户维度，
12
测试
13
到bi后台，增添public数据库毗连
14
导入cube设置装备摆设文件
15
建立cube视图
16
编纂视图