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
編纂視圖