商务智能方法与应用综合实验1参考解决方法

«返回课程汇总页面

【实验手册版本】

当前版本号v20200412

综合练习1

【实验名称】

综合练习1

【实验目的】

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

【实验数据说明】

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

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

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

3. 其中户口地区类型分为三种，即城市、镇和乡村。每个地区类型下，又按集体类型分为两种，家庭户和集体户。

4. 地区名称列需要进行清洗。

【实验环境】

• 操作系统：Windows
• MySQL
• Saiku
• Python
• Excel

【实验步骤1参考解决方法】

1.使用你学过的编程语言或工具，对people.csv数据进行清洗，并导出清洗后的people.csv。可以使用你熟悉的工具或编程语言。

第一题解决办法参考：实验数据提到地区名称列需要进行清洗。那我们第1步就是需要对数据的地区名称进行查看，思考清洗方法。

方法1：使用 Excel 复制出地区名称列，使用去重和排序进行查看。这种方法只适用于小量的数据（10万条记录以内）。

（1）复制出地区列

（2）使用Excel的删除重复值，并排序查看地区列。

（3）对需要修正的数据进行标注，使用Excel的替换功能（ctrl+H）进行手动替换。

方法2：使用编程方式进行替换。这种方法处理大量的数据会更有效率。以下是Python的清洗代码。

distDict={'上海市':'上海','内蒙古自治区':'内蒙古','北京市':'北京','宁夏回族自治区':'宁夏','山 东 省':'山东','江苏省':'江苏','江西省':'江西','河南省':'河南','湖北省':'湖北','甘肃省':'甘肃','四川省':'四川','广东省':'广东'}
w=open('d:/清洗后-people.csv','w',encoding='utf-8-sig',errors='ignore')
try:
    with open('d:/清洗前-people.csv','r',encoding='utf-8-sig') as f:
        file =f.read()
        lines=file.split('\n')
        for line in lines:
            cols=line.split(',')
            dist=cols[0]
            if dist in distDict:
                cols[0]=distDict[dist]
            rs=','.join(cols)+'\n'
            w.write(rs)
finally:
    if w:
        w.close()

distSet=set([])
with open('d:/清洗后-people.csv','r',encoding='utf-8-sig') as f:
    file=f.read()
    lines=file.split('\n')
    for line in lines:
        if line !='':
            cols=line.split(',')
            distSet.add(cols[0])
dists=list(distSet)
dists.sort()
print(len(dists))
print(dists)

【实验步骤2参考解决方法】

2：把清洗后的 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 utf8
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

【实验步骤3参考解决方法】

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

【实验步骤4参考解决方法】

4. 根据星型模式，编写 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)
)
COLLATE='utf8_general_ci'
COMMENT = '性别维度表'
ENGINE=InnoDB;

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

/* 构建地区维度表 */
drop table if exists dim_dist;
CREATE TABLE dim_dist(
    dist_id INT NOT NULL   COMMENT 'dist_id 地区ID' ,
    dist VARCHAR(128)    COMMENT 'dist 地区' ,
    PRIMARY KEY (dist_id)
)
COLLATE='utf8_general_ci'
COMMENT = '地区维度表'
ENGINE=InnoDB;

set @row=0;
insert into dim_dist select (@row:=@row+1) as dist_id,dist from (select dist from dist_pp group by dist) a;

/* 构建户口地区类型维度表 */
drop table if exists dim_hk_dtype;
CREATE TABLE dim_hk_dtype(
    hk_dtype_id INT NOT NULL   COMMENT 'hk_dtype_id 户口地区类型ID' ,
    hk_dtype VARCHAR(128)    COMMENT 'hk_dtype 户口地区类型' ,
    PRIMARY KEY (hk_dtype_id)
)
COLLATE='utf8_general_ci'
COMMENT = '户口地区类型维度表'
ENGINE=InnoDB;

set @row=0;
insert into dim_hk_dtype select (@row:=@row+1) as hk_dtype_id,hk_dtype from (select hk_dtype from dist_pp group by hk_dtype) a;

/* 构建户口集体类型维度表 */
drop table if exists dim_hk_gtype;
CREATE TABLE dim_hk_gtype(
    hk_gtype_id INT NOT NULL   COMMENT 'hk_gtype_id 户口地区类型ID' ,
    hk_gtype VARCHAR(128)    COMMENT 'hk_gtype 户口地区类型' ,
    PRIMARY KEY (hk_gtype_id)
)
COLLATE='utf8_general_ci'
COMMENT = '户口集体类型维度表'
ENGINE=InnoDB;

set @row=0;
insert into dim_hk_gtype select (@row:=@row+1) as hk_gtype_id,hk_gtype from (select hk_gtype from dist_pp group by hk_gtype) a;

/* 构建年份维度表 */
drop table if exists dim_year;
CREATE TABLE dim_year(
    year_id INT NOT NULL   COMMENT 'year_id 年份ID' ,
    year INT    COMMENT 'year 年份' ,
    PRIMARY KEY (year_id)
)
COLLATE='utf8_general_ci'
COMMENT = '年份维度表'
ENGINE=InnoDB;

set @row=4;
insert into dim_year select (@row:=@row+1) as year_id,stat_year from (select stat_year from dist_pp group by stat_year) a;

/* 事实表*/
drop table if exists fact_dist_pp;
CREATE TABLE fact_dist_pp(
    dist_id INT    COMMENT 'dist_id 地区ID' ,
    hk_dtype_id INT    COMMENT 'hk_dtype_id 户口地区类型ID' ,
    hk_gtype_id INT    COMMENT 'hk_gtype_id 户口集体类型ID' ,
    stat_year_id INT    COMMENT 'stat_year_id 统计年ID' ,
    gender_id INT    COMMENT 'gender_id 性别ID' ,
    nop INT    COMMENT 'nop 人数'
)
COLLATE='utf8_general_ci'
COMMENT = '地区人口事实表'
ENGINE=InnoDB;

通过原始表连接各个维度表来构建事实表。

insert into fact_dist_pp
select dist_id,hk_dtype_id,hk_gtype_id,stat_year_id,gender_id,nop
from (
select b.dist_id,d.hk_dtype_id,e.hk_gtype_id,f.year_id as stat_year_id,c.gender_id,a.nop
from dist_pp a
left join dim_dist b on a.dist=b.dist
left join dim_gender c on a.gender=c.gender
left join dim_hk_dtype d on a.hk_dtype=d.hk_dtype
left join dim_hk_gtype e on a.hk_gtype=e.hk_gtype
left join dim_year f on a.stat_year=f.year
) fact

【实验步骤5参考解决方法】

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

<Schema name="New Schema1">
  <Cube name="Cube-ppstat" visible="true" description="&#20154;&#21475;&#26222;&#26597;&#25968;&#25454;&#20998;&#26512;" cache="true" enabled="true">
    <Table name="fact_dist_pp">
    </Table>
    <Dimension type="StandardDimension" visible="true" foreignKey="dist_id" highCardinality="false" name="dim_dist">
      <Hierarchy name="Hierarchy-Dist" visible="true" hasAll="true" primaryKey="dist_id" primaryKeyTable="dim_dist">
        <Table name="dim_dist">
        </Table>
        <Level name="dist" visible="true" table="dim_dist" column="dist" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="gender_id" name="dim_gender">
      <Hierarchy name="Hierachy-Gender" visible="true" hasAll="true" primaryKey="gender_id" primaryKeyTable="dim_gender">
        <Table name="dim_gender">
        </Table>
        <Level name="gender" visible="true" table="dim_gender" column="gender" type="String" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="hk_dtype_id" name="dim_hk_dtype">
      <Hierarchy name="Hierarchy-Dtype" visible="true" hasAll="true" primaryKey="hk_dtype_id" primaryKeyTable="dim_hk_dtype">
        <Table name="dim_hk_dtype">
        </Table>
        <Level name="hk_dtype" visible="true" table="dim_hk_dtype" column="hk_dtype" type="String" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="hk_gtype_id" name="dim_hk_gtype">
      <Hierarchy name="Hierarchy-Gtype" visible="true" hasAll="true">
        <Table name="dim_hk_gtype">
        </Table>
        <Level name="hk_gtype" visible="true" table="dim_hk_gtype" column="hk_gtype" type="String" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension type="StandardDimension" visible="true" foreignKey="stat_year_id" name="dim_year">
      <Hierarchy name="Hierarchy-Year" visible="true" hasAll="true">
        <Table name="dim_year">
        </Table>
        <Level name="year" visible="true" table="dim_year" column="year" type="Integer" uniqueMembers="false">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="nop" column="nop" aggregator="sum" visible="true">
    </Measure>
  </Cube>
</Schema>

【实验步骤6参考解决方法】

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