adventure案例——小结

前言：

本文是对adventure work案例的一个整体小结，描述在做的过程中收获到的东西。
BI链接

image.png

目录如下：
一、业务背景介绍
二、需求实现步骤
三、个人思考步骤

一、业务背景介绍

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

Adventure Works Cycle这家公司的客户主要有两种:
个体：这些客户购买商品是通过网上零售店铺
商店。 这些是从Adventure Works Cycles销售代表处购买转售产品的零售店或批发店。

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

二、需求实现步骤

1. 将数据导入Hive数据库
2. 探索数据库并罗列分析指标
3. 汇总数据建立数据仓库(Sales主题)
   4.powerbi可视化

三、个人思考步骤

1.将数据导入Hive数据库

1.1Hive数据库是什么？

Hive就是在Hadoop上架了一层SQL接口，可以将SQL翻译成MapReduce去Hadoop上执行

image.png

Hive是基于Hadoop的一个 数据仓库工具，可以将结构化的数据文件映射
成一张表，并提供类SQL查询功能；
Hive是构建在Hadoop 之上的数据仓库；
使用HQL作为查询接口；
使用HDFS存储；
使用MapReduce计算；

Hadoop是一个能够对大量数据进行分布式处理的软件框架。 Hadoop 以一种可靠、高效、可伸缩的方式进行数据处理。

Hadoop的框架最核心的设计就是：HDFS和MapReduce。
HDFS为海量的数据提供了存储，则MapReduce为海量的数据提供了计算。

1.2为何要用HIVE？

hive优缺点
优点： 入门简单，避免了去写MapReduce，减少开发人员的学习成本；
统一的元数据管理，可与impala/spark等共享元数据；
灵活性和扩展性比较好：支持UDF，自定义存储格式等；
适合离线数据处理
缺点： Hive的效率比较低，由于hive是基于hadoop，Hadoop本身是一个批处理，高延迟的计算框架
其计算是通过MapReduce来作业，具有高延迟性
Hive适合对非实时的、离线的、对响应及时性要求不高的海量数据批量计算，即查询，统计分析。

1.3有什么数据需要导入？

存在29份CSV文件，里面的分隔符是“|”，没有表头。
有一份SQL文件，是Oracle导出的建表语句。
如图：

image.png
image.png

存在问题：

1.3.1.Hive导入数据分隔符默认为“,”，所以需要将csv文件中的“|”改为“,”

简单方法是：将csv文件打开，利用查找替换，重复操作29次
复杂方法是：利用python循环读取文件，再重新 输出为csv文件
步骤如下：
1）用df.read_csv("文件名",sep="|")进行读取

2）文件名获取利用os.walk("文件路径的文件夹")，
分别会出现 文件绝对路径、文件夹、文件，利用for root,dirs,files in os.walk("文件路径的文件夹")的for file in files 循环文件名
（当然 利用 os.listdir(路径)直接可获得文件列表）

3）在循环内部进行步骤一，避免出错而终止循环，使用

try：
  df.read_csv()
except Exception as e:
   print(e)
   continue

4）输出csv，df.to_csv("文件路径+文件名"），文件路径和文件名则利用步骤二获取的路径+名字。利用os.path.join(路径,文件名)可实现。

1.3.2.csv文件中无表头（字段），表头需要在SQL语句中提炼出来：

利用python的正则表达式（import re）筛选出"[]"中所需要的值，搭配成字典形式，例如：

 if "CREATE TABLE" in content.upper():
            se0bj = re.search(r"\[(.*?)\].\[(.*?)\]",content,re.I) #re.I对大小写不敏感 （.*？）用于分组，默认返回括号内匹配内容 
            if se0bj:
                table_name = se0bj.group(2)
        matOjb = re.search(r"\[(.*?)\] \[(.*?)\].*",content.lstrip(),re.I)

获得字典table_info

image.png

然后利用读取文件函数，进行写入、输出，用到函数有

shell_file = open("文件名","w") #w为写入模式
shell_file.writelines("写入文字") 按一行写入
shell_file.write("写入文字") 不限制行
shell_file.close() 关闭

以试错方式进行，先按一个表进行操作，实现需求成功后，再进行循环遍历。

导入csv文件到hive中的语句也是同样利用python循环读写进行输出。

输出文件应该为 .sh shell后缀文件，在linux系统中 shell脚本可以进行执行。

1.4 linux系统中的hive

本次案例进行的hive是在云端的linux服务器上进行，所以必须掌握了linux的基本使用。

1.4.1 linux的文件系统：

它的文件是按照类型分类进行储存，然后可按需求摆放在不同路径，但文件本身依旧在根目录下。

1.4.2linux的bash 和 shell 认识：

bash是启动器、shell是解释器
bash类似于开启一个虚拟登录的用户，然后该用户对文件进行读取认识、输出等即shell。
脚本的本质是开启了一个子bash，然后执行脚本，再退出子bash。
一般后缀为.sh的就是shell，读取方式两种：
当前Shell：. file.sh 或者 source file.sh
新建子Shell：/bin/bash file 或者 ./file.sh
将.sh变为可执行文件 ：chmod +x file.sh

1.4.3linux系统的文件读取：

cat 只看不编
vi 可编写

2.探索数据库并罗列分析指标

2.1首先了解有什么

有 维度表（Dim开头） 和 事实表（Fact开头）
维度表：固定标签信息，比如地理维度表中有地理id、国家、省份、城市、经纬度等信息，那么在别的表中有地理id字段，连接则获得相应其他字段，节约表的空间。

事实表：实际业务发生过程的存储表。比如淘宝用户每日下单的记录，买家可获得其汇总信息，包括有userid、订单号、支付时间、支付链接等等。

image.png

2.2目的及问题：

1）存在一份adventure的数据仓库，需要对其加工为可视化界面，减少数据加工的花费时间。
2）以销售情况为主题，给老板、员工提供公司业务情况的基本信息。
3）结合数据仓库情况，分析基本信息的指标应有哪些。
4）搭建指标框架结构，设计可视化界面的布局。

2.3数据了解及整理：

由于主题为销售情况，所以重点放在销售表中。
事实表：
存在FactInternetSales（线上销售）和FactResellerSales（线下销售）表，
里面存在字段差异有：
FactResellerSales存在员工主键和经销商主键，而FactInternetSales无。

维度表：
事实表中的主键Key存在有：ProductKey、promotionKey、OrderDateKey、SalesTerritoryKey、(EmployeeKey、ResellerKey)

ER关系图如下：

image.png

2.4罗列可分析的指标和维度：

维度：
        产品维度：产品分类、产品子分类
        时间维度：年、季、月
        地区维度：销售地区
        推广维度：推广方式
    可分析的指标：
        1.总销售额=销售量*客单价
        2.总成本=产品标准成本*销售量+每笔订单的税收+运费
        3.利润情况=销售额-总成本
        4.销售指标达成情况
        5.销售量最佳的产品Top10
        6.各维度下搭配，如时间维度下的销售情况、销售区域变化情况
                                         地区情况下的销售额、推广情况
                                         产品分类下的销售情况

3.相应SQL语句及步骤：

3.1聚合产品表：

image.png

通过Productkey获得分类和子分类

    
    drop table if exists adventure_dw_hjf.dimproduct_hjf;
    
    create table adventure_dw_hjf.dimproduct_hjf as 
    select a.ProductKey,EnglishProductName,EnglishProductSubcategoryName,EnglishProductCategoryName from 
    (select ProductKey,ProductSubcategoryKey,EnglishProductName from adventure_ods_hjf.dimproduct) a left join
    (select ProductSubcategoryKey,ProductCategoryKey,EnglishProductSubcategoryName from adventure_ods_hjf.dimproductsubcategory) b 
    on a.ProductSubcategoryKey=b.ProductSubcategoryKey
    left join
    (select ProductCategoryKey,EnglishProductCategoryName from adventure_ods_hjf.dimproductcategory) c 
    on b.ProductCategoryKey=c.ProductCategoryKey;
    
    select * from adventure_dw_hjf.dimproduct_hjf;

image.png

3.2连接 事实表 新增销售方式 字段，将线下销售和线上销售表合并

image.png

连接事实表为一个，新增字段 saletype(销售方式：internet,reseller)

第一步：
将factinternetsales的维度变与factresellersales相同

select ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,
null as "EmployeeKey",null as "ResellerKey" 
from  adventure_ods_hjf.factinternetsales;

select ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,EmployeeKey,
ResellerKey from  adventure_ods_hjf.factresellersales;

第二步：分别给予factinternetsales 和 factresellersales增加saletype字段

• Factinternetsales

select ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,
null as "EmployeeKey",null as "ResellerKey","internet" as "saletype" 
from  adventure_ods_hjf.factinternetsales;

• Factresellersales

select ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,EmployeeKey,
ResellerKey,"reseller" as "saletype" from  dventure_ods_hjf.factresellersales;

第三步：上述两表结合union

drop table if exists adventure_dw_hjf.fact_sale_hjf;

create table adventure_dw_hjf.fact_sale_hjf as

(select 
ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,
null as "EmployeeKey",null as "ResellerKey","internet" as "saletype" 
from  adventure_ods_hjf.factinternetsales where OrderDate<current_date()) 

union all

(select 
ProductKey,PromotionKey,SalesTerritoryKey,OrderQuantity,TotalProductCost,
SalesAmount,TaxAmt,Freight,OrderDate,DueDate,ShipDate,EmployeeKey,
ResellerKey,"reseller" as "saletype" from  adventure_ods_hjf.factresellersales 
where OrderDate<current_date()) ;

3.3将事实表通过key与各维度表相连（这一步可在BI或者SQL中实现）

可以将维度表需要字段提取到汇总数据库中
- 梳理所需维度表

• 1）DimSalesTerritory

    select SalesTerritoryKey,SalesTerritoryRegion,SalesTerritoryCountry,
SalesTerritoryGroup from adventure_ods_hjf.dimsalesterritory;

• 2）Dimpromotion

    select PromotionKey,EnglishPromotionName,EnglishPromotionCategory 
from adventure_ods_hjf.dimpromotion;

• 3）dimemployee

    select EmployeeKey,concat(" ",FirstName," ",MiddleName," ",LastName)
 as employee_name from adventure_ods_hjf.dimemployee;

• 4）dimreseller

    select ResellerKey,ResellerName from adventure_ods_hjf.dimreseller;

3.4上述事实表最好通过一条语句实现，不要有过度表，为后期更新每日数据方便。

image.png

4.Power BI 可视化

4.1数据清洗：

导入BI后发现格式不正确的，在HIVE中重新清洗正确。

BI只是辅助手段，里面需要的相减获得利润字段，或者相除获得的客单价字段等，在BI进行模拟时，后续回到HIVE中添加相应语句。

4.2书签：

书签类似导航栏，在“视图”——“书签窗格”打开，新建的书签是以当前状态为保存的，可以选择是否关联“数据、显示、当前页”等。

image.png

可以结合“选择窗格”使用书签，在不同书签下，选择窗格可自行选择“显示”或者“隐藏窗格”。

image.png

4.3布局原则：

贴合场景、主次分明、指标清晰。
一般5~6图表即可。
核心指标显示大、指标构成的其余信息科适当减小。
页面以维度为划分，探讨该维度下的不同信息。

4.4 最终成果：

主页： 基本销售指标情况

image.png

（1） 基本指标情况（总销售额、目标达成金额、总订货量、平均运费、平均税费）
（2） 产品分类下的销售情况
（3） 不同渠道下的销售情况
（4）不同地区下的销售情况

趋势：时间维度下的不同趋势

image.png

（1）基本季度、月份、总的销售金额
（2）不同地区、不同渠道、不同产品类型的切片器
（3）销售金额与目标达成金额的比较
（4）税费、运费、单件成本情况

地区：不同地区的销售情况

image.png

（1）按地区划分的销售金额、目标金额、订单量、客单价情况
（2）按地区划分的不同产品分类的订单量
（3）月份切片器
（4）地区图及堆积卡片图