2- 數(shù)據(jù)建模

2.1 維度建模

2.1.1 維度建模的基本概念

2.1.1.1 事實(shí)表 (Fact Table)

• 包含業(yè)務(wù)過(guò)程的度量值
• 通常是數(shù)值型的、可累加的
• 例如: 銷(xiāo)售金額、數(shù)量等

2.1.1.2 維度表 (Dimension Table)

• 包含描述業(yè)務(wù)實(shí)體的屬性
• 通常是文本類(lèi)型的描述性信息
• 例如: 產(chǎn)品、顧客、時(shí)間、地點(diǎn)等

2.1.1.3 維度 (Dimension)

• 提供分析數(shù)據(jù)的角度
• 用于過(guò)濾、分組和標(biāo)記

2.1.1.4 度量 (Measure)

• 事實(shí)表中的數(shù)值型數(shù)據(jù)
• 可以進(jìn)行數(shù)據(jù)運(yùn)算(如: 求和、平均等)

2.1.2 維度建模的主要模型

2.1.2.1 星型模型 (Star Schema)

• 中心是事實(shí)表, 周?chē)蔷S度表
• 維度表直接與事實(shí)表相連
• 優(yōu)點(diǎn): 查詢(xún)性能好, 易于理解
• 缺點(diǎn): 可能存在數(shù)據(jù)冗余

2.1.2.2 雪花模型 (Snowflake Schema)

• 星型模型的變體, 維度表被進(jìn)一步規(guī)范化
• 減少了數(shù)據(jù)冗余, 增加了表的數(shù)量
• 優(yōu)點(diǎn): 節(jié)省存儲(chǔ)空間, 方便維護(hù)
• 缺點(diǎn): 查詢(xún)性能可能下降, 結(jié)構(gòu)復(fù)雜

2.1.2.3 星座模型 (Constellation Schema)

• 多個(gè)實(shí)時(shí)表共享維表
• 用于復(fù)雜的、多主體的數(shù)據(jù)倉(cāng)庫(kù)

2.1.3 維度設(shè)計(jì)技術(shù)

2.1.3.1 緩慢變化維 (Slowly Changing Dimensions, SCD)

• 處理維度屬性隨時(shí)間變化的方法
• 類(lèi)型1: 直接覆蓋舊值
• 類(lèi)型2: 增加新記錄, 保留歷史
• 類(lèi)型3: 增加新屬性列, 保留當(dāng)前和歷史
• 類(lèi)型4: 增加歷史表
• 類(lèi)型5: 結(jié)合1、2、3的混合方法

2.1.3.2 退化維度 (Degenerate Dimension)

• 存儲(chǔ)在事實(shí)表中的維度屬性
• 通常是業(yè)務(wù)過(guò)程產(chǎn)生的標(biāo)識(shí)符
• 例如: 訂單號(hào)、發(fā)票號(hào)等

2.1.3.3 巨型維度 (Junk Dimension)

• 將多個(gè)低基數(shù)的標(biāo)志或?qū)傩越M合成一個(gè)維度
• 減少維度數(shù)量, 簡(jiǎn)化模型

2.1.3.4 角色扮演維度 (Role-Playing Dimension)

• 同一個(gè)維度表在事實(shí)表中扮演多個(gè)角色
• 例如: 日期維度可以是訂單日期、發(fā)貨日期等

2.1.3.5 橋接表 (Bridge Table)

• 用于處理多對(duì)多關(guān)系
• 鏈接事實(shí)表和維度表

2.1.4 維度建模的步驟

1. 選擇業(yè)務(wù)過(guò)程: 確定要建模的具體業(yè)務(wù)活動(dòng)
2. 聲明粒度: 定義事實(shí)表中單條記錄代表的最小細(xì)節(jié)級(jí)別
3. 確定維度: 識(shí)別描述該業(yè)務(wù)過(guò)程的所有可能角度
4. 確定事實(shí): 確定與該業(yè)務(wù)過(guò)程相關(guān)的可度量指標(biāo)
5. 存儲(chǔ)預(yù)計(jì)算結(jié)果: 在適當(dāng)?shù)那闆r下, 預(yù)先計(jì)算并存儲(chǔ)聚合數(shù)據(jù)

2.1.5 維度建模的最佳實(shí)踐

1. 使用代理鍵: 為每個(gè)維度表使用獨(dú)立的整數(shù)型代理鍵
2. 創(chuàng)建一致性維度: 垮多個(gè)事實(shí)表使用相同的維度定義
3. 創(chuàng)建日期維度: 包含豐富的日期相關(guān)屬性, 便于時(shí)間分析
4. 避免過(guò)度規(guī)范化: 在性能和易用性之間找平衡
5. 考慮未來(lái)的擴(kuò)展性: 設(shè)計(jì)時(shí)預(yù)留空間為將來(lái)可能的變化

2.1.6 維度建模的優(yōu)勢(shì)

• 直觀易懂, 便于業(yè)務(wù)用戶(hù)理解
• 查詢(xún)性能優(yōu)秀, 特別是對(duì)于復(fù)雜的分析查詢(xún)
• 靈活性高, 易于應(yīng)對(duì)需求變化
• 支持自助式BI和OLAP分析

2.1.7 維度建模的挑戰(zhàn)

• 可能存在數(shù)據(jù)冗余
• 初次設(shè)計(jì)時(shí)可能難以確定最佳粒度
• 處理快速變化的維度可能比較復(fù)雜
• 需要權(quán)衡存儲(chǔ)空間和查詢(xún)性能

2.2 星型模型

星型模型是一種經(jīng)典的數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)模式, 因其結(jié)構(gòu)類(lèi)似于星星而得名 ; 它由一個(gè)中心事實(shí)表和多個(gè)圍繞它的維度表組成, 并通過(guò)外鍵關(guān)聯(lián) ;

這種簡(jiǎn)單而直觀的結(jié)構(gòu)使其易于理解、查詢(xún)效率高, 被廣泛應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)和商業(yè)智能領(lǐng)域 ;

2.2.1 組成部分

• 事實(shí)表 (Fact Table): 位于星型模型的中心, 存儲(chǔ)關(guān)鍵業(yè)務(wù)指標(biāo)的數(shù)值數(shù)據(jù), 例如銷(xiāo)售額、訂單數(shù)量、網(wǎng)站訪問(wèn)量等 ; 每個(gè)事實(shí)表都與一個(gè)特定的業(yè)務(wù)過(guò)程相關(guān)聯(lián), 例如銷(xiāo)售、訂單、網(wǎng)站訪問(wèn)等 ;
  • 事實(shí)表通常包含大量數(shù)據(jù)行, 因?yàn)樗麄冇涗浟嗣總€(gè)業(yè)務(wù)事件的詳細(xì)信息 ;
  • 事實(shí)表的結(jié)構(gòu)相對(duì)簡(jiǎn)單, 通常包含一下兩種類(lèi)型的列:
    • 度量值 (Measures): 表示業(yè)務(wù)指標(biāo)的數(shù)值數(shù)據(jù), 例如銷(xiāo)售額、數(shù)量、成本、例如等 ;
    • 外鍵 (Foreign Keys): 用于鏈接到維表, 表示與該事實(shí)相關(guān)的維度信息 ;
• 維度表 (Dimension Table): 圍繞在事實(shí)表周?chē)? 提供關(guān)于事實(shí)的上下文, 例如時(shí)間、地點(diǎn)、產(chǎn)品、客戶(hù)等 ; 維度表回答了關(guān)于事實(shí)的 “誰(shuí)、什么、何時(shí)、何地、為什么” 等問(wèn)題 ;
  • 維度表通常包含較少的數(shù)據(jù)行, 因?yàn)樗麄兠枋龅氖菢I(yè)務(wù)體的屬性, 例如產(chǎn)品類(lèi)別、地區(qū)、客戶(hù)類(lèi)型等 ;
  • 維度表的結(jié)構(gòu)可以比較復(fù)雜, 可以包含多個(gè)層次結(jié)構(gòu), 例如時(shí)間維度可以包含年、季度、月、日等層次結(jié)構(gòu) ;
  • 維度表通常包含以下兩種類(lèi)型的列:
    • 主鍵 (Primary Key): 唯一標(biāo)識(shí)維度表中的每一行數(shù)據(jù) ;
    • 屬性 (Attributes): 描述維度信息的文本或數(shù)值數(shù)據(jù), 例如產(chǎn)品名稱(chēng)、類(lèi)別、顏色、尺寸、地區(qū)名稱(chēng)、郵編、客戶(hù)姓名、性別、年齡、地址等 ;

2.2.2 優(yōu)點(diǎn)

• 易于理解: 星型模型的結(jié)構(gòu)簡(jiǎn)單直觀, 及時(shí)是非技術(shù)人員也能輕松理解, 方便業(yè)務(wù)人員理解數(shù)據(jù)結(jié)構(gòu)和進(jìn)行數(shù)據(jù)分析 ;
• 查詢(xún)性能高: 星型模型采用去規(guī)范化的設(shè)計(jì), 將數(shù)據(jù)冗余存儲(chǔ)在維度表中, 避免復(fù)雜的多表連接, 因此查詢(xún)效率非常高 ;
• 易于擴(kuò)展: 可以很容易地添加新的維度表或事實(shí)表, 以適應(yīng)不斷變化的業(yè)務(wù)需求, 例如增加新的產(chǎn)品線、開(kāi)拓新的市場(chǎng)等 ;

2.2.3 應(yīng)用場(chǎng)景

星型模型適用于一下場(chǎng)景:

• 數(shù)據(jù)分析和報(bào)表: 星型模型非常適合用于構(gòu)建報(bào)表和儀表板, 因?yàn)樗梢钥焖俚貙?duì)數(shù)據(jù)進(jìn)行切片、切塊、鉆取等操作 ;
• 商業(yè)智能(BI): 星型模型是許多BI工具的基礎(chǔ), 因?yàn)樗梢蕴峁?duì)數(shù)據(jù)的快速訪問(wèn)和分析 ;
• 數(shù)據(jù)挖掘: 星型模型可以用于構(gòu)建數(shù)據(jù)挖掘模型, 因?yàn)樗梢蕴峁┙Y(jié)構(gòu)化的數(shù)據(jù), 方便算法進(jìn)行訓(xùn)練和預(yù)測(cè) ;

2.2.4 示例

以一個(gè)電商網(wǎng)站的銷(xiāo)售數(shù)據(jù)為例, 我們可以設(shè)計(jì)一下的星型模型:

• 事實(shí)表: 銷(xiāo)售事實(shí)表 (FactSales)
  • 銷(xiāo)售額 (SalesAmount)
  • 成本 (Cost)
  • 利潤(rùn) (Profit)
  • 訂單 ID (外鍵) (OrderID)
  • 產(chǎn)品ID (外鍵) (ProductID)
  • 時(shí)間ID (外鍵) (TimeID)
  • 客戶(hù)ID (外鍵) (CustomerID)
• 維度表:
  • 訂單維度表 (DimOrders): 訂單ID (OrderID), 訂單日期 (OrderDate), 訂單狀態(tài) (OrderStatus)等
  • 產(chǎn)品維度表 (DimProducts): 產(chǎn)品ID (ProductID), 產(chǎn)品名稱(chēng) (ProductName), 產(chǎn)品類(lèi)別 (ProductCategory), 產(chǎn)品價(jià)格 (Price) 等
  • 時(shí)間維度表 (DimTime): 時(shí)間ID(TimeID), 日期 (Date), 星期 (Week), 月份 (Month), 季度 (Quarter), 年份 (Year) 等
  • 客戶(hù)維度表 (DimCustomers): 客戶(hù)ID (CustomerID), 客戶(hù)姓名 (CustomerName), 客戶(hù)性別 (Gender), 年齡 (Age), 客戶(hù)地區(qū) (Region) 等

通過(guò)將這些表連接起來(lái), 我們可以輕松地查詢(xún)各種銷(xiāo)售數(shù)據(jù), 例如:

• 2024 年第一季度華東地區(qū)各個(gè)產(chǎn)品類(lèi)別的銷(xiāo)售額
• 2024 年 3 月份女性客戶(hù)購(gòu)買(mǎi)的各個(gè)產(chǎn)品的銷(xiāo)售數(shù)量
• 不同年齡段客戶(hù)的平均訂單金額

2.2.5 總結(jié)

星型模型是一種簡(jiǎn)單、高效的數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)模型, 使用與各種數(shù)據(jù)分析和商業(yè)智能應(yīng)用 ; 其易于理解、查詢(xún)性能高和易于擴(kuò)展的特性使其成為構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)的首選方案之一 ;

2.3 雪花模型

雪花模型是星型模型的一種擴(kuò)展, 它進(jìn)一步將維度表規(guī)范化, 將具有層次關(guān)系的維度屬性分離到不同的表中, 形成類(lèi)似雪花的分支結(jié)構(gòu) ;

2.3.1 定義

雪花模型是一種數(shù)據(jù)庫(kù)設(shè)計(jì)模型, 其中維度表被進(jìn)一步規(guī)范化, 形成多層結(jié)構(gòu), 看起來(lái)像雪花的形狀 ;

2.3.2 結(jié)構(gòu)組成

• 事實(shí)表
  • 位于模型的中心
  • 包含業(yè)務(wù)度量和指向主要維度表的外鍵
• 主要維度表
  • 直接與事實(shí)表相連
  • 包含部分描述性屬性和指向次級(jí)維度表的外鍵
• 次級(jí)維度表
  • 與主要維度表相連
  • 進(jìn)一步細(xì)分維度屬性

2.3.3 雪花模型的特點(diǎn)

• 高度規(guī)范化: 維度表被分解成多個(gè)相關(guān)的表
• 減少數(shù)據(jù)冗余: 通過(guò)規(guī)范化降低了數(shù)據(jù)重復(fù)
• 層次結(jié)構(gòu)清晰: 明確展示了維度之間的層次關(guān)系
• 靈活性: 可以詳細(xì)描述復(fù)雜的維度關(guān)系

2.3.4 設(shè)計(jì)步驟

• 創(chuàng)建星型模型: 先設(shè)計(jì)基本的星型結(jié)構(gòu)
• 識(shí)別維度層次: 確定維度內(nèi)的層次關(guān)系
• 規(guī)范化維度: 將維度表拆分成多個(gè)相關(guān)表
• 建立關(guān)系: 在拆分后的維度表之間建立關(guān)系

2.3.5 示例 以零售銷(xiāo)售為例:

• 事實(shí)表: 銷(xiāo)售事實(shí)
  • 包含: 日期ID、產(chǎn)品ID、商品ID、銷(xiāo)售額、銷(xiāo)售數(shù)量
• 主要維度表:
  • 產(chǎn)品維度: 產(chǎn)品ID、產(chǎn)品名稱(chēng)、類(lèi)別ID、品牌ID
  • 商店維度: 商店ID、商店名稱(chēng)、城市ID
• 次級(jí)維度表:
  • 產(chǎn)品類(lèi)別: 類(lèi)別ID、類(lèi)別名稱(chēng)
  • 品牌: 品牌ID、品牌名稱(chēng)
  • 城市: 城市ID、城市名稱(chēng)、州ID
  • 州: 州ID、州名稱(chēng)、國(guó)家ID
  • 國(guó)家: 國(guó)家ID、國(guó)家名稱(chēng)

2.3.6 優(yōu)勢(shì)

• 數(shù)據(jù)一致性: 減少數(shù)據(jù)冗余, 提高了一致性
• 節(jié)省存儲(chǔ)空間: 規(guī)范化結(jié)構(gòu)減少了數(shù)據(jù)重復(fù)
• 維護(hù)方便: 更新某些屬性時(shí)只需要再一個(gè)地方修改
• 支持復(fù)雜的維度層次: 可以表示更復(fù)雜的維度關(guān)系

2.3.7 局限性

• 查詢(xún)性能可能下降: 需要更多的表連接操作
• 復(fù)雜度增加: 模型結(jié)構(gòu)比星型模型更復(fù)雜
• 不太直觀: 對(duì)非技術(shù)用戶(hù)來(lái)說(shuō)可能較難理解

2.3.8 與星型模型的對(duì)比

• 雪花模型: 更規(guī)范化, 節(jié)省空間, 但查詢(xún)可能較慢
• 星型模型: 非規(guī)范化, 查詢(xún)性能好, 結(jié)構(gòu)簡(jiǎn)單

2.3.9 實(shí)施考慮

• 性能優(yōu)化: 可能需要更復(fù)雜的索引策略
• 視圖: 考慮使用物化視圖來(lái)提高查詢(xún)性能
• ETL復(fù)雜性: 數(shù)據(jù)加載和轉(zhuǎn)換過(guò)程可能更復(fù)雜

2.3.10 使用場(chǎng)景

• 大型數(shù)據(jù)倉(cāng)庫(kù): 當(dāng)數(shù)據(jù)量巨大, 需要嚴(yán)格控制冗余時(shí)
• 復(fù)雜的維度層次: 當(dāng)維度有多層次結(jié)構(gòu)需要表示時(shí)
• 頻繁更新的維度屬性: 當(dāng)某些維度屬性經(jīng)常變化時(shí)

2.4 事實(shí)表和維度表設(shè)計(jì)

在數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)中, 事實(shí)表和維度表是兩個(gè)最基本的概念, 采用維度模型設(shè)計(jì) ; 事實(shí)表存儲(chǔ)時(shí)間度量值, 維度表描述業(yè)務(wù)實(shí)體 ; 兩者協(xié)同工作, 為分析和報(bào)表提供完整的數(shù)據(jù)視圖 ;

2.4.1 事實(shí)表設(shè)計(jì)

2.4.1.1 確定業(yè)務(wù)過(guò)程和粒度

首先需要明確要分析的業(yè)務(wù)過(guò)程是什么, 例如: 銷(xiāo)售、訂單、網(wǎng)站訪問(wèn)等 ;

然后確定事實(shí)表的粒度, 即每行數(shù)據(jù)代表什么級(jí)別的業(yè)務(wù)事件 ;

• 粒度越細(xì), 數(shù)據(jù)越詳細(xì), 但也意味著數(shù)據(jù)量越大, 查詢(xún)銷(xiāo)量越低 ;
• 粒度越粗, 數(shù)據(jù)越匯總, 數(shù)據(jù)量越小, 但可能會(huì)丟失一些細(xì)節(jié)信息 ;

選擇合適的粒度需要權(quán)衡數(shù)據(jù)量、查詢(xún)性能和業(yè)務(wù)需求 ;

2.4.1.2 確定度量值

度量值是事實(shí)表中最重要的部分, 它們是可以計(jì)量的數(shù)值指標(biāo), 用于描述業(yè)務(wù)過(guò)程的關(guān)鍵特征; 常見(jiàn)的度量值包括:

• 數(shù)量: 例如銷(xiāo)售數(shù)量、訂單數(shù)量、訪問(wèn)次數(shù)等 ;
• 金額: 例如銷(xiāo)售額、成本、利潤(rùn)等 ;
• 時(shí)間: 例如訂單處理時(shí)間、頁(yè)面加載時(shí)間等 ;
• 比率: 例如轉(zhuǎn)化率、點(diǎn)擊率、滿(mǎn)意度等 ;

選擇度量值時(shí)需要考慮業(yè)務(wù)需求和數(shù)據(jù)可獲得性 ;

2.4.1.3 設(shè)計(jì)外鍵

事實(shí)表通過(guò)外鍵與維度表關(guān)聯(lián), 每個(gè)外鍵都對(duì)應(yīng)一個(gè)維度表的主鍵, 表示該事實(shí)發(fā)生在哪個(gè)維度下 ;

• 外鍵數(shù)量: 取決于事實(shí)表需要關(guān)聯(lián)的維度數(shù)量 ;
• 外鍵類(lèi)型: 通常使用整數(shù)類(lèi)型, 以提高查詢(xún)效率 ;

2.4.1.4 其它設(shè)計(jì)考慮

• 添加時(shí)間戳: 記錄每行數(shù)據(jù)的創(chuàng)建時(shí)間和更新時(shí)間, 方便數(shù)據(jù)跟蹤和審計(jì) ;
• 預(yù)留擴(kuò)展字段: 為未來(lái)可能增加的度量值預(yù)留空間, 提高數(shù)據(jù)模型的可擴(kuò)展性 ;

2.4.2 維度表設(shè)計(jì)

2.4.2.1 確定維度

維度是描述業(yè)務(wù)實(shí)體的屬性, 例如時(shí)間、地點(diǎn)、產(chǎn)品、客戶(hù)等; 每個(gè)維度對(duì)應(yīng)一個(gè)維度表, 用于存儲(chǔ)該維度的所有可能取值 ;

2.4.2.2 設(shè)計(jì)屬性

維度表的屬性用于描述維度的特征, 例如:

• 時(shí)間維度: 年、月、日、小時(shí)、分鐘、秒等
• 地點(diǎn)維度: 國(guó)家、省份、城市、地區(qū)等
• 產(chǎn)品維度: 產(chǎn)品類(lèi)型、名稱(chēng)、品牌、型號(hào)、規(guī)格等
• 客戶(hù)維度: 客戶(hù)姓名、性別、年齡、地址、聯(lián)系方式等

選擇屬性時(shí)需要考慮業(yè)務(wù)需求和數(shù)據(jù)可獲得性 ;

2.4.2.3 設(shè)計(jì)層次結(jié)構(gòu)

一些維度可以按照層次結(jié)構(gòu)組織, 例如:

• 時(shí)間維度: 年 > 季度 > 月 > 日 > 小時(shí) > 分鐘 > 秒
• 地點(diǎn)維度: 國(guó)家 > 省份 > 城市 > 區(qū)縣

層次結(jié)構(gòu)可以方便進(jìn)行多層次的鉆取分析, 例如從年度銷(xiāo)售額下鉆到月度銷(xiāo)售額, 再下鉆到每日銷(xiāo)售額 ;

2.4.2.4 其他設(shè)計(jì)考慮

• 使用代理鍵: 使用自增整數(shù)作為維度表的主鍵, 而不是使用業(yè)務(wù)主鍵, 可以提高查詢(xún)效率 ;
• 添加描述性字段: 為每個(gè)屬性添加簡(jiǎn)短的描述信息, 提高數(shù)據(jù)可讀性 ;
• 處理緩慢變化維度: 對(duì)于隨時(shí)間緩慢變化的維度, 例如客戶(hù)地址, 需要采用合適的策略來(lái)處理歷史數(shù)據(jù) ;

2.4.3 總結(jié)

事實(shí)表和維度表的設(shè)計(jì)是數(shù)據(jù)倉(cāng)庫(kù)建設(shè)的基礎(chǔ), 需要根據(jù)具體的業(yè)務(wù)需求和數(shù)據(jù)特點(diǎn)進(jìn)行設(shè)計(jì) ;

一個(gè)良好的數(shù)據(jù)模型可以提高數(shù)據(jù)查詢(xún)效率, 方便進(jìn)行多維分析, 并支持業(yè)務(wù)決策 ;

end