商务智能方法与应用综合实验1

2020-04-12

4分钟阅读时长

【实验手册版本】

当前版本号v20200412

版本	修改说明
v20200506	修改导入csv脚本的字符，utf8改为utf8mb4；新增常见问题解答
v20200412	初始化版本

综合练习1

【实验名称】

综合练习1

【实验目的】

综合前四章学习的知识，完成对数据的清洗，提取，维度建模和 OLAP 分析。

【实验数据说明】

请从下面链接下载练习数据：

https://pan.baidu.com/s/1Xj6s2evPcx8TpzpHkvjBDA#提取码u0jg

people.csv 中的数据是中国第五次人口普查（2000年）和第六次人口普查（2010年）的数据。以下为数据列的说明

列序号	说明
1	地区名称
2	户口地区类型，分别为城市/镇/乡村
3	户口集体类型，分别为家庭户/集体户
4	统计年份
5	性别
6	人数

其中户口地区类型分为三种，即城市、镇和乡村。每个地区类型下，又按集体类型分为两种，家庭户和集体户。
地区名称列需要进行清洗。

【实验环境】

操作系统：Windows
MySQL
Saiku
Python
Excel

【实验步骤】

使用你学过的编程语言或工具，对people.csv数据进行清洗，并导出清洗后的people.csv。可以使用你熟悉的工具或编程语言。
把清洗后的 people.csv 导入数据库。可以参考使用以下代码建库，建表和导入。

create database ppstat;

use ppstat;

create user 'ppstat'@'localhost' identified by 'ppstat';
create user 'ppstat'@'%' identified by 'ppstat';
grant all on ppstat.* to 'ppstat'@'localhost';
grant all on ppstat.* to 'ppstat'@'%';

drop table if exists dist_pp;
create table dist_pp(
dist char(20) comment '地区名称',
hk_dtype char(20) comment '户口地区类型，分别为 城市/镇/乡村',
hk_gtype char(20) comment '户口集体类型，分别为 家庭户/集体户',
stat_year char(20) comment '统计年份',
gender char(20) comment '性别',
nop int comment '人数'
)
COMMENT='初始人数统计表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


load data infile 'd:/清洗后-people.csv'
into table dist_pp character set utf8mb4
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

常见错误：

（1）执行load data语句出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement错误。

解决方法：打开windows的服务，找到类似MySQL字样的服务，打开查看my.ini文件的路径。 Snipaste_2020-04-29_12-30-11.png

打开my.ini，在文件最后一行加上这句，并保存。

secure_file_priv=''

重启一下MySQL服务。

接下来的分析会用到dist_pp表的所有的列，主要分析各个维度下的人数。开始维度建模，先确认dist_pp表中数据哪些是属于维度表，哪些是度量，事实表应该如何构造。画出星型模型。
根据星型模式，编写 SQL 脚本。例如构造性别维度表脚本如下。

/* 构建性别维度表 */
drop table if exists dim_gender;
CREATE TABLE dim_gender(
    gender_id INT NOT NULL   COMMENT 'gender_id 性别ID' ,
    gender CHAR(1)    COMMENT 'gender 性别' ,
    PRIMARY KEY (gender_id)
)
COMMENT='初始人数统计表'
COLLATE='utf8_general_ci'
COMMENT = '性别维度表';

insert into dim_gender (gender_id,gender) values(1,'男');
insert into dim_gender (gender_id,gender) values(2,'女');

根据ppstat数据库来构造多维的数据集Cube。使用Schema Workbench来进行构造。

提示1：构造的层级如下所示。

schema
  |- Cube
      |- Table(事实表，必须)
      |- Measure(度量，必须，可以有多个)
      |- Dimesion(维度，必须，可以有多个)
	      |- Hierarchy(层级)
	          |- Table(维度表)
		      |- Level(维度表的列)

提示2：使用Schema Workbench来进行构造多维数据集，每个要素的必填项。

# schema
name

# Cube
name

# Table
name

# Measure
name, aggregator(聚合函数), column(指定事实表哪个列为度量)

# Dimesion
name, foreignKey(事实表连接键), type

# Hierarchy
name, primaryKey(维度表的主键)

# Level
name, table(维度表), column(维度表的列), type(列数据类型), levelType(非时间一般填Regular)

根据上一步生成的多维数据集Cube，导入Saiku，并增加数据源。按以下表头查询数据。

常见问题

1. 导入 csv 到 MySQL 数据库提示`Error Code: 1300. Invalid utf8 character string: ''`。

答: 有可能是csv的编码不是UTF8导致的。

（1）使用 Notepad++ 修改csv的文件编码为 UTF8.

（2）修改字符集为utf8mb4进行导入

load data infile 'd:/清洗后-people.csv'
into table dist_pp character set utf8mb4
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

2. 导入 csv 到 MySQL 数据库提示`Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement`。

答：可能是由于 MySQL 的数据导入和导出操作安全限制选项设置，导致不能导入导出数据。

（1）运行以下命令查看，如果选项为Null，则需要执行第2步后面的操作。

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

（2）打开Windows电脑的控制面板\所有控制面板项\管理工具\服务，找到MYSQL或者MYSQLD服务，右键查看属性。找到MySQL 设置文件my.ini的路径。