前言：

本文是對adventure work案例的一個整體小結(jié)窝稿，描述在做的過程中收獲到的東西。
BI鏈接

image.png

目錄如下：
一凿掂、業(yè)務(wù)背景介紹
二伴榔、需求實(shí)現(xiàn)步驟
三、個人思考步驟

一庄萎、業(yè)務(wù)背景介紹

Adventure Works Cycles是AdventureWorks樣本數(shù)據(jù)庫所虛構(gòu)的公司踪少，這是一家大型跨國制造公司。該公司生產(chǎn)和銷售金屬和復(fù)合材料自行車到北美糠涛，歐洲和亞洲的商業(yè)市場援奢。

Adventure Works Cycle這家公司的客戶主要有兩種:
個體：這些客戶購買商品是通過網(wǎng)上零售店鋪
商店。 這些是從Adventure Works Cycles銷售代表處購買轉(zhuǎn)售產(chǎn)品的零售店或批發(fā)店忍捡。

這家公司主要有下面四個產(chǎn)品線:
? Adventure Works Cycles 生產(chǎn)的自行車
? 自行車部件集漾，例如車輪，踏板或制動組件
? 從供應(yīng)商處購買的自行車服裝砸脊，用于轉(zhuǎn)售給Adventure Works Cycles的客戶具篇。
? 從供應(yīng)商處購買的自行車配件，用于轉(zhuǎn)售給Adventure Works Cycles客戶凌埂。

二栽连、需求實(shí)現(xiàn)步驟

1. 將數(shù)據(jù)導(dǎo)入Hive數(shù)據(jù)庫
2. 探索數(shù)據(jù)庫并羅列分析指標(biāo)
3. 匯總數(shù)據(jù)建立數(shù)據(jù)倉庫(Sales主題)
   4.powerbi可視化

三、個人思考步驟

1.將數(shù)據(jù)導(dǎo)入Hive數(shù)據(jù)庫

1.1Hive數(shù)據(jù)庫是什么？

Hive就是在Hadoop上架了一層SQL接口秒紧，可以將SQL翻譯成MapReduce去Hadoop上執(zhí)行

image.png

Hive是基于Hadoop的一個 數(shù)據(jù)倉庫工具，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射
成一張表挨下，并提供類SQL查詢功能熔恢；
Hive是構(gòu)建在Hadoop 之上的數(shù)據(jù)倉庫；
使用HQL作為查詢接口臭笆；
使用HDFS存儲叙淌；
使用MapReduce計算；

Hadoop是一個能夠?qū)Υ罅繑?shù)據(jù)進(jìn)行分布式處理的軟件框架愁铺。 Hadoop 以一種可靠鹰霍、高效、可伸縮的方式進(jìn)行數(shù)據(jù)處理茵乱。

Hadoop的框架最核心的設(shè)計就是：HDFS和MapReduce茂洒。
HDFS為海量的數(shù)據(jù)提供了存儲，則MapReduce為海量的數(shù)據(jù)提供了計算瓶竭。

1.2為何要用HIVE督勺？

hive優(yōu)缺點(diǎn)
優(yōu)點(diǎn)： 入門簡單，避免了去寫MapReduce斤贰，減少開發(fā)人員的學(xué)習(xí)成本智哀；
統(tǒng)一的元數(shù)據(jù)管理，可與impala/spark等共享元數(shù)據(jù)荧恍；
靈活性和擴(kuò)展性比較好：支持UDF瓷叫，自定義存儲格式等；
適合離線數(shù)據(jù)處理
缺點(diǎn)： Hive的效率比較低送巡，由于hive是基于hadoop摹菠，Hadoop本身是一個批處理，高延遲的計算框架
其計算是通過MapReduce來作業(yè)授艰，具有高延遲性
Hive適合對非實(shí)時的辨嗽、離線的、對響應(yīng)及時性要求不高的海量數(shù)據(jù)批量計算淮腾，即查詢糟需，統(tǒng)計分析。

1.3有什么數(shù)據(jù)需要導(dǎo)入谷朝？

存在29份CSV文件洲押，里面的分隔符是“|”，沒有表頭圆凰。
有一份SQL文件杈帐，是Oracle導(dǎo)出的建表語句。
如圖：

image.png

image.png

存在問題：

1.3.1.Hive導(dǎo)入數(shù)據(jù)分隔符默認(rèn)為“,”，所以需要將csv文件中的“|”改為“,”

簡單方法是：將csv文件打開挑童，利用查找替換累铅，重復(fù)操作29次
復(fù)雜方法是：利用python循環(huán)讀取文件，再重新 輸出為csv文件
步驟如下：
1）用df.read_csv("文件名",sep="|")進(jìn)行讀取

2）文件名獲取利用os.walk("文件路徑的文件夾")站叼，
分別會出現(xiàn) 文件絕對路徑娃兽、文件夾、文件尽楔，利用for root,dirs,files in os.walk("文件路徑的文件夾")的for file in files 循環(huán)文件名
（當(dāng)然 利用 os.listdir(路徑)直接可獲得文件列表）

3）在循環(huán)內(nèi)部進(jìn)行步驟一投储，避免出錯而終止循環(huán)，使用

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

4）輸出csv阔馋，df.to_csv("文件路徑+文件名"）玛荞，文件路徑和文件名則利用步驟二獲取的路徑+名字。利用os.path.join(路徑,文件名)可實(shí)現(xiàn)呕寝。

1.3.2.csv文件中無表頭（字段）勋眯，表頭需要在SQL語句中提煉出來：

利用python的正則表達(dá)式（import re）篩選出"[]"中所需要的值，搭配成字典形式壁涎，例如：

 if "CREATE TABLE" in content.upper():
            se0bj = re.search(r"\[(.*?)\].\[(.*?)\]",content,re.I) #re.I對大小寫不敏感 （.*凡恍？）用于分組，默認(rèn)返回括號內(nèi)匹配內(nèi)容 
            if se0bj:
                table_name = se0bj.group(2)
        matOjb = re.search(r"\[(.*?)\] \[(.*?)\].*",content.lstrip(),re.I)

獲得字典table_info

image.png

然后利用讀取文件函數(shù)怔球，進(jìn)行寫入嚼酝、輸出，用到函數(shù)有

shell_file = open("文件名","w") #w為寫入模式
shell_file.writelines("寫入文字") 按一行寫入
shell_file.write("寫入文字") 不限制行
shell_file.close() 關(guān)閉

以試錯方式進(jìn)行竟坛，先按一個表進(jìn)行操作闽巩，實(shí)現(xiàn)需求成功后，再進(jìn)行循環(huán)遍歷担汤。

導(dǎo)入csv文件到hive中的語句也是同樣利用python循環(huán)讀寫進(jìn)行輸出涎跨。

輸出文件應(yīng)該為 .sh shell后綴文件，在linux系統(tǒng)中 shell腳本可以進(jìn)行執(zhí)行崭歧。

1.4 linux系統(tǒng)中的hive

本次案例進(jìn)行的hive是在云端的linux服務(wù)器上進(jìn)行隅很，所以必須掌握了linux的基本使用。

1.4.1 linux的文件系統(tǒng)：

它的文件是按照類型分類進(jìn)行儲存率碾，然后可按需求擺放在不同路徑叔营，但文件本身依舊在根目錄下。

1.4.2linux的bash 和 shell 認(rèn)識：

bash是啟動器所宰、shell是解釋器
bash類似于開啟一個虛擬登錄的用戶绒尊，然后該用戶對文件進(jìn)行讀取認(rèn)識、輸出等即shell仔粥。
腳本的本質(zhì)是開啟了一個子bash婴谱，然后執(zhí)行腳本蟹但，再退出子bash。
一般后綴為.sh的就是shell谭羔，讀取方式兩種：
當(dāng)前Shell：. file.sh 或者 source file.sh
新建子Shell：/bin/bash file 或者 ./file.sh
將.sh變?yōu)榭蓤?zhí)行文件 ：chmod +x file.sh

1.4.3linux系統(tǒng)的文件讀然恰：

cat 只看不編
vi 可編寫

2.探索數(shù)據(jù)庫并羅列分析指標(biāo)

2.1首先了解有什么

有 維度表（Dim開頭） 和 事實(shí)表（Fact開頭）
維度表：固定標(biāo)簽信息，比如地理維度表中有地理id口糕、國家缅阳、省份、城市景描、經(jīng)緯度等信息，那么在別的表中有地理id字段秀撇，連接則獲得相應(yīng)其他字段超棺，節(jié)約表的空間。

事實(shí)表：實(shí)際業(yè)務(wù)發(fā)生過程的存儲表呵燕。比如淘寶用戶每日下單的記錄棠绘，買家可獲得其匯總信息，包括有userid再扭、訂單號氧苍、支付時間、支付鏈接等等泛范。

image.png

2.2目的及問題：

1）存在一份adventure的數(shù)據(jù)倉庫让虐，需要對其加工為可視化界面，減少數(shù)據(jù)加工的花費(fèi)時間罢荡。
2）以銷售情況為主題赡突，給老板、員工提供公司業(yè)務(wù)情況的基本信息区赵。
3）結(jié)合數(shù)據(jù)倉庫情況惭缰，分析基本信息的指標(biāo)應(yīng)有哪些。
4）搭建指標(biāo)框架結(jié)構(gòu)笼才，設(shè)計可視化界面的布局漱受。

2.3數(shù)據(jù)了解及整理：

由于主題為銷售情況，所以重點(diǎn)放在銷售表中骡送。
事實(shí)表：
存在FactInternetSales（線上銷售）和FactResellerSales（線下銷售）表昂羡，
里面存在字段差異有：
FactResellerSales存在員工主鍵和經(jīng)銷商主鍵，而FactInternetSales無各谚。

維度表：
事實(shí)表中的主鍵Key存在有：ProductKey紧憾、promotionKey、OrderDateKey昌渤、SalesTerritoryKey赴穗、(EmployeeKey、ResellerKey)

ER關(guān)系圖如下：

image.png

2.4羅列可分析的指標(biāo)和維度：

維度：
        產(chǎn)品維度：產(chǎn)品分類、產(chǎn)品子分類
        時間維度：年般眉、季了赵、月
        地區(qū)維度：銷售地區(qū)
        推廣維度：推廣方式
    可分析的指標(biāo)：
        1.總銷售額=銷售量*客單價
        2.總成本=產(chǎn)品標(biāo)準(zhǔn)成本*銷售量+每筆訂單的稅收+運(yùn)費(fèi)
        3.利潤情況=銷售額-總成本
        4.銷售指標(biāo)達(dá)成情況
        5.銷售量最佳的產(chǎn)品Top10
        6.各維度下搭配，如時間維度下的銷售情況甸赃、銷售區(qū)域變化情況
                                         地區(qū)情況下的銷售額柿汛、推廣情況
                                         產(chǎn)品分類下的銷售情況

3.相應(yīng)SQL語句及步驟：

3.1聚合產(chǎn)品表：

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連接 事實(shí)表 新增銷售方式 字段，將線下銷售和線上銷售表合并

image.png

連接事實(shí)表為一個埠对，新增字段 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;

第三步：上述兩表結(jié)合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將事實(shí)表通過key與各維度表相連（這一步可在BI或者SQL中實(shí)現(xiàn)）

可以將維度表需要字段提取到匯總數(shù)據(jù)庫中
- 梳理所需維度表

• 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上述事實(shí)表最好通過一條語句實(shí)現(xiàn)络断，不要有過度表，為后期更新每日數(shù)據(jù)方便项玛。

image.png

4.Power BI 可視化

4.1數(shù)據(jù)清洗：

導(dǎo)入BI后發(fā)現(xiàn)格式不正確的貌笨，在HIVE中重新清洗正確。

BI只是輔助手段襟沮，里面需要的相減獲得利潤字段锥惋，或者相除獲得的客單價字段等，在BI進(jìn)行模擬時开伏，后續(xù)回到HIVE中添加相應(yīng)語句膀跌。

4.2書簽：

書簽類似導(dǎo)航欄，在“視圖”——“書簽窗格”打開固灵，新建的書簽是以當(dāng)前狀態(tài)為保存的捅伤，可以選擇是否關(guān)聯(lián)“數(shù)據(jù)、顯示怎虫、當(dāng)前頁”等暑认。

image.png

可以結(jié)合“選擇窗格”使用書簽，在不同書簽下大审，選擇窗格可自行選擇“顯示”或者“隱藏窗格”蘸际。

image.png

4.3布局原則：

貼合場景、主次分明徒扶、指標(biāo)清晰粮彤。
一般5~6圖表即可。
核心指標(biāo)顯示大姜骡、指標(biāo)構(gòu)成的其余信息科適當(dāng)減小导坟。
頁面以維度為劃分，探討該維度下的不同信息圈澈。

4.4 最終成果：

主頁： 基本銷售指標(biāo)情況

image.png

（1） 基本指標(biāo)情況（總銷售額惫周、目標(biāo)達(dá)成金額、總訂貨量康栈、平均運(yùn)費(fèi)递递、平均稅費(fèi)）
（2） 產(chǎn)品分類下的銷售情況
（3） 不同渠道下的銷售情況
（4）不同地區(qū)下的銷售情況

趨勢：時間維度下的不同趨勢

image.png

（1）基本季度喷橙、月份、總的銷售金額
（2）不同地區(qū)登舞、不同渠道贰逾、不同產(chǎn)品類型的切片器
（3）銷售金額與目標(biāo)達(dá)成金額的比較
（4）稅費(fèi)、運(yùn)費(fèi)菠秒、單件成本情況

地區(qū)：不同地區(qū)的銷售情況

image.png

（1）按地區(qū)劃分的銷售金額疙剑、目標(biāo)金額、訂單量践叠、客單價情況
（2）按地區(qū)劃分的不同產(chǎn)品分類的訂單量
（3）月份切片器
（4）地區(qū)圖及堆積卡片圖