一、項(xiàng)目介紹
1、Adventure Works Cycles相關(guān)簡介
Adventure Works Cycles是AdventureWorks樣本數(shù)據(jù)庫所虛構(gòu)的公司，這是一家大型跨國制造公司。該公司生產(chǎn)和銷售金屬和復(fù)合材料自行車到北美，歐洲和亞洲的商業(yè)市場。
2000年，Adventure Works Cycles收購了一家位于墨西哥的小型制造工廠Importadores Neptuno。Importadores Neptuno為Adventure Works Cycles產(chǎn)品系列制造了幾個(gè)關(guān)鍵子組件。這些子組件被運(yùn)送到Bothell位置進(jìn)行最終產(chǎn)品組裝。2001年，Importadores Neptuno成為旅游自行車產(chǎn)品集團(tuán)的唯一制造商和分銷商。
在成功實(shí)現(xiàn)財(cái)政年度之后，Adventure Works Cycles希望通過下面三種方式擴(kuò)大銷售額，第一銷售目標(biāo)定位到最佳客戶、第二通過外部網(wǎng)站擴(kuò)展適用的產(chǎn)品、第三通過降低生產(chǎn)成本來降低銷售成本
其中關(guān)于客戶類型、產(chǎn)品介紹、采購和供應(yīng)商這三個(gè)方面來做一個(gè)簡單的介紹
客戶類型
Adventure Works Cycle這家公司的客戶主要有兩種:
個(gè)體：這些客戶購買商品是通過網(wǎng)上零售店鋪
商店。 這些是從Adventure Works Cycles銷售代表處購買轉(zhuǎn)售產(chǎn)品的零售店或批發(fā)店。
產(chǎn)品介紹
這家公司主要有下面四個(gè)產(chǎn)品線:
?Adventure Works Cycles 生產(chǎn)的自行車
?自行車部件，例如車輪，踏板或制動組件
?從供應(yīng)商處購買的自行車服裝，用于轉(zhuǎn)售給Adventure Works Cycles的客戶。
?從供應(yīng)商處購買的自行車配件，用于轉(zhuǎn)售給Adventure Works Cycles客戶。
采購和供應(yīng)商
在Adventure Works Cycles，采購部門購買用于制造Adventure Works Cycles自行車的原材料和零件。 Adventure Works Cycles還購買轉(zhuǎn)售產(chǎn)品，如自行車服裝和自行車附加裝置，如水瓶和水泵。
2、數(shù)據(jù)源:
本人已經(jīng)提取上傳百度云
鏈接：https://pan.baidu.com/s/1B3doA9yYtCXrgmik-wAaUw
提取碼：jk9z
項(xiàng)目數(shù)據(jù)描述：數(shù)據(jù)來源于adventure Works Cycles公司的的樣本數(shù)據(jù)庫，包括了公司4大應(yīng)用場景的數(shù)據(jù)：Sales、Finance、Product、Manufacture，內(nèi)含30個(gè)csv文件和1個(gè)sql文件，我已經(jīng)打包上傳到百度。
3、項(xiàng)目任務(wù)
(1)將數(shù)據(jù)導(dǎo)入Hive數(shù)據(jù)庫
(2)探索數(shù)據(jù)，匯總數(shù)據(jù)建立數(shù)據(jù)倉庫(Sales主題)
(3)tableau實(shí)現(xiàn)數(shù)據(jù)可視化
二、項(xiàng)目過程
1、將數(shù)據(jù)導(dǎo)入Hive數(shù)據(jù)庫
目標(biāo)：為了操作方便，不直接在hive里面去建表、導(dǎo)數(shù)，而是把建表、導(dǎo)數(shù)語句寫入shell腳本中，然后在Linux服務(wù)器上運(yùn)行腳本，從而實(shí)現(xiàn)在hive庫里面建表、導(dǎo)數(shù)。
（1）數(shù)據(jù)來源
數(shù)據(jù)來源于adventure Works Cycles公司的的樣本數(shù)據(jù)庫，包括了公司4大應(yīng)用場景的數(shù)據(jù)：Sales、Finance、Product、Manufacture
拿到的數(shù)據(jù)是30個(gè)csv文件和一個(gè)sql文件，其中里面的格式使用“|”分隔的。
（2）數(shù)據(jù)清洗
數(shù)據(jù)源文件是以“|"為分隔符的utf-16LE的CSV文件，以其中的FactFinance.csv表為例，數(shù)據(jù)如下：
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將其讀取并轉(zhuǎn)換成通常的CSV文件。
把 | 統(tǒng)一改為，分隔符，轉(zhuǎn)換后數(shù)據(jù)如下，還是以以其中的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內(nèi)包含所有的建表語句，
使用python代碼解析create_table.txt文件，這里會用到文件讀寫、字符串的處理、正則表達(dá)式等方法。
定義一個(gè)字典，從create_table.txt中逐行讀取這30個(gè)表的表名和字段名，并且存放在這個(gè)字典中。代碼如下：
創(chuàng)建create_table.sh文件，解析字典中的表名和字段名，在sh文件中寫入建表語句
#! /bin/sh 是指此腳本使用/bin/sh來解釋執(zhí)行，#!是特殊的表示符，其后面跟的是解釋此腳本的shell的路徑。
hive -v -e，-v打印執(zhí)行的sql語句，-e后面接執(zhí)行的sql語句。
（3）導(dǎo)入表數(shù)據(jù)?
創(chuàng)建load_create_data.sh文件，將30個(gè)csv文件導(dǎo)入數(shù)據(jù)庫對應(yīng)的表中。
生成的load_create_data.sh如下圖，直接在linux下面 運(yùn)行sh load_create_data.sh即可導(dǎo)入到hive中
進(jìn)入hive 可以看到表已經(jīng)建立好，查詢其中一個(gè)表可以看到相關(guān)數(shù)據(jù)
可以安裝Oracle SQL Developer并添加hive連接驅(qū)動（driver可以在cloudera官網(wǎng)下載）且成功連接到hive server2服務(wù)
Oracle SQL Developer查看到表已經(jīng)都存在
在linux服務(wù)器中查詢可以看到如下結(jié)果
2、數(shù)據(jù)探索與數(shù)據(jù)倉庫的搭建
（1）數(shù)據(jù)探索
1.查看數(shù)據(jù)庫，了解包含哪些可用信息
數(shù)據(jù)表總共有30個(gè)，基本可以分成兩類，以fact開頭的事實(shí)表和以dim開頭的維度表，兩種表通過主鍵連接，表的結(jié)構(gòu)設(shè)計(jì)為星型結(jié)構(gòu)。
ps：星型模型是一種多維的數(shù)據(jù)關(guān)系，它由一個(gè)事實(shí)表和一組維表組成。每個(gè)維表都有一個(gè)維作為主鍵，所有這些維的主鍵組合成事實(shí)表的主鍵。強(qiáng)調(diào)的是對維度進(jìn)行預(yù)處理，將多個(gè)維度集合到一個(gè)事實(shí)表，形成一個(gè)寬表。
維度表：表示對分析主題屬性的描述。比如地理位置維度表，包含地理位置id、城市、州/省代碼、州/省名稱、國家/地區(qū)代碼等描述信息；產(chǎn)品維度表，包含產(chǎn)品id、產(chǎn)品名稱、顏色、尺寸、重量等描述信息。通常來說維度表信息比較固定，且數(shù)據(jù)量小。
事實(shí)表：表示對分析主題的度量。比如網(wǎng)絡(luò)銷售事實(shí)表，包含客戶id、下單時(shí)間、銷售額、下單量等信息。事實(shí)表包含了與各維度表相關(guān)聯(lián)的外碼，并通過JOIN方式與維度表關(guān)聯(lián)。事實(shí)表的度量通常是數(shù)值類型，且記錄數(shù)會不斷增加，表規(guī)模迅速增長。
以factinternetsales為例，通過自身的主鍵可以與各個(gè)維度表進(jìn)行關(guān)聯(lián)
2.明確分析目標(biāo)，分解任務(wù)
(1)結(jié)合項(xiàng)目目標(biāo)和現(xiàn)有數(shù)據(jù)，明確分析目標(biāo)是要向老板以及項(xiàng)目團(tuán)隊(duì)展示產(chǎn)品的銷售情況；
(2)整合數(shù)據(jù)倉庫的數(shù)據(jù)，構(gòu)建E-R圖，挖掘銷售事實(shí)表與各維度表的關(guān)聯(lián)；
(3)構(gòu)建與銷售相關(guān)的指標(biāo)體系。
3.數(shù)據(jù)分析與初步整理
產(chǎn)品的銷售渠道有兩種，一種是線上銷售（網(wǎng)絡(luò)），銷售數(shù)據(jù)存在factinternetsales事實(shí)表中；另一種是線下銷售（經(jīng)銷商），銷售數(shù)據(jù)存在factresellersales事實(shí)表中。
a. E-R圖
通過E-R圖進(jìn)一步分析事實(shí)表與各維度表之間的關(guān)聯(lián)，比如線上銷售渠道，factinternetsales事實(shí)表中productkey、customerkey、promotionkey、salesterritorykey等字段與維度表有關(guān)聯(lián)。同時(shí)，產(chǎn)品相關(guān)的維度表有三個(gè)，它們之間也存在一定的關(guān)聯(lián)。
factresellersales事實(shí)表與factinternetsales事實(shí)表的區(qū)別在于，線上銷售每一筆訂單都直接面向最終客戶，因此通過customerkey與dimcustomer維度表關(guān)聯(lián)。而線下銷售是通過經(jīng)銷商進(jìn)行售賣，每一筆訂單都有記錄經(jīng)銷商和銷售人員的信息，因此通過resellerkey與dimreseller維度表關(guān)聯(lián)，通過employee與dimemployee關(guān)聯(lián)。
b. 指標(biāo)體系
分析維度：
時(shí)間維度——年、季度、月、周、日
地區(qū)維度——銷售大區(qū)、國家、州/省、城市
產(chǎn)品維度——產(chǎn)品類別、產(chǎn)品子類
推廣維度
客戶維度
經(jīng)銷商維度
員工維度
分析指標(biāo)：
總銷售額
總訂單量
總成本=產(chǎn)品標(biāo)準(zhǔn)成本+稅費(fèi)+運(yùn)費(fèi)
總利潤=總銷售額-總成本
收入利潤率=總利潤/總銷售額
客單價(jià)=總銷售額/客戶總數(shù)
稅費(fèi)
運(yùn)費(fèi)
銷售額、銷量目標(biāo)達(dá)成率
不同維度（時(shí)間、地區(qū)、產(chǎn)品）下的銷售額、訂單量
（2）搭建數(shù)據(jù)倉庫
目的：根據(jù)實(shí)際業(yè)務(wù)需要，對已經(jīng)建立好的基礎(chǔ)層數(shù)據(jù)進(jìn)行加工，并存放到數(shù)據(jù)倉庫匯總層。
數(shù)據(jù)倉庫的設(shè)計(jì)分為兩層，一個(gè)是 ODS 基礎(chǔ)層，一個(gè)是 DW 匯總層 ?；A(chǔ)層用來存放基礎(chǔ)數(shù)據(jù)，即前面使用shell腳本導(dǎo)入的數(shù)據(jù)，而匯總層用來存放我們使用基礎(chǔ)層加工生成的數(shù)據(jù)。
前面已經(jīng)從實(shí)際業(yè)務(wù)出發(fā)，分析了網(wǎng)絡(luò)銷售事實(shí)表（factinternetsales）、經(jīng)銷商銷售事實(shí)表（factresellersales）與各維度表之間的關(guān)聯(lián)，并且羅列出銷售方面的關(guān)鍵分析指標(biāo)。接下來需要建立一個(gè)匯總層，用于存放加工后的維度表以及新建的銷售數(shù)據(jù)匯總表。
這里為什么要對維度表進(jìn)行加工呢？雖然不經(jīng)加工、直接導(dǎo)入tableau也可以，但是數(shù)據(jù)表較多、數(shù)據(jù)量較大，加載速度會很慢。而且字段太多，不是每一個(gè)字段都會用到。所以這里的加工包括兩個(gè)層面，一個(gè)是對相同類型的維度表做連接，減少表的數(shù)量；另一個(gè)是篩選過濾，提取需要分析的關(guān)鍵字段。
另外，這里對網(wǎng)絡(luò)銷售事實(shí)表（factinternetsales）和經(jīng)銷商銷售事實(shí)表（factresellersales）進(jìn)行整合，提取需要分析的字段（銷售額、產(chǎn)品標(biāo)準(zhǔn)成本、運(yùn)費(fèi)、稅費(fèi)等），并且創(chuàng)建新的字段（成本、利潤等），以便全面分析線上和線下的銷售情況。
1.建立數(shù)據(jù)倉庫
新建一個(gè)數(shù)據(jù)庫，用于存放加工生成的數(shù)據(jù)，包括加工后的維度表和事實(shí)表。
2.建立數(shù)據(jù)倉庫維度加工表
a. 連接三個(gè)產(chǎn)品方面的維度表
連接三個(gè)與產(chǎn)品相關(guān)的維度表：產(chǎn)品維度表（dimproduct）、產(chǎn)品子類別維度表（dimproductsubcategory）、產(chǎn)品類別維度表（dimproductcategory）。提取需要使用的字段：產(chǎn)品id、產(chǎn)品名稱、產(chǎn)品類別id、產(chǎn)品類別名稱、產(chǎn)品子類id、產(chǎn)品子類名稱。
b. 連接兩個(gè)區(qū)域方面的維度表
連接兩個(gè)與區(qū)域相關(guān)的維度表：區(qū)域維度表（dimsalesterritory）、地理位置維度表（dimgeography）。提取需要使用的字段：區(qū)域id、銷售大區(qū)、銷售國家、銷售地區(qū)、州/省、地理位置id、城市。
c. 從各維度表提取分析字段
3.建立數(shù)據(jù)倉庫事實(shí)加工表
創(chuàng)建銷售匯總表sales_total_dw，這里使用union all連接網(wǎng)絡(luò)銷售事實(shí)表（factinternetsales）和經(jīng)銷商銷售事實(shí)表（factresellersales），注意union all連接的兩個(gè)表，列名和列數(shù)必須完全一致，否則會報(bào)錯(cuò)。為了區(qū)分每一筆訂單是線上還是線下銷售記錄，新增一個(gè)標(biāo)簽銷售渠道（sales_channel），線上為“internet”，線下為“reseller”。
PS：factinternetsales需要補(bǔ)齊ResellerKey和EmployeeKey字段 （'null' as ResellerKey,'null' as EmployeeKey,）factresellersales需要補(bǔ)齊CustomerKey字段（'null' as CustomerKey）。
三、報(bào)表制作
目的：將匯總層數(shù)據(jù)導(dǎo)入tableau，建立各表之間的關(guān)聯(lián)，并制作銷售報(bào)表。
1、數(shù)據(jù)導(dǎo)入
將tableau連接到hive數(shù)據(jù)庫，將加工后的事實(shí)表和維度表導(dǎo)入。
2、tableau數(shù)據(jù)關(guān)聯(lián)
把事實(shí)表和維度表進(jìn)行關(guān)聯(lián)
3、數(shù)據(jù)清洗
數(shù)據(jù)格式：hive數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入后，可能需要進(jìn)行格式轉(zhuǎn)換。比如文本格式轉(zhuǎn)換為日期格式，文本格式轉(zhuǎn)換為整數(shù)格式，文本格式轉(zhuǎn)換為小數(shù)格式，小數(shù)格式轉(zhuǎn)換為百分比格式。
新建度量值：對于新增的客單價(jià)、收入利潤率等指標(biāo)，可以通過新建度量值的方式進(jìn)行處理。當(dāng)然，最好還是在hive中創(chuàng)建字段，這樣代碼對其他項(xiàng)目做銷售數(shù)據(jù)分析更有借鑒意義。
4、整合制作儀表盤顯示數(shù)據(jù)
