Adventure Works Cycles案例分析

本文是对Adventure Works案例的一个总结，记录了整个项目需求分析与实现的过程，主要任务是使用Hive SQL完成ETL过程，并且连接到PowerBI实现可视化。

---

可视化报表链接

---

一、背景介绍

Adventure Works Cycles是Adventure Works样本数据库所虚构的公司，这是一家大型跨国制造公司。该公司生产和销售自行车到北美，欧洲和亚洲的商业市场。虽然其基地业务位于华盛顿州博塞尔，拥有290名员工，但几个区域销售团队遍布整个市场。

产品介绍

这家公司主要有下面三个产品线：
Adventure Works Cycles生产的自行车；
自行车部件，例如车轮，踏板或制动组件；
从供应商处购买的自行车服装，用于转售给Adventure Works Cycles的客户；

二、需求分析与实现

项目目标：通过现有数据监控商品的线上和线下销售情况，并且获取最新的商品销售趋势，以及区域分布情况，为公司的制造和销售提供指导性建议，以增加公司的收益。

项目任务：

将数据导入Hive数据库
探索数据库并罗列分析指标
汇总数据建立数据仓库(Sales主题)

2.1 使用sqoop+shell导入数据到 hive 数据库

表不存在创建并复制数据
表存在则追加数据

sqoop import \
--hive-import \
--connect jdbc:mysql://127.0.0.1:3306/数据库名 \
--table 表名 \
--username 数据库用户名 \
--password 数据库密码 \
--hive-database hive数据库名 \
--hive-overwrite \
--m 1

--hive-overwrite \  覆盖hive表数据

使用sqoop部分导入数据到 hive 数据库:query 查询要导入的数据集

sqoop import \
--hive-import \
--connect jdbc:mysql://127.0.0.1:3306/数据库名 \
--username 数据库用户名 \
--password 数据库密码 \
--query 'select * from course where 条件 and $CONDITIONS' \
--hive-overwrite \
--hive-table ods.表名 \
--target-dir /tmp/lantest \
--delete-target-dir \
--m 1

表不存在创建并复制数据
表存在则追加数据

使用query sql语句来进行查找不能加参数 --table，必须要添加 where 条件。
并且where 条件后面必须带一个 $CONDITIONS 这个字符串，

2.2 指标体系

分析维度：

时间维度——年、季度、月、日
地区维度——国家、州/省、城市
产品维度——产品类别、产品子类
客户味道
分析指标：

总销售额
总订单量
客单价=总销售额/客户总数
销售额、销量目标达成率
不同维度（时间、地区、产品）下的销售额、订单量

2.3 建立数据仓库汇总层

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

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

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

三、制作报表

选择合适的可视化工具，从多个维度展示销售情况。

3.1 数据清洗

数据格式：hive数据库中的数据导入后，可能需要进行格式转换。比如文本格式转换为日期格式，文本格式转换为整数格式，文本格式转换为小数格式，小数格式转换为百分比格式。虽然在汇总层中已经将日期处理成yyyy-mm-dd格式，但是在Power BI中默认是文本格式，要进行设置。这里想说明一点，虽然Power BI中可以设置格式，但是最好还是回到hive中进行数据清洗，否则刷新数据时可能会出现格式不匹配。
新建度量值：对于新增的客单价、销售额目标完成率目标等指标，可以通过新建度量值的方式进行处理。当然，最好还是在hive中创建字段，这样代码对其他项目做销售数据分析更有借鉴意义。

3.2 核心操作

可视化工具：这里用到的可视化工具有折线图、柱形图、折线-柱形组合图、仪表、卡片、柱状图、切片器、地图等。可以根据需要选择图例、轴、列，以及设置数据处理方式，求和、平均值、最大值、最小值等。
筛选器：有三种筛选器：视觉对象、此页、所有页面。这里用于日期、区域等字段的筛选。
书签窗格：这里将按钮和书签结合使用，用于制作导航栏和动态图表。
选择窗格：可以选择显示/隐藏视觉对象。

3.3 报表展示

报表一共有3页，包括主页、时间趋势图、区域分布图。

a. 主页展示内容：

• 基本销售指标，包括销售额、订单量、客单价、销售额目标完成率等
• 从时间维度分析年度、季度、月度、日销售情况
• 从地区维度分析在各国的销售情况
• 从产品类别及子类维度分析各类商品的销售情况，以及占主要销售额的自行车类销售情况
  

b. 时间趋势图展示内容：

• 按照季度、月份展示销售额、订单量、销售目标完成率、客单价等指标
• 区域切片器
• 当然，这里还可以增加更多的时间维度，比如年、周、日。

c. 区域分布图展示内容：

• 按照国家、州/省、城市，逐级展示销售额和订单量等指标
• 区域、商品类型切片器