簡(jiǎn)介某零售集團(tuán)商業(yè)智能系統(tǒng)需求分析報(bào)告以Word文檔形式交付面向零售行業(yè)信息化規(guī)劃人員、商業(yè)智能產(chǎn)品經(jīng)理、數(shù)據(jù)倉(cāng)庫(kù)工程師與實(shí)施顧問(wèn)用于在BI二期建設(shè)中理清需求邊界、功能模塊與數(shù)據(jù)流轉(zhuǎn)。報(bào)告系統(tǒng)拆解三大功能日常業(yè)務(wù)報(bào)表支持脫機(jī)/聯(lián)機(jī)查詢(xún)可自定義內(nèi)容、時(shí)間范圍和展示形式業(yè)務(wù)探索式分析基于多維數(shù)據(jù)立方體支持區(qū)域、品類(lèi)、時(shí)間等維度鉆取、切片與切塊KPI指標(biāo)分析圍繞利潤(rùn)率、庫(kù)存周轉(zhuǎn)率等關(guān)鍵指標(biāo)進(jìn)行監(jiān)測(cè)。流程部分描述了從ERP、CRM等系統(tǒng)抽取數(shù)據(jù)經(jīng)清洗整合進(jìn)入數(shù)據(jù)倉(cāng)庫(kù)再由BI平臺(tái)處理并輸出報(bào)表與分析結(jié)果的完整鏈路并細(xì)分日常報(bào)表處理流程與OLAP交互流程。報(bào)告還包含數(shù)據(jù)說(shuō)明、系統(tǒng)運(yùn)行環(huán)境及界面基本形式章節(jié)結(jié)構(gòu)完整可直接作為同類(lèi)零售集團(tuán)BI需求分析模板或評(píng)審依據(jù)。資源為1個(gè)Word文檔壓縮包僅547KB輕量易讀已有88人瀏覽/學(xué)習(xí)適合需要快速搭建商業(yè)智能需求分析框架的讀者。1. 把BI需求分析報(bào)告讀出數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)意圖拿到一份BI需求分析報(bào)告軟件工程里叫SRS多數(shù)團(tuán)隊(duì)的讀法是翻目標(biāo)、看報(bào)表清單、然后交給開(kāi)發(fā)估工時(shí)。但這類(lèi)文檔真正值錢(qián)的部分往往是那些容易被跳過(guò)的章節(jié)數(shù)據(jù)來(lái)源怎么定義、報(bào)表和OLAP的邊界劃在哪、ETL做幾層。某零售集團(tuán)BI二期需求分析報(bào)告的價(jià)值恰恰在這里——它把一期跑通數(shù)據(jù)之后的擴(kuò)展方向收斂成三層功能體系并明確所有分析最終依賴(lài)“信息倉(cāng)庫(kù)ODS OLAP”的兩層存儲(chǔ)結(jié)構(gòu)。數(shù)據(jù)來(lái)源限定為總部主檔庫(kù)和各業(yè)務(wù)系統(tǒng)按日聚合的單品明細(xì)日常報(bào)表拆成定制脫機(jī)報(bào)表與聯(lián)機(jī)報(bào)表查詢(xún)KPI指標(biāo)被刻意限制在5個(gè)以?xún)?nèi)。這些約束不是業(yè)務(wù)側(cè)拍腦袋而是直接決定數(shù)倉(cāng)建模和ETL工作量的關(guān)鍵輸入。這篇解讀按功能體系、數(shù)據(jù)流、模型映射、評(píng)審驗(yàn)收的順序展開(kāi)適合正在準(zhǔn)備BI項(xiàng)目需求文檔或接手類(lèi)似零售數(shù)倉(cāng)項(xiàng)目的同學(xué)參考。2. BI功能三層體系拆解固定報(bào)表、探索式OLAP與KPI指標(biāo)的邊界劃分2.1 為什么把“日常業(yè)務(wù)報(bào)表”拆成脫機(jī)與聯(lián)機(jī)兩條線需求文檔里把日常業(yè)務(wù)報(bào)表分成定制脫機(jī)報(bào)表和聯(lián)機(jī)報(bào)表查詢(xún)這個(gè)拆分容易被誤讀成“一個(gè)導(dǎo)出Excel、一個(gè)在線看”實(shí)際兩者在數(shù)據(jù)時(shí)效和計(jì)算時(shí)點(diǎn)上完全不同。定制脫機(jī)報(bào)表面向的是格式和周期都穩(wěn)定的公共需求。典型的例子是門(mén)店中類(lèi)銷(xiāo)售報(bào)表按日、周、月三種粒度匯總銷(xiāo)售額、數(shù)量、毛利及同環(huán)比。這類(lèi)報(bào)表的特點(diǎn)是查詢(xún)條件相對(duì)固定業(yè)務(wù)部門(mén)不希望每次打開(kāi)都要等待實(shí)時(shí)計(jì)算更傾向于系統(tǒng)在固定時(shí)間點(diǎn)批量生成結(jié)果再以Excel或其他通用格式推送給指定用戶或用戶組。從實(shí)現(xiàn)角度看這對(duì)應(yīng)數(shù)倉(cāng)里的定時(shí)批處理任務(wù)比如每天凌晨計(jì)算前一日數(shù)據(jù)輸出到報(bào)表服務(wù)器目錄。聯(lián)機(jī)報(bào)表查詢(xún)則對(duì)應(yīng)大量、經(jīng)常性的歷史數(shù)據(jù)查詢(xún)需求例如查詢(xún)門(mén)店某大類(lèi)商品在某個(gè)時(shí)間段的每日銷(xiāo)售金額。核心差異是數(shù)據(jù)已經(jīng)被預(yù)先處理并以數(shù)據(jù)表形式存放在信息倉(cāng)庫(kù)中用戶查詢(xún)時(shí)只是讀取匯總結(jié)果而不是觸發(fā)對(duì)明細(xì)數(shù)據(jù)的重算。文檔里特別提到用戶界面可以呈現(xiàn)柱狀圖或曲線圖說(shuō)明這類(lèi)查詢(xún)還要預(yù)置好圖表模型。兩類(lèi)報(bào)表的另一個(gè)區(qū)別在于參數(shù)化程度。脫機(jī)報(bào)表的參數(shù)在生成時(shí)固化比如指定門(mén)店、指定時(shí)段聯(lián)機(jī)查詢(xún)則允許用戶隨時(shí)調(diào)整條件組合。這直接影響數(shù)據(jù)集市的設(shè)計(jì)——脫機(jī)報(bào)表對(duì)應(yīng)的數(shù)據(jù)表可以按固定維度預(yù)聚合聯(lián)機(jī)查詢(xún)則要求多維度的組合覆蓋通常要借助OLAP立方體或列式存儲(chǔ)來(lái)保證響應(yīng)速度。2.2 業(yè)務(wù)探索式分析OLAP承接“報(bào)表裝不下的隨機(jī)問(wèn)題”固定報(bào)表能解決80%的日常查看需求但業(yè)務(wù)分析人員經(jīng)常會(huì)問(wèn)出報(bào)表上沒(méi)有的問(wèn)題比如“華東區(qū)便利店業(yè)態(tài)里哪幾個(gè)供應(yīng)商的庫(kù)存周轉(zhuǎn)在最近兩個(gè)月持續(xù)下降”。這類(lèi)問(wèn)題沒(méi)有預(yù)設(shè)的報(bào)表模板如果每一個(gè)都走開(kāi)發(fā)流程需求排期會(huì)拖垮項(xiàng)目。文檔把這塊定義為業(yè)務(wù)探索式分析即OLAP。核心價(jià)值在于給分析人員一個(gè)從不同角度和因素組合切入業(yè)務(wù)的平臺(tái)支持旋轉(zhuǎn)、切片、鉆取等操作。比如按區(qū)域看銷(xiāo)售不行就切到商品類(lèi)別再看時(shí)間從月度鉆到周、到日或者固定門(mén)店組合、自由切換度量指標(biāo)。OLAP的多維立方體模型讓這些操作都發(fā)生在預(yù)計(jì)算好的聚合數(shù)據(jù)上避免了每次探索都掃描全量明細(xì)。這個(gè)設(shè)計(jì)還隱含了一個(gè)重要判斷一期系統(tǒng)不能滿足這類(lèi)需求最大的障礙不是沒(méi)有前端工具而是“決策數(shù)據(jù)的不一致性”。多個(gè)業(yè)務(wù)系統(tǒng)對(duì)同一信息的數(shù)據(jù)定義不一致相同命名的數(shù)據(jù)可能指代不同的業(yè)務(wù)含義。不從數(shù)據(jù)源頭把這些口徑統(tǒng)一掉OLAP查詢(xún)出來(lái)的數(shù)字沒(méi)人敢信。因此文檔明確要求做OLAP之前必須先做ODS信息倉(cāng)庫(kù)建設(shè)把關(guān)鍵業(yè)務(wù)基礎(chǔ)數(shù)據(jù)做抽取、清洗和整合否則多維分析就是空中樓閣。2.3 KPI指標(biāo)分析報(bào)告限制在5個(gè)以?xún)?nèi)反而更有價(jià)值KPI部分在需求文檔里很容易被寫(xiě)成“我們要一堆指標(biāo)”但這份需求里刻意做了減法——“KPI指標(biāo)個(gè)數(shù)絕對(duì)不能超過(guò)5個(gè)”。理由寫(xiě)得很直接信息過(guò)多不利于決策者的快速消化KPI就失去意義了。這不是保守而是對(duì)KPI定位的準(zhǔn)確理解。KPI是用來(lái)監(jiān)測(cè)管理目標(biāo)的指數(shù)化結(jié)果比如庫(kù)存周轉(zhuǎn)率、毛利率、供應(yīng)商貢獻(xiàn)度它解決的是“現(xiàn)狀好不好”的問(wèn)題而不是“為什么好、為什么不好”。深挖原因交給OLAP和報(bào)表KPI只負(fù)責(zé)把最關(guān)鍵的健康度指標(biāo)呈現(xiàn)給管理層。如果一張KPI看板上堆了20個(gè)指標(biāo)決策者根本分不清優(yōu)先級(jí)。從實(shí)施角度看KPI指標(biāo)分析報(bào)告是報(bào)表和OLAP之上的一層應(yīng)用。它基于信息倉(cāng)庫(kù)的數(shù)據(jù)通過(guò)“分析框架”對(duì)報(bào)表數(shù)據(jù)進(jìn)行計(jì)算處理和統(tǒng)計(jì)建模。文檔里也明確提出這是示范性應(yīng)用目的是為三期建立更系統(tǒng)的KPI指標(biāo)體系做鋪墊。這里透露了一個(gè)務(wù)實(shí)的落地策略KPI體系不要試圖一步到位先選不超過(guò)5個(gè)核心指標(biāo)跑通全鏈路驗(yàn)證數(shù)據(jù)質(zhì)量和計(jì)算邏輯再逐步擴(kuò)展。下面把三類(lèi)功能模塊的定位整理成表格方便后續(xù)設(shè)計(jì)評(píng)審時(shí)對(duì)齊。功能模塊解決什么問(wèn)題主要輸出實(shí)現(xiàn)方式日常業(yè)務(wù)報(bào)表定制脫機(jī)固定周期、固定格式的經(jīng)營(yíng)數(shù)據(jù)分發(fā)Excel或通用格式報(bào)表定時(shí)批處理預(yù)計(jì)算結(jié)果后分發(fā)日常業(yè)務(wù)報(bào)表聯(lián)機(jī)查詢(xún)經(jīng)常性、條件組合相對(duì)固定的歷史查詢(xún)查詢(xún)結(jié)果表、圖表歷史數(shù)據(jù)預(yù)聚合到信息倉(cāng)庫(kù)業(yè)務(wù)探索式分析OLAP多角度、多因素組合的臨時(shí)探索問(wèn)題多維查詢(xún)立方體及圖表基于數(shù)據(jù)集市構(gòu)建Cube支持鉆取、切片、旋轉(zhuǎn)KPI指標(biāo)分析報(bào)告管理目標(biāo)指數(shù)化、關(guān)鍵因素影響量化KPI分析報(bào)告基于分析框架計(jì)算指標(biāo)數(shù)量控制在5個(gè)以?xún)?nèi)2.4 三層結(jié)構(gòu)對(duì)前端選型和數(shù)據(jù)模型的影響把這三層功能放在一起看可以得出一個(gè)通用結(jié)論不論前端最終選擇Power BI、帆軟FineBI還是自研報(bào)表平臺(tái)后端的數(shù)據(jù)準(zhǔn)備方式都是由功能形態(tài)決定的。脫機(jī)報(bào)表需要的是“模塊化、可定時(shí)執(zhí)行的數(shù)據(jù)集”聯(lián)機(jī)查詢(xún)需要的是“查詢(xún)效率高的預(yù)聚合表”O(jiān)LAP需要的是“多維立方體或兼容MDX查詢(xún)的模型”KPI則依賴(lài)一個(gè)統(tǒng)一的口徑計(jì)算層。這意味著數(shù)倉(cāng)建模不能只做一套明細(xì)層就交付而是要把公共明細(xì)層對(duì)應(yīng)ODS、主題匯總層對(duì)應(yīng)OLAP庫(kù)、應(yīng)用層報(bào)表數(shù)據(jù)集和KPI指標(biāo)從物理或邏輯上拆開(kāi)。文檔里“兩次ETL”的提法已經(jīng)把這個(gè)分層結(jié)構(gòu)寫(xiě)清楚了第一次從業(yè)務(wù)系統(tǒng)到ODS第二次從ODS到OLAP庫(kù)。3. 雙ETL數(shù)據(jù)流設(shè)計(jì)從總部主檔與業(yè)務(wù)庫(kù)到ODS、再到OLAP的落地細(xì)節(jié)3.1 先分清兩類(lèi)數(shù)據(jù)源主檔庫(kù)與業(yè)務(wù)庫(kù)需求文檔對(duì)數(shù)據(jù)來(lái)源的說(shuō)明非常關(guān)鍵原文提到“系統(tǒng)數(shù)據(jù)來(lái)源總體上可以分為某零售集團(tuán)總部主檔數(shù)據(jù)庫(kù)和A業(yè)務(wù)系統(tǒng)等外部業(yè)務(wù)數(shù)據(jù)源”。這兩類(lèi)來(lái)源的性質(zhì)完全不同處理方式也必須分開(kāi)?？偛恐鳈n庫(kù)保存的是企業(yè)基礎(chǔ)信息供應(yīng)商主檔、商品主檔、門(mén)店主檔、公司組織機(jī)構(gòu)、業(yè)務(wù)人員主檔。主檔數(shù)據(jù)的特點(diǎn)是變化頻率低、業(yè)務(wù)含義穩(wěn)定通常作為維度表的來(lái)源。商品主檔會(huì)包含商品編碼、品類(lèi)層級(jí)、品牌、規(guī)格等屬性門(mén)店主檔則包含區(qū)域、業(yè)態(tài)、面積等維度屬性。業(yè)務(wù)數(shù)據(jù)源提供各業(yè)態(tài)、各銷(xiāo)售單位前一天的按單品聚合的明細(xì)業(yè)務(wù)數(shù)據(jù)包括銷(xiāo)售、退貨等。注意這里的關(guān)鍵詞是“前一天的按單品聚合”說(shuō)明源系統(tǒng)已經(jīng)做了最粗粒度的匯總數(shù)倉(cāng)拿到的是每日單品級(jí)事實(shí)數(shù)據(jù)而不是交易流水。按單品聚合對(duì)零售場(chǎng)景是合理的——絕大多數(shù)分析需求到單品粒度就足夠不需要逐個(gè)交易明細(xì)。3.2 第一次ETL把雜亂的業(yè)務(wù)數(shù)據(jù)清洗進(jìn)ODS第一次ETL的目標(biāo)是把總部主檔庫(kù)和業(yè)務(wù)系統(tǒng)的數(shù)據(jù)抽取、轉(zhuǎn)換、加載到ODS庫(kù)。ODS層做的事情可以概括為四件事字段格式統(tǒng)一、編碼規(guī)則轉(zhuǎn)換、臟數(shù)據(jù)清洗、衍生變量生成。格式統(tǒng)一最常見(jiàn)的場(chǎng)景是日期、金額、數(shù)量在不同系統(tǒng)中的存儲(chǔ)格式不一致。A系統(tǒng)日期用yyyyMMddB系統(tǒng)用yyyy-MM-dd金額有的含稅、有的不含稅數(shù)量有的按銷(xiāo)售單位、有的按庫(kù)存單位。這些差異必須在ODS層徹底解決。編碼轉(zhuǎn)換發(fā)生在業(yè)務(wù)系統(tǒng)的內(nèi)部編碼和統(tǒng)一編碼不一致時(shí)。比如門(mén)店編碼總部主檔里一套編碼業(yè)務(wù)系統(tǒng)里另一套ETL時(shí)需要通過(guò)映射關(guān)系完成轉(zhuǎn)換。3.2.1 一個(gè)可運(yùn)行的增量ETL腳本骨架# daily_incremental_etl.py import logging from datetime import date, timedelta import pandas as pd def extract_business_data(table, biz_date): 從業(yè)務(wù)系統(tǒng)按業(yè)務(wù)日期抽取增量數(shù)據(jù) 零售場(chǎng)景下通常是前一天的單品聚合銷(xiāo)售數(shù)據(jù) sql f SELECT store_code, sku_code, sale_qty, sale_amt, biz_date FROM {table} WHERE biz_date {biz_date} # 實(shí)際項(xiàng)目中從業(yè)務(wù)庫(kù)連接讀取 df pd.read_sql(sql, business_db_conn) logging.info(extracted %s rows from %s, len(df), table) return df def transform_ods(df, master_data): 清洗和轉(zhuǎn)換 1. 門(mén)店編碼替換為統(tǒng)一編碼 2. 金額統(tǒng)一轉(zhuǎn)換為不含稅金額 3. 過(guò)濾異常記錄 df df[df[sale_qty] 0] df df[df[sku_code].isin(master_data[sku_list])] df[store_id] df[store_code].map(master_data[store_mapping]) df[sale_amt_net] df[sale_amt] / 1.13 # 默認(rèn)13%增值稅具體稅率以業(yè)務(wù)規(guī)則庫(kù)為準(zhǔn) df[etl_date] date.today() return df def load_to_ods(df, ods_table): 追加寫(xiě)入ODS表重復(fù)執(zhí)行不產(chǎn)生臟數(shù)據(jù) append_sql fINSERT INTO {ods_table} VALUES (%s, %s, %s, ...) # 實(shí)際使用批量寫(xiě)入單條insert性能太差 logging.info(loaded %s rows to %s, len(df), ods_table) if __name__ __main__: biz_date (date.today() - timedelta(days1)).strftime(%Y-%m-%d) raw extract_business_data(sale_daily_agg, biz_date) master load_master_data_from_ods() cleaned transform_ods(raw, master) load_to_ods(cleaned, ods_sale_daily)這個(gè)腳本演示的是最樸素的增量ETL寫(xiě)法有幾個(gè)細(xì)節(jié)值得注意業(yè)務(wù)日期顯式傳入而不是依賴(lài)系統(tǒng)時(shí)間是為了支持補(bǔ)數(shù)過(guò)濾邏輯放在編碼轉(zhuǎn)換之前減少無(wú)效維度查找開(kāi)銷(xiāo)sale_amt_net的稅率計(jì)算只是示意真實(shí)項(xiàng)目中稅率來(lái)源應(yīng)該是業(yè)務(wù)規(guī)則庫(kù)而不是硬編碼這一點(diǎn)在3.3節(jié)展開(kāi)。3.3 業(yè)務(wù)規(guī)則庫(kù)與關(guān)聯(lián)關(guān)系庫(kù)分析口徑的固化需求文檔里提到系統(tǒng)會(huì)收集業(yè)務(wù)部門(mén)提供的業(yè)務(wù)規(guī)則形成“業(yè)務(wù)規(guī)則庫(kù)”并對(duì)其進(jìn)行人工關(guān)聯(lián)關(guān)系分析后生成“關(guān)聯(lián)關(guān)系庫(kù)”。這兩部分是零售BI里最容易忽略、但實(shí)際決定報(bào)表可信度的內(nèi)容。業(yè)務(wù)規(guī)則庫(kù)存儲(chǔ)的是計(jì)算公式和合并抵消原則。例如零售行業(yè)常見(jiàn)的返利計(jì)算規(guī)則——供應(yīng)商按銷(xiāo)售額的一定比例給門(mén)店返利具體比例可能隨品類(lèi)和銷(xiāo)售額檔位變化。這類(lèi)規(guī)則必須由業(yè)務(wù)部門(mén)確認(rèn)后固化成可執(zhí)行配置而不是寫(xiě)在SQL注釋里。一個(gè)規(guī)則庫(kù)的典型結(jié)構(gòu)包括規(guī)則編碼、規(guī)則名稱(chēng)、計(jì)算公式、生效時(shí)間、適用業(yè)態(tài)、狀態(tài)。關(guān)聯(lián)關(guān)系庫(kù)更偏分析模型層面用于描述外部因素與業(yè)務(wù)指標(biāo)的關(guān)聯(lián)關(guān)系比如天氣與飲料品類(lèi)銷(xiāo)售的關(guān)聯(lián)、節(jié)假日與客流量的關(guān)聯(lián)。需求文檔里提到“包括相關(guān)性的判定以及關(guān)聯(lián)函數(shù)形式”說(shuō)明這部分已經(jīng)超出了簡(jiǎn)單報(bào)表的范疇是為后續(xù)的數(shù)據(jù)挖掘做鋪墊。在實(shí)際項(xiàng)目中我的建議是不在二期把這層做重先只沉淀一份“影響因素清單”和對(duì)應(yīng)的基礎(chǔ)分析框架真正建模放到三期。3.4 第二次ETL面向主題構(gòu)建OLAP庫(kù)ODS層解決了數(shù)據(jù)一致性問(wèn)題但它仍然是關(guān)系型表結(jié)構(gòu)距離OLAP需要的多維模型還有距離。第二次ETL從ODS中提取數(shù)據(jù)按照分析主題重新組織生成數(shù)據(jù)集市和Cube。需求文檔明確提到這個(gè)轉(zhuǎn)換過(guò)程包括“生成新的衍生變量”比如計(jì)算同比環(huán)比、累計(jì)值、排名、占比等。分層存儲(chǔ)內(nèi)容典型表/模型更新頻率ODS按單品聚合的清洗后明細(xì)ods_sale_daily每日增量數(shù)據(jù)集市面向主題的匯總事實(shí)表dm_store_cat_sale_daily每日增量OLAP Cube多維預(yù)聚合按門(mén)店、品類(lèi)、時(shí)間等維度構(gòu)建每日刷新數(shù)據(jù)集市層在零售數(shù)倉(cāng)里通常按主題拆分銷(xiāo)售主題、庫(kù)存主題、供應(yīng)商主題、合同主題等。每個(gè)主題對(duì)應(yīng)一組事實(shí)表和維度表例如銷(xiāo)售數(shù)據(jù)集市的事實(shí)表包含門(mén)店維度、商品維度、日期維度以及銷(xiāo)售額、銷(xiāo)售數(shù)量、毛利等多個(gè)度量。第二次ETL的工作量往往被低估。需求文檔特別強(qiáng)調(diào)“對(duì)整個(gè)BI系統(tǒng)而言共有兩次ETL過(guò)程在工作量上要予以充分評(píng)估”這一點(diǎn)在實(shí)際項(xiàng)目中非常真實(shí)。第一次ETL解決的是單日增量數(shù)據(jù)的清洗入庫(kù)邏輯相對(duì)固定第二次ETL因?yàn)橐幚須v史數(shù)據(jù)重建、維度緩慢變化、Cube預(yù)計(jì)算調(diào)優(yōu)空間和執(zhí)行復(fù)雜度都要高出不少。4. 從需求文到維度模型事實(shí)表、維表與指標(biāo)口徑映射4.1 用“中類(lèi)銷(xiāo)售報(bào)表”練習(xí)一次星型建模需求文檔里給了一個(gè)可以當(dāng)練手素材的例子“所選門(mén)店各中類(lèi)的銷(xiāo)售額、數(shù)量、毛利及同環(huán)比日?qǐng)?bào)、周報(bào)、月報(bào)”。這句話翻譯到數(shù)倉(cāng)模型里就是典型的星型模式一張事實(shí)表掛多張維度表。事實(shí)表應(yīng)該記錄的粒度量是“門(mén)店 × 中類(lèi) × 日期 一行”度量字段包括銷(xiāo)售額、銷(xiāo)售數(shù)量、毛利額。為什么粒度選到中類(lèi)而不是單品因?yàn)閳?bào)表本身只到中類(lèi)如果事實(shí)表粒度比需求更細(xì)比如到單品會(huì)浪費(fèi)存儲(chǔ)和計(jì)算資源如果更粗比如到大類(lèi)就沒(méi)辦法支持用戶想看中類(lèi)的需求。門(mén)店維度表屬性包括門(mén)店編碼、門(mén)店名稱(chēng)、區(qū)域、業(yè)態(tài)、開(kāi)業(yè)日期。日期維度表在零售BI里不能只存日期本身還需要年、季、月、周、是否節(jié)假日、是否促銷(xiāo)日等屬性否則同環(huán)比和節(jié)假日分析都要臨時(shí)處理。類(lèi)別維度表在這里是商品維度的簡(jiǎn)化版本只需要中類(lèi)編碼、中類(lèi)名稱(chēng)以及上一級(jí)大類(lèi)的歸屬關(guān)系。4.2 事實(shí)表與維表的SQL落地4.2.1 銷(xiāo)售事實(shí)表DDL-- 銷(xiāo)售事實(shí)表粒度 門(mén)店 中類(lèi) 日期 CREATE TABLE dm_sale_store_cat_daily ( store_id INT NOT NULL COMMENT 門(mén)店維度ID, cat_id INT NOT NULL COMMENT 中類(lèi)維度ID, date_id INT NOT NULL COMMENT 日期維度ID格式y(tǒng)yyyMMdd, sale_qty DECIMAL(18,2) NOT NULL COMMENT 銷(xiāo)售數(shù)量按中類(lèi)匯總, sale_amt DECIMAL(18,2) NOT NULL COMMENT 銷(xiāo)售額不含稅, gross_profit DECIMAL(18,2) NOT NULL COMMENT 毛利額, etl_time DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (store_id, cat_id, date_id), KEY idx_date_cat (date_id, cat_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;事實(shí)表建模有幾個(gè)關(guān)鍵決策點(diǎn)。主鍵用三張維表的ID組合而不是直接用業(yè)務(wù)編碼因?yàn)榫S表ID更穩(wěn)定即使業(yè)務(wù)編碼變化也不影響事實(shí)表。金額字段統(tǒng)一為DECIMAL而不是FLOAT零售金額計(jì)算必須避免浮點(diǎn)誤差。idx_date_cat這個(gè)聯(lián)合索引服務(wù)于頻繁的按日期和品類(lèi)過(guò)濾查詢(xún)是實(shí)際業(yè)務(wù)中最高頻的查詢(xún)路徑。4.2.2 維度表公共設(shè)計(jì)-- 門(mén)店維度表SCD2方式處理區(qū)域調(diào)整 CREATE TABLE dim_store ( store_id INT PRIMARY KEY, store_code VARCHAR(32) NOT NULL COMMENT 門(mén)店業(yè)務(wù)編碼, store_name VARCHAR(128) NOT NULL, region VARCHAR(64) COMMENT 所屬區(qū)域, format_type VARCHAR(32) COMMENT 業(yè)態(tài)超市/賣(mài)場(chǎng)/便利店, open_date DATE, eff_date DATE COMMENT 生效日期, exp_date DATE COMMENT 失效日期默認(rèn)9999-12-31, is_current TINYINT DEFAULT 1 );零售集團(tuán)的區(qū)域組織和業(yè)態(tài)歸屬可能會(huì)調(diào)整門(mén)店從A區(qū)域劃到B區(qū)域后如果直接更新維度表記錄歷史報(bào)表中的區(qū)域統(tǒng)計(jì)就會(huì)錯(cuò)亂。用SCD2策略保留歷史版本查詢(xún)時(shí)通過(guò)eff_date 業(yè)務(wù)日期 AND exp_date 業(yè)務(wù)日期關(guān)聯(lián)才能在區(qū)域變更后仍然回看正確的歷史歸屬。4.3 同環(huán)比指標(biāo)在SQL中的實(shí)現(xiàn)需求文檔反復(fù)提到同環(huán)比這是一個(gè)要看懂就實(shí)現(xiàn)的常見(jiàn)指標(biāo)但口徑必須統(tǒng)一。同環(huán)比通常指“與上一統(tǒng)計(jì)周期比”和“與去年同期比”日環(huán)比就是對(duì)比前一日日同比就是對(duì)比去年同一天。麻煩的是節(jié)假日和促銷(xiāo)日并不對(duì)齊直接日期減1天或減1年會(huì)對(duì)出異常值。-- 日?qǐng)?bào)表同環(huán)比計(jì)算示例 SELECT s.date_id, d.year, d.month, d.day, s.sale_amt AS cur_sale_amt, LAG(s.sale_amt, 1) OVER (PARTITION BY s.store_id, s.cat_id ORDER BY s.date_id) AS prev_day_amt, SUM(s.sale_amt) OVER (PARTITION BY s.store_id, s.cat_id, d.year ORDER BY s.date_id) AS ytd_amt FROM dm_sale_store_cat_daily s JOIN dim_date d ON s.date_id d.date_id WHERE s.store_id 1001 AND s.cat_id 2003 AND s.date_id 20240601;上例用的是窗口函數(shù)實(shí)現(xiàn)前一日金額LAG的PARTITION BY必須以門(mén)店和類(lèi)別的組合為單位否則跨門(mén)店錯(cuò)位。ytd_amt按年度累計(jì)用于年報(bào)中的累計(jì)對(duì)比。如果需求是“七日平均”“本月至今”這類(lèi)更靈活的口徑在Cube里預(yù)計(jì)算比在SQL里現(xiàn)算更高效這也是OLAP庫(kù)存在的原因之一。4.4 指標(biāo)口徑字典讓每個(gè)數(shù)字說(shuō)得清來(lái)源零售數(shù)倉(cāng)項(xiàng)目中“同一個(gè)指標(biāo)、不同人算出來(lái)數(shù)字不一樣”是引發(fā)信任危機(jī)的最常見(jiàn)原因。比如銷(xiāo)售額按訂單實(shí)付算、按商品原價(jià)算、含稅不算稅、退貨沖減不沖減結(jié)果差很多。需求文檔里強(qiáng)調(diào)多個(gè)業(yè)務(wù)系統(tǒng)對(duì)同一信息的定義不一致這套問(wèn)題必須在指標(biāo)字典層面解決。指標(biāo)名稱(chēng)口徑定義計(jì)算時(shí)點(diǎn)來(lái)源表銷(xiāo)售額不含稅銷(xiāo)售訂單金額減去退貨金額剔除增值稅每日業(yè)務(wù)結(jié)束時(shí)點(diǎn)ods_sale_daily毛利額銷(xiāo)售額不含稅減去商品進(jìn)貨成本每日業(yè)務(wù)結(jié)束時(shí)點(diǎn)ods_sale_daily join ods_goods_cost庫(kù)存周轉(zhuǎn)天數(shù)期末庫(kù)存金額 / 近30日平均銷(xiāo)售成本每月末ods_stock_daily供應(yīng)商貢獻(xiàn)度該供應(yīng)商商品銷(xiāo)售額 / 總銷(xiāo)售額每周末dm_supplier_sale_weekly指標(biāo)字典是需求文檔與數(shù)據(jù)模型之間的橋梁。在需求評(píng)審階段數(shù)據(jù)產(chǎn)品經(jīng)理應(yīng)該和業(yè)務(wù)部門(mén)逐項(xiàng)確認(rèn)口徑定義確認(rèn)后寫(xiě)入指標(biāo)字典開(kāi)發(fā)人員嚴(yán)格按照字典建模型。這一份字典也可以直接作為測(cè)試階段的驗(yàn)收依據(jù)——測(cè)試人員按字典口徑手工核算樣本數(shù)據(jù)與系統(tǒng)輸出對(duì)比。5. 需求評(píng)審里能直接用上的驗(yàn)證與驗(yàn)收動(dòng)作5.1 用對(duì)賬SQL做“數(shù)出一致”需求評(píng)審到設(shè)計(jì)定稿最怕的是數(shù)據(jù)流“看起來(lái)通、實(shí)際對(duì)不上”。評(píng)審時(shí)可以直接要求項(xiàng)目組拿出一套跨系統(tǒng)對(duì)賬SQL驗(yàn)證從業(yè)務(wù)源到ODS、再到數(shù)據(jù)集市的每個(gè)環(huán)節(jié)數(shù)據(jù)量守恒。-- 對(duì)賬驗(yàn)證ODS日匯總 vs 源系統(tǒng)日匯總 SELECT COALESCE(ods.biz_date, src.biz_date) AS biz_date, COALESCE(ods.total_amt, 0) AS ods_amount, COALESCE(src.total_amt, 0) AS src_amount, COALESCE(ods.total_amt, 0) - COALESCE(src.total_amt, 0) AS diff_amt FROM ( SELECT biz_date, SUM(sale_amt_net) AS total_amt FROM ods_sale_daily WHERE biz_date 2024-06-01 GROUP BY biz_date ) ods FULL JOIN ( SELECT biz_date, SUM(net_sale_amt) AS total_amt FROM business_sale_daily WHERE biz_date 2024-06-01 GROUP BY biz_date ) src ON ods.biz_date src.biz_date;差值為0只能證明傳輸沒(méi)丟不能證明口徑正確。評(píng)審時(shí)要進(jìn)一步驗(yàn)證金額口徑——如果源系統(tǒng)的net_sale_amt已經(jīng)是不含稅金額而ODS層的轉(zhuǎn)換邏輯里又一次做了稅率換算就會(huì)對(duì)不上。5.2 KPI指標(biāo)的復(fù)算驗(yàn)證KPI限定的5個(gè)指標(biāo)每個(gè)都要能手工復(fù)算。找一個(gè)第三方工具比如Excel按指標(biāo)字典的公式取同一批源數(shù)據(jù)手工算一遍與系統(tǒng)KPI輸出對(duì)比。誤差出現(xiàn)在小數(shù)點(diǎn)后兩位以上就算失敗需要回溯計(jì)算鏈路。特別要檢查的是指標(biāo)在跨年、跨月邊界時(shí)的計(jì)算邏輯很多數(shù)倉(cāng)項(xiàng)目會(huì)在“去年累計(jì)”這類(lèi)時(shí)間口徑上栽跟頭。5.3 驗(yàn)收用例落到文檔需求文檔的評(píng)審?fù)ㄟ^(guò)不代表開(kāi)發(fā)可以放飛。建議把每類(lèi)功能對(duì)應(yīng)的驗(yàn)收標(biāo)準(zhǔn)直接寫(xiě)進(jìn)需求文檔附件的用例表讓測(cè)試階段有據(jù)可依。驗(yàn)收項(xiàng)前置條件操作步驟通過(guò)標(biāo)準(zhǔn)中類(lèi)銷(xiāo)售日?qǐng)?bào)已生成前一日ODS數(shù)據(jù)選擇門(mén)店、日期生成日?qǐng)?bào)報(bào)表數(shù)據(jù)與源系統(tǒng)手工匯總一致OLAP鉆取Cube已刷新從大類(lèi)鉆取到中類(lèi)各層級(jí)合計(jì)值逐級(jí)吻合KPI庫(kù)存周轉(zhuǎn)率月末數(shù)據(jù)完整查看KPI看板手工復(fù)算誤差小于0.01驗(yàn)收項(xiàng)前置條件操作步驟通過(guò)標(biāo)準(zhǔn)聯(lián)機(jī)銷(xiāo)售連續(xù)性查詢(xún)2個(gè)月歷史數(shù)據(jù)已加載查詢(xún)某大類(lèi)60日每日銷(xiāo)售首尾日期完整無(wú)斷點(diǎn)無(wú)跳變報(bào)表定時(shí)分發(fā)ETL任務(wù)調(diào)度已配置等待每日自動(dòng)發(fā)送指定用戶按時(shí)收到可打開(kāi)的文件本文還有配套的精品資源點(diǎn)擊獲取