超详细~冒险单车销售公司BI-tableau项目可视化

一、项目介绍

1、Adventure Works Cycles相关简介

Adventure Works Cycles是AdventureWorks样本数据库所虚构的公司，这是一家大型跨国制造公司。该公司生产和销售金属和复合材料自行车到北美，欧洲和亚洲的商业市场。

2000年，Adventure Works Cycles收购了一家位于墨西哥的小型制造工厂Importadores Neptuno。Importadores Neptuno为Adventure Works Cycles产品系列制造了几个关键子组件。这些子组件被运送到Bothell位置进行最终产品组装。2001年，Importadores Neptuno成为旅游自行车产品集团的唯一制造商和分销商。

在成功实现财政年度之后，Adventure Works Cycles希望通过下面三种方式扩大销售额，第一销售目标定位到最佳客户、第二通过外部网站扩展适用的产品、第三通过降低生产成本来降低销售成本

其中关于客户类型、产品介绍、采购和供应商这三个方面来做一个简单的介绍

客户类型

Adventure Works Cycle这家公司的客户主要有两种:

个体：这些客户购买商品是通过网上零售店铺

商店。 这些是从Adventure Works Cycles销售代表处购买转售产品的零售店或批发店。

产品介绍

这家公司主要有下面四个产品线:

•Adventure Works Cycles 生产的自行车

•自行车部件，例如车轮，踏板或制动组件

•从供应商处购买的自行车服装，用于转售给Adventure Works Cycles的客户。

•从供应商处购买的自行车配件，用于转售给Adventure Works Cycles客户。

采购和供应商

在Adventure Works Cycles，采购部门购买用于制造Adventure Works Cycles自行车的原材料和零件。 Adventure Works Cycles还购买转售产品，如自行车服装和自行车附加装置，如水瓶和水泵。

2、数据源:

https://download.csdn.net/download/fl02china/12790331

项目数据描述：数据来源于adventure Works Cycles公司的的样本数据库，包括了公司4大应用场景的数据：Sales、Finance、Product、Manufacture，内含30个csv文件和1个sql文件，我已经打包上传到百度。

3、项目任务

(1)将数据导入Hive数据库

(2)探索数据，汇总数据建立数据仓库(Sales主题)

(3)tableau实现数据可视化

二、项目过程

1、将数据导入Hive数据库

目标：为了操作方便，不直接在hive里面去建表、导数，而是把建表、导数语句写入shell脚本中，然后在Linux服务器上运行脚本，从而实现在hive库里面建表、导数。

（1）数据来源

数据来源于adventure Works Cycles公司的的样本数据库，包括了公司4大应用场景的数据：Sales、Finance、Product、Manufacture

拿到的数据是30个csv文件和一个sql文件，其中里面的格式使用“|”分隔的。

 

（2）数据清洗

数据源文件是以“|"为分隔符的utf-16LE的CSV文件，以其中的FactFinance.csv表为例，数据如下：

1|20101229|3|1|1|60|22080|2010-12-29 00:00:00

2|20101229|3|1|2|60|20200|2010-12-29 00:00:00

3|20101229|3|1|2|61|2000|2010-12-29 00:00:00

4|20101229|3|1|1|61|2208|2010-12-29 00:00:00

5|20101229|3|1|1|62|1546|2010-12-29 00:00:00

 

使用python，通过pandas将其读取并转换成通常的CSV文件。

 

把 | 统一改为，分隔符，转换后数据如下，还是以以其中的FactFinance.csv表为例

1,20101229,3,1,1,60,22080,2010-12-29 00:00:00

2,20101229,3,1,2,60,20200,2010-12-29 00:00:00

3,20101229,3,1,2,61,2000,2010-12-29 00:00:00

4,20101229,3,1,1,61,2208,2010-12-29 00:00:00

5,20101229,3,1,1,62,1546,2010-12-29 00:00:00

6,20101229,3,1,2,62,1800,2010-12-29 00:00:00

（3）提取建表语句并生成建表shell文件

从instawdbdw.sql文件中提取建表语句，存放到create_table.txt中。create_table.txt内包含所有的建表语句，

 

使用python代码解析create_table.txt文件，这里会用到文件读写、字符串的处理、正则表达式等方法。

定义一个字典，从create_table.txt中逐行读取这30个表的表名和字段名，并且存放在这个字典中。代码如下：

 

创建create_table.sh文件，解析字典中的表名和字段名，在sh文件中写入建表语句

 

#! /bin/sh 是指此脚本使用/bin/sh来解释执行，#!是特殊的表示符，其后面跟的是解释此脚本的shell的路径。

hive -v -e，-v打印执行的sql语句，-e后面接执行的sql语句。

（3）导入表数据 

创建load_create_data.sh文件，将30个csv文件导入数据库对应的表中。

 

生成的load_create_data.sh如下图，直接在linux下面 运行sh load_create_data.sh即可导入到hive中

 

进入hive 可以看到表已经建立好，查询其中一个表可以看到相关数据

可以安装Oracle SQL Developer并添加hive连接驱动（driver可以在cloudera官网下载）且成功连接到hive server2服务

 

Oracle SQL Developer查看到表已经都存在

在linux服务器中查询可以看到如下结果

2、数据探索与数据仓库的搭建

（1）数据探索

1.查看数据库，了解包含哪些可用信息

数据表总共有30个，基本可以分成两类，以fact开头的事实表和以dim开头的维度表，两种表通过主键连接，表的结构设计为星型结构。

ps：星型模型是一种多维的数据关系，它由一个事实表和一组维表组成。每个维表都有一个维作为主键，所有这些维的主键组合成事实表的主键。强调的是对维度进行预处理，将多个维度集合到一个事实表，形成一个宽表。

维度表：表示对分析主题属性的描述。比如地理位置维度表，包含地理位置id、城市、州/省代码、州/省名称、国家/地区代码等描述信息；产品维度表，包含产品id、产品名称、颜色、尺寸、重量等描述信息。通常来说维度表信息比较固定，且数据量小。

事实表：表示对分析主题的度量。比如网络销售事实表，包含客户id、下单时间、销售额、下单量等信息。事实表包含了与各维度表相关联的外码，并通过JOIN方式与维度表关联。事实表的度量通常是数值类型，且记录数会不断增加，表规模迅速增长。

 

以factinternetsales为例，通过自身的主键可以与各个维度表进行关联

 

2.明确分析目标，分解任务

(1)结合项目目标和现有数据，明确分析目标是要向老板以及项目团队展示产品的销售情况；

(2)整合数据仓库的数据，构建E-R图，挖掘销售事实表与各维度表的关联；

(3)构建与销售相关的指标体系。

3.数据分析与初步整理

产品的销售渠道有两种，一种是线上销售（网络），销售数据存在factinternetsales事实表中；另一种是线下销售（经销商），销售数据存在factresellersales事实表中。

a. E-R图

通过E-R图进一步分析事实表与各维度表之间的关联，比如线上销售渠道，factinternetsales事实表中productkey、customerkey、promotionkey、salesterritorykey等字段与维度表有关联。同时，产品相关的维度表有三个，它们之间也存在一定的关联。

factresellersales事实表与factinternetsales事实表的区别在于，线上销售每一笔订单都直接面向最终客户，因此通过customerkey与dimcustomer维度表关联。而线下销售是通过经销商进行售卖，每一笔订单都有记录经销商和销售人员的信息，因此通过resellerkey与dimreseller维度表关联，通过employee与dimemployee关联。

 

factinternetsales事实表与维度表的关联

 

factresellersales事实表与维度表的关联

b. 指标体系

分析维度：

时间维度——年、季度、月、周、日

地区维度——销售大区、国家、州/省、城市

产品维度——产品类别、产品子类

推广维度

客户维度

经销商维度

员工维度

分析指标：

总销售额

总订单量

总成本=产品标准成本+税费+运费

总利润=总销售额-总成本

收入利润率=总利润/总销售额

客单价=总销售额/客户总数

税费

运费

销售额、销量目标达成率

不同维度（时间、地区、产品）下的销售额、订单量

（2）搭建数据仓库

目的：根据实际业务需要，对已经建立好的基础层数据进行加工，并存放到数据仓库汇总层。

数据仓库的设计分为两层，一个是 ODS 基础层，一个是 DW 汇总层 。基础层用来存放基础数据，即前面使用shell脚本导入的数据，而汇总层用来存放我们使用基础层加工生成的数据。

前面已经从实际业务出发，分析了网络销售事实表（factinternetsales）、经销商销售事实表（factresellersales）与各维度表之间的关联，并且罗列出销售方面的关键分析指标。接下来需要建立一个汇总层，用于存放加工后的维度表以及新建的销售数据汇总表。

这里为什么要对维度表进行加工呢？虽然不经加工、直接导入tableau也可以，但是数据表较多、数据量较大，加载速度会很慢。而且字段太多，不是每一个字段都会用到。所以这里的加工包括两个层面，一个是对相同类型的维度表做连接，减少表的数量；另一个是筛选过滤，提取需要分析的关键字段。

另外，这里对网络销售事实表（factinternetsales）和经销商销售事实表（factresellersales）进行整合，提取需要分析的字段（销售额、产品标准成本、运费、税费等），并且创建新的字段（成本、利润等），以便全面分析线上和线下的销售情况。

1.建立数据仓库

新建一个数据库，用于存放加工生成的数据，包括加工后的维度表和事实表。

2.建立数据仓库维度加工表

a. 连接三个产品方面的维度表

连接三个与产品相关的维度表：产品维度表（dimproduct）、产品子类别维度表（dimproductsubcategory）、产品类别维度表（dimproductcategory）。提取需要使用的字段：产品id、产品名称、产品类别id、产品类别名称、产品子类id、产品子类名称。

 

产品维度表

b. 连接两个区域方面的维度表

连接两个与区域相关的维度表：区域维度表（dimsalesterritory）、地理位置维度表（dimgeography）。提取需要使用的字段：区域id、销售大区、销售国家、销售地区、州/省、地理位置id、城市。

 

区域维度表

c. 从各维度表提取分析字段

3.建立数据仓库事实加工表

创建销售汇总表sales_total_dw，这里使用union all连接网络销售事实表（factinternetsales）和经销商销售事实表（factresellersales），注意union all连接的两个表，列名和列数必须完全一致，否则会报错。为了区分每一笔订单是线上还是线下销售记录，新增一个标签销售渠道（sales_channel），线上为“internet”，线下为“reseller”。

PS：factinternetsales需要补齐ResellerKey和EmployeeKey字段 （'null' as ResellerKey,'null' as EmployeeKey,）factresellersales需要补齐CustomerKey字段（'null' as CustomerKey）。

 

三、报表制作

目的：将汇总层数据导入tableau，建立各表之间的关联，并制作销售报表。

1、数据导入

将tableau连接到hive数据库，将加工后的事实表和维度表导入。

2、tableau数据关联

把事实表和维度表进行关联

 

3、数据清洗

数据格式：hive数据库中的数据导入后，可能需要进行格式转换。比如文本格式转换为日期格式，文本格式转换为整数格式，文本格式转换为小数格式，小数格式转换为百分比格式。

新建度量值：对于新增的客单价、收入利润率等指标，可以通过新建度量值的方式进行处理。当然，最好还是在hive中创建字段，这样代码对其他项目做销售数据分析更有借鉴意义。

4、整合制作仪表盘显示数据